凌晨两点，手机震动。值班群里贴出来的告警截图显示，订单查询接口的P99从日常的200ms直接飙到7800ms，链路追踪里卡死的SQL指向同一条分页查询。当时的业务表数据量已经超过1700万行，接口每页返回20条，用户翻到第600页左右，请求就开始超时，然后触发重试，数据库连接被迅速打满，整个运营后台跟着雪崩。这不是什么高深莫测的问题，就是MySQL深分页的经典病，但把它彻底治好的过程，涉及执行计划分析、索引设计、SQL改写和业务交互调整，值得完整复盘一遍。
这篇文章我会按实际处理的顺序来写：从故障现场还原开始，接着拆解深分页为什么慢的底层原理，然后给出两轮优化方案——覆盖索引加延迟关联、以及最终的游标分页，最后放上压测数据和生产落地时踩过的坑。适合正在维护百万级甚至千万级MySQL表、又对分页性能束手无策的工程师参考。
1. 故障现场复盘：一次触达第600页的“惩罚”
1.1 事故是怎么冒出来的
先说背景。订单表 orders 用了 InnoDB，单表约1720万行，主键是自增 id，业务上主要按 create_time 倒序展示订单列表。运营后台的订单查询页支持按状态过滤，默认展示最近订单，也可以点击页码翻页。
线上告警的时间点正好赶上大促期间的运营盘点，运营同学在后台筛选了某段时间的订单，然后一页一页往下翻。翻到第600页左右，接口响应时间突然拉长，前端请求超时，运营刷新页面重试，重试请求又把数据库连接池占满，最终导致整个后台登录都开始卡。
当时的第一反应是数据库负载是不是被大促流量打满了，但看了监控发现数据库 CPU 和 IO 并没有明显尖峰，反而是某条SQL的平均执行时间异常。于是直接查慢查询日志，定位到下面这条SQL：
SELECT id, order_no, user_id, status, create_time, amount, ... -- 业务需要的全部字段 FROM orders WHERE biz_type = 1 AND status IN (1, 2) ORDER BY create_time DESC LIMIT 600000, 20;这条SQL本身语法没有任何问题，但问题恰恰出在LIMIT 600000, 20上。
1.2 用 EXPLAIN 确认问题根源
拿到SQL之后，我习惯性先跑一遍 EXPLAIN，看看 MySQL 自己是怎么规划这条查询的：
EXPLAIN SELECT * FROM orders WHERE biz_type = 1 AND status IN (1, 2) ORDER BY create_time DESC LIMIT 600000, 20;执行计划里最关键的三列是这样：
| 字段 | 值 | 说明 |
|---|---|---|
| type | ALL | 全表扫描 |
| rows | 7210300 | 优化器估算扫描约721万行 |
| Extra | Using where; Using filesort | 需要额外排序 |
type=ALL 说明没有走索引，直接扫聚簇索引全表；rows 超过700万，意味着优化器认为要扫700多万行才能找到目标数据；Extra 里的 Using filesort 说明 create_time 排序没有可利用索引，MySQL 要把满足条件的行先加载到临时内存/磁盘排序，再取前20条。
这里要顺便解释一个很多人误解的地方：LIMIT 600000, 20在 MySQL 里的执行逻辑并不是“跳到第600000行，然后取20行”，而是“从第一条开始数，数到600000行之后，再把后面20行拿出来”。也就是说，前面那600000行不是不查，而是全部扫描后被丢掉。这个机制是深分页性能问题的第一个根源。
1.3 为什么这个问题之前没暴露
复盘的时候我们发现，这条SQL其实已经存在一年多，之前并没有出过问题。原因有三个叠加。第一，数据量在半年内从500万涨到了1700万，翻到相同页数的扫描行数翻了3倍多。第二，测试环境的数据量只有几十万行，测试用例也基本只翻了前5页，根本触发不了深分页。第三，线上监控慢查询阈值设置的是2秒，而这SQL之前还没到这个阈值，属于“温水煮青蛙”。
这也是我想强调的一点：在MySQL分页优化上，问题往往不会在小数据量时暴露，一旦暴露就是事故级别。所以代码评审阶段就要对深分页敏感，而不是等线上炸了再救火。
2. 深分页性能瓶颈的底层原理：三个叠加的放大器
2.1 OFFSET 的“数行”逻辑
要理解深分页为什么慢，必须先把LIMIT offset, count的执行机制刻在脑子里。MySQL 要返回第 offset+1 到 offset+count 行，就必须先找到 offset 行的位置。InnoDB 的索引是B+树，找到起点靠的是遍历叶子节点链表，逐条数过去，而不是像数组下标那样 O(1) 定位。
所以当 offset 是600000时，InnoDB 就真的会从第一个满足条件的叶子节点开始，逐条数60万条记录，再把第600001到第600020条拿出来。注意，这里“数”的成本可不只是数60万条索引项，已经比“返回20条记录”本身重了3万倍。
更麻烦的是，表数据是持续增删改的，索引叶子节点的物理分布并不连续，InnoDB 插入时可能产生页分裂，删除后可能留下空洞。遍历时相邻记录可能在不同数据页上，每换一个页就是一次IO操作，页如果不在 buffer pool 里就得走磁盘。offset 越大，涉及的数据页越分散，IO 次数越多。
2.2 回表产生的随机IO
第二个放大器是回表。当SQL里写了SELECT *或者选了二级索引没有覆盖到的列时，InnoDB 需要把从二级索引拿到的每个主键值，再去聚簇索引里找对应的完整数据行，这个动作叫回表。
如果SQL只用了二级索引做条件过滤和排序，那么扫过的每一行候选记录几乎都要回表。深分页时，MySQL 并不是只对最终要返回的20行做回表，而是对扫描过程中遇到的所有候选行——包括被丢弃的前600000行——都要到聚簇索引里取一次完整记录。也就是说，即使最终只返回20条，回表次数也可能接近60万甚至更多。
可以类比一下：你要在一本几千页的电话簿里，按“姓氏首字母”找到第600页之后的人，而且每看到一个人名，都要翻到电话簿最后的附录看这个人的详细住址，再把住址抄下来，然后扔掉不要。这么做不慢才怪。
这个阶段我们做过的实验很直观：同样的深分页SQL，如果把SELECT *改成SELECT id，耗时直接从7秒降到0.8秒左右。差距几乎全部来自回表。所以很多号称“优化分页”的文章只讲索引、不讲回表，是不够落地的。
2.3 filesort 排序的额外开销
第三个放大器是排序。EXPLAIN 里 Extra 字段出现 Using filesort 时，MySQL 需要把满足 WHERE 条件的行整个加载到 sort buffer 或磁盘临时表，按ORDER BY字段排序，再执行 LIMIT。
有人可能会问，MySQL 不是有优先队列优化吗，ORDER BY ... LIMIT 20应该只需要维护20条的最大/最小堆，不需要全量排序。确实，MySQL 5.6 以后的 filesort 算法对带 LIMIT 的排序做了优化，在 sort buffer 足够大时，通过堆排序只保留前 N 条，避免了完整排序的内存开销。
但注意，filesort 优化并不能减少前面的扫描和回表数量。深分页真正的痛点是“找到第600000行”的过程，而不是“把60万行做完整排序”。排序优化只是让你在同样扫描行数下排序更快一点，改变不了数量级。
而且排序还有一个隐性成本：如果ORDER BY字段没有索引，MySQL必须在WHERE过滤之后、LIMIT之前生成排序结果。这个过程中间结果集的规模由满足WHERE条件的总行数决定，而不是由LIMIT决定。当过滤条件很宽（比如 status IN (1,2) 覆盖了大部分数据）时，中间结果集可能有上百万行，排序内存需求巨大，可能要落盘，产生临时文件IO，进一步拖慢查询。
所以深分页的7秒，其实是“60万次扫描 + 60万次回表 + 大结果集排序”三者叠加的结果。理解了这套机制，优化方向就清晰了：要么减少扫描行数，要么砍掉回表，要么让排序走索引。
3. 第一轮优化：用覆盖索引和延迟关联先治“回表伤”
3.1 联合索引如何同时服务 WHERE、ORDER BY 和覆盖
第一轮优化，我选择先治回表。思路是设计一个联合索引，让WHERE过滤、ORDER BY排序、以及查询所需字段都能在索引内部解决。
先看原SQL的过滤和排序字段：
- WHERE:
biz_type = 1 AND status IN (1, 2) - ORDER BY:
create_time DESC - SELECT: 业务需要的所有字段，这个暂时做不到全字段覆盖
最自然的联合索引是 (biz_type, status, create_time)。但业务查询里 status 是 IN (1, 2) 的范围查询，而联合索引遵循最左前缀原则，范围查询后面的列无法用于索引排序。所以 status 不能放在 create_time 前面用于排序优化。
我们实际使用时为了兼顾 status 过滤和 create_time 倒序，会根据业务的实际情况调整。因为 biz_type 是等值，status 是一个小范围，而排序字段是 create_time，一个比较合理的索引是：
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time, id);索引里额外加了一个 id 字段，目的是让排序在 create_time 相同的场景下有确定的次序，同时可能让索引成为覆盖索引的一部分。
不过这里要坦诚地说明：status IN (1,2) 这个条件，优化器不一定能用上索引来精确过滤。对一个只有两个取值的字段，优化器可能认为走索引的成本比全表扫描还高。所以光建这个索引，EXPLAIN 里的 type 可能从 ALL 变成 range 或 index，但未必能彻底改头换面。真正的关键在下一层的 SQL 改写。
3.2 延迟关联：先查主键再查详情
延迟关联，也叫“推迟关联”，是处理深分页回表问题的经典手段。核心思想是：先用覆盖索引查出满足条件的主键 id，然后把 LIMIT 下推到子查询里，最后再和原表做一次关联，取出完整数据行。
改写后的SQL长这样：
SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE biz_type = 1 AND status IN (1, 2) ORDER BY create_time DESC LIMIT 600000, 20 ) t ON o.id = t.id ORDER BY o.create_time DESC;子查询只返回 id，所以它需要的所有列都来自索引。MySQL 可以直接在 idx_status_create_time 这个二级索引上做扫描、排序，取到600000+20个 id 后，只用最后20个 id 去回表。
这带来的改变是：回表次数从“扫描过程中每一行都回表”降到了“只对最终要返回的20行回表”。前面扫描的60万行统统不需要回聚簇索引取数据，省掉了几十万次随机IO。
3.3 第一轮优化的效果与未解决的痛
我们在测试环境模拟了1700万行数据，把这条延迟关联SQL压了一下，结果确实很明显：第600000行分页从原来的7.8秒降到了约430毫秒，响应快了一个数量级。
但这个方案有一个绕不开的天花板：子查询里的LIMIT 600000, 20依然要数60万行索引记录。扫描索引叶子节点的成本虽然比回表小很多，但也是线性增长的。offset 越大，耗时越高，不可能收敛到稳定低延迟。
| 分页位置 | 原SQL耗时 | 覆盖索引+延迟关联耗时 |
|---|---|---|
| 第1页 | 45ms | 18ms |
| 第1000页 | 120ms | 35ms |
| 第10000页 | 780ms | 72ms |
| 第60000页 | 7.8s | 430ms |
| 第100000页 | 13.5s | 690ms |
第100000页时，延迟关联方案的耗时已经到690ms，虽然还在可接受范围，但趋势不对。按这个趋势，数据量再翻一倍，或者翻到更后面的页数，迟早回到秒级。所以延迟关联只能算是“缓兵之计”，不能根治深分页。
而且延迟关联方案还多了一次嵌套子查询和一次JOIN，如果原表本身还有其他过滤条件、或者和别的表还有关联，SQL整体复杂度会上升，优化器选错执行计划的概率也变大。
4. 第二轮优化：游标分页，直接干掉 OFFSEET
4.1 先从业务形态反推：真的需要“跳页”吗
第一轮优化做完，我开始和产品团队聊分页交互。看了运营后台的访问日志，发现用户翻页行为其实有两个特点：一是基本不会一口气跳到第几千页，二是翻页路径永远是上一页/下一页连续浏览。
真正需要“跳到第xxx页”的场景非常少，即便有，也可以通过搜索条件缩小结果集，比如按时间区间过滤、按订单号精确搜索。既然如此，为什么不让分页接口天然不支持深翻页？
于是我们决定采用游标分页，也就是业内常说的 keyset pagination 或者 seek method。简单说，不告诉数据库“我要从第600000行开始”，而是告诉数据库“我要从‘上一条记录’之后开始”。数据库直接通过索引定位到那一条记录，接着往后取20行，整个查询的复杂度只取决于返回的数据量，而不是数据的偏移量。
4.2 游标分页的SQL到底怎么写
以这个订单查询为例，假设上一页返回的最后一条记录的 create_time 是2025-06-21 12:00:00，id 是1000001，下一页查询就是：
SELECT id, order_no, user_id, status, create_time, amount FROM orders WHERE biz_type = 1 AND status IN (1, 2) AND ( create_time < '2025-06-21 12:00:00' OR (create_time = '2025-06-21 12:00:00' AND id < 1000001) ) ORDER BY create_time DESC, id DESC LIMIT 20;这里有几个关键细节。第一，排序从单一的create_time DESC改成了create_time DESC, id DESC，加上了 id 作为次级排序字段，确保两条订单即使 create_time 完全相同，也有确定的先后顺序。否则分页可能出现重复或漏数据。
第二，WHERE条件里用了(create_time < 游标时间) OR (create_time = 游标时间 AND id < 游标Id)的写法。这个条件表达的语义是“比我这一页最后一条记录更早的记录”，并且它能充分利用联合索引 idx_status_create_time。当 create_time 相等时，用 id 区分先后，保证排序完全一致。
第三，LIMIT 20 不再带 offset，MySQL 通过索引直接定位到游标位置，然后顺着B+树的叶子链表连续读取20条，完全避免了深分页的扫描量。
我也试过把游标条件直接写进WHERE (create_time, id) < ('2025-06-21 12:00:00', 1000001)这种行值比较，但 MySQL 对行值比较和范围条件的优化并不统一，加上 status IN (1,2) 的过滤，执行计划可能不稳定。OR 写法虽然啰嗦一点，但对优化器友好，EXPLAIN 验证能走 range 扫描。
4.3 代码层怎么把游标传进来
游标分页需要后端把上一页最后一条记录的排序键值作为参数，传到下一页请求里。我们当时的实现方式是这样：
前端翻页时，把当前列表最后一条记录的create_time和id组合成一个不透明的游标串，例如MjAyNS0wNi0yMSAxMjowMDowMF8xMDAwMDAx，其实就是对2025-06-21 12:00:00_1000001做了一层 Base64 编码。请求下一批时带上cursor参数，后端解析出游标值，拼进SQL的 WHERE 条件。
首屏没有游标，SQL 退化成普通的前20条查询，走联合索引天然高效。每次翻页请求只需要处理游标下一条到第20条之间的记录，不管数据总量是多少，不管翻到多深，延迟都基本恒定。
这里有一个产品上的取舍要提前想清楚：页码跳转没法用了。我们为了照顾运营偶尔需要跳回某一页的场景，保留了“回到首页”和“按时间区间重新查询”的能力。想跳第几百页时，直接用筛选条件缩小范围重新拉首页，比硬翻几十万行来得更快，体验也更好。
4.4 游标分页也有边界，别掉以轻心
游标分页不是银弹，有几个边界情况需要处理。
第一个是数据实时写入导致的现象：如果用户在翻页过程中，有一批新的订单到达，下一页查询拿到的第一条数据可能和上一页最后一条数据之间出现间隙，看起来就是“漏了一条”或者“翻页时列表自动往前跳”。这其实是所有分页方案在并发写入下都会遇到的一致性问题，游标分页也一样。我们的处理方式是接受这个现象，因为运营场景不用追求事务级一致，返回的数据只要最终一致即可。
第二个是排序字段的可变性：如果业务允许用户修改 create_time，那上一页最后一条记录的时间戳可能变化，导致游标失效。我们的订单 create_time 创建后不可变，所以没有这个问题。如果你的业务排序字段可能被更新，建议复制一份不可变的“创建时间快照”字段，或者换用自增主键 id 作为唯一排序键。
第三个是表数据被物理删除：如果上一页最后一条记录刚好被删了，用它的 id 做游标不会影响查询，因为条件只是“小于”，新查询会从下一个有效记录开始，不会报错也不会重复。
5. 实测结果与把方案推广出去的注意事项
5.1 优化前后的压测数据对比
方案上线前，我们专门做了一轮压测，模拟线上1700万行数据、100并发、翻页到不同深度的场景。测试结果如下：
| 分页位置 | 原始SQL | 延迟关联方案 | 游标分页方案 |
|---|---|---|---|
| 第1页 | 45ms | 18ms | 5ms |
| 第1000页 | 120ms | 35ms | 6ms |
| 第10000页 | 780ms | 72ms | 5ms |
| 第60000页 | 7.8s | 430ms | 6ms |
| 随机深页 | 13s+或超时 | 800ms+ | 6ms |
游标分页在任意页码下响应时间都稳定在6ms左右，相比原始SQL从7s超时降到10ms以内，提升超过700倍。更重要的是复杂度稳定，不会随着翻页加深而劣化，这对生产环境来说比单纯快更重要。
数据库压力也明显下降。原来的深分页SQL每次执行要扫600000行并做几十万次回表，游标方案每次只扫20行，慢查询基本消失，数据库连接池占用率也回归正常。
5.2 这套方案能复制的边界条件
不是所有分页场景都适合游标分页。你需要先评估业务是不是具备以下条件：
- 列表有明确的排序字段，且该字段的排序顺序稳定。最理想的是自增主键，其次是创建时间这类不可变字段。
- 分页交互以“上一页/下一页”为主，而不是强制要求页码跳转。
- 数据量确实到了百万级以上，常规LIMIT已经出现明显性能问题。几十万行以内的表，好好用覆盖索引就够了，不要过度设计。
如果你的业务无论如何都要支持任意跳页，可以考虑折中方案：限制最大翻页深度，比如超过100页之后禁止跳转，只允许连续翻页。这个限制要写到产品逻辑里，而不是等数据库扛不住再改。
5.3 踩坑记录与Code Review检查项
写这一节前我特意翻了下这半年的工单，把踩过的坑挑几个有代表性的列出来。
第一个坑是排序规则不一致。上线前自测全用ORDER BY create_time DESC, id DESC，看着没问题。后来有一个管理端页面单独用了 create_time 排序，没带 id，结果同一个时间戳的记录在翻页时反复出现。排查半天发现是同一秒内插入了几十条订单，单靠时间戳根本区分不了先后。后来我们把所有订单列表查询的排序规格统一成“create_time + id”双字段，并写进了开发规范。
第二个坑是INDEX和ORDER BY的方向问题。索引是默认升序的，如果我们业务要ORDER BY create_time DESC，依然可以用同一个索引倒序扫描，MySQL 8.0 之前对倒序扫描优化一般，8.0 以后支持降序索引，可以定义INDEX (create_time DESC)。当时业务量没有大到需要为倒序索引单独调优，但如果你的表特别大，8.0 的降序索引值得考虑。
第三个坑是大页面的COUNT(*)统计。分页接口往往需要返回总条数给前端，但这个统计SQL在高并发下同样可能成为新瓶颈。我们最终的方案是：列表本身用游标分页，不用返回总条数；运营后台的统计数字通过单独的数仓/汇总表获取，不再实时精确统计。如果实时性要求极高，可以用 Redis 缓存计数并设置短过期时间。
[建议] 把这几个检查项写进代码评审清单：
- 有深分页嫌疑的SQL，必须EXPLAIN跑一遍，看rows数量级。
LIMIT的 offset 超过10000，就要问产品：这个交互能否改成游标分页？SELECT *在列表查询里尽量改成显式字段，减少回表。- 排序字段必须唯一或有唯一组合，否则分页会乱序。
这套方案上线后稳定运行了半年多，订单查询P99稳定在27ms以内，数据库慢查询归零。最让我有感触的其实是后面带新人排查问题时，新人看到LIMIT 100000, 20这种SQL还是会下意识觉得“这有什么问题”，等他把 EXPLAIN 拿出来，看到 rows 扫了700多万行才理解。MySQL 分页优化没有太多花活，就是吃透索引和回表这两件事，然后逼自己在写每一条分页SQL之前都想清楚：数据量翻十倍时，这条SQL还能不能扛住。
