五分钟告诉你什么是MySQL的覆盖索引

Love The Way You Lie 2022-09-04 09:54 169阅读 0赞

### 文章目录 ###

*   *  五分钟告诉你什么是MySQL的覆盖索引
     *   *  覆盖索引
         *  总结
         *  参考

## 五分钟告诉你什么是MySQL的覆盖索引 ##

前面我们已经对MySQL索引底层原理多少有一定的了解了，还不是很了解的小伙伴可以看我之前的博文:[《不会吧不会吧，难道还有人不了解MySQL索引底层原理?》][MySQL] 和 [《MySQL底层为什么要选用B+树作为索引的数据结构呢？》][MySQL_B]

其实在之前的博文中也提到了覆盖索引啦，然后今天就单独把这个拿出来讲一讲这个MySQL的性能优化: 覆盖索引。

### 覆盖索引 ###

> 覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

首先要了解覆盖索引之前，你必须要了解什么是聚簇索引和非聚簇索引，对这个不了解的小伙伴可以看我之前的博文，还有一个必须要了解的就是回表，覆盖索引其实就是跟到底需不需要回表有直接的关系的。

什么是回表呢? 通俗的讲就是，如果索引的列在 select 所需获得的列中 或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。只有非聚簇索引是需要回表的，所以如果你懂得非聚簇索引的存储的结构，你自然就知道为啥需要回表了。

> 注意：不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

我这里举一个例子你就可以很快明白了。

create table t1
    (
        a int primary key ,
        b int,
        c int,
        d int,
        e varchar(20)
    )engine=InnoDB;
    
    insert into t1 value (4, 3, 1, 1, 'd');
    insert into t1 value (1, 1, 1, 1, 'a');
    insert into t1 value (8, 8, 8, 8, 'h');
    insert into t1 value (2, 2, 2, 2, 'b');
    insert into t1 value (5, 2, 3, 5, 'e');
    insert into t1 value (3, 3, 2, 2, 'c');
    insert into t1 value (7, 4, 5, 5, 'g');
    insert into t1 value (6, 6, 4, 4, 'f');
    
    create index idx_t1_bcd on t1(b,c,d); -- 创建复合索引

接下来我们来看这些SQL，看看哪些SQL满足了覆盖索引。

explain select * from t1 where b = 1; -- 回表
    explain select e from t1 where b = 1; -- 回表
    explain select b from t1 where b = 1; -- 不用回表 Using index 覆盖索引
    explain select b,c from t1 where b = 1; -- 不用回表 Using index 覆盖索引
    explain select b,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引
    explain select b,c,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引
    explain select a,b,c,d from t1 where b = 1; -- 不用回表 Using index 覆盖索引

其实上面已经给出答案了。

我们通过执行计划就可以知道是不是满足了覆盖索引的条件了。

![在这里插入图片描述][ca7510ca1ff346e59fc8e439f7fab439.png]

如果Extra使用了**Using index**，就说明了他是满足了覆盖索引了，这个就是覆盖索引的标志了。

而下面这种很明显就是不满足索引覆盖了。

![在这里插入图片描述][05b6f1b42c1b48358c96823b6f356898.png]

因为我们建立的是复合索引，所以就是非聚簇索引，非聚簇索引的叶子节点上会存放键的值，也就是我们的(b,c,d) 这三个字段，还会存放主键a字段用于回表操作。

所以只要查询的列是你建立的索引字段再加上主键字段，都是满足索引覆盖的，这个时候我们在非聚簇索引的叶子节点就能给够获取到这些数据，不需要回表操作。

### 总结 ###

如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列(并且这些列是有索引的)，不可以是SELECT \*，但有的人说可以全部列都加索引，但如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做。

### 参考 ###

[回表与覆盖索引，索引下推][Link 1]

[mysql高效索引之覆盖索引][mysql]

[什么叫做覆盖索引？][Link 2]

[MySQL]: https://blog.csdn.net/cckevincyh/article/details/119618674?spm=1001.2014.3001.5501
[MySQL_B]: https://blog.csdn.net/cckevincyh/article/details/119003282?spm=1001.2014.3001.5501
[ca7510ca1ff346e59fc8e439f7fab439.png]: /images/20220829/d00f52dca5684e3986eab4f1e645a626.png
[05b6f1b42c1b48358c96823b6f356898.png]: /images/20220829/f7b4cd25d4df469aaecb9f08262e732c.png
[Link 1]: https://www.jianshu.com/p/d0d3de6832b9
[mysql]: https://www.cnblogs.com/chenpingzhao/p/4776981.html
[Link 2]: https://www.cnblogs.com/happyflyingpig/p/7662881.html