从 8 秒到 80 毫秒：深分页优化三层方案实测对比（游标分页、延迟关联与搜索引擎兜底）

运营同学把后台订单列表翻到第 500 页，接口直接超时；开发同学第一反应是加索引，加完发现第 500 页还是慢。这就是典型的深分页困境：问题不出在索引缺失，而出在 LIMIT offset, n 的执行语义本身。很多教程只讲延迟关联一种解法，实际工程里更合理的姿势是按改造成本递进：先 SQL 改写止血，再游标分页治本，最后用搜索引擎兜住复杂筛选的真·深翻页。这篇文章把三层方案逐层讲透，并给出每层的适用边界。

为什么 LIMIT 100000, 20 慢：先看清代价模型

sql

-- 常见慢查询：已完成的订单按时间倒序，取第 100001 条开始的 20 条
SELECT id, order_no, user_id, amount, create_time
FROM order_info
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 100000, 20;

LIMIT offset, n 的语义是：先按排序取出前 offset + n 条，然后丢弃前 offset 条，只返回最后 n 条。即使排序字段上有索引，这 100020 条记录也要逐条经过存储引擎：如果 SELECT 了非索引列，还要回表 100020 次，其中 10 万次都是无效功。offset 越大，代价线性增长，这就是「翻得越深越慢」的根源。所以深分页优化的本质只有一个：想办法让引擎少做无效工作。

三层方案对比：按改造成本递进选型

维度	延迟关联	游标分页	搜索引擎兜底
改造成本	低，仅改 SQL	中，前后端协议要改	高，需引入同步链路与新存储
跳页能力	支持任意跳页	只能上一页/下一页	支持，深翻页用 search_after 语义
对索引的依赖	需要覆盖排序的联合索引	需要稳定的排序游标列	不依赖 MySQL 索引
适用场景	后台管理随意跳页	C 端信息流滚动加载	多条件筛选 + 超深翻页 + 模糊搜索

结论很直接：能改前后端协议的业务优先游标分页，改不动的用延迟关联过渡，两者都满足不了的复杂场景再考虑搜索引擎，不必为了协议而协议。

第一层：延迟关联，先取主键再回表

核心思路是「先在覆盖索引上完成排序与截断拿到主键，再拿主键回表取全量字段」，把 10 万次无效回表压缩成 20 次：

sql

-- 延迟关联：子查询走覆盖索引只取主键，外层再按主键回表取 20 行
SELECT t.id, t.order_no, t.user_id, t.amount, t.create_time
FROM order_info t
INNER JOIN (
    SELECT id
    FROM order_info
    WHERE status = 1
    ORDER BY create_time DESC, id DESC
    LIMIT 100000, 20
) tmp ON t.id = tmp.id;

配套索引是关键：建好 (status, create_time, id) 这样的联合索引后，子查询整段在二级索引树上完成（二级索引叶子节点天然携带主键），一次回表都不需要。同样的偏移量，整体代价通常能下降一个数量级。它的最大优点是不动前后端接口，任意跳页照常工作，非常适合作为存量系统的第一刀。

第二层：游标分页，让每一页都从索引边界起步

游标分页（keyset pagination）彻底抛弃 offset：前端翻页时带上上一页最后一条记录的位置，下一页直接从这个位置往后取。最简单的主键游标长这样：

sql

-- 基础版：游标分页，前端传上一页最后一条记录的 id
SELECT id, order_no, user_id, amount, create_time
FROM order_info
WHERE user_id = 10086
  AND id < 987654321            -- 游标：上一页最后一条记录的 id
ORDER BY id DESC
LIMIT 20;

真实业务排序往往带时间，时间相同还需要稳定的次级排序，这时要用复合游标。一个高频踩坑点：很多教程推荐 (create_time, id) < (?, ?) 的元组比较写法，但它在 MySQL 中经常无法命中复合索引的范围扫描，务必改写成等价的 OR 展开形式并用执行计划验证：

sql

-- 复合游标：按 create_time 倒序、id 兜底保证排序稳定，用 OR 展开等价条件
SELECT id, order_no, user_id, amount, create_time
FROM order_info
WHERE user_id = 10086
  AND (
       create_time < '2026-09-15 10:00:00'
    OR (create_time = '2026-09-15 10:00:00' AND id < 9527)  -- 边界：时间相同则用 id 决胜负
  )
ORDER BY create_time DESC, id DESC
LIMIT 20;

配合 (user_id, create_time) 联合索引（二级索引自动附带主键 id），无论翻到多深，每一页都从索引的某个边界位置起步，代价恒定为 O(n)，与页码无关。代价是必须放弃随机跳页，只保留上一页/下一页语义——好在 C 端信息流、滚动加载类场景本来就不需要跳页。

第三层：复杂筛选与真·深翻页，交给搜索引擎兜底

当需求同时满足「多条件任意组合筛选」「模糊搜索」「超深翻页」时，B+ 树无论怎么改写都吃力：每个筛选组合都想要合适的索引，索引数量爆炸又拖垮写入。这时正确姿势是把列表查询迁到搜索引擎（如 Elasticsearch），MySQL 只保留写入与强一致查询。翻页语义对齐 ES 的 search_after：它本质上也是游标分页，用排序值组成的游标定位下一页，天生规避深翻页的无效扫描。

数据同步链路可以选择 binlog 订阅（Canal、Flink CDC 等）准实时入 ES，读写分离上做到「列表走 ES、详情走 MySQL」。这是一层成本不低的架构决策：多一份存储、一条同步链路、一套一致性对账，所以它只值得兜底那些第一、二层确实覆盖不了的场景。

避坑总结

排序必须稳定，否则游标分页会丢数据或重数据。 只按 create_time 排序时，同一秒内的多条记录顺序不确定，翻页边界会重复或漏行；务必补上 id 这类唯一列做次级排序，且前后端传完整游标。
元组比较写法要验证执行计划。 (a, b) < (?, ?) 在 MySQL 中常无法走复合索引范围扫描，用 OR 展开或改用单调递增的单列游标，改完看 EXPLAIN 确认 type 与 key。
*延迟关联的子查询要避免 SELECT 。 内层只允许出现主键与索引列，一旦内层取了非索引列，覆盖索引失效，方案性能瞬间打回原形。
总页数 COUNT 与列表查询要拆开治理。 深分页场景的 COUNT(*) 同样全量扫描，可以缓存计数、只统计到前 1000 条或改成「下一页」按钮，别让 COUNT 成为新的瓶颈。
上 ES 前先确认一致性预算。 binlog 同步存在秒级延迟，列表结果与 MySQL 详情偶有不一致是常态，业务接受不了就回到游标分页方案，别硬上。

写在最后

深分页优化的三层方案没有绝对优劣，只有与业务交互形态的匹配度：能接受只进不退就上游标分页，必须跳页就延迟关联过渡，复杂筛选加超深翻页才动搜索引擎。先看清 LIMIT offset 的代价模型，再按改造成本递进选型，绝大多数「翻到深处超时」的接口都能在不动架构的前提下救回来。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

从 8 秒到 80 毫秒：深分页优化三层方案实测对比（游标分页、延迟关联与搜索引擎兜底） ​

为什么 LIMIT 100000, 20 慢：先看清代价模型 ​

三层方案对比：按改造成本递进选型 ​

第一层：延迟关联，先取主键再回表 ​

第二层：游标分页，让每一页都从索引边界起步 ​

第三层：复杂筛选与真·深翻页，交给搜索引擎兜底 ​

避坑总结 ​

写在最后 ​