凌晨两点手机震动。值班群里贴出来的告警截图显示订单查询接口的P99从日常的200ms直接飙到7800ms链路追踪里卡死的SQL指向同一条分页查询。当时的业务表数据量已经超过1700万行接口每页返回20条用户翻到第600页左右请求就开始超时然后触发重试数据库连接被迅速打满整个运营后台跟着雪崩。这不是什么高深莫测的问题就是MySQL深分页的经典病但把它彻底治好的过程涉及执行计划分析、索引设计、SQL改写和业务交互调整值得完整复盘一遍。这篇文章我会按实际处理的顺序来写从故障现场还原开始接着拆解深分页为什么慢的底层原理然后给出两轮优化方案——覆盖索引加延迟关联、以及最终的游标分页最后放上压测数据和生产落地时踩过的坑。适合正在维护百万级甚至千万级MySQL表、又对分页性能束手无策的工程师参考。1. 故障现场复盘一次触达第600页的“惩罚”1.1 事故是怎么冒出来的先说背景。订单表 orders 用了 InnoDB单表约1720万行主键是自增 id业务上主要按 create_time 倒序展示订单列表。运营后台的订单查询页支持按状态过滤默认展示最近订单也可以点击页码翻页。线上告警的时间点正好赶上大促期间的运营盘点运营同学在后台筛选了某段时间的订单然后一页一页往下翻。翻到第600页左右接口响应时间突然拉长前端请求超时运营刷新页面重试重试请求又把数据库连接池占满最终导致整个后台登录都开始卡。当时的第一反应是数据库负载是不是被大促流量打满了但看了监控发现数据库 CPU 和 IO 并没有明显尖峰反而是某条SQL的平均执行时间异常。于是直接查慢查询日志定位到下面这条SQLSELECT 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;执行计划里最关键的三列是这样字段值说明typeALL全表扫描rows7210300优化器估算扫描约721万行ExtraUsing where; Using filesort需要额外排序typeALL 说明没有走索引直接扫聚簇索引全表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 要返回第 offset1 到 offsetcount 行就必须先找到 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 DESCSELECT: 业务需要的所有字段这个暂时做不到全字段覆盖最自然的联合索引是 (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 这个二级索引上做扫描、排序取到60000020个 id 后只用最后20个 id 去回表。这带来的改变是回表次数从“扫描过程中每一行都回表”降到了“只对最终要返回的20行回表”。前面扫描的60万行统统不需要回聚簇索引取数据省掉了几十万次随机IO。3.3 第一轮优化的效果与未解决的痛我们在测试环境模拟了1700万行数据把这条延迟关联SQL压了一下结果确实很明显第600000行分页从原来的7.8秒降到了约430毫秒响应快了一个数量级。但这个方案有一个绕不开的天花板子查询里的LIMIT 600000, 20依然要数60万行索引记录。扫描索引叶子节点的成本虽然比回表小很多但也是线性增长的。offset 越大耗时越高不可能收敛到稳定低延迟。分页位置原SQL耗时覆盖索引延迟关联耗时第1页45ms18ms第1000页120ms35ms第10000页780ms72ms第60000页7.8s430ms第100000页13.5s690ms第100000页时延迟关联方案的耗时已经到690ms虽然还在可接受范围但趋势不对。按这个趋势数据量再翻一倍或者翻到更后面的页数迟早回到秒级。所以延迟关联只能算是“缓兵之计”不能根治深分页。而且延迟关联方案还多了一次嵌套子查询和一次JOIN如果原表本身还有其他过滤条件、或者和别的表还有关联SQL整体复杂度会上升优化器选错执行计划的概率也变大。4. 第二轮优化游标分页直接干掉 OFFSEET4.1 先从业务形态反推真的需要“跳页”吗第一轮优化做完我开始和产品团队聊分页交互。看了运营后台的访问日志发现用户翻页行为其实有两个特点一是基本不会一口气跳到第几千页二是翻页路径永远是上一页/下一页连续浏览。真正需要“跳到第xxx页”的场景非常少即便有也可以通过搜索条件缩小结果集比如按时间区间过滤、按订单号精确搜索。既然如此为什么不让分页接口天然不支持深翻页于是我们决定采用游标分页也就是业内常说的 keyset pagination 或者 seek method。简单说不告诉数据库“我要从第600000行开始”而是告诉数据库“我要从‘上一条记录’之后开始”。数据库直接通过索引定位到那一条记录接着往后取20行整个查询的复杂度只取决于返回的数据量而不是数据的偏移量。4.2 游标分页的SQL到底怎么写以这个订单查询为例假设上一页返回的最后一条记录的 create_time 是2025-06-21 12:00:00id 是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 不再带 offsetMySQL 通过索引直接定位到游标位置然后顺着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页45ms18ms5ms第1000页120ms35ms6ms第10000页780ms72ms5ms第60000页7.8s430ms6ms随机深页13s或超时800ms6ms游标分页在任意页码下响应时间都稳定在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还能不能扛住。