MySQL 深度分页如何优化？

深度分页问题：当使用 LIMIT offset, size 且 offset 值很大时，MySQL 需要扫描 offset + size 条记录后丢弃前面的 offset 条，性能急剧下降。

优化方案对比：

方案	核心思路	适用场景	性能提升
子查询优化	先查主键 ID，再回表查数据	有自增主键索引	⭐⭐⭐⭐
INNER JOIN	同上，写法不同	有自增主键索引	⭐⭐⭐⭐
游标分页	记录上次最大 ID，下次从该 ID 开始	连续翻页、不支持跳页	⭐⭐⭐⭐⭐
BETWEEN 范围查询	用 ID 范围替代 LIMIT offset	ID 连续、无断层	⭐⭐⭐⭐⭐
业务限制	限制最大页数，禁止深度跳页	搜索引擎类场景	⭐⭐⭐

一句话总结：深度分页的本质是 offset 导致的全表扫描，优化核心是 "利用索引直接定位起始点，避免扫描后丢弃"。

为什么深度分页会慢？

先看一个典型的深度分页 SQL：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

很多人的误解：MySQL 会"直接跳到第 100 万条记录"，然后取 10 条。

实际情况：MySQL 的执行流程是：

上图展示了 LIMIT 1000000, 10 的执行过程。关键问题在于：

• 扫描量大：MySQL 需要扫描 1000000 + 10 = 1000010 条记录，而不是只扫描 10 条
• 丢弃浪费：前 100 万条记录全部被扫描后丢弃，做了无用功
• 回表开销：如果查询的是 SELECT *，每条记录都要回表获取完整数据，代价更大
• 时间复杂度：O(offset + size)，offset 越大，性能越差

这就是为什么 LIMIT 100, 10 很快，但 LIMIT 1000000, 10 会慢几个数量级。

子查询优化

核心思想：先通过子查询利用覆盖索引快速定位起始 ID，再回表查数据，避免大量无意义的回表操作。

-- 原始慢查询（假设执行时间：5 秒）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;-- 优化后（假设执行时间：0.1 秒）
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) t ON o.id = t.id;

为什么更快？

上图展示了子查询优化的核心原理。关键优化点在于：

• 覆盖索引：子查询只查 id 字段，可以直接在索引上完成，无需回表
• 减少回表：子查询阶段扫描 100 万条时不需要回表，只有最终的 10 条才回表
• 性能对比：原始查询回表 100 万次，优化后只回表 10 次

游标分页（Cursor-based Pagination）—— 性能最优

核心思想：记录上一页最后一条记录的 ID，下次查询时直接从该 ID 之后开始，完全避开 offset。

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;-- 假设第一页最后一条记录的 id = 10
-- 第二页（传统方式：LIMIT 10, 10）
-- 第二页（游标方式：记住上一页最后的 id）
SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;-- 第 N 页：假设上一页最后的 id = 1000000
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

上图对比了传统分页和游标分页的性能差异。关键优势在于：

• 稳定性能：无论翻到第几页，每次都只扫描 size 条记录，性能恒定
• 索引定位：WHERE id > last_id 可以直接利用主键索引定位到起始位置
• 无丢弃浪费：不存在"扫描后丢弃"的问题

游标分页的局限性：

• 不支持跳页：用户只能"上一页/下一页"，不能直接跳到第 N 页
• 需要连续 ID：如果 ID 不连续或有删除，可能导致某些记录被跳过或重复
• 前端需要记住游标：需要保存上一页最后的 ID 值

BETWEEN 范围查询

如果 ID 连续且业务允许，可以用 ID 范围替代 LIMIT offset：

-- 假设每页 10 条，要查第 100001 页
-- 起始 ID = (100001 - 1) * 10 + 1 = 1000001
-- 结束 ID = 1000001 + 9 = 1000010SELECT * FROM orders
WHERE id BETWEEN 1000001 AND 1000010;

适用条件：

• ID 必须连续（没有断层）
• 需要知道 ID 的分布规律

业务层面限制 —— 最实用的方案

很多大厂的做法：直接限制用户只能翻到前 N 页。

实现方式：

// 后端校验
public PageResult<Order> listOrders(int pageNum, int pageSize) {// 限制最大页数int maxPage = 100;if (pageNum > maxPage) {throw new BusinessException("最多只能查看前 " + maxPage + " 页");}// 正常查询return orderMapper.selectPage(pageNum, pageSize);
}