简单聊聊联合索引

迷南。 2023-01-13 10:22 121阅读 0赞

# 你真的了解联合索引吗？ #

但凡准备过面试，都一定了解这样两个词，”联合索引，最左匹配”

# 从一个问题开始 #

这里先用前两天美团面试官的一个问题抛砖引玉一下吧

面试官：联合索引了解吗，假设有表A，字段a,b,c，我建立一个索引abc，什么情况下可以使用这个索引？或者说，select \* from A where 后跟什么条件可以使用这个索引？

了解联合索引的你，马上就会脱口而出，a开头的条件，但是考虑到MySQL可以做优化，你随机补充道，包含a就可以使用！

面试官：还有吗？

> 空气凝滞，小丑竟是我自己。

事实上，这道题目看似在提问联合索引的条件，我们忽略了他另一个重要的特性，他同时也可以作为索引覆盖来加速我们的查询。

![tempImage1617442648747][]

来，我们确认一下正确答案：a,b,c,ab,ac,bc,abc以及他们的全排列，都会使用索引（没说怎么用）

其中 bc，b，c并非索引查询，而是在二级索引abc上做了全表扫描。

我们使用explain来进行尝试验证，首先需要大伙了解explain的用法

我们在上面的问题中，为了能证明并非\*号的问题，我们新建一个字段d

## 使用Explain进行测试 ##

![image-20210403201609784][]

explain SELECT a, b, c FROM test_index WHERE b=1 and c=1 and a=2;

![image-20210403203542204][]

没问题，MySQL优化器调整了where的顺序，使得其能使用联合索引abc，我们接着测试

去掉一个b，我们测试一下这种情况：a+ b/c

explain SELECT a, b, c FROM test_index WHERE  c=1 and a=2;

![image-20210403203959383][]

MySQL仍然使用了联合索引来进行优化，没问题，符合预期，下面我们来试试bc，或者单独用b做条件

explain SELECT a, b, c FROM test_index WHERE  c=1 ;
    explain SELECT a, b, c FROM test_index WHERE  c=1 and b=1 ;

![image-20210403205739464][]

![image-20210403205828965][]

我们发现即使没有办法使用索引进行查询了，MySQL仍然使用了这个联合索引进行优化查询，留意这里的type，这里两者的type与之前是不同的，这两种为index，而之前为ref

> 关于explain命令的type
> 
> 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`，
> 
> 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`

所以，没问题，所有的语句都会使用索引进行优化，只不过没有a时，将进行**全索引扫描**，有a在的时候直接定位，效率更快（黑心工厂MySQL，苦逼索引007

我们从头开始认识一下MySQL的索引，

> 这里暂且不谈HashMap，我们来讲讲B+数索引

索引是啥？

Innodb存储引擎，数据存储的形式是聚簇索引（其实就是一个B+数）

我们这里提到的B+树并不是这里，索引所在的树，通常是在另一个空间存储的，所以我们称之为二级索引

索引的结构如下：

![img][]

当我们需要一条数据时，索引将能提供**稳定的**查询效率

同时，索引可以有联合索引的形式，如果我们使用的是联合索引，结构是下面这样的

> 图片偷懒，来源：https://juejin.cn/post/6844904073955639304

![img][img 1]

因为在索引树上进行可能使用索引列的查询时，速度相比主树更快（因为按照你的索引列有序排列），并且如果二级索引树已经有了足够的信息，就不再需要主树了（减少IO）

所以出现了在**二级索引上进行了全部扫描**

[tempImage1617442648747]: /images/20221022/7e534019f952457e8569ee8692a11d97.png
[image-20210403201609784]: /images/20221022/5cf4c5ffa47a4be4a2824829e31e97f5.png
[image-20210403203542204]: /images/20221022/3cff5854af2445c5a98ace18228937bb.png
[image-20210403203959383]: /images/20221022/043850fb3e50428bb88f2d36a3be439c.png
[image-20210403205739464]: /images/20221022/2f20a57972b44d28a8ac82f8e227e0f8.png
[image-20210403205828965]: /images/20221022/73ddfa3ed7dd41a3928134f05ce8af0d.png
[img]: /images/20221022/9100613ee5514437a3f3a9cd2f27b05b.png
[img 1]: /images/20221022/efea5bf3e0974b97b24e3fedfa036237.png