MySQL 深度分页如何优化？

zhangchangsheng2026-09-242026-09-24

面试考察点

问题识别能力：面试官不仅仅是想知道优化方案，更是想看你能否识别出深度分页的性能瓶颈——为什么 LIMIT 1000000, 10 会慢？MySQL 到底在做什么？
原理理解深度：考察你是否理解 MySQL 的 LIMIT offset, size 执行机制——它需要扫描 offset + size 条记录再丢弃前面的，而不是”直接跳到第 offset 条”。
方案选型思维：深度分页有多种优化方案，考察你能否根据业务场景（是否允许用户跳页、是否有连续自增主键等）选择最合适的方案。

核心答案

深度分页问题：当使用 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 的执行流程是：

  flowchart TB
    S["扫描第 1 ~ 1000000 条"] --> D["全部丢弃：白白扫描了 100 万条"]
    D --> K["扫描第 1000001 ~ 1000010 条"]
    K --> R["保留，最终返回这 10 条"]
    R --> P["时间复杂度 O（offset + limit），偏移量越大性能越差"]
    P --> T["建议：游标分页、覆盖索引或 WHERE id 大于 last_id"]

上图展示了 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;

为什么更快？

  flowchart TB
    subgraph O["原始查询：SELECT * LIMIT 1000000, 10"]
        A["索引扫描"] --> B["回表获取所有列"] --> C["丢弃"]
    end
    subgraph N["优化查询：子查询 + JOIN"]
        D["子查询只扫描 id，不需要回表"] --> E["得到 10 个目标 id"] --> F["JOIN 只对最终 10 条记录回表"]
    end
    C -.-> X["每条记录都要回表，重复 100 万次，性能极差"]
    F -.-> Y["用覆盖索引避免大量回表，性能提升百倍以上"]

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

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

核心思想：记录上一页最后一条记录的 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;

页码	传统分页 LIMIT offset, size	游标分页 WHERE id 大于 last_id
第 1 页	LIMIT 0, 10（扫描 10 条）	id > 0 LIMIT 10（扫描 10 条）
第 2 页	LIMIT 10, 10（扫描 20 条）	id > 10 LIMIT 10（扫描 10 条）
第 3 页	LIMIT 20, 10（扫描 30 条）	id > 20 LIMIT 10（扫描 10 条）
第 100001 页	LIMIT 1000000, 10（扫描 1000010 条）	id > 1000000 LIMIT 10（扫描 10 条）
复杂度与趋势	O（offset + size），页数越深越慢	O（size），永远稳定不衰减

游标分页适合大数据量深分页场景。

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

稳定性能：无论翻到第几页，每次都只扫描 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 = 1000010

SELECT * FROM orders
WHERE id BETWEEN 1000001 AND 1000010;

适用条件：

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

五、方案四：业务层面限制 —— 最实用的方案

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

  flowchart TB
    W["为什么限制深度分页"] --> A["用户行为数据"]
    A --> A1["99% 的用户只看前 3 页"]
    A --> A2["99.9% 的用户只看前 10 页，翻到第 100 页极其罕见"]
    W --> B["搜索引擎的做法"]
    B --> B1["Google 最多显示 100 页（1000 条结果）"]
    B --> B2["百度最多 76 页，电商通常限制前 100 页"]
    W --> C["技术与业务的平衡"]
    C --> C1["极少数用户的需求 vs 数据库压力，合理限制是性价比最高的选择"]

实现方式：

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

六、实际性能对比（100 万条数据测试）

查询方式	SQL	执行时间
原始查询	SELECT * FROM orders LIMIT 1000000, 10	~5s
子查询优化	SELECT * FROM orders o INNER JOIN (SELECT id FROM orders LIMIT 1000000, 10) t ON o.id = t.id	~0.1s
游标分页	SELECT * FROM orders WHERE id > 1000000 LIMIT 10	~0.01s

面试高频追问

追问一：游标分页有什么缺点？什么场景不适合用？
- 答：不支持跳页、需要前端保存游标状态、ID 不连续时可能漏数据。电商搜索结果页适合，但后台管理系统需要跳页就不适合。
追问二：如果排序字段不是主键 ID，而是 create_time，怎么优化？
- 答：可以用联合索引 (create_time, id)，子查询改为 SELECT id FROM orders ORDER BY create_time, id LIMIT offset, size，游标分页改为 WHERE (create_time, id) > (last_time, last_id)。
追问三：为什么子查询只查 id 就能提升性能？
- 答：因为只查主键/索引列时，MySQL 可以使用”覆盖索引”，直接在索引树上获取数据，不需要回表去聚簇索引查完整记录。

常见面试变体

“为什么 LIMIT 1000000, 10 会很慢？”
“MySQL 分页查询怎么优化？”
“百万级数据分页怎么处理？”
“介绍几种分页优化方案及其适用场景”

记忆口诀

深度分页优化三句话：

子查询先查 id：覆盖索引省回表
游标记住 last_id：直接定位无扫描
业务限制最大页：性价比高最实用

总结

深度分页的性能问题本质是 offset 导致的大量无效扫描。优化核心是 “用索引直接定位，避免扫描后丢弃”：生产环境推荐 子查询 + JOIN（兼容跳页）或 游标分页（性能最优），同时配合 业务层限制最大页数，这是最经济实用的方案。

声明：本文转载自小哈的博客《MySQL 深度分页如何优化？》，版权归原文作者所有。转载请保留本声明与原文链接。