MySQL：深分页LIMIT 1000000,20要8秒，索引都建了为什么越翻越慢？延迟关联怎么写才对？

目录

一、问题的本质：深分页慢在哪里

先把现象说清楚。一张订单表，主键 id，业务索引 idx_create_time(create_time)，执行 SELECT * FROM orders ORDER BY create_time LIMIT 1000000,20，耗时约 8 秒。很多人第一反应是"索引没生效"，但 EXPLAIN 显示 type=index，key=idx_create_time，索引确实被用上了。慢的原因不是"没走索引"，而是"走索引的方式"决定了它必须做大量无用功。

在 MySQL 的 LIMIT offset, count 语义中，offset 不是"跳过"的指令，而是"先取出、再丢弃"的计数。服务器层必须按顺序把前 offset+count 行都定位出来，然后丢弃前 offset 行，只返回最后 count 行。也就是说，翻到第 5 万页和翻到第 1 页，返回的数据量一样，但前者要"摸"过 100 万行。本文评述：这是 SQL 标准与存储引擎实现共同造成的结构性代价，不是某个参数能调掉的，任何"加个索引就快了"的直觉在这里都会失效。

更关键的是，如果查询列不是索引覆盖的（比如 SELECT *），那么这 100 万行的每一行都要回表——按主键去聚簇索引里取完整行。回表是随机 I/O，代价远高于顺序扫描二级索引。于是成本被放大成"顺序扫索引 + 百万次随机回表 + 丢弃"，8 秒并不夸张。下面用一个简化的成本表把这条链路摊开。

表中可见，真正随 offset 线性膨胀且最贵的是②回表。这解释了为什么"索引都建了"仍然慢：索引解决的是"找到行"，不解决"取回行"和"丢弃行"。笔者认为，把深分页问题重新定义为"回表次数 × offset"，是理解所有优化手段的钥匙——延迟关联减少回表次数，覆盖索引消除回表，游标分页消除 offset，三者恰好对应三个乘数。

二、B+ 树、回表与排序：成本链路的逐层拆解

2.1 二级索引叶子节点存的是什么

InnoDB 的二级索引（secondary index）叶子节点存储的是"索引列值 + 主键值"，而不是整行数据。这是理解回表的前提。当查询需要索引之外的列时，必须拿主键值回到聚簇索引（clustered index）再查一次，这就是回表（bookmark lookup）。本文评述：二级索引本质上是"索引列的排序副本 + 主键指针"，它的存在意义是加速定位，而非替代数据读取；把二级索引当成"小表"来用（即覆盖索引）才是发挥其价值的方式。

在深分页场景中，ORDER BY create_time LIMIT 1000000,20 会沿 idx_create_time 的叶子链表顺序前进，每读到一个索引项就拿到主键，然后回表取整行。前 100 万行全部回表，最后只输出 20 行。这 100 万次回表就是 8 秒的主体。若缓冲池（buffer pool）足够大、数据全在内存，回表退化为内存随机访问，耗时会显著下降——这也解释了为什么同一 SQL 在不同机器上耗时差异巨大，本文评述：深分页的耗时高度依赖缓冲池命中率，脱离硬件谈"8 秒"没有普遍意义，但成本结构是普适的。

2.2 排序是否真的发生了

如果 ORDER BY 的列与索引顺序一致，优化器可以省去排序，直接按索引顺序输出，EXPLAIN 的 Extra 不出现 Using filesort。但一旦 ORDER BY 涉及多列且与索引前缀不匹配，或者 WHERE 条件导致索引选择变化，就会退化为 filesort：先取出满足条件的全部行，再在内存/磁盘排序，最后取前 offset+count 行。此时成本又多了一层"全量取行 + 排序"，深分页会更慢。本文评述：判断深分页慢因，第一步永远是看 EXPLAIN 里有没有 Using filesort，有则先解决排序，再谈分页优化，否则优化方向会跑偏。

2.3 一个可复现的量化模型

为便于推理，构造一个简化成本模型（模拟数据，用于说明量级关系，非实测）：设单次回表平均代价为 c_r，索引顺序扫描单行代价为 c_s，则深分页总代价近似为 (offset+count) × (c_s + c_r)。若 c_r ≈ 10 × c_s（随机 I/O 远贵于顺序扫描），则回表占总代价约 91%。延迟关联把回表次数从 offset+count 降到 count，理论上可把总代价压到原来的约 1/10 量级。本文评述：这个模型刻意忽略了缓冲池命中、页预读、自适应哈希等真实因素，但它抓住了主要矛盾——回表次数与 offset 的乘积，足以指导工程决策。

-- 复现实验的骨架（请在测试库执行，勿在生产直接跑）
-- 1) 造一张百万级表
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  create_time DATETIME NOT NULL,
  KEY idx_create_time (create_time)
) ENGINE=InnoDB;

-- 2) 用存储过程或脚本灌入 200 万行（略），并 ANALYZE TABLE orders;

-- 3) 对比三条 SQL 的耗时与 Handler_read 计数
SELECT * FROM orders ORDER BY create_time LIMIT 1000000,20;
SELECT id FROM orders ORDER BY create_time LIMIT 1000000,20;
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY create_time LIMIT 1000000,20) t
  ON o.id = t.id;

建议用 SHOW SESSION STATUS LIKE 'Handler_read%' 观察回表次数，比单纯看耗时更能定位问题。关于 InnoDB 索引结构的权威说明，可参考 MySQL 官方手册的 InnoDB Index 章节（dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html），以及经典教材《High Performance MySQL》第 3 版第 5 章。

三、延迟关联：写法、原理与常见误用

3.1 正确写法

延迟关联（deferred join）的核心是：先在覆盖索引上完成"定位 + 偏移丢弃"，只对最终需要的 count 行回表。标准写法如下：

-- 原始深分页
SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 20;

-- 延迟关联：子查询只取主键，外层按主键回表
SELECT o.*
FROM orders o
JOIN (
  SELECT id
  FROM orders
  ORDER BY create_time
  LIMIT 1000000, 20
) AS t ON o.id = t.id;

子查询 SELECT id ... ORDER BY create_time 在 idx_create_time 上是覆盖索引扫描：叶子节点已有 create_time 和主键 id，无需回表。它扫过 100 万行、丢弃 100 万行，但只输出 20 个主键。外层再用这 20 个主键做 20 次回表。总回表次数从约 100 万降到 20。本文评述：延迟关联并没有消除"扫描 100 万行"这个动作，它消除的是"扫描 100 万行并逐行回表"中最贵的那部分；因此它能把 8 秒降到百毫秒级，但无法让深分页变成 O(1)。

3.2 为什么"延迟"能生效：执行顺序的细节

MySQL 8.0 对派生表（derived table）默认可能做物化（materialization），也可能合并（merge）。如果子查询被合并进外层，延迟关联就失效了。实践中需确认 EXPLAIN 中派生表是 DERIVED 且被物化为临时表，或使用 EXPLAIN FORMAT=TREE 观察是否先做 LIMIT 再 JOIN。若优化器选择了不理想的计划，可用 /*+ NO_MERGE(t) */ 提示（MySQL 8.0 支持优化器提示）。本文评述：延迟关联不是"写了就快"，它依赖优化器按预期执行；不验证执行计划的优化等于赌博。

3.3 常见误用清单

关于派生表物化与合并的官方说明，见 MySQL 8.0 手册 Optimization of Derived Tables（dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html）。另外，Percona 博客有大量关于分页优化的实测文章（percona.com/blog），可作为工程验证的补充阅读。

四、游标分页与覆盖索引：从根上绕开偏移

4.1 游标分页（Keyset Pagination）

游标分页不传页码，而传"上一页最后一条记录的排序键值"，SQL 变成范围条件：

-- 第一页
SELECT * FROM orders ORDER BY create_time, id LIMIT 20;

-- 后续页：传入上一页最后一行的 (create_time, id)
SELECT * FROM orders
WHERE (create_time, id) > ('2024-05-01 10:00:00', 1234567)
ORDER BY create_time, id
LIMIT 20;

这里 (create_time, id) 是复合排序键，必须与索引顺序一致（建议建 KEY idx_ct_id(create_time, id)）。MySQL 8.0 支持行构造器比较，能正确利用复合索引做范围定位。这样每页都是"从某个位置开始取 20 行"，与页码无关，复杂度 O(count)。本文评述：游标分页是唯一能把深分页从 O(offset) 降到 O(count) 的方案，但它牺牲了"随机跳页"能力——用户不能直接跳到第 5000 页，只能"下一页/上一页"。这是产品语义与性能的权衡，不是纯技术问题。

4.2 覆盖索引：让回表消失

若业务确实需要随机跳页，且返回列不多，可把返回列全部放进索引，构造覆盖索引。例如列表页只需 id, user_id, amount, create_time，可建 KEY idx_cover(create_time, user_id, amount)（id 隐含在主键中），查询只读索引即可。代价是索引变大、写入变慢。本文评述：覆盖索引是"用写放大换读性能"的典型交易，适合读多写少、返回列稳定的列表页；对 SELECT * 的宽表不现实。

关于 keyset pagination 的系统性讨论，可参考 Use The Index, Luke 的分页章节（use-the-index-luke.com/no-offset），该站点对"索引顺序即分页顺序"的讲解非常透彻。视频方面，MySQL 官方 YouTube 频道的索引与优化器讲座（youtube.com/@MySQL）可作为补充。

五、优化器成本模型与执行计划的再审视

MySQL 优化器基于成本（cost-based）选择执行计划，成本由 I/O 成本与 CPU 成本加权构成，相关常量可在 server_cost 与 engine_cost 表中查看（MySQL 8.0 起）。优化器对 LIMIT 有特殊处理：当发现只需少量行时，可能倾向选择"能快速返回前几行"的索引，而不是"整体选择性最好"的索引。这在浅分页是优势，在深分页可能反而选了不合适的索引。本文评述：优化器的 LIMIT 感知是"局部最优"，它不知道你要跳过 100 万行，因此深分页场景下应主动用 FORCE INDEX 或优化器提示校正计划，并以 EXPLAIN ANALYZE（8.0.18+）的实际行数与耗时为准。

此外，innodb_buffer_pool_size 决定了回表是内存访问还是磁盘 I/O，optimizer_switch 中的 prefer_ordering_index 等开关也会影响计划选择。本文评述：调参能改善深分页的绝对耗时，但改变不了线性复杂度；把希望寄托在"调大缓冲池"上，等于用硬件掩盖算法问题，数据量再涨一个数量级仍会失效。

工程判断：先量后调。用 EXPLAIN ANALYZE 看实际扫描行数，用 Handler_read_* 看回表次数，用慢查询日志确认 offset 分布。若 90% 的慢查询 offset 都超过 10 万，应优先改产品交互（游标分页），而不是继续在 SQL 层打补丁。

六、分布式与前沿：分页问题的边界外推

单机深分页的结论迁移到分布式数据库时，会多出一层"跨分片归并"成本。以分库分表为例，LIMIT 1000000,20 通常被改写成"每个分片取 LIMIT 0,1000020，再在中间件归并排序取 20 行"，网络传输与内存排序成本被放大 N 倍。本文评述：分布式场景下延迟关联的收益会被归并成本稀释，游标分页（带全局有序键）几乎是唯一可扩展的方案；若业务必须跳页，通常需要在中间件层做"二次查询 + 内存归并"，并接受深页的延迟上限。

学术与工业界对"深分页"的关注，近年更多转向"近似分页"与"学习型索引"。学习型索引（learned index）用模型预测键的位置，理论上可降低索引空间与查找成本，Kraska 等人 2018 年的论文（SIGMOD）奠定了这一方向；但截至 2025 年，其在 MySQL 生态的生产落地仍有限，主要障碍是更新频繁时的模型维护成本。本文评述：学习型索引解决的是"定位"成本，而深分页的主要成本在"回表"与"丢弃"，两者并不直接对应；因此短期内它不会成为深分页的银弹，工程上仍应优先采用游标分页与延迟关联。

另一个值得关注的方向是"物化分页视图"：对高频深页预计算并缓存结果集（如按时间窗口分片缓存），把随机深页转成"按窗口命中"。这在报表、导出类场景有效，但需要处理数据新鲜度与缓存失效。本文评述：这是典型的空间换时间，适合读多写少、可容忍分钟级延迟的业务；对强一致列表页不适用。

七、结论与展望

回到最初的问题：索引建了为什么越翻越慢？因为索引只保证"能找到"，不保证"找得便宜"。深分页的成本由"扫描行数 × 回表代价"决定，offset 越大，乘数越大。延迟关联把回表从 offset+count 降到 count，是性价比最高的 SQL 层改造；游标分页把 offset 彻底消除，是复杂度最优但需产品配合的方案；覆盖索引用写放大换读性能，适合列少且稳定的列表。本文评述：三者不是互斥选项，而是按"产品能否接受游标语义 → 返回列是否可覆盖 → 是否需要跳页"的顺序做决策树，先改交互，再改 SQL，最后才调参。

展望未来，深分页问题的根本解法在"语义层"：把"第 N 页"这种无状态请求，改成"从某游标之后取 N 条"的有状态请求，让数据库只做范围扫描。随着分布式数据库与流式查询接口的普及，这一范式会越来越普遍。工程团队应尽早把分页 API 从 page/size 迁移到 cursor/limit，并在网关层做兼容。本文评述：这是一次"接口契约"的升级，越早做，迁移成本越低。