详解MySQL覆盖索引、索引下推

深藏阁楼爱情的钟 2023-09-27 21:20 171阅读 0赞

**目录**

1.覆盖索引

1.1.概述

1.2.聚集索引、非聚集索引

1.3.回表查询

1.4.覆盖索引

2.索引下推

--------------------

## 1.覆盖索引 ##

### 1.1.概述 ###

覆盖索引，是为了避免“回表查询”，从而降低查询耗时的一种使用索引的方法，所以要聊覆盖索引首先我们要知道什么是"回表查询，“回表查询”是因为MySQL的索引结构决定的，是因为非聚集索引要找聚集索引拿数据而出现的现象，所以我们又要先了解MySQL中的聚集索引和非聚集索引。

文章的脉络就是先聊聚集索引、非聚集索引是怎么带来了“回表查询”的问题，然后怎么用用覆盖索引解决这个问题。

### 1.2.聚集索引、非聚集索引 ###

关于索引基础、B树、B+树等相关的详细内容可以看博主之前的两篇文章：

[数据结构（8）树形结构——B树、B+树（含完整建树过程）\_b+树构造过程\_\_BugMan的博客-CSDN博客][8_B_B_b_BugMan_-CSDN]

[详解MySQL索引\_\_BugMan的博客-CSDN博客][MySQL_BugMan_-CSDN]

众所周知mysql的索引有两种：

*  聚集索引
 *  非聚集索引

聚集索引：

聚集索引的叶子节点上挂着的是一条分支上的所有索引对应的数据。

![260e0bfdc23f4465bea040eb91ed36bb.png][]

MySQL会默认为每张表维护一个聚集索引，如果表有主键，那么这个聚集索引就是主键索引，如果没有主键，MySQL仍然会为每个表维护一个隐藏的聚集索引。在innodb引擎中，这个隐藏的聚集索引是一个6字节长的ROWID，每个数据行都会有一个隐藏的ROWID。

非聚集索引：

非聚集索引叶子结点上挂的是索引值和对应值指向的聚集索引的值，也就是说真正的数据一定是要找聚集索引拿的。

![6406303148ab41fa82d16ea70f8ae060.png][]

### 1.3.回表查询 ###

上面聊完非聚集索引一定会找聚集索引拿数据后，其实“回表查询”问题就已经引出来了，所谓的“回表查询”，指的是当我们使用非聚集索引查询时我们是无法直接得到数据，而需要通过叶子结点上的聚集索引的值去聚集索引的B+树里查找数据的过程。

“回表查询”带来的问题一目了然，索引是存在磁盘上的，查完一棵B+树后，还要去查第二棵B+树进行遍历匹配，查第二棵B+树就带来了额外的IO耗时，磁盘IO本来就是磁盘旋转+磁头上下摆动的机械动作，这个动作在计算机的运行体系里是个极度慢的动作，极度的耗时。

### 1.4.覆盖索引 ###

覆盖索引，指的是select具体字段代替select \*，将复合索引建立在要查询的具体字段上，这样在非聚集索引上，也就是第一颗B+树上就能拿到数据，从而不用“回表查询”。

做个实验：

我建了一个sys\_user表，里面随即插入了100万条数据：

![c59fe54fe64f439384e45f4679500c2a.png][]

然后在username上建立一个索引：

create index index_username on sys_user(username);

未使用覆盖索引查询其中一条数据：

explain select * from sys_user where username='test_852107';

![1e491b8820854ffc96b13d757b942926.png][]

使用覆盖索引查询其中一条数据：

explain select username from sys_user where username='test_852107';

![a15b2c71a68247ce8c823942432b17c3.png][]

## 2.索引下推 ##

索引下推，是自MySQL5.6版本引入的一个新特性，目的也是减少“回表查询”，从而提升整体的查询效率。

假设表中有三个字段name、age、sex，

SQL写成这样是没问题的，完美满足左前缀原则：

select * from tuser where name ='zou' and age=10 and sex=1;

但如果SQL写成这样，就会出问题，索引断开了，sex这个索引值是没有用上的：

select * from tuser where name ='zou' and sex=1;

在5.6之前索引断了就不会往下走了，匹配到的所有name='zou'的数据，挨个“回表”，无疑这将会造成很多无意义的额外IO开销：

![93aa25aaee724fcf82cb1fb2360450f0.png][]

而索引下推的意思是，索引断裂、走不下去后，不会立即回表，还会向下推一步再继续比较其它索引字段，从而减少无意义的额外IO。以上表为例，就是会匹配到name='zou'后发现索引断掉了也不会立即回表，而是继续向下比对sex是不是等于1：

![f9ce6ea5112645a2a7788ae4348c73af.png][]

[8_B_B_b_BugMan_-CSDN]: https://blog.csdn.net/Joker_ZJN/article/details/128153273?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522168381847216800182785908%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=168381847216800182785908&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-128153273-null-null.blog_rank_default&utm_term=B&spm=1018.2226.3001.4450
[MySQL_BugMan_-CSDN]: https://blog.csdn.net/Joker_ZJN/article/details/130467852?spm=1001.2014.3001.5501#4.%E7%B4%A2%E5%BC%95%E7%9A%84%E5%88%86%E7%B1%BB
[260e0bfdc23f4465bea040eb91ed36bb.png]: https://img-blog.csdnimg.cn/260e0bfdc23f4465bea040eb91ed36bb.png
[6406303148ab41fa82d16ea70f8ae060.png]: https://img-blog.csdnimg.cn/6406303148ab41fa82d16ea70f8ae060.png
[c59fe54fe64f439384e45f4679500c2a.png]: https://img-blog.csdnimg.cn/c59fe54fe64f439384e45f4679500c2a.png
[1e491b8820854ffc96b13d757b942926.png]: https://img-blog.csdnimg.cn/1e491b8820854ffc96b13d757b942926.png
[a15b2c71a68247ce8c823942432b17c3.png]: https://img-blog.csdnimg.cn/a15b2c71a68247ce8c823942432b17c3.png
[93aa25aaee724fcf82cb1fb2360450f0.png]: https://img-blog.csdnimg.cn/93aa25aaee724fcf82cb1fb2360450f0.png
[f9ce6ea5112645a2a7788ae4348c73af.png]: https://img-blog.csdnimg.cn/f9ce6ea5112645a2a7788ae4348c73af.png