凌晨 2 点的对账任务,跑到 4,800 万行时卡住了
引言
对账任务平时 2 分钟跑完,那天跑了 6 小时 40 分。单批耗时从 40ms 一路涨到 38 秒,到凌晨 4 点把从库 IO 打满,早高峰的读请求开始超时。
代码就一行:
SELECT * FROM t_order LIMIT #{offset}, 1000
offset 每批加 1000。第一反应是"索引没加"——可这条 SQL 连 WHERE 都没有,索引无从加起。表有 5,000 万行,自增主键,该有的索引一个不缺。
[配图:对账任务的单批耗时曲线,随 offset 增长从 40ms 线性爬到 38 秒]
先给结论:LIMIT offset, n 不是"跳到第 offset 行",而是"先数出 offset+n 行,再把前 offset 行扔掉"。 数多少行,就得扫多少行——数据库没有"第 100 万行"这个概念,偏移量本身不是索引。这篇用同一条 SQL 的三种写法实测,把"越翻越慢"的账算清楚,再给出游标分页能落地的完整写法。
一、先给结论:慢的不是 SQL,是"数过去"这个动作
三种写法的复杂度差别,一句话就能说清:
LIMIT offset, n 是 O(offset),而且 SELECT * 时每一行都要回表——它把 1,000,020 行全部取出来,扔掉前 1,000,000 行,只留 20 行返给你。
延迟关联(deferred join)还是 O(offset),但把"回表"从 1,000,020 次降到 20 次:先在窄索引上数行,只对最后 20 个 id 回表。扫描量没变,单行成本降了一个数量级。
游标分页(seek / keyset)是 O(log N):把"第 offset 行"换成"上一页最后一行的排序键值",直接按 B+ 树定位,扫 20 行就停。
关键认知:偏移量不是索引,排序键才是。 只要你的分页条件里出现"第几行",数据库就只能从头数;换成"某个值之后",它才能用索引跳。
[配图:左侧 OFFSET 逐个数过 1,000,020 行,右侧游标直接用排序键在 B+ 树里定位到起始叶子节点]
B+ 树的节点按 key 有序排列,查找的入口永远是"key 等于多少"或"key 大于多少"。它没有"第 N 个元素"的概念——想拿到第 100 万个叶子节点,只能从最左边顺着链表一路走过去。这就是为什么加了索引也没用:索引能加速"找值",不能加速"数位置"。
二、测试条件
同一张表、同一台机器,只改 SQL 写法:
MySQL 8.0.36,InnoDB,8C16G,innodb_buffer_pool_size=12G。表 t_order 5,000 万行,行宽约 120 字节,聚簇索引常驻内存,所以测出来的差异基本都来自扫描行数,不是磁盘 IO——这一点很重要,否则你会误以为是"冷数据导致的慢"。
索引:主键 id,以及 idx_ct (create_time, id)。每页 20 条。页码与 offset 的换算:第 50,001 页对应 offset 1,000,000。
判断"到底扫了多少行",别只看 EXPLAIN 的 rows(那是估算值),用 SHOW SESSION STATUS LIKE 'Handler_read%' 看真实的 Handler 计数。
Handler_read_next 就是实际顺着索引读过的行数。这个数字不会骗人,优化器估算会。
三、三种写法实测
3.1 基准:LIMIT offset, n
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;
EXPLAIN 显示 type=index,rows=1000020——注意,估算值本身就把扫描量写在脸上了。实际执行 0.98 秒,Handler_read_next 约 1,000,020。
offset 加到 10,000,000 时,耗时 9.6 秒,Handler 计数 10,000,020。线性的,一分不多一分不少。 这也解释了那个对账任务:offset 到 4,800 万,单批就要扫 4,800 万行,38 秒已经算快的了。
这里还有个容易被忽略的放大项:SELECT * 意味着每行都要回表取完整记录。如果表更宽(比如带 TEXT 字段),同样扫 100 万行,耗时会翻几倍——因为回表读的是聚簇索引的完整行。
顺带说一句估算偏差:EXPLAIN 的 rows 只能当参考,它算的是优化器估计要访问的行数,遇上 LIMIT 和覆盖索引时经常偏小。判断一条分页 SQL 到底扫了多少,最可靠的是 Handler_read_next 加上 Innodb_rows_read 两个计数——线上慢查询复盘时,先看这两个数,比看执行计划更有说服力。
3.2 延迟关联:把回表次数从 100 万降到 20
SELECT o.* FROM t_order o
JOIN (SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;
内层只读 id,走覆盖索引,不回表;数完 100 万行之后,只拿 20 个 id 去外层回表。
同样 offset 1,000,000,耗时 0.42 秒,比基准快 2.3 倍;offset 10,000,000 时 4.1 秒,快 2.3 倍。倍数很稳定,因为它省掉的是"每行回表"这个固定开销,而数行数这件事一点没少。
所以延迟关联不是"解决深分页",它是"把深分页的单位成本打下来"。offset 继续涨,它照样线性变慢。很多人在这里就收工了,结果半年后同样的告警再来一次。
它还有一个隐藏收益:内层只查 id,如果外层还能命中覆盖索引,整体可以完全不回表——这也是为什么很多"只查列表字段"的接口用延迟关联效果比表格里写的还要好。但收益的前提不变:扫描量还是 offset 那么多,只是每一行更便宜了。
3.3 游标分页:把"第几行"换成"哪个值之后"
-- 第一页
SELECT * FROM t_order ORDER BY create_time DESC, id DESC LIMIT 20;
-- 后续页:上一页最后一行的 (create_time, id) 作为游标
SELECT * FROM t_order
WHERE (create_time, id) < ('2026-03-01 10:00:00', 8800123)
ORDER BY create_time DESC, id DESC
LIMIT 20;
同一位置(第 50,001 页),耗时 0.02 秒,Handler_read_next 是 20。扫描量和页码无关,翻到第 500 万页也是 20 行。
两个技术前提必须满足,否则索引会失效:
第一,排序键必须唯一且稳定。只用 create_time 会有大量并列值,翻页时同一条数据可能重复出现或漏掉;跟上主键 id 组成复合键,顺序才唯一。索引也要建成 (create_time, id),和排序顺序一致。
第二,升降序要统一。MySQL 8.0 才支持真正的降序索引,在此之前 ORDER BY a DESC, b ASC 这种混合方向用不上索引,会退化成 filesort。我们的写法是全 DESC 或全 ASC,宁可多带一个 id DESC。
行构造器比较 (a, b) < (?, ?) 在 MySQL 里能用索引,但要注意:如果写成 a < ? OR (a = ? AND b < ?),优化器在部分版本上会选不到 range,性能反而更差。能用行构造器就别手写 OR。
四、一张表看清代价,然后决定用哪个
| 方案 | 第 1 页 | 第 50,001 页 | 第 500,001 页 | 实际扫描行数 | 支持跳页 |
|---|---|---|---|---|---|
LIMIT offset, n(SELECT *) | 3ms | 0.98s | 9.6s | offset + n | 支持 |
| 延迟关联 | 3ms | 0.42s | 4.1s | offset + n(不回表) | 支持 |
| 游标分页(复合键) | 2ms | 0.02s | 0.02s | n | 不支持 |
| 主键区间切批 | 2ms | 0.02s | 0.02s | 每批 n | 不支持 |
ES search_after | 8ms | 12ms | 15ms | 每页 n | 不支持 |
[配图:四种方案的耗时随页码变化,OFFSET 两条线线性上扬,游标与区间切批始终贴着横轴]
选型上我不给绝对答案,给边界:
用户翻页、页码在几百页以内,LIMIT offset, n 完全够用,别为了"可能的深分页"把产品交互改成只能下一页——这是过度设计。真正该管的是"最大可翻页数",比如只允许翻到第 500 页,再深就让用户加筛选条件。
后台导出、定时批处理,用主键区间切批而不是游标:记住上一批的 max(id),下一批 WHERE id > max_id LIMIT 1000,天然 O(1),还不用改排序逻辑。那个对账任务最后就是这么改的,6 小时 40 分变回 2 分钟。
面向用户的无限滚动、时间线,用游标分页,把 nextCursor 返回给前端。这类交互本来就不需要跳页,是游标最舒服的场景。
必须支持任意跳页 + 数据量大,游标和 OFFSET 都不合适:可以用"锚点表"(提前把每页首行的排序键存下来),或者把查询交给 ES 的 search_after。跨库场景的细节在《分库分表后跨库分页和聚合查询怎么办》里讲过,这里不重复。
还有一种现实里最常见的折中:前 3 页走 OFFSET,之后走游标。用户在手机上能翻到的页数本来就很少,前几页保留页码和跳页体验,成本可以忽略;一旦用户真翻到第 10 页,前端就切成"加载更多",后端走游标。这套混合策略不需要改数据库,只需要前端多一个判断分支。
五、游标分页落地:三个产品和技术细节
5.1 游标要编码,不要裸传
直接把 create_time + id 拼成字符串返回给前端,用户改一个数字就能构造任意位置——这不是安全问题,但会让"游标无效"的排查变得很痛苦。我们统一 Base64 编码并带一个短签名,服务端校验失败就直接返回第一页,而不是抛异常。
游标里还要带上排序字段名和方向,否则前端把"按时间排序"的游标拿去请求"按金额排序"的接口,服务端要么报错要么返回错误数据。
编码之后还有个好处:游标里能塞进过滤条件的快照,比如当前的时间范围。用户在筛选后翻页,条件变了但游标没变,会出现"翻页翻着翻着数据全变了"——把过滤条件一起编码,服务端就能拒绝跨条件的游标,而不是默默返回一堆不相关数据。
5.2 上一页怎么办
游标分页不能算"上一页的游标",但可以反向做:把排序条件整体反转、用当前页第一行的键值做 > 查询,取回 20 条后再在内存里反转一次。
这里有个坑:反向查询的边界是 >,不是 >=,否则会把当前页第一条重复取出来。这个 off-by-one 我在 Code Review 里见过至少三次。
5.3 数据漂移要接受
翻页期间如果有人插入或删除了数据,游标分页会出现"漏一条"或"重复一条",OFFSET 分页同样会重复或跳过——只是两种错法不同。分页从来不是一致性快照,要严格一致只能靠"按时间范围查 + 业务侧去重",别指望分页 SQL 解决。
所以游标的排序键要选"不会变"的字段:用 create_time 和 id 都合适,用 update_time 就是灾难——一条数据被改了,它在列表里的位置也跟着跳,用户翻页会看到自己刚看过的那条又出现一次。这个细节没有文档会提醒你,只能靠踩一次记住。
5.4 避坑清单
LIMIT offset, n的成本和 offset 成正比,跟有没有索引无关,这是语义决定的。- 延迟关联只降单位成本,不降复杂度,深分页照样会随 offset 线性变慢。
- 游标的排序键必须唯一,只按时间排序会重复或漏数据,必须补主键组成复合键。
- 混合升降序用不上索引(MySQL 8.0 之前),排序方向要统一。
EXPLAIN的 rows 是估算,判断真实扫描量要看Handler_read_next。
收尾
深分页的解法不是把 SQL 改快,而是换一个"能定位"的条件——偏移量没有索引,排序键有。
你们线上还有多少地方在循环里用 LIMIT offset, n 扫表?有没有算过 offset 到百万级时单批要多久?
关注我,每周拆一个真实生产故障。
标题:凌晨 2 点的对账任务,跑到 4,800 万行时卡住了
作者:jiangyi
地址:http://jiangyi.space/articles/2026/11/04/1793798730805.html
公众号:服务端技术精选
评论