引言：一个被低估的性能陷阱
在 Web 应用开发中，分页查询几乎无处不在。无论是电商订单列表、社交动态流，还是后台管理系统，LIMIT offset, size 都是开发者最熟悉的 SQL 写法之一。然而，当 offset 变得很大时——比如翻到第 1000 页、第 10000 页——这条看似无害的语句会突然变成性能杀手。
这不是危言耸听。许多团队在业务初期数据量小的时候一切正常，等到单表数据量突破百万、千万级，分页查询的响应时间从几十毫秒飙升到几秒甚至几十秒，数据库 CPU 被打满，接口超时频发。更棘手的是，这类问题往往在面试中被高频追问，因为它同时考察了数据库原理、索引设计和工程优化能力。
本文将从底层原理出发，剖析深度分页的性能瓶颈，并给出以“延迟关联”为核心的多种优化方案。
一、深度分页为什么慢？
1.1 LIMIT offset, size 的执行逻辑
很多人误以为 LIMIT 1000000, 10 是“跳过前 100 万行，只取 10 行”。但 MySQL 的实际执行过程是：
- 通过索引或全表扫描，读取并定位到满足 WHERE 条件的前 1000010 行；
- 将前 1000000 行全部丢弃；
- 返回最后的 10 行。
也就是说，offset 越大，需要扫描和丢弃的行数越多。这些被丢弃的行虽然不返回给客户端，但服务端必须真实地读取它们——可能是回表读、可能是回表后再过滤。磁盘 I/O、Buffer Pool 的页面加载、CPU 的解码开销，一样都少不了。
1.2 回表才是真正的成本大头
假设有一张订单表：
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT,
status TINYINT,
amount DECIMAL(10,2),
created_at DATETIME,
remark VARCHAR(500),
KEY idx_created (created_at)
) ENGINE=InnoDB;
执行：
SELECT * FROM orders ORDER BY created_at LIMIT 1000000, 10;
如果 MySQL 选择走 idx_created 索引，那么对于扫描到的每一行，它都需要拿主键 id 回到聚簇索引中读取完整行（回表）。100 万次回表意味着 100 万次随机 I/O，即便有 Buffer Pool 缓存，代价也极其高昂。
如果优化器放弃索引走全表扫描，那么就是顺序读整张表再排序，代价同样不可接受。
核心矛盾：分页需要排序，排序依赖索引，但索引不包含所有列，于是产生大量回表；而 offset 又把回表量放大到了 offset + size 行。
二、延迟关联：用覆盖索引换回表
2.1 基本思路
延迟关联（Deferred Join）的核心思想是：先在索引上完成分页定位，拿到主键，再用主键去关联原表取完整数据。这样，昂贵的回表操作只发生在最终需要的 size 行上，而不是 offset + size 行上。
改写后的 SQL：
SELECT o.*
FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY created_at
LIMIT 1000000, 10
) AS t ON o.id = t.id;
2.2 为什么快
子查询 SELECT id FROM orders ORDER BY created_at LIMIT 1000000, 10 只涉及 id 和 created_at 两列，而 idx_created 索引本身就包含这两列（二级索引叶子节点存的是索引列 + 主键）。因此这个子查询可以完全在索引上完成，无需回表，属于覆盖索引扫描。
扫描 100 万行索引页是顺序 I/O，成本远低于 100 万次随机回表。拿到 10 个 id 后，外层再回表 10 次。总回表次数从 100 万次降到 10 次。
2.3 适用条件与注意事项
- 排序列必须有索引，且索引能覆盖子查询所需字段；
- 子查询的 ORDER BY 列如果不在索引中，优化会失效；
- 如果 WHERE 条件复杂，需要确保条件列也在索引中，否则子查询仍会回表；
- 该方案对
SELECT *尤其有效，因为原表列越多、行越大，回表收益越明显。
三、其他主流优化方案对比
3.1 游标分页（Keyset Pagination）
不传 offset，而是传上一页最后一条记录的排序键：
SELECT * FROM orders
WHERE created_at > '2024-01-01 10:00:00'
ORDER BY created_at
LIMIT 10;
优点是任何页都是 O(log n) 定位 + O(size) 扫描，性能恒定，与页码无关。缺点是只能顺序翻页，无法跳页，且排序键必须唯一（通常用 (created_at, id) 组合）。
3.2 子查询定位主键范围
SELECT * FROM orders
WHERE id >= (SELECT id FROM orders ORDER BY created_at LIMIT 1000000, 1)
ORDER BY created_at
LIMIT 10;
本质与延迟关联类似，但写法更依赖排序键与主键的单调关系，适用面较窄。
3.3 业务层限制最大页数
很多产品（如 Google 搜索）根本不提供无限翻页。限制最大 offset，或改用“加载更多”模式，是从产品层面根治问题的办法。
3.4 方案对比表
| 方案 | 跳页支持 | 性能 | 实现复杂度 | 适用场景 |
|---|---|---|---|---|
| LIMIT offset | 支持 | 随 offset 线性下降 | 低 | 小数据量 |
| 延迟关联 | 支持 | 中等偏优 | 中 | 中大数据量、需跳页 |
| 游标分页 | 不支持 | 恒定优秀 | 中 | 信息流、无限滚动 |
| 限制页数 | 部分 | 可控 | 低 | 产品可接受 |
四、面试视角：如何回答这个问题
面试官问“MySQL 深度分页怎么优化”，通常期待这样的回答层次：
- 先讲清原理：LIMIT offset 会扫描并丢弃前 offset 行，回表是主要成本；
- 给出核心方案：延迟关联，用覆盖索引先取主键再回表；
- 补充替代方案：游标分页适合无限滚动，性能恒定；
- 点出权衡：能否跳页、排序键是否唯一、索引是否覆盖；
- 加分项：提到
EXPLAIN验证、Using index与Using filesort的区别、覆盖索引的定义。
如果还能结合具体数据量说明“百万级 offset 下回表次数从 100 万降到 10 次”，基本就是满分回答。
五、总结
深度分页的本质问题是：offset 让数据库做了大量无用功，而回表让这些无用功变得极其昂贵。延迟关联通过“先索引定位、后精准回表”的思路，把回表次数从 offset + size 压缩到 size，是兼容跳页需求下最实用的优化手段。
但没有任何一种方案是银弹。游标分页在无限滚动场景下性能更优，产品层限制页数则能从根源上避免问题。真正成熟的工程师，会根据业务对跳页的需求、数据量级、排序键特征，选择组合方案，并用 EXPLAIN 和压测数据验证效果，而不是背一条 SQL 模板了事。
未经允许不得转载：任鹏个人博客 » MySQL 深度分页性能问题与延迟关联优化
