回表查询与索引覆盖

发布网友发布时间：2022-10-18 09:46

共1个回答

热心网友时间：2023-11-11 18:21

首先需要从InnoDB的索引实现说起，InnoDB有两类索引：
1、聚集索引（clustered index）
2.、普通索引，也叫二级索引（secondary index）

聚集索引的叶子节点储存的是行记录，且每张表有且仅有一个聚集索引：
(1)、如果表中定义了主键，那么该主键索引就是聚集索引。
(2)、如果表中没有定义主键，那么从第一个字段开始往后，第一个 not null的unique列为聚集索引。
(3)、如果(1)、(2)都不满足，那么InnoDB会创建一个隐藏的row-id作为聚集索引。
所以，主键查询特别的快，直接定位到行记录。

普通索引的叶子节点保存的是索引字段值和主键值，并不是完整的行记录。

假设我们现在有一张表:
表结构为 t (id PK , name KEY , sex , flag)，其中id为聚集索引，name为普通索引。
表中有四条这样的数据:
(1, shenjian, m, A)、(3, zhangsan, m, A)、(5, lisi, m, A)、(9, wangwu, f, B )

上图分别为它们在索引中的储存状态:
(1)、聚集索引的叶子节点储存了整个行记录。
(2)、普通索引只储存了name字段值和id值。

在假设现在有一条查询sql select * from t where name = 'lisi'; 索引是怎么执行的呢?

mysql官方没有对此进行定义，但是有这方面的概述: 只需要通过一颗索引树，就可以获取sql所需要的列数据，无需回表，速度更快。
仍旧选择之前的表，但是我们的sql换成： select id,name from t where name = 'lisi'; ,查看explain计划：

可以看到，命中了name索引，索引储存了id和name，所以 Extra 项的值为 Using index ，无需回表查询，符合索引覆盖，效率高。

另外一条sql : select id,name,sex from t where name = 'lisi'; ,查看explain计划：

可以看到，同样的，也命中了name索引，但是索引的叶子节点并没有储存sex字段值，所以 Extra 项的值为 Using index condition ，需要回表查询通过聚集索引获取到sex的值，不符合索引覆盖，效率相对较低。
解决的办法是，将name索引修改为name、sex的组合索引，这样第二条sql查询就也满足索引覆盖的要求了。

文章摘取出处: https://www.cnblogs.com/myseries/p/11265849.html