MySQL 深分页性能优化实战：LIMIT 深翻页的坑与游标分页、延迟关联方案

分类：数据库与中间件 · 作者：管理员 · 发布于 2026-10-09 · 阅读 0 · 标签：MySQL / 分页优化 / 索引 / 游标分页 / 性能调优

一、痛点：为什么 LIMIT 100000, 20 慢得离谱

场景：电商中台订单查询页，用户翻到第 5000 页时，接口响应时间从 50ms 飙到 3s，数据库 CPU 被打满。典型 SQL 长这样：

SELECT * FROM t_order
WHERE user_id = 10086
ORDER BY create_time DESC
LIMIT 100000, 20;

用 EXPLAIN 看执行计划：type=ALL 或 type=index，rows≈100020，Extra 里经常带着 Using filesort。

为什么深翻页这么慢？LIMIT offset, size 的语义是先扫描 offset+size 行，再丢弃前 offset 行。每翻一页 offset 翻倍增长，扫描行数线性膨胀；如果 SELECT * 还要回表，回表次数等于 offset+size；再叠加排序字段没有索引导致的 filesort，三层叠加就是灾难。

二、方案一：延迟关联（先取主键再回表）

核心思路：子查询只查主键列，走覆盖索引拿到目标行的 id，外层再按 id 回表取完整数据，把回表次数从 offset+size 降为 size。

SELECT o.*
FROM t_order o
INNER JOIN (
    SELECT id
    FROM t_order
    WHERE user_id = 10086
    ORDER BY create_time DESC, id DESC
    LIMIT 100000, 20
) tmp ON o.id = tmp.id;

关键点：

子查询只查 id，可用 (user_id, create_time, id) 联合索引覆盖索引完成，不需要回表；
外层回表次数 = 20 次，而不是 100020 次；
排序字段加 id DESC 兜底，解决 create_time 相同时排序不稳定问题。

适用：必须保留页码跳转、但 offset 较深（万级）的场景，MySQL 5.7+ 均可直接使用。

三、方案二：游标分页（keyset / seek pagination）

思路完全不同：不用 offset，改用「上一页最后一条记录的排序位置」作为游标，每次查询都是等价的索引范围扫描，翻 10 万页和翻第 1 页一样快。

SELECT id, order_no, user_id, amount, create_time
FROM t_order
WHERE user_id = 10086
  AND (create_time, id) < (:lastCreateTime, :lastId)
ORDER BY create_time DESC, id DESC
LIMIT 20;

Java 侧配合：

public PageResult<OrderVO> pageOrders(Long userId, String lastCreateTime,
                                      Long lastId, int size) {
    // 1. 游标存在则带上，首屏为空
    List<Order> list = orderMapper.pageByCursor(userId, lastCreateTime, lastId, size + 1);
    // 2. 多取一条判断是否还有下一页
    boolean hasMore = list.size() > size;
    if (hasMore) {
        list = list.subList(0, size);
    }
    // 3. 用最后一条记录构造下一页游标
    String nextCursor = null;
    if (hasMore && !list.isEmpty()) {
        Order last = list.get(list.size() - 1);
        nextCursor = last.getCreateTime().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
                + "_" + last.getId();
    }
    return new PageResult<>(list, nextCursor);
}

Mapper XML（注意 < 在 XML 中必须转义为 <）：

<select id="pageByCursor" resultType="Order">
    SELECT id, order_no, user_id, amount, create_time
    FROM t_order
    WHERE user_id = #{userId}
    <if test="lastCreateTime != null">
        AND (create_time, id) < (#{lastCreateTime}, #{lastId})
    </if>
    ORDER BY create_time DESC, id DESC
    LIMIT #{size}
</select>

优点：

稳定走 (user_id, create_time, id) 索引，每次只扫 size 行，时间复杂度 O(1)；
天然规避「翻页期间新增数据导致下一页元素重复」的问题；
MySQL 5.7 起支持行构造器元组比较 (a, b) < (x, y)，两个排序键一次比较搞定。

代价与限制：

不能随便跳页，只能上一页/下一页，适合 App「加载更多」、订单流水、消息列表；
排序字段必须是唯一可比较的，日期类字段必须加 id 兜底；
游标字段类型要一致，datetime 与字符串混用会导致比较错乱。

四、治本：覆盖索引与排序字段进索引

无论用哪种方案，排序字段进索引是底线：联合索引 (user_id, create_time, id) 让 ORDER BY create_time DESC, id DESC 直接走索引顺序，彻底消除 filesort。同时：

分页查询别用 SELECT *，只查需要的列，必要时让查询完全落在覆盖索引内；
为高频分页查询单独设计索引，不要复用业务主索引硬扛；
上线前用 EXPLAIN 验证 Extra 列不再出现 Using filesort / Using temporary。

五、踩坑记录

排序不稳定：只 ORDER BY create_time，同一秒内订单顺序随机，翻页出现重复/遗漏——加 id DESC 兜底后解决。
行构造器类型坑：(create_time, id) < (?, ?) 两边类型必须一致，传 String 与列类型 datetime 比较时 MySQL 会做隐式转换，容易走错索引。
游标分页与并发写入：新插入的数据排在游标之前，下一页不会重复，但实时性要求高的列表可能「看不全最新数据」，需评估业务可接受度。
延迟关联的失效场景：子查询 where 条件太宽（如只按 status 过滤、无联合索引前缀列）时覆盖索引失效，照样全表扫——务必先 EXPLAIN。
深翻页 + filesort 是双重灾难：曾遇到 ORDER BY amount（无索引）且 offset 上万，单条 SQL 直接 5s+，补索引后降到 30ms——先看 Extra，再谈优化。

六、最佳实践总结

C 端列表优先「加载更多 + 游标分页」，放弃页码跳转，收益最大、实现最稳；
B 端表格必须页码跳转时，限制最大翻页深度（如前 100 页），超出提示用筛选条件缩小范围，而不是无限深翻页；
所有分页查询统一规范：排序字段进索引、id 兜底、不 select *、游标类型一致；
性能验证别拿一万行的表推断千万行的表现——用接近生产的数据量做压测。