“MySQL 扫描 1,000,010 行 → 磁盘 I/O 爆炸”是深度分页查询的典型性能灾难。其本质是全表扫描 + 随机 I/O + 内存不足的三重叠加效应。
一、执行机制：为什么必须扫描 1,000,010 行？
▶ 1.LIMIT offset, size的执行逻辑
SELECT*FROMordersORDERBYidLIMIT1000000,10;- 步骤：
- 按
id排序（若无索引则 filesort） - 逐行读取前 1,000,010 行
- 丢弃前 1,000,000 行
- 返回后 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=1000000SELECT*FROMordersWHEREid>1000000ORDERBYidLIMIT10;- 优势：
- 利用聚簇索引直接定位起始点
- 仅扫描 10 行（而非 1,000,010 行）
▶ 2.性能对比
| 指标 | OFFSET 方案 | 游标方案 |
|---|---|---|
| 扫描行数 | 1,000,010 | 10 |
| HDD 耗时 | 2.78 小时 | 0.1ms |
| SSD 耗时 | 1.67 分钟 | 0.001ms |
▶ 3.实现要点
- 必须使用自增主键（或唯一索引）
- 前端传递游标值（如
?cursor=1000000） - 复合排序需加主键兜底：
SELECT*FROMlogsWHERE(created_at,id)>('2023-01-01',1000)ORDERBYcreated_at,idLIMIT10;
五、避坑指南
| 陷阱 | 破局方案 |
|---|---|
| 忽略排序字段唯一性 | 复合排序末尾加主键确保连续 |
| 未使用覆盖索引 | 确保WHERE+ORDER BY字段有联合索引 |
| 盲目使用 OFFSET | 深度分页必用游标方案 |
六、终极心法
**“扫描不是查询，
而是性能的悬崖——
- 当你使用 OFFSET，
你在支付线性成本；- 当你切换游标，
你在享受常数时间；- 当你利用索引，
你在消除随机 I/O。真正的查询优化，
始于对执行计划的敬畏，
成于对细节的精控。”
结语
从今天起：
- 深度分页必用游标方案（
WHERE id > last_id） - 用
EXPLAIN验证执行计划（type=range） - 监控慢查询日志（
long_query_time=1）
因为最好的分页，
不是跳过百万行，
而是精准定位下一程。
