核心内容摘要
小柔被房东3P玩出水了：情感纠葛离奇曲折的故事
“MySQL 扫描 1,000,010 行 → 磁盘 I/O 爆炸”是深度分页查询的典型性能灾难。
其本质是全表扫描 随机 I/O 内存不足的三重叠加效应。
执行机制为什么必须扫描 1,000,010 行▶
LIMIT offset, size的执行逻辑SELECT*FROMordersORDERBYidLIMIT1000000,10;步骤按id排序若无索引则 filesort逐行读取前 1,000,010 行丢弃前 1,000,000 行返回后 10 行核心认知MySQL 无法“跳过”中间行必须物理扫描所有前置行▶
索引的影响场景扫描方式I/O 类型无索引全表扫描 filesort随机 I/OHDD ≈ 10ms/行有主键索引索引扫描顺序 I/OHDD ≈
1ms/行关键点即使有索引仍需扫描 1,000,010 行仅避免排序开销
I/O 路径磁盘如何响应▶
Buffer Pool 未命中流程否MySQL 请求第 N 行Buffer Pool 有缓存?发起磁盘 I/OHDD 随机寻道 4ms 旋转延迟 4ms 传输
1ms加载 16KB 页到内存返回数据问题每行可能分布在不同页 →每次 I/O 仅获取 1 行▶
HDD vs SSD 性能对比指标HDDSSD随机读 I/O 延迟8–12ms
05–
1ms1,000,010 行总耗时
78 小时
67 分钟⚠️现实即使使用 SSD100 万行扫描仍需分钟级响应
量化影响资源消耗分析▶
时间成本HDD 场景1,000,010 行 × 10ms 10,000,100ms ≈
78 小时SSD 场景1,000,010 行 ×
1ms 100,001ms ≈
67 分钟▶
内存与 CPU 开销内存排序缓冲区sort_buffer_size溢出 → 创建磁盘临时文件CPU行比较操作ORDER BY消耗大量 CPU 周期▶
系统级影响锁竞争InnoDB 行锁持有时间过长 → 阻塞其他写操作连接池耗尽单个慢查询占用连接 → 新请求被拒绝
破局之道游标分页▶
原理-- 记录上一页最后 id1000000SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT10;优势利用聚簇索引直接定位起始点仅扫描 10 行而非 1,000,010 行▶
性能对比指标OFFSET 方案游标方案扫描行数1,000,01010HDD 耗时
78 小时
1msSSD 耗时
67 分钟
001ms▶
实现要点必须使用自增主键或唯一索引前端传递游标值如?cursor1000000复合排序需加主键兜底SELECT*FROMlogsWHERE(created_at,id)(
,
ORDERBYcreated_at,idLIMIT10;
避坑指南陷阱破局方案忽略排序字段唯一性复合排序末尾加主键确保连续未使用覆盖索引确保WHEREORDER BY字段有联合索引盲目使用 OFFSET深度分页必用游标方案
终极心法**“扫描不是查询而是性能的悬崖——当你使用 OFFSET你在支付线性成本当你切换游标你在享受常数时间当你利用索引你在消除随机 I/O。
真正的查询优化始于对执行计划的敬畏成于对细节的精控。
”结语从今天起深度分页必用游标方案WHERE id last_id用EXPLAIN验证执行计划typerange监控慢查询日志long_query_time1因为最好的分页不是跳过百万行而是精准定位下一程。