阿国运维网技术分享平台:桌面运维、网络运维、系统运维、服务器运维(及云服务器),精品软件分享、阿国网络、尽在北京运维网
之前我们在《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 或更高版本,确保索引下推功能开启。 在复杂查询条件中,通过索引下推尽量过滤无用数据。





