MySQL 分页越翻越慢怎么改：从深分页到覆盖索引的一个列表接口

订单列表在第一页只要几十毫秒，运营人员翻到第 200 页却要等上几秒，慢日志里常见的罪魁祸首是 LIMIT 100 OFFSET 19900。MySQL 不是瞬间跳到第 19901 行，而是先按排序路径找到并丢掉前面的记录，再返回这一页。真正稳妥的改法，是用上一页最后一条记录做游标，并让索引顺着同一套排序直接取数。

深分页优化的核心不是把 OFFSET 写得更漂亮，而是把“跳过 N 行”改成“从上一页边界继续扫描”，再用 EXPLAIN 确认筛选、排序和回表成本确实下降。

订单列表为什么在 OFFSET 变大后变慢

先做一个最小实验。假设列表只看已支付订单，并按下单时间倒序：

SELECT id, created_at, buyer_name, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100 OFFSET 19900;

这个写法的语义很直白，但深页的工作量也被写进了 SQL：数据库需要找到符合条件的有序结果，跳过 19900 条，再交付 100 条。即使 LIMIT 让排序可以提前停止，偏移量本身仍然会随页码增长。

这里先别急着加一堆索引。先看计划：

EXPLAIN
SELECT id, created_at, buyer_name, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 100 OFFSET 19900;

重点看 key 是否命中预期索引、rows 是否远大于 100，以及 Extra 是否出现 Using filesort 或 Using index。官方文档也提醒，带有 ORDER BY 和 LIMIT 的查询可能优先利用有序索引，但最终选择仍取决于过滤条件和优化器判断。

图里的结果端明确标成 100 rows，它代表接口真正交付的记录数；前面的长段才是深 OFFSET 带来的额外扫描。

先把排序边界固定下来

游标分页的第一步不是改成 WHERE id ，而是确定一个不会含糊的排序键。只按 created_at 排序时，同一秒创建的订单可能很多；如果上一页刚好截断在这一组记录中，下一页就可能重复或漏掉数据。

本例把 created_at 和唯一的 id 组成稳定顺序：

ORDER BY created_at DESC, id DESC

接口返回最后一条记录的两个值，命名为 last_created_at 和 last_id。客户端只需要把它们带到下一次请求，不要把数据库内部的页码当作永久游标。

把 OFFSET 改成上一页游标

假设上一页最后一条订单是 last_created_at = '2026-08-30 09:10:00'、last_id = 8120，下一页可以这样取：

SELECT id, created_at, buyer_name, total_amount
FROM orders
WHERE status = 'paid'
  AND (
    created_at 

这条边界条件对应一个很具体的顺序：时间更早的行都在下一页；时间相同的行，再取编号更小的行。它不需要从第一页重新数到第 200 页，数据库可以从联合索引的相应范围继续向后读。

图中的 100 rows 是这一页的固定上限，而不是需要先跳过的数量；真正的起点由 last_created_at 和 last_id 给出。

用覆盖索引减少列表查询回表

查询还要返回 buyer_name 和 total_amount，因此只建立状态、时间和编号索引未必能完全覆盖它。可以在测试环境比较下面这条索引：

CREATE INDEX idx_orders_status_created_id
ON orders (status, created_at, id, buyer_name, total_amount);

联合索引的左侧先放等值过滤列 status，随后放排序和游标边界用到的 created_at、id，最后补齐列表展示字段。这样做可能增大写入成本和索引体积，所以不要把所有详情字段都塞进来。列表页与详情页应当分开衡量。

再次执行 EXPLAIN，如果计划中的 key 是 idx_orders_status_created_id，且 Extra 出现 Using index，说明这次读取可以直接从索引记录得到所需列。若仍有 Using filesort，先检查排序列顺序、比较方向和条件是否与索引设计一致，不要只盯着索引名字。

本地运行与验收：别只测第一页

把接口参数从 page、size 改成 size、last_created_at、last_id 后，需要验证连续性。可以准备 30000 条测试订单，分别请求第一页、带游标的第二页和重复游标请求。

• 第一页不带游标，返回 100 条，并给出最后一条的 created_at 与 id。
• 第二页带上这两个值，第一条不能与第一页重复，且整体仍按倒序排列。
• 重复发送同一个游标，结果顺序应稳定；数据变更导致的实时漂移要在接口文档中说明。
• 用 EXPLAIN 对比浅页和深页，关注扫描行数与排序方式，而不是只比较一次网络耗时。

常见问题

游标分页还能跳到指定页吗？

不能像 OFFSET 一样廉价地任意跳页。如果产品必须显示页码，可以保留浅页页码，深页改成“加载更多”，或维护受控的游标快照。

为什么只用 id

如果页面按下单时间排序，单独按 id 比较并不等价于时间顺序。只有排序键和边界条件保持同一顺序，才能避免漏数和重复。

覆盖索引越宽越好吗？

不是。索引会增加存储和写入维护成本。只覆盖列表必要字段，并用真实读写负载和 EXPLAIN 验证收益。

收尾：把分页优化落到可核对的执行计划

这次改造可以归结为三件事：固定 created_at DESC, id DESC 的稳定顺序，用 last_created_at 与 last_id 代替深 OFFSET，再用 idx_orders_status_created_id 验证过滤、排序和回表路径。真正上线前，别省掉深页、重复游标和数据变化这三组验收。