MySQL 深分页为什么慢？
LIMIT m,n会扫描并丢弃前 m 条数据，页码越大越慢。
怎么优化？
1️⃣ 建索引
2️⃣ 子查询先查 ID
3️⃣ 游标分页（
id > 上页最大值）4️⃣ 业务限制最大页数
MySQL深分页性能下降的根本原因在于LIMIT offset, size语句的执行机制。其核心流程是：
limit 必须从头逐行扫描，然后跳过offset行。offset越大，无效扫描量线性上涨，性能就断崖式下跌。
具体来说，一个典型的深分页查询SELECT * FROM table ORDER BY id LIMIT 100000, 10;的执行过程如下：
- 通过索引（如主键索引）或全表扫描，定位到符合条件的数据起始位置。
- 顺序扫描前100,000条记录。
- 将这100,000条记录丢弃。
- 返回接下来的10条记录。
这个过程导致了两个主要的性能瓶颈：
- 巨大的无效I/O与CPU开销：即使只需要最后10条数据，引擎也必须读取、解析并丢弃前100,000条记录的所有数据页，造成了大量的磁盘I/O和CPU计算浪费。
- 回表与锁竞争加剧：如果查询无法被覆盖索引完全满足，在通过二级索引定位到主键ID后，还需要进行大量的“回表”操作来获取完整行数据。在事务隔离级别较高（如RR）时，长时间扫描大量数据还可能加剧锁竞争，影响并发性能。
为了更清晰地对比不同优化方案的特性，下表汇总了主流解决方案：
| 优化方案 | 核心原理 | 是否支持跳页 | 性能影响 | 适用场景 |
|---|---|---|---|---|
| 游标分页 (Cursor-based) | 使用WHERE id > last_max_id LIMIT size，避免OFFSET。 | ❌ | ⭐⭐⭐⭐⭐ (最优) | 无限滚动、连续翻页（如App信息流）。 |
| 延迟关联 (Deferred Join) | 子查询先利用覆盖索引快速获取目标页的主键ID，再通过JOIN回表取数据，减少回表量。 | ✅ | ⭐⭐⭐⭐ (很好) | 中大型表，排序字段有索引，且需要支持跳页。 |
| 覆盖索引优化 | 创建包含所有查询字段的覆盖索引，使查询仅扫描索引即可完成，避免回表。 | ✅ | ⭐⭐⭐⭐ (很好) | 查询字段较少，可以建立覆盖索引的场景。 |
| 业务层限制 | 产品层面限制可查询的最大页码或深度（如只允许查前100页）。 | ✅ | ⭐⭐⭐⭐⭐ (最优) | 所有分页场景，作为兜底方案。 |
代码示例：延迟关联优化
将原始的低效深分页查询：
-- 原始低效查询 SELECT * FROM `order` ORDER BY create_time DESC LIMIT 100000, 10;优化为延迟关联查询：
-- 优化后的延迟关联查询 SELECT o.* FROM `order` o JOIN ( SELECT id -- 子查询只选取主键ID，利用(create_time, id)索引高效定位 FROM `order` ORDER BY create_time DESC LIMIT 100000, 10 ) AS t ON o.id = t.id; -- 通过主键快速关联回表获取完整数据此优化利用了(create_time, id)联合索引的有序性，子查询可以快速地在索引树上定位到第100000条记录之后的位置，只读取10个ID，然后通过主键精准回表，极大地减少了需要扫描和回表的数据量。
参考来源
- mysql深分页问题
- 实战！如何解决 MySQL 深分页问题
- 实战！如何解决 MySQL 深分页问题
- MySQL深分页详解与优化实践
- 关于MySQL深分页的问题及优化方案
- MySQL-深分页问题的背景和影响
