Skip to content

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

一次数据查询所经历的磁盘I/O次数并非固定值，它受到多种因素的综合影响，包括索引结构、查询条件的复杂度、索引覆盖情况以及数据库优化策略等。在深入探讨这个问题时，理解B+树的存储结构、MySQL的索引下推（Index Condition Pushdown, ICP）等技术至关重要。

通常情况下，一次复合索引查询的磁盘I/O可以分为两个主要阶段：索引访问阶段和数据表访问阶段（回表）。

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

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

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

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

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

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

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

索引下推（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。 在理想情况下，如果索引中已经包含了所有需要判断的列，甚至可以直接在索引层面完成所有过滤，完全避免回表。

场景分析：

假设我们有一个用户表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的情况。

覆盖索引

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

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