Skip to content

一次数据查询可能经历几次磁盘IO

1. 索引访问阶段的磁盘 I/O

数据库索引通常采用 B+树结构存储在磁盘上。B+树的特点是其高度相对较低，通常 3 到 4 层就能支持千万甚至上亿级别的数据。由于 B+树的非叶子节点只存储索引键和指向下一层节点的指针，因此每个节点可以容纳大量的索引项。

当进行一次索引查询时，数据库会从 B+树的根节点开始，逐层向下查找，直到找到叶子节点中对应的索引条目。在这个过程中，每一层节点的访问都可能对应一次磁盘 I/O 操作。因此，索引访问阶段的磁盘 I/O 次数大致等于 B+树的高度。对于一个高度为 3 的 B+树索引，通常需要 3 次磁盘 I/O 才能定位到具体的索引记录。

然而，在实际应用中，由于数据库通常会将 B+树的根节点和部分常用节点缓存到内存中，因此实际的磁盘 I/O 次数可能会少于 B+树的高度。

2. 数据表访问阶段的磁盘 I/O（回表）

在通过复合索引找到主键值后，如果查询需要获取索引之外的其他列数据，就需要根据主键值回到主键索引（聚簇索引）中去查找完整的行记录，这个过程被称为“回表”。每一次回表，都可能产生一次或多次磁盘 I/O（通常是随机 I/O，性能开销较大）。

因此，总的磁盘 I/O 次数 = 索引访问 I/O 次数 + 回表 I/O 次数。

3. 索引下推（Index Condition Pushdown, ICP）的优化

为了减少回表次数，从而降低磁盘 I/O，MySQL 5.6 版本引入了索引下推（ICP）这一重要优化。

在没有索引下推之前： 当一个查询的 WHERE 条件中，部分条件可以利用索引，而部分条件不能时（例如，对于一个 (col1, col2) 的复合索引，查询条件为 WHERE col1 = 'A' AND col2 LIKE '%B%'），存储引擎层会根据 col1 = 'A' 找到所有满足条件的索引记录，然后逐一回表到主键索引，将完整的行数据返回给 Server 层。Server 层再根据 col2 LIKE '%B%' 这个条件对返回的数据进行过滤。这个过程中，可能会有大量不符合 col2 条件的数据被回表，产生了不必要的磁盘 I/O。

引入索引下推之后： 索引下推将部分可以在索引层面直接判断的过滤条件下推到存储引擎层。在上述例子中，虽然 col2 LIKE '%B%' 无法用于索引的快速定位，但是存储引擎在遍历 col1 = 'A' 的索引记录时，可以直接在索引内部判断 col2 的值是否满足 LIKE '%B%' 的条件。只有满足条件的索引记录，才会被用于回表查询。

通过索引下推，可以显著减少回表的次数，从而大幅降低数据表访问阶段的磁盘 I/O。 在理想情况下，如果索引中已经包含了所有需要判断的列，甚至可以直接在索引层面完成所有过滤，完全避免回表。

4. 场景分析：

假设我们有一个用户表 user，其中有 (name, age) 的复合索引。

  1. 查询条件完全匹配复合索引：SELECT * FROM user WHERE name = '张三' AND age = 30;

    • 索引访问 I/O： 约等于 B+树的高度（例如 3 次），定位到 name='张三' 且 age=30 的索引记录。
    • 回表 I/O： 由于需要获取所有列（*），需要进行 1 次回表操作，获取完整的数据行。
    • 总 I/O： 大约为 3 + 1 = 4 次。

  2. 查询条件部分匹配，未使用索引下推（MySQL 5.6 之前）：SELECT * FROM user WHERE name LIKE '张%' AND age = 10;

    • 索引访问 I/O： 约等于 B+树的高度（例如 3 次），定位到第一个 name 以“张”开头的索引记录。
    • 回表 I/O： 存储引擎会遍历所有 name 以“张”开头的索引记录，并对每一条记录都进行回表。假设有 100 条符合 name LIKE '张%' 的记录，就需要 100 次回表 I/O。
    • 总 I/O： 远大于 3 + 1 次，具体取决于符合 name 条件的记录数。

  3. 查询条件部分匹配，使用索引下推（MySQL 5.6 及之后）：SELECT * FROM user WHERE name LIKE '张%' AND age = 10;

    • 索引访问 I/O： 约等于 B+树的高度（例如 3 次），定位到第一个 name 以“张”开头的索引记录。
    • 回表 I/O： 存储引擎在遍历 name 以“张”开头的索引记录时，会同时判断 age 是否等于 10。只有同时满足这两个条件的索引记录才会进行回表。假设 100 条 name 以“张”开头的记录中，只有 5 条的 age 等于 10，那么就只需要 5 次回表 I/O。
    • 总 I/O： 大约为 3 + 5 = 8 次，远少于未使用 ICP 的情况。

5. 覆盖索引

如果查询所需的所有列都包含在复合索引中，那么数据库就可以直接从索引中获取所有数据，而无需回表。这种情况被称为“覆盖索引”。

例如，对于 (name, age) 的复合索引，查询 SELECT name, age FROM user WHERE name LIKE '张%'; 就可以直接通过索引扫描完成，回表 I/O 次数为 0，极大地提升了查询性能。