一条 LIMIT 1000000, 10 的 SQL 能把一个正常的数据库实例拖到告警，而同样的查询翻到第 1 页只要几毫秒。本文从 MySQL 执行机制讲清深分页慢在哪里，再依次给出游标分页、子查询、延迟关联、覆盖索引和搜索引擎 search_after 五种解法，最后落到业务层的规避手段。

它解决什么问题

先说清楚什么叫深度分页：不是结果集大，而是偏移量（OFFSET）大。典型写法是：

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10;

直觉上这条 SQL 只取 10 行，应该很快。实际执行时它可能扫描上百万行、耗时几秒甚至几十秒。问题是 MySQL 根本没有”直接跳到第 100 万行”的能力，想要理解优化方案，先得理解这笔成本花在了哪里。

深分页为什么慢

LIMIT offset, N 在 InnoDB 上的执行过程可以拆成三步：

  1. 扫描：按排序键（这里是主键 id）从第一条开始逐条遍历，直到凑够 offset + N 条记录。MySQL 没有随机定位到第 100 万条的手段，只能一条条数过去。
  2. 排序：如果 ORDER BY 的字段没有合适的索引，这 offset + N 条记录还要先走一次 filesort，把前 100 多万条全部排好序，成本进一步放大。
  3. 丢弃：排完序、数够数之后，前 1000000 条全部扔掉，只返回最后 10 条。

最伤的是第三步之前的回表。当查询走二级索引但 SELECT * 的字段不在索引里时，每扫描一条记录都要拿着叶子节点里的主键回聚簇索引查一次完整行——对前 100 万条记录各做一次随机 I/O，然后把这些辛苦读出来的整行数据原样丢弃。I/O 放大了上百万倍，这就是深分页性能崩塌的根因。

另一个坑是优化器的”叛逃”。MySQL 基于成本估算选择执行计划，当它判断扫描行数占全表比例过高时，可能干脆放弃索引退化为全表扫描——顺序读百万行，延迟直接失控。这个临界点不固定，取决于表大小、统计信息、硬件配置，所以同一条 SQL 在测试库飞快、到生产库翻车的情况很常见。

五种优化方案

游标分页：WHERE id > last_id

记录上一页最后一条记录的主键，下一页直接从该位置往后读：

SELECT * FROM t_order WHERE id > 1000000 ORDER BY id LIMIT 10;

执行路径变成：在 B+ 树上一次定位到 last_id，然后顺序读 10 条。扫描成本与页码无关，翻到第 10 万页和第 1 页一样快。它不要求 ID 连续——中间有删除造成的空洞无所谓，定位的是值而不是第几行。

代价同样明确：

  • 不能跳页：无法直接给第 5000 页，只适合”上一页/下一页/加载更多”的交互。
  • 排序键必须唯一：按 create_time 这类可重复字段排序时，要用 (create_time, id) 联合游标，即 WHERE (create_time, id) > (?, ?)，否则翻页时会漏数据或重复。
  • 并发读写会漂移：翻页间隙有新数据插入到已翻过的区间，会被后续页错过；有数据删除则可能让下一条记录重复出现。信息流类业务通常能接受这种弱一致。

子查询：先在索引上找到起点

思想是把”数偏移量”这件事挪到不回表的索引扫描上：

SELECT * FROM t_order
WHERE id >= (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 1
)
ORDER BY id LIMIT 10;

子查询只查 id 一列，天然走覆盖索引，扫描前 100 万条时不需要回表，纯索引顺序读的成本远低于百万次随机 I/O。拿到第 1000001 条的主键值后，外层查询从这个值开始回表 10 次即可。带过滤条件的场景收益更明显——子查询可以借助 (status, id) 联合索引完成覆盖扫描，外层照样只回表 10 次。需要注意的是，某些写法下子查询可能产生临时表，复杂查询优先考虑下面的延迟关联。

延迟关联：INNER JOIN 版本

延迟关联（Late Row Lookup）与子查询同源，只是把取 ID 和取数据用 JOIN 组装起来：

SELECT t1.*
FROM t_order t1
INNER JOIN (
    SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10
) t2 ON t1.id = t2.id;

子查询在索引上完成偏移并取出目标页的 10 个主键，JOIN 阶段只回表 10 次。相比子查询写法，它不会引入临时表，优化器对 JOIN 的处理也更成熟，是保留传统页码交互时的首选。这也是阿里巴巴《Java 开发手册》对超深分页给出的推荐方向。

覆盖索引：让翻页全程不回表

如果列表页需要的列不多，可以建联合索引把这些列全部装进去，让深分页查询本身就不需要回表：

-- 已有联合索引 (code, type)，InnoDB 二级索引叶子节点自带主键 id
SELECT id, code, type FROM t_order
ORDER BY code LIMIT 1000000, 10;

扫描和丢弃都只发生在索引上，成本降一到两个数量级。但它治标的方式很挑场景：查询列必须固定且少，否则索引膨胀、写入放大；结果集占全表比例过高时优化器仍可能退化为全表扫描，FORCE INDEX 强扭往往会换来更差的整体计划。

搜索引擎：search_after

当查询还携带多条件筛选、全文检索或复杂排序时，正确答案是别让 MySQL 干这个活。Elasticsearch 的 from + size 在深分页时同样代价高昂（协调节点要向每个分片拉取 from + size 条做归并，默认 from + size 上限 10000），官方推荐的深翻页方式是 search_after：以上一页最后一条的排序值作为游标，下一页带上它继续查：

GET /orders/_search
{
  "size": 10,
  "sort": [{"create_time": "desc"}, {"_id": "desc"}],
  "search_after": [1718900000000, "order_1000001"]
}

思路与 MySQL 游标完全一致：不数偏移量，只从排序值定位点继续向后读。要求排序组合唯一（通常用业务字段加 _id 兜底），同样不支持跳页。需要随机跳页的滚动导数场景，则用 scroll / PIT + search_after 做快照式遍历。

业务层的规避手段

数据库侧优化再多，都不如问一句：用户真的需要跳到第 10000 页吗？

  • 禁止跳页：C 端信息流、评论、订单历史改成无限滚动或”加载更多”，天然匹配游标分页，跳页需求直接消失。
  • 限制页深：Google 搜索翻到几十页后就不再给出结果，理由就是深页码对真实用户没有价值。后台系统可以限制最多翻 100 页，更深的数据引导用户加过滤条件。
  • 大跨度导出异步化：运营要”导出全年订单”时，不走同步分页接口，改为异步任务按游标分批拉取、生成文件后推送下载链接，既躲开深分页，也避免长事务和连接占用。
  • 防爬虫击穿：对携带大页码参数的请求单独限流或要求登录，爬虫扫深页是深分页慢查询的常见来源。

对比与权衡

方案 核心思路 是否支持跳页 适用场景 主要限制
游标分页 记住上页末尾主键，从定位点继续读 否 信息流、消息、订单列表 排序键要唯一；并发下有漂移
子查询 索引上完成偏移，取到起点主键再回表 是 后台列表的传统页码 部分写法产生临时表
延迟关联 INNER JOIN 把偏移收敛到索引，只回表目标页 是 必须保留跳页的大表列表 SQL 较复杂
覆盖索引 查询列全部进索引，全程不回表 是 列少且固定的榜单、列表 索引维护成本高，大结果集可能退化
search_after 搜索引擎侧以排序值为游标 否 多条件检索、全文搜索、复杂排序 需要引入 ES；不支持跳页

选型顺序建议：能改交互就上游标分页，一次到位；必须保留页码就用延迟关联，同时设最大页深兜底；列表列少可以叠加覆盖索引；带复杂筛选的深翻页直接交给搜索引擎。

面试视角

LIMIT 1000000, 10 为什么慢？ 分三层答：执行上，MySQL 无法随机定位偏移量，必须扫描并排序前 offset + N 条再丢弃前 offset 条；I/O 上，SELECT * 走二级索引时每条记录都要回表，前 100 万条各自产生一次随机 I/O 后被白白扔掉；优化器上，成本估算可能让它在偏移量过大时退化为全表扫描。

游标分页有什么前提？ 排序键必须唯一单调，否则要用 (排序字段, 主键) 联合游标；只适合连续翻页，不支持跳页；并发增删会导致漏读或重复，业务上要能接受弱一致。

延迟关联为什么比原查询快？ 把成本最高的”边数偏移边回表”拆成两步：子查询只取主键、纯索引扫描完成偏移，外层 JOIN 只对目标页的 10 个主键回表。随机 I/O 从 100 万次降到 10 次。

上亿数据的复杂条件翻页怎么办？ MySQL 不背这个锅。数据同步到 ES，用 search_after 以排序值做游标深翻页，或用 PIT + search_after 做快照遍历；同时评估业务上能否直接禁止跳页。

小结

  • 深分页的成本 = 扫描 offset + N 条 + 排序 + 回表，最后把 offset 条全部丢弃；偏移量越大，浪费越惊人。
  • 游标分页把翻页成本变成常数，是连续翻页场景的最优解；延迟关联是保留跳页能力时的最优解。
  • 覆盖索引、子查询是同一思路的不同写法：让偏移发生在索引上，回表只针对目标页。
  • 搜索引擎的 search_after 与游标分页同源，复杂检索场景应尽早把深翻页迁出 MySQL。
  • 最有效的优化往往在产品侧：禁止跳页、限制页深、导出异步化，能让一大半深分页问题根本不必发生。