阿国运维网技术分享平台：桌面运维、网络运维、系统运维、服务器运维(及云服务器)，精品软件分享、阿国网络、尽在北京运维网
之前我们在《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（索引下推） |
|---|---|---|
| 过滤条件处理位置 | age = 99 条件 | age = 99条件 |
| 回表次数 | ||
| 性能 |
优点：
减少数据传输：通过存储引擎过滤掉不符合条件的数据，减少返回 MySQL 层的数据量。 提升性能：尤其在范围查询或复合条件查询中，索引下推的效果更明显。
对比与关系
| 概念 | 定义 | 关键点 |
|---|---|---|
| 回表查询 | 索引中无法满足查询需求，需要回到聚簇索引 | |
| 索引覆盖 | ||
| 索引下推 | 将部分查询条件下推到存储引擎层 |
优化建议
尽量避免回表：
为常用查询创建覆盖索引，减少回表操作。 使用精简的索引列，避免索引过大导致额外开销。 使用覆盖索引：
覆盖索引对性能提升显著，特别是常用的查询条件可以完全利用索引。 针对常见查询场景设计复合索引，覆盖需要查询的所有列。 利用索引下推：
升级 MySQL 至 5.6 或更高版本，确保索引下推功能开启。 在复杂查询条件中，通过索引下推尽量过滤无用数据。
