“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 时开始收集结果
• 伪代码：

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

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

---

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

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

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

  -- 上一页最后 id=1000000
  SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
  

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

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

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

  SELECT t.* FROM orders t
  INNER JOIN (
      SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
  ) tmp ON t.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	深度分页必用游标方案

---

五、终极心法

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

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

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

---

结语

从今天起：

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

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