“MySQL 扫描 1,000,010 行 → 磁盘 I/O 爆炸” 是深度分页查询的典型性能灾难。其本质是 全表扫描 + 随机 I/O + 内存不足 的三重叠加效应。

---

一、执行机制：为什么必须扫描 1,000,010 行？

▶ 1. LIMIT offset, size 的执行逻辑

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

• 步骤：
  1. 按 id 排序（若无索引则 filesort）
  2. 逐行读取前 1,000,010 行
  3. 丢弃前 1,000,000 行
  4. 返回后 10 行

💡 核心认知：
MySQL 无法“跳过”中间行，必须物理扫描所有前置行

▶ 2. 索引的影响

场景	扫描方式	I/O 类型
无索引	全表扫描 + filesort	随机 I/O（HDD ≈ 10ms/行）
有主键索引	索引扫描	顺序 I/O（HDD ≈ 0.1ms/行）

📌 关键点：
即使有索引，仍需扫描 1,000,010 行（仅避免排序开销）

---

二、I/O 路径：磁盘如何响应？

▶ 1. Buffer Pool 未命中

• 流程：
• 问题：
  • 每行可能分布在不同页 → 每次 I/O 仅获取 1 行

▶ 2. HDD vs SSD 性能对比

指标	HDD	SSD
随机读 I/O 延迟	8–12ms	0.05–0.1ms
1,000,010 行总耗时	2.78 小时	1.67 分钟

⚠️ 现实：
即使使用 SSD，100 万行扫描仍需分钟级响应

---

三、量化影响：资源消耗分析

▶ 1. 时间成本

• HDD 场景：
  • 1,000,010 行 × 10ms = 10,000,100ms ≈ 2.78 小时
• SSD 场景：
  • 1,000,010 行 × 0.1ms = 100,001ms ≈ 1.67 分钟

▶ 2. 内存与 CPU 开销

• 内存：
  • 排序缓冲区（sort_buffer_size）溢出 → 创建磁盘临时文件
• CPU：
  • 行比较操作（ORDER BY）消耗大量 CPU 周期

▶ 3. 系统级影响

• 锁竞争：
  • InnoDB 行锁持有时间过长 → 阻塞其他写操作
• 连接池耗尽：
  • 单个慢查询占用连接 → 新请求被拒绝

---

四、破局之道：游标分页

▶ 1. 原理

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

• 优势：
  • 利用 聚簇索引 直接定位起始点
  • 仅扫描 10 行（而非 1,000,010 行）

▶ 2. 性能对比

指标	OFFSET 方案	游标方案
扫描行数	1,000,010	10
HDD 耗时	2.78 小时	0.1ms
SSD 耗时	1.67 分钟	0.001ms

▶ 3. 实现要点

• 必须使用自增主键（或唯一索引）
• 前端传递游标值（如 ?cursor=1000000）
• 复合排序需加主键兜底：

  SELECT * FROM logs 
  WHERE (created_at, id) > ('2023-01-01', 1000)
  ORDER BY created_at, id 
  LIMIT 10;
  

---

五、避坑指南

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

---

六、终极心法

**“扫描不是查询，
而是性能的悬崖——

• 当你 使用 OFFSET，
  你在支付线性成本；
• 当你 切换游标，
  你在享受常数时间；
• 当你 利用索引，
  你在消除随机 I/O。

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

---

结语

从今天起：

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

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