从I/O 的物理成本理解回表和索引覆盖

问题：

1. 回表毕竟是从主键出发的，查聚集索引，然后找到具体一行的，那这里主要是消耗在哪步？
2. 随机IO和顺序IO ，索引是排好序的，按顺序找就快，那随机IO，上面的回表过程每一步都是走的类似链表的过程，按地址跳应该也挺快的啊。那为什么极致性能优化要避免回表、而走索引覆盖。

回表真正的消耗在哪？

单次回表不慢。问题出在量上：

SELECT * FROM orders WHERE user_id = 12345;
-- idx_user_id 扫出 500 个匹配的主键
-- 500 次回表，每次访问聚集索引的一个位置

这 500 个主键是什么样子的？
→ 主键值可能是：12, 580, 3291, 8812, 15003, ...
→ 在聚集索引的物理存储上是 500 个散布在各处的页
→ 每次跳到一个新位置，磁盘需要重新寻道（HDD）/ 发起新的 I/O 请求

成本在哪：不是 B+Tree 自身慢，是 500 次磁盘随机寻址。

---

随机 I/O vs 顺序 I/O

先忘掉 B+Tree，想底层：磁盘读数据是按页来的（InnoDB 一页 16KB）。

顺序 I/O：
磁盘上连续的页： [页1][页2][页3][页4][页5]...
→ 磁盘磁头不移动（或 SSD 一次大块读取）
→ 100 MB/s（HDD）/ 500+ MB/s（SSD）

随机 I/O：
磁盘上分散的页： [页1]........[页37]........[页8]........[页99]...
→ 磁头跳到位置A读一页 → 跳到位置B读一页 → 跳到位置C...
→ HDD 磁头每次移动约 5-10ms
→ 100 次随机 I/O ≈ 1 秒（HDD）
→ 10000 次随机 I/O ≈ 100 秒（HDD）

HDD 上差距最明显：顺序比随机快 100-1000 倍。

---

场景分析

回到回表场景

orders 表在磁盘上的物理存储（聚集索引，按 id 排序）：

磁盘地址:  [0x0001][0x0002][0x0003]...[0x5F00]...[0xA100]...
             ↑id=1   id=2    id=3     user_id=  user_id=
                                      12345的   12345的
                                      第1个订单  第500个订单

idx_user_id 二级索引扫出 500 个主键 → 对应 500 个散布在磁盘各处的页
→ 500 次独立磁盘寻址 → 这就是"随机 I/O"

而如果走覆盖索引：

idx_user_time (user_id, created_at, status)
索引中按 (user_id, created_at) 排序存储：

[user=12345, time=Jan]  [user=12345, time=Feb]  [user=12345, time=Mar]...
    页0x2000                  页0x2001                  页0x2002
                              ↑ 物理上是连续的！
→ 一次定位到起始位置，然后顺序往后读 → "顺序 I/O"

---

用数字感受一下

场景：查 user_id=12345 的 500 个订单

方式A：idx_user_id + 回表
→ 二级索引扫 500 条（顺序，快，~1ms）
→ 500 次回表（随机 I/O）
HDD: 500 × 5ms ≈ 2.5秒
SSD: 500 × 0.1ms ≈ 50ms（SSD 随机也快得多但仍有开销）

方式B：覆盖索引 idx(user_id, created_at, status, amount)
→ 整个查询在连续页上从头扫到尾
→ 5-10 次 I/O，全部顺序
HDD: ~5ms
SSD: <1ms

所以关键认知是：
B+Tree 内部遍历很快（2-4 次跳转），真正的差异来自回表的次数 × 每次回表的随机 I/O 成本。Buffer Pool 能把热数据缓存在内存里从而消除磁盘 I/O，但对于百万行级别的大表，不可能全装进内存，所以磁盘 I/O 模式仍然决定性能。