数据库查询 随机 IO 的次数
1. 随机磁盘 I/O#
1.1. 随机磁盘 I/O 的定义#
B + Tree 索引的时候总是提到 随机磁盘 IO, 这是什么意思呢?
既然查询的主要事件就是 随机 IO 的时间, 一次查询怎么估算进行的随机 IO 的次数呢?
读取或写入彼此不连续、位置不可预测的物理块（页、扇区）时，就叫随机（Random）I/O
-
访问顺序跳来跳去 → 每次都要重新定位磁头或重新建立闪存地址映射
-
在数据库语境里，随机 I/O 通常指 “把单个 4 KB/8 KB/16 KB 页从持久化介质搬进内存” 这一原子动作
-
与之对立的是顺序（Sequential）I/O：按连续 LBA 逐块读写，一次“拉”大量数据，定位成本只付一次
1.2. 为什么“随机”比“顺序”慢得多？#
| 介质 | 顺序 I/O 吞吐 | 随机 I/O 吞吐 | 随机单次延迟主要来源 |
|---|---|---|---|
| 机械硬盘 (HDD) | 200 MB/s | ≈ 1 MB/s (≈ 200×差) | 寻道 + 旋转等待 (ms) |
| SATA SSD | 550 MB/s | 40 MB/s | 闪存块映射、队列对齐 (µs) |
| NVMe SSD | 3 GB/s | 500 MB/s | 同上，但控制器更快 (µs) |
随机 I/O 把定位成本均摊到 16 KB 这种很小的 payload 上, 顺序 I/O 则把定位成本摊到数百 KB ～数 MB, 故单位数据成本悬殊
1.3. 数据库为何经常出现随机 I/O？#
-
B⁺Tree 把整张表拆成很多 16 KB 页, 键值排序后物理位置不再按主键连续
-
一次查询只需要极少量行 ⇒ 只读几个离散页
-
并发事务各跑各的语句 ⇒ 访问模式高度交错
1.4. 随机 I/O 在执行计划中的角色#
- Optimizer 估算“若某条索引路径需 n 个未命中的页” ⇒ 代价 = n × (随机 I/O 单价)
- 高度 h = 3 的 B⁺Tree：理论最坏 3 次随机读即可命中目标叶子 ⇒ 成本大幅低于全表顺序扫（需读很多页）
- Buffer Pool 命中 ⇒ 把“随机 I/O”降为“逻辑读”，代价由毫秒/微秒→纳秒级
1.5. 典型日志/指标中如何看到随机 I/O#
-
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';→ 物理随机读次数 -
Performance Schema 表 file_summary_by_instance.*→ 按文件统计随机 I/O 次数与平均延迟
1.6. 一次二级索引等值查找#
-
索引根页不在缓存 → 随机读取 16 KB
-
内部页 miss → 再随机读取 16 KB
-
叶子页 miss → 第三次随机读取
如果页面已缓存, 上述步骤变成 0 次随机 I/O, 仅逻辑读
取到主键 → 去聚簇索引重走 1～3 步 ⇒ 最多再 3 次随机 I/O
因此常说 “随机 I/O 次数 ≈ 树高（+回表）”
2. 磁盘随机 IO 次数怎么计算的#
下面只讨论 InnoDB（8.x）+ B⁺Tree 的情形, 重点说明
-
随机 I/O “怎么数”
-
影响它的典型因素与估算方法
2.1. 概念对齐#
| 名词 | 含义 |
|---|---|
| 随机 I/O 次数 | 查询执行过程中不得不从磁盘把某个页读取进 Buffer Pool 的次数（若页已命中缓冲，则记作逻辑读，不计入随机 I/O） |
| 树高 h | 自根页 → 目标叶子页所需经过的层数；根也算 1 |
| 回表 (bookmark lookup) | 二级索引命中后, 再去聚簇索引找整行所触发的额外路径 |
2.2. 单条记录查询时的估算公式#
假设页都不在缓冲区（最坏情形）
| 查询类型 | 随机 I/O 估算 |
|---|---|
| PRIMARY KEY 等值 | h(PK) |
| 二级索引等值（唯一） | h(Secondary) + h(PK) |
| 二级索引非唯一，匹配 k 条 | h(Secondary) + k × h(PK) + (k – 1)（额外叶子页） |
说明
- 根页通常常驻内存，可把最顶层减 1；但在“最坏估算”里仍按 h 计算
- 如果同一叶子页内包含多条目标记录，只算 1 次随机 I/O
2.3. Range-Scan / 全表扫描#
| 场景 | 随机 I/O 估算 |
|---|---|
| 主键顺序扫整表 | 页数 = ⌈行数 ÷ 每页行数⌉ → 近似顺序 I/O，成本常按“顺序读”估算而非随机 |
| 二级索引范围 (l ≈ r) | h(Secondary) + #命中叶子页 + 回表成本（同上） |
2.4. 多表 JOIN 时如何累加#
优化器使用**块嵌套循环（BNL）或索引嵌套循环（BKA）**等模型；以下给最常见的“嵌套循环 + 内表走索引”场景：
随机 I/O ≈ outer_rows_not_in_cache × outer_row_cost + outer_rows × (inner_index_height + inner_pk_height)
其中
- outer_row_cost 指外表行本身的读取成本（可能为 0，若外表已全缓冲）
- inner_index_height 与 inner_pk_height 计算规则同上
2.5. Optimizer 如何拿到这些数字#
- 统计信息 (ANALYZE TABLE) 告诉它行数、基数、平均每页记录数
- innodb_page_size 决定页大小；结合记录长度可推算每页能放几条
- 根页、最热的几层常常假设缓存命中 → 物理 I/O 被扣掉
- 剩余部分按一次访问 = 一次随机 I/O的假设累加
SHOW STATUS LIKE 'InnoDB_buffer_pool_read%';
| 变量 | 含义 |
|---|---|
| Innodb_buffer_pool_read_requests | 逻辑读（包括命中） |
| Innodb_buffer_pool_reads | 物理随机读（未命中） |
| Innodb_buffer_pool_pages_flushed | 物理写 |
2.6. 例子 二级索引等值查询#
SELECT age
FROM users
WHERE name = 'Grace';
已知
-
h(Secondary)=3，h(PK)=3 -
'Grace'唯一
随机 I/O 估算
-
二级索引：3 页
-
回表：再次走聚簇 3 页
==> 总计 6 次（若根页 & 内部页已缓存，可能只剩 1~2 次实际磁盘 I/O）
2.7. 例子 索引嵌套循环连接 INLJ#
假设所有目标页最开始都不在 Buffer Pool（最坏路径）, 介质为 NVMe（一次随机 I/O≈ 20 µs）, 页大小 16 KB
| 表 | 主键聚簇索引 | 辅助索引 | 行数 (N) | 平均行长 | 行 / 页 (估) | 页数 |
|---|---|---|---|---|---|---|
| orders | (id) | (customer_id, id) | 1 000 000 | 150 B | ≈100 | ≈10 000 |
| order_items | (id) | (order_id, id) | 5 000 000 | 120 B | ≈120 | ≈41 700 |
- 两棵 B⁺Tree（聚簇 + 二级）高度都为 3：Root→Internal→Leaf
- 根页常驻内存，但在“最坏估算”里仍计 1 次 I/O
- 每遇到一层，可能触发一次随机 I/O；一次 miss = 1 次 I/O
查询
SELECT o.id, oi.product_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 123;
场景参数（假设）
| 名称 | 数值 | 说明 |
|---|---|---|
| orders.customer_id = 123 的订单数 | 100 行 | 外表有 100 行 |
| 一张订单的明细数 | 5 行 | 内表平均值 5 行 |
| B+Tree 高度 | 3 | 根 + 内部 + 叶子 |
| 查询开始时 Buffer Pool 为空 | 最坏路径；所有碰到的页都会 miss |
访问外表 orders 的随机 I/O
| 步骤（一次性完成） | 随机 I/O 次数 |
|---|---|
| a. 走二级复合索引 (customer_id, id) | 3 页 |
| b. 回到聚簇索引拿整行 | 3 页 |
| c. 继续扫描叶子页把 100 行都读完 | 1 页（100 行刚好在同一叶子） |
| 小计 | 7 次 |
说明
- “走索引 3 页”= 根 1 + 内部 1 + 叶子 1
- “回表再 3 页” 同理
- 最后那张叶子页已经在内存，往后读同页不再算随机 I/O
查询 customer_id = 123 的所有订单及其订单中包含的商品信息, 这种查询场景
orders是外表,order_items oi是内表:
查询从 orders 表（外表）开始
WHERE o.customer_id = 123条件会先筛选出orders表中所有customer_id = 123的记录
- 一次成本 IO 次数: 7 次, 这 7 次 加载了一页数据, 假设这一页包含了 100 个订单数据,
- 如果 100 个订单需要两页, 则需要 7 + 1 = 8 次 IO, 而不是 7 * 2 = 14, 第二个叶子页只有 1 I/O，而不是再付 7 I/O
- 路径上的根页、内部页已经在内存 → 不再 miss
- 只需把第二个叶子页从磁盘搬进来 → 1 I/O
数据库将上一步的
orders表结果与order_items表（内表）进行INNER JOIN, 根据ON oi.order_id = o.id条件匹配
- 一次成本 IO 次数: 同上 7 次, 这只是 对于一个订单, 找出其所有 items 的查询, 平均 5 个 items, 一页肯定放得下, 所以就是 7 次, 不是 上面的那种 可能为 7 + 1 次 或者 7 * 2 次, 即 不会是 2 页, 三页 只用来放了 5 个 item, 当然也有可能,除非 item 很大, 一个就超过 8kb, 2 * 8 >= 16 kb > 一页的大小
- 因为有 100 个订单, 查询 100 次, 所以 100 * 7 = 700 次 IO
- 这么计算是错的, 因为 根页、内部页只在第一次 miss, 当处理第 2 ~ 100 张订单时:
- 二级索引根 / 内部页 已经在 Buffer Pool
- 聚簇索引根 / 内部页 也已在 Buffer Pool
- 因此后 99 次只剩：
- 索引叶子 1 次 IO + 聚簇叶子 1 次 IO = 2 次, 即
2 IO / 订单而不是 7- 第一张订单 7 I/O + 后面 99 张 × 2 I/O ≈ 205 I/O, 远小于 700
总计 内表 order_items 表进行了 205 次 IO + 外表 7 次 IO = 212 次IO
可见 真正决定数量级的是：根/内部页是否命中, 优化器在成本模型里正是用“根页大概率命中、内部页可能命中、叶子易 miss”这套概率，去预估 I/O 量