跳至主要內容

程序员小富大约 5 分钟

大家好，我是小富。

《十万个why》系列持续更新中

后台管理系统的分页列表，前几页秒开，翻到几百页开始明显变慢，翻到几千页直接超时。如果你的表有个几百万数据，LIMIT 1000000, 20 这种查询可能要跑 10 秒以上。

明明有索引，前几页也很快，为什么翻到后面就卡死了？

先看看 LIMIT OFFSET 到底做了什么

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

你可能以为 MySQL 会直接跳到第 100 万条，然后取 20 条。但实际上 MySQL 的执行过程是这样的：

1. 扫描索引（或全表），找到前 1000020 条记录
2. 丢弃前 1000000 条
3. 返回第 1000001 ~ 1000020 条

没错，它把前 100 万条数据全读出来了，然后扔掉。你要的只是 20 条数据，但 MySQL 实际干了 100 万条的活。

这就是为什么 LIMIT 10, 20 飞快（只需要读 30 条），而 LIMIT 1000000, 20 卡死（要读 100 万条）。OFFSET 越大，要扔掉的数据越多，越慢。

回表才是性能杀手

如果你的查询是 SELECT *，情况会更糟。

假设有个联合索引 idx_created_at(created_at)，执行 ORDER BY created_at DESC LIMIT 1000000, 20 时：

1. 在二级索引 idx_created_at 上扫描，拿到 1000020 个主键 id
2. 用这 1000020 个 id 回表查聚簇索引，拿到完整的行数据
3. 丢弃前 1000000 条
4. 返回 20 条

第 2 步叫做「回表」。每次回表都是一次随机 IO（因为二级索引的顺序和聚簇索引的顺序不一样），100 万次随机 IO，这才是真正卡死的原因。

如果你只 SELECT id, created_at（索引覆盖查询），不需要回表，同样的 LIMIT 1000000, 20 会快很多——因为所有数据都能直接从二级索引上拿到。

优化方案一：延迟关联（子查询定位 ID）

核心思路：先用覆盖索引快速定位 ID，再用 ID 回表查完整数据。

-- 优化前：直接 LIMIT OFFSET，回表 100 万次
SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20;
-- 可能耗时 10s+

-- 优化后：子查询只走索引，主查询只回表 20 次
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20
) t ON o.id = t.id;
-- 可能耗时 0.5s

为什么快了这么多？

  • 子查询 SELECT id FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 只需要扫描二级索引，拿 ID，不回表。索引页在内存里连续排列，顺序扫描 100 万条很快。
  • 外层查询只对这 20 个 ID 做回表，20 次随机 IO 几乎可以忽略。

从 100 万次回表降到 20 次回表，性能差距就是这么来的。

优化方案二：游标翻页（记住上次位置）

如果你的业务是连续翻页（不支持跳页），可以用上一页最后一条记录的值做定位：

-- 第一页
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;
-- 返回结果中最后一条的 created_at = '2025-06-15 10:30:00', id = 98765

-- 第二页（用上一页的游标定位）
SELECT * FROM orders 
WHERE created_at < '2025-06-15 10:30:00' 
   OR (created_at = '2025-06-15 10:30:00' AND id < 98765)
ORDER BY created_at DESC 
LIMIT 20;

-- 第 N 页，同理

这种方式无论翻到第几页，性能都是恒定的，因为每次都是从索引的某个位置开始往后扫 20 条，不存在 OFFSET。

缺点是不支持跳页（不能直接跳到第 500 页），只能上一页/下一页。但对于 App 的无限下拉列表、消息流、时间线这类场景，完全够用。

优化方案三：如果 ID 是连续的

如果你的主键 ID 是自增且没有物理删除（或者能接受偶尔的空洞），可以直接用 ID 范围过滤：

-- 假设要查第 1000001 ~ 1000020 条
SELECT * FROM orders WHERE id >= 1000001 AND id <= 1000020 ORDER BY id;

主键范围查询走聚簇索引，效率极高。但这个前提比较苛刻——实际业务中 ID 通常不是连续的。

优化方案四：限制最大翻页深度

这是最"简单粗暴"但最实用的方案。你见过谁在 Google 搜索结果里翻到第 1000 页吗？

// 后端限制：最多翻到第 500 页
if (pageNum > 500) {
    throw new BusinessException("请缩小搜索范围");
}

大多数业务场景下，用户翻到很后面的需求本身就是不合理的。与其在技术上兜底无限深分页，不如在产品层面引导用户通过搜索条件缩小范围。

各方案对比

方案性能适用场景限制
延迟关联（子查询 ID）好后台管理系统的分页列表SQL 写法稍复杂
游标翻页极好（恒定）App 无限下拉、消息流、时间线不支持跳页
ID 范围过滤极好ID 连续且无物理删除条件苛刻
限制翻页深度——几乎所有业务产品层面需要配合

总结

MySQL 深分页慢的本质是：LIMIT OFFSET 不是"跳过"，而是"读了再扔"。 OFFSET 越大，读得越多，扔得越多，IO 浪费越严重。

再叠加上 SELECT * 导致的大量回表随机 IO，性能雪上加霜。

解决思路就两个方向：

  1. 减少回表：用覆盖索引 + 延迟关联，先在索引上定位 ID，再用 ID 精准回表
  2. 干掉 OFFSET：用游标翻页或 ID 范围查询，直接从目标位置开始扫描

记住这句话：凡是 OFFSET 大于 10 万的 LIMIT 查询，都应该被优化掉。

我是小富，下期见。

上次编辑于: