MySQL 深分页优化实战：用延迟关联减少无效扫描

后台订单列表、日志列表、流水明细这类页面，经常会遇到一个现象：第一页很快，第 1000 页开始明显变慢，越往后越卡。很多同学第一反应是“加索引”，但深分页的问题往往不是完全没索引，而是数据库扫描并丢弃了太多无用行。

本文用一个订单列表场景，演示如何把普通的 LIMIT offset,size 改造成“覆盖索引先定位主键，再回表取详情”的延迟关联方案。它不神秘，但在大表后台列表里很实用。

摘要

本文会完成四件事：复现深分页变慢的原因、设计适合排序条件的联合索引、用延迟关联改写 SQL、给出游标式分页的替代方案。适合正在维护 MySQL 列表查询、管理后台、订单流水和日志检索的开发者阅读。

适合人群

• 能写基本 SQL，但列表页越翻越慢的后端同学。
• 正在做订单、账单、日志、工单等大表分页查询的开发者。
• 想理解覆盖索引、回表、延迟关联之间关系的 MySQL 使用者。

目录

• 为什么深分页会越来越慢
• 先把排序条件变成可用索引
• 延迟关联：先拿主键，再取完整行
• 什么时候改成游标式分页
• 上线前检查清单

为什么深分页会越来越慢

假设后台订单表按创建时间倒序分页，SQL 可能是这样：

SELECT id, order_no, user_id, amount, status, created_at
FROM orders
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 50000, 20;

这条 SQL 的意思是：找到符合条件并排好序的结果，跳过前 50000 条，再返回 20 条。问题就出在“跳过”上。即使最后只返回 20 行，数据库也可能需要沿着索引读过大量记录，甚至回表取出很多最终会被丢弃的数据。

当偏移量越来越大，扫描和丢弃的成本也越来越高。列表页看起来只是“翻页”，数据库实际做的是“从头数到目标位置”。

先把排序条件变成可用索引

优化前先确认过滤条件和排序条件是否能共用一个联合索引。以上面的订单查询为例，常见索引可以这样设计：

ALTER TABLE orders
ADD INDEX idx_status_created_id (status, created_at, id);

这个索引把 status 放在前面用于过滤，把 created_at 和 id 放在后面用于稳定排序。这里的 id 很重要：当多个订单创建时间相同，继续用 id 排序可以避免翻页时出现重复或漏行。

可以用下面的方式观察访问路径：

EXPLAIN
SELECT id, order_no, user_id, amount, status, created_at
FROM orders
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 50000, 20;

如果看到结果需要额外排序，或者扫描行数远大于返回行数，就要继续收缩扫描范围。

延迟关联：先拿主键，再取完整行

延迟关联的核心思路是：第一次查询只在覆盖索引里拿到目标页的主键，第二次再按主键回表取完整字段。这样可以减少“扫描了很多行，还反复回表取宽字段”的浪费。

SELECT o.id, o.order_no, o.user_id, o.amount, o.status, o.created_at
FROM orders AS o
JOIN (
    SELECT id
    FROM orders
    WHERE status = 1
    ORDER BY created_at DESC, id DESC
    LIMIT 50000, 20
) AS page_ids ON page_ids.id = o.id
ORDER BY o.created_at DESC, o.id DESC;

内层查询只读取联合索引里的字段，先确定这页需要的 20 个 id。外层再根据这 20 个 id 回表，读取订单号、金额、用户等详情字段。对于字段很多、行记录较宽的大表，这种改写通常更稳。

为什么叫延迟关联

因为它把“拿完整行”的动作延后了。先用轻量索引完成定位，再用少量主键取详情。它不能让大偏移完全消失，但能把大量无意义的回表成本降下来。

注意排序一致性

内层和外层排序字段要保持一致，尤其是有相同创建时间时，要带上 id 作为稳定排序字段。否则页面顺序可能在两次查询之间发生轻微变化。

什么时候改成游标式分页

如果产品不要求跳到任意页，只需要“下一页、上一页、加载更多”，游标式分页会更适合。它不再传第几页，而是传上一页最后一条记录的位置。

SELECT id, order_no, user_id, amount, status, created_at
FROM orders
WHERE status = 1
  AND (created_at, id) 

这种写法让数据库从上次结束位置继续往后读，不需要从第一页一路数到第 50000 行。对于消息流、日志流、订单流水，游标式分页通常比深分页更稳定。

上线前检查清单

• 确认联合索引顺序。 过滤字段在前，排序字段紧跟其后，不要只给单列随手建索引。
• 确认返回字段宽度。 如果列表页不展示大字段，就不要把详情、备注、长文本一起查出来。
• 确认排序稳定。 时间字段可能重复，建议加唯一字段作为第二排序条件。
• 确认最大页限制。 管理后台可以限制最多翻到某个页数，超出后引导使用筛选条件。
• 确认慢查询日志。 上线后观察扫描行数、响应时间和高频查询条件，避免只在测试数据上判断。

常见坑

• 只改 SQL，不改索引。 延迟关联依赖内层查询能走覆盖索引，否则效果有限。
• 忽略业务筛选。 如果用户经常按时间范围、状态、店铺筛选，索引要围绕真实查询条件设计。
• 把所有分页都改成深分页优化。 小表、小偏移不一定需要复杂改写，先看数据量和慢查询证据。
• 让用户无限跳页。 真正的大数据浏览更适合搜索、筛选和游标式加载，而不是翻到第几万页。

总结

MySQL 深分页慢，本质上是大偏移带来的无效扫描和无意义回表。优化路径可以分三步：先让过滤和排序走上联合索引，再用延迟关联减少宽行回表，最后在适合的业务场景下改成游标式分页。这样处理后，列表查询的压力会更可控，也更容易解释每一步优化为什么有效。