MySQL 无法“跳过”中间行，必须物理扫描所有前置行的庖丁解牛

“MySQL 无法‘跳过’中间行，必须物理扫描所有前置行”是深度分页（LIMIT offset, size）性能灾难的根本原因。这并非 MySQL 的设计缺陷，而是由其存储引擎架构与 SQL 语义决定的必然结果。

一、B+ 树结构：为什么不能“跳过”？

▶ 1.InnoDB 聚簇索引布局

• 数据存储：
  • 行数据按主键顺序存储在B+ 树叶子节点
  • 叶子节点通过双向链表连接
• 示例：

  [1] ↔ [2] ↔ [3] ↔ ... ↔ [1000000] ↔ [1000001] ↔ ...

▶ 2.LIMIT 1000000, 10的执行路径

• 关键限制：
  • B+ 树不存储“第 N 行的物理位置”
  • 必须从头（或上一次位置）顺序遍历链表

💡核心认知：
B+ 树优化的是“范围查询”，而非“随机跳转”

二、执行模型：SQL 语义的约束

▶ 1.SQL 标准的要求

• ORDER BY+LIMIT语义：
  • 必须返回排序后第 (offset+1) 到 (offset+size) 行
  • 无法假设数据分布（如自增 ID 连续）

▶ 2.执行器的工作方式

• 步骤：
  1. 打开表扫描（Table Scan）或索引扫描（Index Scan）
  2. 逐行读取并计数
  3. 当计数 > offset 时开始收集结果
• 伪代码：

  intcount=0;while(row=fetch_next_row()){if(count>=offset){result.add(row);if(result.size()==size)break;}count++;}

📌关键点：
执行器必须保证结果的正确性，无法“猜测”跳过行

三、替代方案：如何实现真正的“跳过”？

▶ 方案 1：游标分页（推荐）

• 原理：
  • 利用已知的排序字段值直接定位起始点
• 示例：

  -- 上一页最后 id=1000000SELECT*FROMordersWHEREid>1000000ORDERBYidLIMIT10;

• 执行计划：
  • type: range→ 直接跳转到 B+ 树的id=1000000位置
  • 仅扫描 10 行

▶ 方案 2：延迟关联（Deferred Join）

• 原理：
  • 先通过覆盖索引获取主键，再回表
• 示例：

  SELECTt.*FROMorders tINNERJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,10)tmpONt.id=tmp.id;

• 适用场景：
  • 主键为聚簇索引（InnoDB）
  • 覆盖索引可避免回表

▶ 方案 3：记录偏移量（静态数据）

• 原理：
  • 预先计算每页的起始主键，存储到缓存
• 示例：

  // Redis 存储第 100000 页起始 ID$startId=Redis::get('page_100000_start_id');$rows=DB::select("SELECT * FROM orders WHERE id >= ? ORDER BY id LIMIT 10",[$startId]);

四、避坑指南

五、终极心法

**“跳过不是功能，
而是索引的舞蹈——

• 当你利用游标，
  你在跳过扫描；
• 当你延迟关联，
  你在减少回表；
• 当你预计算偏移，
  你在铸造缓存。

真正的查询优化，
始于对存储的敬畏，
成于对细节的精控。”

结语

从今天起：

1. 深度分页必用游标方案（WHERE id > last_id）
2. 用EXPLAIN验证执行计划（type=range）
3. 监控慢查询日志（long_query_time=1）

因为最好的分页，
不是跳过百万行，
而是精准定位下一程。