深分页优化：OFFSET 100000 为什么慢，以及游标分页怎么救

运营同学反馈后台的订单列表翻到 200 页以后越来越卡，第 500 页要 4 秒才出来。看了眼 SQL 我血压就上来了：

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

这是每个后端都迟早要踩的坑——深分页。这篇讲清楚为什么慢，以及怎么救。

OFFSET 慢在哪

LIMIT 20 OFFSET 100000 的语义是：找到前 100020 条，扔掉前 100000 条，返回最后 20 条。

MySQL 没有"直接跳到第 100000 条"的魔法。它只能老老实实扫：

按 ORDER BY 的顺序遍历（有索引走索引顺序）
每过一行，OFFSET 计数减一
计数减到 0 后才开始真正收集数据返回

OFFSET 越大，白扫的行越多。更要命的是 SELECT *：如果排序走的是二级索引，每行还得回表查完整数据，10 万次回表谁都顶不住。

方案一：游标分页（推荐）

核心思想：别数"跳过多少条"，而是记住"上一页最后一条是谁"。

第一页：

SELECT * FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

记录本页最后一条的 (created_at, id)，比如 ('2022-07-01 10:00:00', 9527)。下一页：

SELECT * FROM orders
WHERE (created_at, id) < ('2022-07-01 10:00:00', 9527)
ORDER BY created_at DESC, id DESC
LIMIT 20;

WHERE 条件直接命中索引定位到起点，扫的行数永远只有 20 行，翻到第 5000 页和第 2 页一样快。这就是游标分页（cursor-based pagination），也叫 keyset pagination。

为什么要带上 id 做第二排序键？因为 created_at 可能重复（同一秒两单），单靠它定位会漏数据或重复。加一个唯一列做 tie-breaker 就严谨了。

-- 配套索引
CREATE INDEX idx_created_id ON orders(created_at, id);

代价：不能跳页

游标分页的短板：只能"上一页/下一页"，没法直接跳第 37 页。所以适用场景是：

无限滚动的 feed 流（天然就是游标的形状）
后台数据列表的"加载更多"
数据导出、同步任务

前端传参也随之变化：

// 不再传 page=37，而是传上一页末尾的游标
GET /api/orders?cursor=2022-07-01T10:00:00_9527&limit=20

方案二：覆盖索引优化 OFFSET

如果产品坚持要保留页码跳转（后台系统常见），可以用覆盖索引 + 延迟关联抢救一下：

-- 先用覆盖索引快速定位 20 个 id（不回表）
SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- 再 JOIN 回去取完整行
SELECT o.*
FROM orders o
JOIN (
  SELECT id FROM orders
  ORDER BY created_at DESC
  LIMIT 20 OFFSET 100000
) t ON o.id = t.id;

子查询里索引已经覆盖了 id（二级索引叶子自带主键），扫 100020 行索引不回表，快很多；最后只回表 20 次。实测 4 秒的查询能压到 200ms 左右。

治标，但胜在不用改前端交互，老系统救急很合适。

方案三：业务上限制深度

最诚实的方式：很多场景根本不需要深分页。

搜索引擎的做法：Google 翻几百页后也拒绝继续，提示"结果过多请细化关键词"
后台系统：加筛选条件（时间范围、状态），把结果集先缩小
导出需求：别分页了，走异步导出任务

和产品聊聊，"第 500 页"往往是个伪需求。

三个方案怎么选

方案	性能	能跳页	改造成本
游标分页	任何深度都 O(pageSize)	❌	前端改交互
覆盖索引延迟关联	大幅改善但仍随深度退化	✅	只改 SQL
限制分页深度	根治	✅	说服产品

我的实践：新接口一律游标分页；老接口先上覆盖索引止血，排期允许再切游标。

小结

OFFSET 100000 = 扫描并扔掉 10 万行，SELECT * 还会放大成 10 万次回表
游标分页：WHERE (排序键, id) < 上一页末尾值，任何深度都快
排序键要加唯一列做 tie-breaker，防止重复值漏数据
必须跳页就用覆盖索引子查询延迟关联
深分页也可能是伪需求，先问产品

下次设计列表接口时，把"这个接口最大会翻到多少页"列进评审问题清单。分页方式的选择，决定了系统三年后的性能天花板。