LIMIT 分页的性能优化：深分页到底慢在哪？

---

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第14天内容。

---

背景引入

💡 分页查询谁没写过？LIMIT 100000, 20 这么写，翻到后面几页就卡死了，你知道为什么吗？

做个填空题：一个 SQL 执行了 5 秒，大概率是因为——

• A. 没加索引
• B. 深分页
• C. 表太大
• D. 以上都是

答案是：D，但 B 往往是最隐蔽的那个。

第 1 页飞快的 SQL，翻到第 100 页突然慢了 100 倍。加索引也救不了。

今天的目标：搞懂深分页为什么慢，以及三种最实用的优化方案。

---

核心概念

一个常见的分页查询，MySQL 是怎么跑的？

SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;

你脑子里想的可能是：从第 100000 行开始，取 20 行，MySQL 就扫了 20 行吧？

MySQL 实际的内心戏是：

1. 走全表或索引扫描，找到所有满足条件的行
2. 按 create_time DESC 排序
3. 从头数到第 100020 行
4. 丢掉前 100000 行，留下 100001 ~ 100020 行
5. 返回这 20 行

说白了：LIMIT 100000, 20 的意思是「排好序后，数到第 100000 行，扔掉前面的，我要后面的 20 行」。

MySQL 再聪明也不知道你只关心后面的 20 行——它得先数到第 100000 行。

随着页码增大，扫描行数线性增长。 第 1 页扫描几十行，第 1000 页就要扫描几十万行——这就是「深分页」的代价。

---

面试必问的三种优化方案

---

方案一：游标分页（Cursor-based / Keyset Pagination）

核心思想：不翻页，只翻篇。

传统的 LIMIT OFFSET 需要先「跳过」前面的行，而游标分页直接记住上一页最后一条的位置，从那里开始取。

-- 传统分页（第 10001 页）
SELECT * FROM orders 
ORDER BY id 
LIMIT 100000, 20;

-- 游标分页（记住上一页最后一条的 id = 100000）
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 20;

第二条 SQL 的 WHERE id > 100000 直接走主键索引定位到 100000，然后往后取 20 行就结束了——每一页扫描的行数都一样，不管你在第几页。

这里用 id 举例是因为主键唯一且有序，最容易理解。换成 create_time 也一样——WHERE create_time > 上次的时间 ORDER BY create_time LIMIT 20，只是排序字段有重复值时要带上主键做决胜条件（后面会讲）。

关键条件：

• 需要排序字段是有序且唯一的（通常是主键）
• 不能直接跳到任意页码，只能「上一页/下一页」
• 翻页稳定性好：中间有数据插入也不会导致重复或遗漏

适用场景：朋友圈、信息流、下拉加载更多（无限滚动）

---

方案二：延迟关联（Deferred Join / 先查主键再 JOIN）

适合不能改用游标分页、又必须用 OFFSET 的场景：

-- 原 SQL（慢）
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 100000, 20;

-- 延迟关联优化
SELECT * FROM orders 
INNER JOIN (
  SELECT id FROM orders 
  ORDER BY create_time DESC 
  LIMIT 100000, 20
) AS tmp ON orders.id = tmp.id;

为什么快？

子查询只查 id，如果排序字段有索引，走的就是覆盖索引扫描（Extra: Using index），不需要回表取完整行数据。等拿到 20 个 id 后，再回表取 20 次完整行。

原 SQL 的流程是：引擎层先回表取 100020 行的完整数据 → 丢给 server 层排序 → 扔掉 100000 行。等于做了 100020 次回表，大部分都白费了。

但说实话，这个优化的效果取决于三个条件：

1. 数据量要够大——几百行的时候回表 100 次和回表 20 次几乎没区别，性能瓶颈根本不在 IO 上
2. 行宽要大——如果一行只有几十字节，sort buffer 轻松装下，回表开销也不明显。但如果一行有 text/blob 字段、几百字节甚至更多，sort buffer 装不下就会写磁盘临时文件，差距就大了
3. create_time 字段有索引——如果没有索引，排序会很慢，回表开销也会很大。

一句话：深分页（OFFSET 上万）且行宽较大时，优化效果明显；前几页或窄表场景下，差别微乎其微。

适用场景：后台分页报表、搜索列表等不适用游标分页、又确实有深分页压力的业务

---

方案三：范围分页（Range-based Pagination）

如果你的排序字段是连续递增的（比如自增 id），还可以这么做：

-- 假设每页 20 条
SELECT * FROM orders 
WHERE id BETWEEN 100001 AND 100020 
ORDER BY id;

这个方案极度高效，但局限性也明显——必须用连续自增字段做分页，按时间排序就不适用了。

---

实战案例

以下是在 5 万行数据上，三个方案的真实 EXPLAIN 对比：

-- 方案一：传统 LIMIT OFFSET（深分页，扫全表 + filesort）
EXPLAIN SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 30000, 20\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ALL
possible_keys: NULL
          key: NULL
         rows: 50440
     filtered: 100.00
        Extra: Using filesort

-- 方案二：延迟关联（子查询只走索引，不回表）
EXPLAIN SELECT * FROM orders 
INNER JOIN (
  SELECT id FROM orders 
  ORDER BY create_time DESC 
  LIMIT 30000, 20
) AS tmp ON orders.id = tmp.id\G

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
         rows: 30020

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: orders
         type: eq_ref
         rows: 1

*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: orders
         type: index
          key: idx_create_time
         rows: 30020
        Extra: Backward index scan; Using index

-- 方案三：游标分页（直接范围定位，只扫 20 行）
EXPLAIN SELECT * FROM orders 
WHERE create_time < '2026-05-05 21:36:27' 
ORDER BY create_time DESC 
LIMIT 20\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
  possible_keys: idx_create_time,idx_covering
          key: idx_create_time
         rows: 20
        Extra: Using index condition; Backward index scan

三个方案的 rows 字段差距一目了然：

方案	rows	Extra	回表次数
传统深分页	50440	Using filesort	50440 次
延迟关联	30020（索引）+ 20（主表）	Using index	仅 20 次
游标分页	20	无 filesort	20 次

传统方案预估扫 50440 行还要 filesort；延迟关联虽然也扫了 30020 行索引（Using index），但这是索引只读，不需要回表取完整行数据；游标分页更直接，只扫 20 行就结束了。

---

游标分页的边界处理细节

游标分页容易踩的一个坑：排序字段有重复值。

假设 create_time 有重复，只用 WHERE create_time < '2025-10-01' 可能漏掉数据。正确的写法是带上主键作为决胜条件：

-- 上一页最后一条：create_time = '2025-10-01', id = 50000

-- 正确：用 (create_time, id) 二元组定位
WHERE create_time < '2025-10-01' 
   OR (create_time = '2025-10-01' AND id < 50000)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

这也要求游标分页必须有一个联合索引 (create_time, id) 才能高效执行。

---

避坑指南

⚠️ 真实踩过的坑：

1. OFFSET 越大越慢，加索引也没用

   • 遇到过运营后台的分页报表，翻到第 200 页直接超时
   • 根因不是索引问题，是 OFFSET 的设计问题——优化方案直接改成游标分页，从 8 秒降到了 0.01 秒

2. 游标分页不能随便跳页，且排序字段有重复时需要联合索引

   • 产品经理问：「为什么不能直接跳到第 100 页？」
   • 游标分页的限制就是不能跳页。如果业务要求跳页，可以用延迟关联做折中
   • 排序字段有重复值时，必须建 (排序字段, id) 联合索引才能高效执行游标分页

3. 延迟关联别忘了 ORDER BY 字段要建索引

   • 子查询里的 ORDER BY create_time DESC LIMIT 50000, 20，如果 create_time 没有索引，子查询本身就要 filesort，优化的效果大打折扣

---

思考题

🤔 互动时间：

1. 游标分页下，用户 A 翻到第 3 页时，管理员插入了一条新数据。用户 A 翻到第 4 页会看到什么？（提示：考虑游标位置 vs OFFSET 位置）
2. LIMIT 50000, 20 和 LIMIT 20 OFFSET 50000 有区别吗？
3. 如果你的排序字段不是主键且允许重复，游标分页的最佳实践是什么？

---

总结

🎯 面试考点

• 深分页慢的根本原因：OFFSET 越大，MySQL 扫描和丢弃的行数越多
• 三种优化方案：游标分页（性能最好，不能跳页）、延迟关联（能跳页，兼容性好）、范围分页（最简单，限制最多）
• 游标分页的核心：用 WHERE 排序字段 > 上一页最后值 替代 LIMIT OFFSET，每页扫描行数恒定
• 游标分页必须处理重复值：排序字段 + 主键做二元组，建联合索引

今天就试一下：找一个线上最慢的分页查询，把 EXPLAIN 拉出来看看 rows 有多大。如果 rows 远大于 LIMIT 的条数，恭喜你——这就是可以优化的深分页。

下篇第 15 天，我们会分享 INSERT 批量插入相关的内容，敬请关注。

---

下期预告：INSERT 批量插入的最佳实践 —— 别再逐条 INSERT 了！

---

有问题欢迎评论区交流，明天见！