分页为什么越翻越慢：offset 陷阱、seek 分页与索引排序优化

目标：你能把“分页慢”讲成一个可解释的 IO 模型，并掌握可落地的改造：seek 分页、覆盖索引、延迟关联。

1. offset 分页的本质：扫描 + 丢弃

经典写法：

select*fromtwhereuser_id=?orderbycreate_timedesclimit100000,20;

直觉：

• MySQL 必须先找到前 100000 行（满足 where + order 的结果）
• 然后丢弃，再返回 20 行

这意味着：

• 页码越大，被丢弃的行越多
• 扫描成本线性增长

1.1 一个可复现的最小例子：同一张表对比 offset 与 seek 的 EXPLAIN

准备表：

createtablet_order(idbigintprimarykey,user_idbigintnotnull,create_timedatetimenotnull,titlevarchar(64)notnull,contentvarchar(2000)notnull,keyidx_user_time(user_id,create_time,id,title));

对比两条查询。

对照 1：offset 分页（越翻越慢）

explainselect*fromt_orderwhereuser_id=1orderbycreate_timedesc,iddesclimit10000,20;

你要重点观察：

• rows是否显著变大
• 是否发生大量回表（select *且Extra不会Using index）

对照 2：seek 分页（稳定）

explainselectid,title,create_timefromt_orderwhereuser_id=1and(create_time<?or(create_time=?andid<?))orderbycreate_timedesc,iddesclimit20;

你要重点观察：

• rows不随页码线性增长
• 更容易出现Using index（覆盖）

2. 即使走索引，也会慢：因为“走的是长距离顺扫”

如果索引是(user_id, create_time)：

• 能按顺序找到记录
• 但仍需要向后移动 100000 步才能到达起点

如果还select *：

• 会回表 100020 次（或接近）
• 随机 IO/缓存失效进一步放大

3. seek 分页：把“跳过”变成“从游标继续”

思路：

• 用上一页最后一条记录的排序键作为游标
• 下一页从游标之后继续取

示例：

select*fromtwhereuser_id=?and(create_time<?)orderbycreate_timedesclimit20;

如果存在同时间戳并发写入，建议加 tie-breaker：

whereuser_id=?and(create_time<?or(create_time=?andid<?))orderbycreate_timedesc,iddesclimit20;

对应索引：

• (user_id, create_time, id)

优势：

• 不随页码变慢
• 能稳定利用索引有序性

3.1 对照组：只用 create_time 做游标可能不稳定

如果你的数据里存在相同时间戳：

• 错：只用create_time < lastTime，可能出现漏数据/重复数据
• 对：加 tie-breaker（create_time相等时用id）

4. 覆盖索引 + 延迟关联：解决回表放大

4.1 列表页优先覆盖索引

如果列表只需要少量列：

• 让索引覆盖返回列
• Extra: Using index

4.2 必须返回全字段：用延迟关联压缩回表次数

select*fromtwhereidin(selectidfromtwhereuser_id=?orderbycreate_timedesclimit20);

目的：

• 内层只拿 20 个 id
• 外层只回表 20 次

对照点：延迟关联只解决“回表次数”，不解决 offset 的“扫描丢弃”。

• 页码很深时：优先 seek 分页
• 页码不深但select *很重：延迟关联收益明显

5. 排序为什么会慢：filesort 与临时表

当where用的索引与order by不一致：

• MySQL 可能先过滤再排序
• 触发Using filesort

优化：

• 让联合索引同时满足 where + order
• 让排序字段方向一致（desc/asc）

6. 线上排查 checklist

• 是否大 offset：limit 100000, 20
• EXPLAIN：
  • rows是否随页码增长
  • Extra是否Using filesort/temporary
  • 是否Using index（覆盖）
• 是否select *导致回表放大

6.1 更流程化的排查顺序（从现象到动作）

1. 确认现象
   • 是否“页码越大越慢”
2. 用 EXPLAIN 看 3 个指标
   • rows是否随 offset 增长
   • Extra是否Using filesort/temporary
   • 是否覆盖索引Using index
3. 按根因选方案
   • offset 导致的扫描丢弃：改 seek 分页
   • 回表放大：覆盖索引或延迟关联
   • 排序代价：调整联合索引让 where+order 同索引