较为深入的解析联合索引最左匹配原则

电玩女神 2024-03-29 15:59 47阅读 0赞

## 前言 ##

经历了一次SQL优化的需求，发现联合索引并不是网上大部分讲的那样，经过自己试验+百度，算是明白了一点，小记录一波。

## 基础篇 ##

### 定义 ###

多个字段组合在一起形成的索引，且可以有不同的性质，目前我知道的有：普通联合索引、联合唯一索引。

联合唯一索引是在普通联合索引的基础上增加了唯一约束，即几个字段的值加起来不能有重复。

### 特性 ###

![db90bc8caf4ec196f7845babc3ffb51f.png][]

用红线画出的部分先不看。

用图可以这样看：

![83b63548ecee61f2f1d5805de225c20c.png][]

一条SQL的执行中，每个表最多只能走一个索引，用联合索引可以让多个字段都走到索引，一般来说走的字段越多越好。

改正：mysql5.1之后引入了index merge（索引合并）技术，可以让一条SQL用多个索引合并起来查询，这个后面会讲，但大部分场景用不到这个技术。

### 例子 ###

假设在一个表中创建 ( a ,b ,c ) 三个列的联合索引

别处引用：
    (1) select * from mytable where a=3 and b=5 and c=4;
    # abc 三列都使用索引，而且都有效
    
    (2) select * from mytable where  c=4 and b=6 and a=3;
    # mysql没有那么笨，不会因为书写顺序而无法识辨索引。
    # where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样。
    
    (3) select * from mytable where a=3 and c=7;
    # a 用到索引，sql中没有使用 b列，b列中断，c没有用到索引
    
    (4) select * from mytable where a=3 and b>7 and c=3;
    # a 用到索引，b也用到索引，c没有用到。
    # 因为 b是范围索引，所以b处断点，复合索引中后序的列即使出现，索引也是无效的。
    
    (5) select * from mytable where b=3 and c=4;
    # sql中没有使用a列， 所以b，c 就无法使用到索引
    
    (6) select * from mytable where a>4 and b=7 and c=9;
    # a 用到索引， a是范围索引，索引在a处中断， b、c没有使用索引
    
    (7) select * from mytable where a=3 order by b;
    # a用到了索引，b在结果排序中也用到了索引的效果。前面说过，a下面任意一段的b是排好序的
    
    (8) select * from mytable where a=3 order by c;
    # a 用到了索引，sql中没有使用 b列，索引中断，c处没有使用索引，在 Extra列 可以看到 filesort
    
    (9) select * from mytable where b=3 order by a;
    # 此sql中，先b，后a，导致 b=3 索引无效，排序a也索引无效。
    
    
    补充：
    (10) select * from mytable where c=3 and a=7;
    # (3)的衍生例子，a 用到索引。原因同(2)
    
    (11) select * from mytable where a=3 and b>=7 and c=3;
    # (4)的衍生例子，但 a、b、c 都用到索引，其中b用了一半索引
    # 这是写下这篇博客的主要原因，原因下面详细说。
    
    (12) select * from mytable where a=3 and b between 2 and 5 and c=3;
    # (4)的衍生例子，但 a、b、c 都用到索引
    # 这是写下这篇博客的主要原因，原因下面详细说。
    
    (13) select * from mytable where a=3 and b like 'bbb%' and c=3; —— 假设b字段临时变为varchar
    # (4)的衍生例子，但 a、b、c 都用到索引
    # 这是写下这篇博客的主要原因，原因下面详细说。
    
    (14) select * from mytable where a=3 and b>7 and c=3;
    # (4)的衍生例子，但 a、b 用到索引，c用到一半索引
    # 这是写下这篇博客的主要原因，原因下面详细说。
    
    (15) select * from mytable where a=3 and b in (7,8) and c=3;
    # abc都用到索引，in不会中断索引匹配

## 进阶篇 ##

### 索引失效的情况 ###

1.  范围查询的范围过大，比如>=等范围条件的匹配数量较多，或in后面的条数较多。

1.  联合索引时违背最左匹配原则

1.  条件筛选中有or —— 进一步分析

1.  面经上的各种句子：

1.  1.有or必全有索引;  
    2.复合索引未用左列字段;  
    3.like以%开头;  
    4.需要类型转换;  
    5.where中索引列有运算;  
    6.where中索引列使用了函数;  
    7.如果mysql觉得全表扫描更快时（数据少）;

在下面详细分析这几点：

#### 联合索引时违背最左匹配原则 ####

我们在很多地方都看到说：

***最左优先,以最左边的为起点任何连续的索引都能匹配上。***同时遇到范围查询(>、<、between、like)就会停止匹配。

甚至有些帖子还会把>=、<=也作为中断联合索引的条件写上去，但真的是这样吗？

先说结论：

**>、<、like 的%放在左边，会中断联合索引；**

**>=、<=、between、like的%只出现在右边，不会中断联合索引。**

测试过程：

为了更详细的分析，在查询语句前不用explain了，改为explain extend，后者可以展示更详细的执行过程比如range。

1.  >=:

![ef6b95a36ec434f916961086374b36ef.jpeg][]

![b0e361248c66b64112d4fdce1d032e03.jpeg][]

![da299161f7a70ba040baa04d1196d1e8.jpeg][]

分析range：deleted、status、source字段都走了索引，ds字段走了半个。因为MIN的range里面第二个字段显示了'20221202'，而MAX的显示的是MAX。正好对应了范围是\[20221202,MAX)。且因为后面都走了，所以>=并没有中断索引。

1.  >

![a8b294fb49502241bb1ae46b73e13b41.jpeg][]

![899d6e2dd560f2342eef12351b07a7e3.jpeg][]

![760a5f9beb0ec0d5ded0d4d99396910c.jpeg][]

分析range：左边那一列到ds后就中断了，右边的继续往下走了，判断应该是有一部分会中断索引，另一部分不确定是不是真的没中断。

1.  between

![ee458f5b6043085bcb33f713bcddd631.jpeg][]

![f50ab5e9167f7ada5ffc22f7504c12ef.jpeg][]

分析range：ds后面的字段没有中断索引，且ds字段最小值和最大值都被定义了，很不错。

1.  like

右like：

![df1707442f650d1b76254bc8f65ca47f.jpeg][]

![cfb8724643439885ded1fc27979bbf01.jpeg][]

![01b06397542774edb090532552802121.jpeg][]

分析range：索引没有中断，ds那个字段有几个空格表示模糊匹配。（顺便试了一下like concat，结果一样）

左like：

![ec61c14010c6520dfabd0b06c6225ac2.jpeg][]

![dc74191718fe7f0356e50d7cb51fd2c7.jpeg][]

![382737038596c612d5ce72b97a87c210.jpeg][]

分析range：看不懂了。。但应该是不太好的。

1.  !=

![54ab2c7cff0cf329e5081a18c76f83f1.jpeg][]

![44cc5b9f9b6657d2e21010fc359fe9a1.jpeg][]

分析range：看起来是以'20221202'为分界分成了两部分，且大部分都没有走索引。

**分析原因：**

待补充。。。

#### 条件筛选中有or ####

1.  ##### or的两边是不同表时，一定不走索引 #####

1.  ##### 几个or之间期望用到同一个索引时，必须都满足该索引。例如: #####

1.  联合索引（a,b,c）

1.  select \* from table where a = 1 and b = 2 or b = 3，走不到索引，因为or右边没有走到索引。

1.  select \* from table where a = 1 and b = 2 or (a = 3 and b = 4 and c = 5)，ab两个字段会走到索引，因为or左边用了ab，右边用了abc，取交集。

1.  ##### index merge（索引合并）的情况。 #####

根据官方介绍，索引合并可以分为三种算法

*  [索引合并交集访问算法][Link 1]

*  [索引合并联合访问算法][Link 2]

*  [索引合并排序联合访问算法][Link 3]

经过自己的测试和理解，交集访问算法的意思应该是几个or条件之间的数据可能存在交集，比如：

where a = 1 or b = 2；而联合访问算法是不可能存在交集，比如：where a = 1 and b =2 or a =3 and b = 4； 排序联合则是在上一种的基础上增加了范围查询。

当mysql认为有其他执行方案的效率优于使用索引合并时，也许会不使用索引合并。

实操如下：

表knowledge\_info表有两个唯一索引，分别是(deleted,a,b,c),(deleted,a,d,e)，还有本身自带的主键索引。

*  交集访问算法

where id = 1 or deleted = 2 or (deleted = 3 and d = 3)

![e0660d4d8060a078438c9a340d560275.png][]

发现同一张表用到了三个索引，三个索引的range是分开的：

![4849e540a8f5962fa258df7ee2ba3e6e.png][]

![703b3ce0badd4ae873611512aeca8583.png][]

![755b4750157eaef12e14fec52f01ba9f.png][]

*  联合访问算法

*  where deleted = 3 and a > 3 or deleted = 4 and a = 3

（这里我用等于或范围查询的执行计划是一样的，只要能走到索引就行）

自己测试的执行计划中并没有出现Using union，也可能是我使用的不是原生mysql的原因。

![853ac9d3c7e76fdb0044d145fb67a8bf.png][]

但看详细range时，发现唯一索引的range有两个

![0e5e1e15e5eee102da0001bb74731e8f.png][]

也可能是查询优化器觉得不需要索引合并。

*  where id > 1 and id < 10 or id = 16

![6f25431dde8efcd205ed5d9e05990b80.png][]

一样也是索引名没有展现，但range变为两个。

*  排序联合访问算法

和联合访问算法类似，只是关系有范围查询。上面的例子其实算是排序的算法，但存的图有限就一起用了。测试的时候没发现什么不同。

### 创建索引的思路 ###

1.  联合索引的优先级大于普通索引，因为联合索引可以合并多个字段，且复用率高。

1.  联合唯一索引的优先级大于普通联合索引（业务允许的情况下），因为唯一索引的速度更快，且有些时候可以作为数据唯一性的兜底判断，且可以配合on duplicate key update技术实现新增或更新。

1.  经常作为查询条件；或经常被作为join连接字段；或经常用作分组、排序；或如果某个重要的SQL语句的查询的字段有限，可以把查询的字段都增加索引以避免回表查询。

1.  索引字段需要有区分度，比如如果字段是性别，值只有男、女，那么这个字段就不适合作为索引，或者说不能只依靠这个索引来搜索。

1.  最近就出现了类似的问题，线上表只有1.6w条数据，查询速度却达到了秒级。就是因为索引失效导致了全表扫描。

1.  那张表是数据挖掘信息表，索引是deleted（删除标识）、ds（业务日期，8位）、status（状态）。业务逻辑是前端展示前30天的挖掘数据，设计时没有考虑到删除过期数据的必要性，导致线上表所有数据的deleted值都是0。然后ds因为是>=-30天，范围也较大；status在没有选筛选条件时无值，即便选了也没区分度，大部分的状态都是PENDING。就导致MySQL用全表扫描来执行了。

1.  后来的解决是，先执行脚本把30天以前的deleted值都改为id；然后代码中在接收消息插入数据的地方判断是否有新的过期数据，对其删除；再然后联合索引增加一个字段值。性能提升了几十倍。

### 总结 ###

有时执行计划显示走了索引且耗时短，实际改完后测试的并没有短，甚至可能耗时更长了，只能说索引这部分应该是有点玄学在的。

但基本的一些知识基本还是固定的，只是索引合并那块我实在不想看到了，以后如果有机会接触到再说吧。

[db90bc8caf4ec196f7845babc3ffb51f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/ee730d3147504635a0eeb29705e03c7e.png
[83b63548ecee61f2f1d5805de225c20c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/394e4c202e554be4a57fe14e8aa9f891.png
[ef6b95a36ec434f916961086374b36ef.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/5d877433e4e04765b39f745ab41a8233.png
[b0e361248c66b64112d4fdce1d032e03.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/6f95b08365804aadabbd942a0cd1af70.png
[da299161f7a70ba040baa04d1196d1e8.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/d646f78293404f03833165450eefe9c5.png
[a8b294fb49502241bb1ae46b73e13b41.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/cbe502c98f9a49c38459e19811210b2c.png
[899d6e2dd560f2342eef12351b07a7e3.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/d90421ffc7a54929acc78e4dd07d70af.png
[760a5f9beb0ec0d5ded0d4d99396910c.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/3e448dc5fb554ecc9a56d8123a4f9d2e.png
[ee458f5b6043085bcb33f713bcddd631.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/4d15808546864509ac17363b723bc0ea.png
[f50ab5e9167f7ada5ffc22f7504c12ef.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/6f2d7e21933d4b9f863db5374f62fe95.png
[df1707442f650d1b76254bc8f65ca47f.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/e85b91c8633a485793eb702a4165d6c7.png
[cfb8724643439885ded1fc27979bbf01.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/b90a77858a98443c9aa1ab4c02b0094e.png
[01b06397542774edb090532552802121.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/530a311c118a45e4b176c527051f2901.png
[ec61c14010c6520dfabd0b06c6225ac2.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/52dae65b564e4e46a1bad0300dac64e1.png
[dc74191718fe7f0356e50d7cb51fd2c7.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/6899808b8e96422d826b569e76a5d967.png
[382737038596c612d5ce72b97a87c210.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/b994cae7d22e4929a15320e4fabc7598.png
[54ab2c7cff0cf329e5081a18c76f83f1.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/f66e98a1da9541018e314ec211e37fc7.png
[44cc5b9f9b6657d2e21010fc359fe9a1.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/bc627ec6543b4be6a1b0da1c84c9d68f.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html#index-merge-intersection
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html#index-merge-union
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html#index-merge-sort-union
[e0660d4d8060a078438c9a340d560275.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/fa83f4cf1a814ea9a5851670ab600973.png
[4849e540a8f5962fa258df7ee2ba3e6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/d9690aa55a1249aabdb949832a7d71d5.png
[703b3ce0badd4ae873611512aeca8583.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/3b6364fed90c4d2b8b161f8cc52b582d.png
[755b4750157eaef12e14fec52f01ba9f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/854a695b0ef84c0e8e019ed8c2805619.png
[853ac9d3c7e76fdb0044d145fb67a8bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/9b4a07ca2ad84e838a185d775d21749f.png
[0e5e1e15e5eee102da0001bb74731e8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/4fb3913366d3478f9e0c643faaea39a4.png
[6f25431dde8efcd205ed5d9e05990b80.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/28/4cba2eaf3688495385de533e1027fd38.png