SELECT * FROM table LIMIT 1000000, 10 是典型的 深度分页查询，表面看是“跳过 100 万行取 10 行”，实则触发 全表扫描 + 内存排序，导致 磁盘 I/O 爆炸、响应时间飙升。

---

一、执行机制：MySQL 如何处理 LIMIT offset, size？

▶ 1. 执行流程

▶ 2. 关键问题

• 必须扫描 offset + size 行：
  • 即使只需 10 行，也需读取 1,000,010 行
• 无法跳过中间行：
  • MySQL 不存储“第 N 行的物理位置”（除非聚簇索引）

💡 核心认知：
LIMIT offset, size 的成本 = O(offset + size)，而非 O(size)

---

二、性能陷阱：为什么深度分页如此昂贵？

▶ 1. 磁盘 I/O 爆炸

• 场景：
  • 表数据未完全缓存到 Buffer Pool
  • 每读一行需 1 次磁盘随机读（HDD ≈ 10ms/次）
• 计算：
  • 1,000,010 行 × 10ms = 2.78 小时（理论值，实际因缓存略低）

▶ 2. 内存与 CPU 浪费

• 排序开销：
  • 若无合适索引，需 filesort（磁盘临时文件）
• 网络传输：
  • 丢弃的 100 万行仍需从存储引擎传到 Server 层

▶ 3. 锁竞争加剧

• InnoDB 行锁：
  • 扫描过程中持有行锁 → 阻塞其他写操作
• MVCC 版本链：
  • 大量历史版本堆积 → Undo Log 膨胀

---

三、工程优化：四种替代方案

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

• 原理：
  • 记录上一页最后一条记录的 排序字段值
  • 下一页从该值开始查询
• 示例：

  -- 第一页
  SELECT * FROM orders 
  WHERE id > 0 
  ORDER BY id 
  LIMIT 10;
  
  -- 第二页（假设上一页最大 id=100）
  SELECT * FROM orders 
  WHERE id > 100 
  ORDER BY id 
  LIMIT 10;
  

• 优势：
  • 执行计划：range → 直接定位起始点
  • 成本：O(size)，与 offset 无关

▶ 方案 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]);
  

▶ 方案 4：禁止深度分页

• 产品设计：
  • Google 搜索仅显示前 10 页
  • 电商网站限制“跳转到第 N 页”
• 技术实现：

  if ($page > 100) {
      throw new Exception('超过最大页数');
  }
  

---

四、避坑指南

陷阱	破局方案
盲目使用 OFFSET	深度分页必用游标方案
忽略排序字段选择	游标字段必须是索引且唯一（如自增 ID）
宽表全字段查询	仅 SELECT 必要字段，减少回表

---

五、终极心法

**“LIMIT 不是分页，
而是性能的悬崖——

• 当你 使用 OFFSET，
  你在支付线性成本；
• 当你 切换游标，
  你在享受常数时间；
• 当你 限制深度，
  你在守护系统。

真正的查询优化，
始于对执行计划的敬畏，
成于对细节的精控。”

---

结语

从今天起：

1. 深度分页必用游标方案（WHERE id > last_id）
2. 用 EXPLAIN 验证执行计划（避免 Using filesort）
3. 产品层限制最大页数（如 ≤ 100 页）

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