千万级表深分页 LIMIT 200万 直接超时：一次 keyset 改造把 P99 从 8s 压到 40ms

结论先行

对千万级 InnoDB 表，LIMIT offset, size 这种「偏移分页」在 offset 越大越慢，且慢得非线性的——offset 到 200 万时单页查询 P99 能飙到 8 秒以上。换成 keyset（游标）分页后，同样翻到第 20 万页，P99 稳定在 40ms 以内。下面是一次真实的改造记录，MySQL 8.0.32，InnoDB。

一、现象：后台导出越翻越卡

一个运营后台的「订单列表」接口，表 t_order 约 4200 万行，单表 96GB。运营要按 create_time 倒序翻页导出。前 100 页（offset < 1 万）P99 约 30ms，很健康；但运营反映「翻到后面越来越卡，最后直接转圈」。

抓了几条慢日志，定位到这一句：

SELECT id, order_no, amount, status, create_time
FROM t_order
WHERE status = 1
ORDER BY create_time DESC
LIMIT 2000000, 20;   -- offset=200万

单条查询耗时 8.2s，Handler_read_rnd_next 计数到 200 万+。同一条 SQL 把 offset 换成 1000，只要 28ms。同样的 SQL，唯一变量是 offset，耗时却差了近 300 倍——这不是索引没建好的问题，是这种写法本身的机制问题。

二、原理：offset 为什么是「线性丢弃」

LIMIT offset, size 在 InnoDB 里的执行路径大致是：

1. 按 idx_status_create_time（status, create_time）定位，顺序扫描满足 status=1 的索引项；
2. 每读一条索引项，回表拿 order_no / amount 等业务列；
3. 数到 offset 条后全部丢弃，再取 size 条返回。

关键点：无论你要的是第 1 页还是第 20 万页，前 offset 条记录一条都省不掉，要完整扫描 + 回表 + 丢弃。所以复杂度是 O(offset + size)，offset 越大越慢，且和 size 完全无关——你 LIMIT 2000000, 1 和 LIMIT 2000000, 20 代价几乎一样。

更坑的是 ORDER BY create_time 若不唯一，相同时间戳的行在不同 offset 下的相对顺序可能漂移，导致翻页时跳行或重复（下一页出现上一页的订单）。

三、做法：改成 keyset 分页

核心思想：不再「丢弃前 N 条」，而是用上一页最后一条的游标值做范围过滤，让 B+ 树直接定位到起点。

-- 第一页（保持原有排序）
SELECT id, order_no, amount, status, create_time
FROM t_order
WHERE status = 1
  AND create_time < :lastCreateTime   -- 首页去掉这个条件
ORDER BY create_time DESC, id DESC
LIMIT 20;

要点：

• 游标列必须唯一且参与排序，所以 ORDER BY 加上了 id 兜底，create_time 相同也能稳定推进，彻底消除跳行/重复；
• 客户端每页记住 lastCreateTime + lastId，下一页带上即可；
• 需要复合条件时用 AND (create_time, id) < (…)，MySQL 8.0 支持 row constructor 比较。

配合索引 idx_status_create_time（已有），这条查询是纯索引范围扫描 + 20 次回表，代价与 offset 无关。实测翻到第 20 万页，P99 = 41ms，和第一页基本持平。

如果业务确实需要「跳到第 N 页」（比如运营要直接输入页码），别用大 offset，改成二分定位：在 create_time 上二分找到第 N 页首行的时间戳，再走 keyset 取一页，代价是 O(log N) 次索引探测。

四、踩过的坑

1. 游标列没加唯一兜底：第一版只 ORDER BY create_time，结果 5 月 1 日零点那一批同时间戳订单在翻页时反复出现/丢失，运营投诉「数据对不上」。加上 id 兜底后消失。
2. 复合索引列顺序：WHERE status=1 AND create_time<…，索引必须是 (status, create_time) 而不是 (create_time, status)，否则 range 用不上、退化成全扫。
3. Handler_read_rnd_next 是照妖镜：EXPLAIN 看着 type=range 很「漂亮」，但配合 SHOW SESSION STATUS LIKE 'Handler_read%' 才看出它读了 200 万行。只看 EXPLAIN 的 type 会误判。
4. 别迷信「加个索引就完事」：offset 分页加再多索引都救不了，因为丢弃的开销在回表，不在定位。

五、结论与可复用清单

• LIMIT offset, size 的代价是 O(offset)，offset 越大越慢，且与 size 无关——深分页必须换 keyset。
• keyset 三要素：游标列唯一、参与 ORDER BY、索引覆盖 WHERE + ORDER BY。
• 排序列不唯一时，务必加主键兜底，否则翻页会跳行/重复。
• 诊断深分页慢：用 Handler_read_rnd_next 看实际扫描行数，别只看 EXPLAIN.type。
• 需要「跳页」时改用时间戳二分定位，而非大 offset。

一句话：分页的复杂度由 offset 决定，而不是由你取多少行决定；把「丢弃前 N 条」换成「直接定位到第 N 条」，才是深分页的正解。