分页越深越卡？MySQL LIMIT 优化全攻略

mrkong

在 MySQL 中，如果一张表有数千万条记录，你会发现下面的现象：

1. SELECT * FROM Student LIMIT 0, 10;         -- 非常快
   
2. SELECT * FROM Student LIMIT 1000000, 10;  -- 慢到怀疑人生
   

复制代码

这是典型的 深度分页性能问题。下面我们一步步拆解原因，并给出优化方案。

1. 原因分析1.1 MySQL 的 LIMIT 是怎么工作的？

LIMIT offset, N 的本质是：

• MySQL 会扫描 offset + N 条数据。
• 丢弃前 offset 条，只返回后面的 N 条。
  
  

例如：

1. LIMIT 1000000, 10
   

复制代码

意味着需要扫描 1,000,010 行数据，前 1,000,000 行数据直接丢掉。
扫描越多，耗时越长，自然会慢。

1.2 为什么有索引也慢？

即使对排序字段有索引，比如：

1. ORDER BY create_time DESC
   

复制代码

当 SELECT * 查询了很多非索引列（name、age、gender 等），
MySQL 需要 回表 获取完整数据。

当 offset 很大时，会发生：

• 先扫描大量索引记录。
• 再回表一次次查出对应行数据。
• 回表 IO 开销巨大，速度就慢了。
  
  

2. 优化方法方法一：起始 ID 定位法（Keyset Pagination）

适合 顺序翻页 场景，例如瀑布流、连续加载。

假设上一页的最后一个 ID 是 990000：

1. SELECT *
   
2. FROM Student
   
3. WHERE id > 990000
   
4. ORDER BY id
   
5. LIMIT 10;
   

复制代码

优点：

• 利用主键索引（B+ 树）直接定位，不会扫描和丢弃大量数据。
• 执行非常快，甚至在千万级数据下仍然是毫秒级。
  
  

缺点：

• 不能直接跳到第 100 页等随机页码。
  
  

方法二：覆盖索引 + 子查询

适合 任意跳页 场景。

思路：

• 子查询只查 主键 ID（覆盖索引，避免回表）。
• 再根据这些 ID 回表一次性获取数据。
  
  

示例：

1. SELECT t1.name, t1.age, t1.gender, t1.create_time
   
2. FROM Student AS t1
   
3. INNER JOIN (
   
4.     SELECT id
   
5.     FROM Student
   
6.     ORDER BY create_time DESC
   
7.     LIMIT 1000000, 10
   
8. ) AS t2 ON t1.id = t2.id;
   

复制代码

效果：

• 子查询利用索引快速定位 10 条主键 ID。
• 避免大规模回表，性能大幅提升。
  
  

在 500 万条数据测试中：

• 原查询耗时 3.36 秒。
• 优化后耗时 0.23 秒。
  
  

3. 总结对比

方法	优点	缺点	适用场景
LIMIT offset, N	简单、易写	深度分页性能差	小数据量
起始 ID 定位法	毫秒级性能	不能直接跳任意页	顺序加载/瀑布流
覆盖索引 + 子查询	可跳任意页、性能稳定	SQL 稍复杂	大数据分页

核心原则：

• 尽量减少无效扫描（不要丢弃太多数据）。
• 尽量避免频繁回表（利用覆盖索引）。