MySQL数据库——索引潜规则（回表查询、索引覆盖、索引下推）

大家好，这里是编程Cookbook。本文详细介绍MySQL索引的三个潜规则——回表查询、索引覆盖、索引下推，以提升数据库的性能。

文章目录

索引回顾
聚集索引（Clustered Index）
非聚集索引（Secondary Index/辅助索引/二级索引）
为什么非主键索引的叶子节点存储主键值？

回表查询
MyISAM和InnoDB的文件存储结构

索引覆盖
索引下推
对比与关系
优化建议

之前我们在《MySQL数据库——索引介绍》文章中介绍了按存储方式将索引划分为聚集索引和非聚集索引，由此引出了一些索引设计的潜规则（回表查询、索引覆盖、索引下推）。

回表查询、索引覆盖、索引下推是 InnoDB 的核心优化特性，尤其是索引下推，这是 InnoDB 的独有能力。
如果使用 MyISAM 或 Memory 存储引擎，只能利用回表查询和索引覆盖，无法使用索引下推优化。

索引回顾

聚集索引（Clustered Index）

InnoDB 中的主键索引就是聚簇索引。

叶子节点存储整行数据。
索引即数据，查询到索引即查询到数据。
表中行的物理顺序与键值的逻辑（索引）顺序相同。
每个表只能有一个聚集索引，因为索引只能按照一种方式排序。

非聚集索引（Secondary Index/辅助索引/二级索引）

MyISAM 引擎中的所有索引都是非聚簇索引。InnoDB 中，所有非主键索引都是非聚簇索引。

叶子节点存储的是主键值（对应行数据的PK）。
查询时需要先通过非聚集索引找到主键值，再通过主键回到聚集索引中查询完整数据行（即回表查询）。
需要扫描两次索引树，因此效率较低。

为什么非主键索引的叶子节点存储主键值？

降低二级索引的维护成本：

如果数据发生更新（如行移动或数据页分裂），只需修改（唯一的）聚簇索引，而无需重新构建非聚簇索引。

回表查询

MyISAM和InnoDB的文件存储结构

MyISAM 使用以下文件存储数据和索引：

表结构文件：.frm 文件
- 存储表的元数据（表定义、字段信息等）。
数据文件：.MYD 文件
- 存储实际的数据行。
索引文件：.MYI 文件
- 存储所有索引（包括主键索引、唯一索引、普通索引等）。

InnoDB 使用以下文件存储数据和索引：

表结构文件：.frm 文件
- 存储表的元数据（与 MyISAM 相同）。
表空间文件：
- 共享表空间：默认情况下，所有表的数据和索引存储在一个共享表空间文件（如 ibdata1）。
- 独立表空间：启用独立表空间模式后，每个表的数据和索引存储在独立的 .ibd 文件中。
注意：InnoDB的聚簇索引和非聚簇索引存储在同一个 .ibd 文件中，但在逻辑结构上是分开的。
- 聚簇索引：
  - 索引的叶子节点存储数据行本身。
  - 决定了数据的物理存储顺序。
- 非聚簇索引（二级索引）：
  - 索引的叶子节点存储主键值，用于通过主键值回表到聚簇索引获取数据行。

定义：

当 MySQL 使用非聚簇索引（Secondary Index）进行查询时，索引的叶子节点通常存储的是主键值或数据行的物理地址。
如果查询的列不在该索引中，MySQL 必须通过索引获取主键值或物理地址，然后回到聚簇索引（或数据表）中查询所需的数据行，这个过程称为回表。

场景：

查询的字段不完全被索引覆盖时，如果能够覆盖，则不需要回表。
使用非聚簇索引查询时，必须回表获取完整数据。

示例：

-- 假设表中有一个非聚簇索引 idx_name（列 name），主键为 id。
SELECT id, address FROM table WHERE name = 'John';

查询条件中使用了索引列 name，但结果需要返回 id 和 address。
MySQL 需要先通过索引 idx_name 找到匹配的记录的主键值（id），再回表获取 address 数据。

索引覆盖

定义：

如果查询的列全部包含在索引中，MySQL 可以直接从索引中获取数据，而不需要回表，这种索引被称为覆盖索引。
优化目的：通过覆盖索引避免回表操作，从而提高查询效率。

场景：

查询字段较少，能够完全由索引覆盖时。
常用于只需要索引列的统计或检索场景。

示例：

-- 假设表中有一个复合索引 idx_name_address（列 name 和 address）。
SELECT name, address FROM table WHERE name = 'John';

查询需要的列 name 和 address 都包含在索引 idx_name_address 中，MySQL 可以直接从索引中获取数据，无需回表。

索引下推

定义：

索引下推是 MySQL 5.6 引入的一种查询优化技术。
在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
在使用索引进行查询时，MySQL 会将部分查询条件下推到存储引擎层，在存储引擎中通过索引过滤数据行，而不是将所有数据都返回到 MySQL 服务器层过滤。
优化目的：减少返回 MySQL 层的数据量，提高查询效率。

场景：

查询中包含多个条件，而索引只支持部分条件时，MySQL 会尝试在存储引擎中尽量过滤更多的数据。

示例：

建立联合索引：

CREATE INDEX `username` ON user2(`name`, `age`);

执行查询：

SELECT * FROM user2 WHERE name LIKE 'j%' AND age = 99;

MySQL 5.5 执行流程（无索引下推）：

MySQL 的 Server 层调用存储引擎，找到第一个以 j 开头的 name。
存储引擎使用 username 联合索引在 B+ 树中定位符合 name LIKE 'j%' 的记录。
存储引擎返回该记录的主键值（id），通过回表操作，去主键索引中找到完整数据行。
回表后，将数据返回给 Server 层。
Server 层判断 age 是否等于 99：
- 如果 age = 99，将记录返回给客户端。
- 如果 age != 99，丢弃该记录。
Server 层继续请求下一条记录，重复上述过程。

MySQL 5.6 执行流程（有索引下推）：

MySQL 的 Server 层调用存储引擎，找到第一个以 j 开头的 name。
存储引擎使用 username 联合索引在 B+ 树中定位符合 name LIKE 'j%' 的记录。
存储引擎检查记录中是否包含 age 信息：
- 如果 age = 99，存储引擎返回主键值（id），然后回表查询完整数据。
- 如果 age != 99，存储引擎直接跳过该记录，不回表，继续读取下一条记录。
减少了不必要的回表操作，提高了查询效率。

执行过程对比：

步骤	MySQL 5.5	MySQL 5.6（索引下推）
过滤条件处理位置	Server 层过滤 `age = 99` 条件	存储引擎层先过滤 `age = 99` 条件
回表次数	每一条符合索引条件的记录都会回表查询完整数据行	只有符合所有条件的记录才会回表
性能	增加了 I/O 开销，查询效率较低	减少不必要的回表操作，提高查询效率

优点：

减少数据传输：通过存储引擎过滤掉不符合条件的数据，减少返回 MySQL 层的数据量。
提升性能：尤其在范围查询或复合条件查询中，索引下推的效果更明显。

对比与关系

概念	定义	关键点
回表查询	索引中无法满足查询需求，需要回到聚簇索引或数据表获取完整数据。	增加 I/O 开销，尽量避免回表（如通过覆盖索引）。
索引覆盖	查询所需列完全被索引覆盖，MySQL 可以直接从索引中获取需要的数据，无需回表。	避免回表，提高查询效率。
索引下推	将部分查询条件下推到`存储引擎层`，通过索引过滤更多数据，减少返回的数据量。	优化复合查询条件，减少不必要的数据返回 MySQL 层。