MySQL 深分页：LIMIT 100000, 10 为什么越来越慢？

一句话结论（30s）

深分页慢的本质是 offset 越大、被丢弃的随机回表 IO 越多，因为 LIMIT 100000, 10 会扫描前 100010 条并回表、再丢掉前 100000 条。关键设计是两种优化：延迟关联让扫描在索引层完成、只对返回的 10 个 id 回表；游标分页用索引 range scan 定位替代 offset 跳过、实现与 offset 无关的恒定性能。权衡在于：延迟关联仍要顺序扫描 offset 条索引记录，游标分页只支持上一页/下一页、不能跳页。

核心原理（2min）

LIMIT 100000, 10 的语义是取前 offset+limit 条、丢弃前 offset 条，若走二级索引 idx_create_time，每条扫描记录都要回表读聚簇索引，产生 100010 次随机 IO。方案一延迟关联：子查询只 SELECT id 走覆盖索引 (create_time, id)，100010 次扫描全在索引叶子节点完成、零回表，外层 JOIN 只对 10 个 id 做主键等值回表，随机回表从 100010 次降到 10 次，提升数十到数百倍。方案二游标分页：记录上一页最后一条的 create_time，下一页用 WHERE create_time > 上次值 从索引直接 range scan 定位，每页 O(1)、与 offset 无关，适合无限滚动但不支持跳页。

底层深入（5-10min）

问题

SELECT * FROM orders ORDER BY create_time LIMIT 100000, 10;

这个查询在第 1 页时可能只需要 5ms，第 10000 页却需要 2s。为什么 LIMIT 的 offset 越大越慢？

慢的根因

LIMIT 的执行语义是：取满足条件的前 offset + limit 条记录，丢弃前 offset 条，返回后 limit 条。 也就是说 LIMIT 100000, 10 实际上扫描了 100010 条记录。

更致命的是，如果走二级索引 idx_create_time，每条扫描到的记录都需要回表（去聚簇索引读取完整行），100010 次随机 IO 是性能杀手。

💭 想一想：真正拖慢它的到底是”扫 100010 条”还是”回表 100010 次”？——是回表。扫描本身在索引叶子页里是顺序的，很快；但每扫一条就得到聚簇索引随机读一次完整行，100010 次随机 IO 才是元凶。想通这一点，后面两个优化方案的着力点就都清楚了：一个免回表、一个免扫描。

执行过程:
  idx_create_time 索引扫描:
    → 100010 次随机读索引页
    → 100010 次回表（每次随机读聚簇索引页）
  → 丢弃前 100000 条
  → 返回后 10 条

优化方案一：延迟关联（子查询 + JOIN）

核心思想：让”扫描 100010 条”只在索引层面完成，避免回表。

-- 优化后
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders
    ORDER BY create_time
    LIMIT 100000, 10
) AS t ON o.id = t.id;

子查询只 SELECT id — 走覆盖索引 (create_time, id)，不需要回表，100010 次扫描全在索引 B+ 树的叶子节点完成。外层 JOIN 只对 10 个 id 做一次回表（主键等值查找）。

子查询: 扫描 100010 条 → 全在索引层，无回表 → 返回 10 个 id
外层: 10 次主键等值查找（聚簇索引 O(log n)）→ 回表 10 次

随机回表从 100,010 次降到 10 次。 性能提升数十到数百倍。

💭 想一想：延迟关联已经快了几百倍，为什么还说它有”致命弱点”？——因为它只省了回表、没省扫描：子查询仍要把前 100010 条索引记录顺序扫过去，才能定位到那 10 个 id。offset 到百万级时，即使纯索引顺序扫描也会变慢，所以它不是终极答案，只能算”缓解回表”。

优化方案二：游标分页（Keyset Pagination）

记录上一页的最后一条数据的时间戳，下一页从该时间戳之后开始：

-- 第 1 页
SELECT * FROM orders ORDER BY create_time LIMIT 10;
-- 最后一条: create_time = '2024-01-15 12:00:00'

-- 第 2 页
SELECT * FROM orders
WHERE create_time > '2024-01-15 12:00:00'
ORDER BY create_time LIMIT 10;

直接从 create_time 索引 range scan 定位到第 11 条，不需要跳过任何记录。每一页都是 O(1) 的定位（B+ 树 range scan 直接从目标位置开始），offset 无论多大性能一致。

限制：只能”上一页/下一页”，不能跳到第 N 页（移动端常见的”无限滚动”恰好只需要这种模式）。

💭 想一想：为什么游标分页能做到”offset 多大都恒定 O(1)”？——因为它根本不 skip：用 WHERE create_time > 上次值 让 B+ 树直接从目标位置开始 range scan，跳过了前面所有记录。代价是它只知道”从哪条往后取”，不知道”第 N 页”——所以跳页能力被换成了恒定性能。

两种方案对比

延迟关联消除了回表（随机 IO），但仍需要扫描 offset 条索引记录（顺序 IO）。当 offset 极大（如百万级）时，即使索引扫描也会变慢，此时游标分页是终极方案。

总结

深分页慢的本质：offset 越大 = 被丢弃的随机回表 IO 越多。 优化方向：① 让扫描在索引层面完成、仅对返回行回表（延迟关联）；② 用索引定位替代 offset 跳过（游标分页）。

章末提问

追问 1：LIMIT 100000, 10 为什么慢？根因是什么？

回答思路：结论先行——根因是 offset 越大、被丢弃的随机回表 IO 越多。因为 LIMIT 的语义是”取前 offset+limit 条、丢前 offset 条”，实际扫了 100010 条；若走二级索引，每条扫描记录都要回表读聚簇索引，产生 100010 次随机 IO，而这些 IO 换来的行大部分被丢弃，纯属浪费。

追问 2：延迟关联为什么能快？它的局限是什么？

回答思路：结论先行——快在”让扫描留在索引层、把回表从 10 万次降到 10 次”；局限是仍要顺序扫完 offset 条索引记录。因为子查询只 SELECT id、走覆盖索引（create_time, id），100010 次扫描全在索引叶子页完成、零回表，外层 JOIN 只对 10 个 id 做等值回表。但 offset 到百万级时，纯索引顺序扫描也慢，所以它只能缓解、不能根治。

追问 3：游标分页和延迟关联的本质区别是什么？各适合什么场景？

回答思路：结论先行——延迟关联”省回表但仍扫 offset 条”，游标分页”用索引定位彻底不 skip”。因为游标分页用 WHERE create_time > 上次值 让 B+ 树直接从目标位置 range scan，性能与 offset 无关、恒定 O(1)，但只能顺序翻页；延迟关联支持任意跳页。所以”无限滚动/上下页”用游标，必须跳页的后台管理界面用延迟关联。