MySQL 深分页优化工作流：从 OFFSET 扫描到游标翻页

后台列表页一开始数据不多，分页查询通常写成 LIMIT 20 OFFSET 0。等数据涨到几十万、几百万行后，前几页还很快，越往后翻越慢，用户点到第 5000 页时接口可能直接卡住。

这篇文章不只给一个 SQL 改法，而是整理一套 MySQL 深分页优化工作流：先判断是不是大偏移扫描，再根据页面需求选择游标翻页、延迟关联或覆盖索引，最后用执行计划和接口耗时验证。

目标和边界：先确认分页真的需要跳页

深分页慢的根本原因通常不是 LIMIT，而是 OFFSET 让数据库先跳过大量行，再取少量结果。比如下面这条 SQL：

SELECT id, title, created_at
FROM article
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

它只返回 20 行，但数据库可能需要读过很多符合条件的排序结果，才能找到最终要返回的那 20 行。

优化前先问一个问题：业务真的需要“任意跳到第 N 页”吗？如果只是信息流、订单列表、日志列表、消息列表，用户通常只需要继续往下翻，这时游标翻页更合适。如果运营后台必须跳页，再考虑延迟关联和索引方案。

全流程总览：从慢分页到稳定分页

完整工作流可以拆成四个阶段：定位慢分页、选择翻页方式、改写 SQL、验证结果。每个阶段都有自己的检查点。

阶段一：识别 OFFSET 大扫描

先把慢查询拿出来，看它是否符合两个特征：

• 页码越大越慢，第一页很快，深页明显变慢。
• SQL 里有较大的 OFFSET，比如几万、几十万。

可以用执行计划观察排序字段和索引是否匹配：

EXPLAIN
SELECT id, title, created_at
FROM article
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

这一步的目标不是马上改 SQL，而是确认慢点是否来自“跳过大量结果”。如果筛选条件本身就很宽，排序又不能很好利用索引，深分页会把问题放大。

阶段二：优先改成游标翻页

如果页面不要求跳到任意页，优先使用游标翻页。上一次返回列表时，把最后一条记录的排序字段作为下一页游标，例如 created_at 和 id。

SELECT id, title, created_at
FROM article
WHERE status = 1
  AND (
    created_at 

这种写法不再要求数据库跳过前面 100000 行，而是从上次位置继续往后取。对信息流、消息、订单流水等“继续加载”场景，它通常比页码分页更稳。

阶段三：必须跳页时使用延迟关联

如果后台页面必须支持跳到第 N 页，可以考虑延迟关联：先用窄索引查出目标页的 id，再回表取完整字段。

SELECT a.id, a.title, a.created_at, a.author_id
FROM article AS a
JOIN (
  SELECT id
  FROM article
  WHERE status = 1
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 100000
) AS page_ids ON page_ids.id = a.id
ORDER BY a.created_at DESC, a.id DESC;

这不是把 OFFSET 变没了，而是让大偏移扫描尽量只发生在更窄的索引数据上，减少大量完整行读取。它适合“必须保留页码”的管理后台，但仍然要配合索引。

阶段四：补齐覆盖索引和排序口径

不管选择游标翻页还是延迟关联，都要让筛选条件和排序字段尽量进入同一个索引。以上面的查询为例，可以考虑：

CREATE INDEX idx_article_status_time_id
ON article (status, created_at, id);

索引字段顺序要服务于查询条件和排序口径。这里 status 是等值筛选，后面跟排序字段 created_at 和 id。如果列表还要频繁返回少量固定字段，也可以根据实际情况让查询尽量接近覆盖索引。

同时要固定排序规则。只按 created_at 排序可能遇到同一秒多条记录，翻页时容易重复或漏数据。追加 id 作为稳定排序字段，能让游标条件更可靠。

我的推荐流程

1. 先确认业务是否真的需要任意跳页。
2. 如果只是连续加载，优先改成游标翻页。
3. 如果必须跳页，使用延迟关联减少完整行读取。
4. 给筛选条件和排序字段补齐合适索引。
5. 用执行计划、扫描行数和接口耗时做前后对比。
6. 上线后观察深页访问比例，避免为了极少数跳页请求牺牲主路径。

常见误区

误区一：只把每页条数调小

每页从 50 条改成 20 条会减少返回数据量，但不能解决大偏移扫描。页码足够深时，跳过大量结果的成本仍然存在。

误区二：游标只用 id

如果页面按时间倒序展示，只用 id 当游标可能和页面排序不一致。游标字段应和排序字段保持一致，常见组合是 created_at + id。

误区三：延迟关联后就不用索引

延迟关联只是减少回表读取，底层排序和过滤仍然依赖索引。没有合适索引时，深页查询仍可能很慢。

速查表

总结一下，MySQL 深分页优化的关键不是背一个固定 SQL，而是先判断交互需求，再选择翻页方式。能用游标就不要硬跳页；必须跳页时再用延迟关联和覆盖索引减轻成本。最后一定要用数据验证，而不是只看 SQL 写得更复杂。