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]);

四、避坑指南

陷阱	破局方案
忽略排序字段唯一性	复合排序末尾加主键确保连续
未使用覆盖索引	确保`WHERE`+`ORDER BY`字段有联合索引
盲目使用 OFFSET	深度分页必用游标方案