本文共计5446字，预计阅读时间10分钟

为什么深度分页低效？

分页查询一定有排序吗？
分页查询不一定会有 order by 字段，但是业务上的分页查询语句肯定都是有 order by 的，否则仅仅limit返回的顺序是不确定的，是物理存储顺序，每次查询结果可能不同
例：SELECT * FROM t ORDER BY id LIMIT 100,10
在分页查询sql语句一定有 order by 字段的前提下：

• 若 order by 字段有索引
  ——无额外排序，只是”按索引顺序遍历“
• 若 order by 字段无索引
  ——需要在排序缓冲区排序 (filesort)

• 为什么深度分页效率低？

  步骤	组件	关键动作	与深度分页的关联
  1️⃣ 连接	连接器	验证用户/密码，检查权限，分配线程	无
  2️⃣ 缓存	查询缓存	（MySQL 8.0 已移除）	无
  3️⃣ 解析	分析器	词法分析 → 语法分析 → 生成解析树	确认 ORDER BY id 和 LIMIT 语法合法
  4️⃣ 优化	优化器	生成执行计划： - 选索引（主键聚簇索引） - 确定扫描方向	决定“按 id 顺序扫描聚簇索引”
  5️⃣ 执行	执行器	循环调用存储引擎： ① 要第1行 → 引擎返回完整行 → 执行器计数=1 ② 要第2行 → … … ⑩ 要第1,000,010行 → 引擎返回 → 执行器丢弃前1,000,000行，保留最后10行	❗ 深度分页瓶颈所在： - 执行器需处理 1,000,010 行完整数据 - 每行解析、内存暂存、丢弃 → CPU/内存爆炸
  6️⃣ 返回	执行器	将10行结果封装，通过连接器返回客户端	无
  7️⃣ 断开	连接器	（若设置 wait_timeout）释放连接	无

大数据量下，越往后，分页效率越低(limit 20,10 -> limit 200000,10)

比如 select s.* from tb_sku s order by created_at limit 2000000,10;

此时只用查询10条数据，但是要对200万条数据都排序，最终才通过limit字段获取出需要的 10条数据

就像你要在一本 200 万页的书里找第 200001 到 200010 页的内容，
你必须一页一页翻过去，直到翻到那 10 页为止。
即使你只关心最后 10 页，你也得翻前面 20 万页！

Sql优化

覆盖索引

如果只需要查询 部分几个字段，使用“覆盖索引”就能优化

将查询字段全部包含在索引中，避免回表

-- 创建复合索引
CREATE INDEX idx_order ON t_order(payment_time, id);

-- 查询只返回索引字段
SELECT id, payment_time FROM t_order WHERE payment_time >= ? LIMIT 100000, 20

覆盖索引+子查询

如果需要查询很多字段/全字段，可以使用“覆盖索引+子查询”的方式优化（也被称为“延迟关联”）

• 两次覆盖

  • 子查询中只返回主键id (覆盖索引)，
  • 主查询中利用主键id作为条件(聚簇索引叶子节点包含完整数据行，覆盖了需要查到的字段)

• 子查询：根据order by 条件先获取一个主键 id 的集合

例如：
select s.* from tb_sku s order by id limit 2000000,10;

# 隐式内连接的写法
select s.* from tb_sku s, (select id from tb_sku order by id limit 2000000,10) a where s.id = a.id;
# 显式内连接的写法
select s.* from tb_sku s
INNER JOIN 
(select id from tb_sku order by id limit 2000000,10) temp
ON s.id = temp.id;

子查询只查出主键id的集合（实现了覆盖索引的效果），主查询通过主键 id，直接在聚集索引中获取行数据

避免了原sql语句的低性能全表扫描

为什么“覆盖索引+子查询”会比正常的分页查询更高效？

（以下情形是 两者都对 order by的字段创建了索引来提升效率，同时这也是本方法能提高效率的前提）

正常的分页查询：SELECT * FROM table ORDER BY indexed_col LIMIT offset, size
“覆盖索引+子查询”优化指的是：SELECT t.* FROM table t INNER JOIN (SELECT id FROM table ORDER BY indexed_col LIMIT offset, size) tmp ON t.primary_key = tmp.id（注意：子查询中SELECT的字段必须是覆盖索引包含的，且能用于关联，通常是主键）

sql 复制

SELECT t.* FROM table t INNER JOIN (SELECT id FROM table ORDER BY id LIMIT offset, size) tmp ON t.primary_key = tmp.id

SELECT t.* FROM table t INNER JOIN (SELECT id FROM table ORDER BY balance LIMIT offset, size) tmp ON t.primary_key = tmp.id

1. 正常分页查询的执行过程（以InnoDB为例）：

   • 由于ORDER BY indexed_col有索引，MySQL会使用该索引进行扫描（避免filesort）。
   • 但是，因为SELECT *，需要回表获取完整行数据。
   • 执行步骤：
     a. 从索引中按顺序读取(offset + size)条记录（即5000010条）。
     b. 对于每一条索引记录，根据主键（或聚簇索引）回表查询完整行数据（即5000010次回表）。
     c. 将这5000010条完整行数据放入结果集
     d. 丢弃前offset条（5000000条），返回最后size条（10条）。
   • 问题：回表5000010次，且丢弃了5000000条完整行数据，浪费了大量I/O和CPU。【回表次数太多】

2. 覆盖索引+子查询

   • 子查询：

     sql 复制

     SELECT id FROM table ORDER BY indexed_col LIMIT offset, size
     

     • 由于SELECT的字段（id）在索引中（覆盖索引），所以子查询只需要扫描索引，不需要回表。
     • 扫描索引，按顺序读取(offset + size)条索引记录（5000010条），但只获取索引中的主键id，不回表。
     • 丢弃前offset条，返回最后size条（10条）主键值。

   • 外层查询：用这10个主键值，通过主键索引（聚簇索引）回表查询完整行数据（10次回表）。

   • 总回表次数：10次。

另外，在正常分页查询中，如果ORDER BY的字段是聚簇索引(主键)，此时不需要回表查询，但 SELECT * 会读取整行数据，假设是 limit 100000,10，执行器中需要处理100010条完整数据行 压力极大；而子查询中，仅提取 id ，执行器只用处理1000010个id+10行完整数据，而id通常很小（8字节），所以内存和CPU开销更低

因此此时覆盖索引+子查询效率仍然更高

注意~~~~

对 ORDER BY 字段创建索引是 ”覆盖索引+子查询“能提升查询效率的前提，没有这个索引，第一次覆盖查询就无从谈起

当 ORDER BY 字段无任何索引 时，“覆盖索引+子查询”优化 不仅不会提升效率，反而通常比正常分页查询更慢。【关键：两种方式均需全表扫描 + filesort】

由于 ORDER BY non_indexed_col 无索引，两者都必须进行全表扫描 + 外部排序（filesort），无法避免对全表 N 行数据的处理

执行过程对比（以 InnoDB 为例）

• 子查询只查 id，排序时数据量更小？
  事实：MySQL 的 filesort 优化机制（5.6+）在 SELECT * 且无覆盖索引时，默认仅存储 (排序键, 主键) 进行排序，而非整行数据。这意味着即使 SELECT *，排序阶段也不会加载整行数据，而是只保存排序字段和主键用于后续回表。因此两者的排序数据量差别不大。
• “覆盖索引”失效：子查询中 SELECT id 本可构成覆盖索引，但因 ORDER BY non_indexed_col 无索引，无法避免回表扫描聚簇索引获取排序值，覆盖索引优势彻底丧失。
• 所以此时“覆盖索引+子查询” 因临时表+JOIN 增加固定开销，通常略慢

游标分页

• 原理：用上一页最后一条记录的主键作为下一页的起始条件

SELECT * FROM t_order 
WHERE id > #{lastMaxId} 
ORDER BY id 
LIMIT 20

SELECT * FROM t_order 
WHERE payment_time > #{lastPaymentTime} 
ORDER BY payment_time 
LIMIT 20

为什么效率更高？

1. 扫描数据量小(充分利用索引)：数据库不会从头开始扫描，而是直接利用索引定位到 id = lastMaxId 的位置，从该位置开始扫描20条记录

2. 无需回表：WHERE id > #{lastMaxId} 会使用主键索引进行范围扫描，所以不需要回表查询

限制条件：

• 要求排序字段具有唯一性或组合唯一性（否则可能出现重复或漏数据）；

  例如：若按 created_at 排序，但多个记录时间相同，可能导致下一页漏掉部分数据（可以使用 ORDER BY created_at, id 来解决[组合唯一性]）

• 不支持任意页跳转（只能顺序翻页）

如何选择

在绝大多数场景下，游标分页性能更优，尤其是在深度分页场景

• 游标分页完全避免OFFSET，不扫描前面的数据，而“覆盖索引+子查询”仍然需要扫描offset+limit条（只是用索引扫描）

  “覆盖索引+子查询”优化了回表过程，但是问题解决的不够根本

  • 所以 游标分页性能稳定，不受页码影响，而“覆盖索引+子查询”方案 offset越大，性能越差

什么常见场景适合“覆盖索引+子查询”？

1. 业务场景需要允许用户跳转到任意页(比如直接跳转到100页)，而不是顺序翻页
2. offset 不是非常大

业务/架构优化

业务层限制

• 限制最大页码（如最多100页）
• 限制最大offset值（如最多10000）

物化视图/汇总表

• 预先计算并存储分页结果
• 适合数据变化不频繁的场景

分库分表

• 数据量极大时（亿级），通过分库分表分散查询压力
• 结合ShardingSphere等中间件实现

引入搜索引擎

• 使用Elasticsearch等搜索引擎处理大数据量查询

  Elasticsearch 内置提供了 search_after 机制，本质上就是游标分页的实现

• 适合复杂查询和全文检索场景

都看到这里了，给我点个免费的赞吧 (*❦ω❦)👍