我刚工作的时候，有个列表页做了分页，前两页秒开，翻到 100 页就卡死了。用户投诉说：“你们这破网站，翻页翻到第 100 页就转圈圈！”

DBA 帮我一看 SQL：SELECT * FROM orders ORDER BY id LIMIT 1000000, 10; —— 扫描了 1000010 行，然后丢弃前 1000000 行，只返回 10 行。

今天咱们就来聊聊 MySQL 分页查询的优化，看完这篇，你就能让列表页飞起来。

传统分页的问题

慢在哪里？

-- 第 1 页：很快（扫描 10 行）
SELECT * FROM orders ORDER BY id LIMIT 0, 10;

-- 第 1000 页：开始变慢（扫描 10000 行）
SELECT * FROM orders ORDER BY id LIMIT 10000, 10;

-- 第 100000 页：慢得要命（扫描 1000010 行）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

问题：

  1. LIMIT 1000000, 10 会扫描 1000000 + 10 = 1000010 行
    1. 然后丢弃前 1000000 行，只返回最后 10 行
    1. 扫描的行数越多，性能越差

验证一下

-- 看执行计划
EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

输出：

+----+-------------+--------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 20000000 |       |
+----+-------------+--------+------+---------------+------+---------+------+----------+-------+

问题：rows = 20000000（扫描全表），type = ALL（全表扫描）。

优化方案 1：用主键索引覆盖（延迟关联）

思路：先查主键 ID（覆盖索引，不需要回表），再用 ID 关联查完整数据。

优化前

-- 扫描 1000010 行，丢弃前 1000000 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化后

-- 先查 ID（覆盖索引，扫描 10 行）
SELECT id FROM orders ORDER BY id LIMIT 1000000, 10;

-- 再用 ID 关联查完整数据（只查 10 行）
SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) b
ON a.id = b.id;

为什么快？

  1. 子查询 SELECT id FROM orders ORDER BY id LIMIT 1000000, 10 是覆盖索引（只查主键 ID，不需要回表），性能很好
    1. 外层查询用 JOIN，只查 10 行完整数据（回表 10 次）

验证一下

EXPLAIN SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) b
ON a.id = b.id;

输出：

+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | ALL | NULL          | NULL    | NULL    | NULL | 10       |             |
|  1 | PRIMARY     | a         | eq_ref | PRIMARY       | PRIMARY | 4       | b.id | 1        |             |
|  2 | DERIVED     | orders    | index | NULL          | PRIMARY | 4       | NULL | 1000010  | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+

优化效果：

  1. derived2（子查询）的 Extra = Using index（覆盖索引，不需要回表）
    1. 外层查询的 type = eq_ref（主键关联，只查 1 行）
    1. 实际执行时间从 5 秒降到 0.1 秒（50 倍提升！）

优化方案 2：用游标分页（推荐！）

思路：记住上一页的最后一条记录的 ID，下一页从这个 ID 开始查。

优化前

-- 第 100000 页：扫描 1000010 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化后

-- 第 1 页
SELECT * FROM orders ORDER BY id LIMIT 10;

-- 假设上一页最后一条记录的 id = 1000000
-- 第 100001 页：只扫描 10 行！
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

为什么快？

  1. WHERE id > 1000000 是范围查询，走主键索引
    1. LIMIT 10 只返回 10 行
    1. 不管翻到第几页，都是扫描 10 行，性能恒定

验证一下

EXPLAIN SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

输出：

+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | orders | range | PRIMARY       | PRIMARY | 4       | NULL | 10       | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-------------+

优化效果：

  1. type = range（范围查询，走主键索引）
    1. rows = 10（只扫描 10 行！）
    1. 实际执行时间从 5 秒降到 0.001 秒（5000 倍提升！）

缺点

问题：如果 ID 不连续（比如删除了某些记录），会漏数据。

解决方案：用创建时间代替 ID（如果创建时间是递增的）。

-- 假设上一页最后一条记录的 created_at = '2024-01-15 10:30:00'
SELECT * FROM orders WHERE created_at > '2024-01-15 10:30:00' ORDER BY created_at LIMIT 10;

优化方案 3：用 BETWEEN（适合翻页不多的情况）

思路：如果知道每一页的 ID 范围，用 BETWEEN 代替 LIMIT。

优化前

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

优化后

-- 假设第 100001 页的 ID 范围是 1000001 ~ 1000010
SELECT * FROM orders WHERE id BETWEEN 1000001 AND 1000010;

为什么快？

  1. BETWEEN 是范围查询，走主键索引
    1. 只扫描 10 行

缺点

问题：需要知道每一页的 ID 范围，不适合动态翻页（比如用户随便跳到第 100001 页）。

适用场景：翻页不多（比如最多翻 100 页），或者前端能记住每一页的 ID 范围。

优化方案 4：用子查询（类似延迟关联）

思路：先查到起始 ID，再用 WHERE id >= 起始 ID 限制范围。

优化前

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

优化后

-- 先查起始 ID
SELECT id FROM orders ORDER BY id LIMIT 1000000, 1;

-- 再用 WHERE id >= 起始 ID 限制范围
SELECT * FROM orders WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10;

为什么快？

  1. 子查询是覆盖索引（只查主键 ID）
    1. 外层查询用 WHERE id >= 起始 ID，走主键索引，只扫描 10 行

实战：优化一个慢分页

假设有个订单表，分页查询很慢：

SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 10;

第 1 步：看执行计划

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 10;

输出：

+----+-------------+--------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 20000000 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+----------+----------------+

问题：

  1. type = ALL（全表扫描）
    1. rows = 20000000（扫描全表）
    1. Extra = Using filesort（文件排序）

第 2 步：给 created_at 加索引

CREATE INDEX idx_created_at ON orders(created_at DESC);

再看执行计划：

EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 10;

输出：

+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows     | Extra |
+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+
|  1 | SIMPLE      | orders | index | NULL          | idx_created_at  | 5       | NULL | 1000010  |       |
+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+

优化效果：

  1. type = index（索引扫描）
    1. rows = 1000010（扫描 1000010 行，比全表扫描好点）
    1. Extra 里没有 Using filesort 了（因为索引是有序的）
      但还是慢，因为扫描了 1000010 行。

第 3 步：用游标分页

-- 假设上一页最后一条记录的 created_at = '2024-01-15 10:30:00'
SELECT * FROM orders WHERE created_at < '2024-01-15 10:30:00' ORDER BY created_at DESC LIMIT 10;

再看执行计划：

EXPLAIN SELECT * FROM orders WHERE created_at < '2024-01-15 10:30:00' ORDER BY created_at DESC LIMIT 10;

输出：

+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key             | key_len | ref  | rows     | Extra       |
+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------------+
|  1 | SIMPLE      | orders | range | idx_created_at | idx_created_at | 5       | NULL | 10       | Using where |
+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------------+

优化效果：

  1. type = range（范围查询）
    1. rows = 10（只扫描 10 行！）
    1. 实际执行时间从 5 秒降到 0.001 秒（5000 倍提升！）

实战建议

1. 优先用游标分页

如果业务允许（比如移动端下拉加载更多），优先用游标分页（WHERE id > 上一页最后一条 ID）。

好处：性能最好，不管翻到第几页，都是扫描固定行数。

2. 如果必须用传统分页，用延迟关联

如果业务必须用传统分页（比如 PC 端翻页，用户可能跳到第 100001 页），用延迟关联优化。

SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) b
ON a.id = b.id;

3. 给 ORDER BY 字段加索引

如果 ORDER BY 没走索引，会导致 Using filesort，性能很差。

建议：给 ORDER BY 字段加索引，或者让 ORDER BY 用上联合索引的后缀。

4. 限制最大翻页数

如果表有 1 亿条数据，用户翻到第 10000001 页，性能还是会很差。

建议：限制最大翻页数（比如最多翻 1000 页），或者强制用户用搜索代替翻页。

-- 如果 offset > 10000，报错或者重定向到搜索页
if (offset > 10000) {
    return "请用搜索功能";
    }
    ```
## 总结

- 传统分页 `LIMIT offset, size` 的问题是：扫描 `offset + size` 行，然后丢弃前 `offset` 行，性能极差
- - 优化方案 1：**延迟关联**（先查 ID，再关联）
- - 优化方案 2：**游标分页**（推荐！用 `WHERE id > 上一页最后一条 ID` 限制范围）
- - 优化方案 3：**BETWEEN**（适合翻页不多的情况）
- - 优化方案 4：**子查询**（类似延迟关联）
- - 实战建议：优先用游标分页，如果必须用传统分页就用延迟关联，给 ORDER BY 字段加索引，限制最大翻页数
如果你能把这几种分页优化方案讲清楚，面试官绝对觉得你有实战经验。

---

**实战代码都在我本地跑过，你可以放心复制。** 如果有问题，欢迎评论区交流！
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

Codex：AI 编程助手的核心引擎

在人工智能与编程结合的前沿领域，OpenAI Codex 是一个里程碑式的模型。简单来说，Codex 能够将自然语言描述转化为可执行的代码，或将代码片段转化为清晰的解释，极大地提升了开发者的效率。可以看到，Codex 不仅生成了功能正确的代码，还自动添加了清晰的文档字符串（Docstring）和一个使用示例，体现了其对开发者实践的良好理解。Codex 的工作可以简化为一个“预测下一个 token”

avatar AI编程社区

Claude Code 离线安装方案揭秘：从零搭建私有化 AI 编程助手

方案选型建议：根据团队规模、技术栈、安全要求选择。后续学习路径：官方文档、社区资源、性能调优高级主题。免责与风险提示。

avatar AI编程社区

Codex App接上微信，我开始在厕所里改 Bug 了

我只好蹲在马桶上，用手机写代码。但手机敲代码简直是噩梦，直到我发现了 Codex App 和微信的“骚操作”组合——把 Codex 集成到微信里，让 AI 帮我写代码，我只需要动动嘴，Bug 就修好了。- 避免暴露敏感信息（如数据库密码）- 遵守微信使用条款，建议用企业微信或测试号## 总结通过将 Codex App 接入微信，我成功实现了“厕所改 Bug”的自由。### 代码示例 1：基础的微信

avatar AI编程社区