MySQL

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

编程Cookbook

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

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

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

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

索引回顾

聚集索引（Clustered Index）

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

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

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

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

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

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

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

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

MyISAM和InnoDB的文件存储结构

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

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

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

  1. 表结构文件：.frm 文件

    • 存储表的元数据（与 MyISAM 相同）。

  2. 表空间文件：

    • 共享表空间：默认情况下，所有表的数据和索引存储在一个共享表空间文件（如 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 执行流程（无索引下推）：

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

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

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

执行过程对比：

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

优点：

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

对比与关系

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

优化建议

  1. 尽量避免回表：

    • 为常用查询创建覆盖索引，减少回表操作。
    • 使用精简的索引列，避免索引过大导致额外开销。

  2. 使用覆盖索引：

    • 覆盖索引对性能提升显著，特别是常用的查询条件可以完全利用索引。
    • 针对常见查询场景设计复合索引，覆盖需要查询的所有列。

  3. 利用索引下推：

    • 升级 MySQL 至 5.6 或更高版本，确保索引下推功能开启。
    • 在复杂查询条件中，通过索引下推尽量过滤无用数据。