MySQL覆盖索引的使用

目录

1. 覆盖索引的概述

2. 索引B+树的数据分布

3. 什么是回表查询？

4. 覆盖索引的好处

4.1 避免回表查询

4.2 随机IO变为顺序IO加快查找效率

5. 覆盖索引的使用注意

5.1 查询字段只能少不能多

5.2 注意索引失效

---

1. 覆盖索引的概述

用一句话来说，"索引中包含的字段，能够满足需要查询的字段，这就叫覆盖索引"。

所以，覆盖索引严格意义上来说并不是索引，更像是一种现象。

2. 索引B+树的数据分布

这里我举个例子，假设一张表有C1，C2，C3， C4，C5 五个字段，且C1是主键。

在聚簇索引中：叶子节点都会包含完整的一条数据；那么在聚簇索引形成的B+树中，每个叶子节点都会完整的包含五个字段的信息；

在非聚簇索引中：例如C2和C3组成联合索引，那么联合索引的叶子节点只会包含索引列的数据和主键信息，非聚簇索引形成的B+树叶子节点只保存 C2，C3，C1 的数据信息，不会保存C4和C5的信息。如果想要通过C2，C3非聚簇索引字段查询C4和C5的信息，就要进行回表查询。

3. 什么是回表查询？

回表查就是先查询非聚簇索引的B+树，通过非索引B+树查询到主键信息，然后再去聚簇索引的B+树中通过主键查询自己想要查询的信息。

拿上面的五个字段举例，假设我要通过C2字段查询C4和C5字段，那么数据库在查询的时候，就会先IO加载联合索引的B+树，通过给出的C2值进行查询，但是联合索引B+树中并没有C4和C5的值，所以数据库就会通过C2先查询到对应的主键C1的值，然后再一次IO加载聚簇索引形成的B+树，通过刚才查到的C1字段的值，再到聚簇索引B+树中查找C4和C5的值。即先查询了联合索引的B+树获取主键值，又根据主键值查询了聚簇索引的B+树获取想要的数据，这个查询行为就叫做回表。

4. 覆盖索引的好处

4.1 避免回表查询

说回最开始的话题，假设我要通过C2字段查询C2和C3字段的值

SELECT C2,C3 FROM 表名 WHERE C2 = '查询条件'

那么数据库在查询的时候，因为C2和C3有联合索引，所以数据库就会通过联合索引去查，查找之后发现，我们想要查询的字段C2和C3的信息在联合索引B+树中就有保存，不需要回表查询聚簇索引的B+树。这种情况就称之为覆盖索引，想要查询的字段值在非聚簇索引中就有，不需要回表查询，极大地提高了查询效率。

4.2 随机IO变为顺序IO加快查找效率

同学们想，联合索引形成的B+树，字段顺序是（C2，C3，C1），主键C1放在了最后，只是存储主键数据，并不参与联合索引的排列规则，所以联合索引中主键的大小顺序并不一定和联合索引字段的排列顺序保持一致，大小会参差不齐，这样一来，在进行回表查询的时候，就会在不同的数据页中进行随机IO，效率比较偏低，而在覆盖索引的情况下，我们直接将主键的信息查询返回即可，不需要回表再进行随机IO，说白了还是不需要进行回表查询了。

5. 覆盖索引的使用注意

5.1 查询字段只能少不能多

上面我查询的是C2和C3，我也可以指查询其中一个，可以少于联合索引B+树中的字段信息，不能多，如果想要查询的字段联合索引中没有，必须要回表查询。如下，我只查询C2或者C3，让然满足索引覆盖，不需要进行回表查询；

SELECT C2 FROM 表名 WHERE C2 = '查询条件'
SELECT C3 FROM 表名 WHERE C2 = '查询条件'

对于覆盖索引而言，大多应用于联合索引，联合索引是由多个字段组成的，此时再查询的过程中一定一定要注意，索引是否失效。

5.2 注意索引失效

如下查询语句，如果以C3为查询条件，索引是失效的，数据库会进行全表扫描，索引要满足最左前缀原则，

SELECT C2,C3 FROM 表名 WHERE C3 = '查询条件'

在联合索引B+树中，数据的排列顺序是（C2，C3，C1），所以想让联合索引生效，至少要有C2字段的参与，直接越过C2字段以C3字段为查询条件索引会失效！！！

SELECT C2 FROM 表名 WHERE C3 = '查询条件'
SELECT C3 FROM 表名 WHERE C3 = '查询条件'