MySQL- 技术专题 - 联合索引最左前缀匹配原则
![MySQL-技术专题-联合索引最左前缀匹配原则](https://static001.geekbang.org/infoq/43/438ce11b10f86533fab4da0e247177be.png)
1.联合索引
多个列的值组成的索引,当第一个列的值重复时,按照后面的组合必须查找数据,复合索引相当于字典的
二级目录,当前一个值一致时,再使用后一个值做筛选
2.联合索引的最左前缀匹配原则
MySQL数据库中不仅可以对某一列建立索引,还可以对多列建立一个联合索引,而联合索引存在一个最左前缀匹配原则的概念,如果基于B+树来理解这个最左前缀匹配原则,相对来说就会容易很很多了。
首先我们基于文首的这张表建立一个联合索引:
![](https://static001.geekbang.org/infoq/1d/1dd659c2761f39108dd03ece95ea824f.png)
我们已经了解了索引的数据结构是一颗B+树,也了解了B+树优化查询效率的其中一个因素就是对数据进行了排序,那么我们在创建idx_obj这个索引的时候,也就相当于创建了一颗B+树索引,而这个索引就是依据联合索引的成员来进行排序,这里是age,height,weight。
InnoDB中只要有主键被定义,那么主键列被作为一个聚簇索引,而其它索引都将被作为非聚簇索引,所以自然而然的,这个索引就会是一个非聚簇索引。
所以根据这些我们可以得出结论:
1、idx_obj这个索引会根据age,height,weight进行排序
2、idx_obj这个索引是一个非聚簇索引,查询时需要回表
单列排序很简单,比大小嘛,谁都会,但是多列排序是基于什么原则的呢(重点)?
实际上在MySQL中,联合索引的排序有这么一个原则,从左往右依次比较大小,就拿刚才建立的索引
举例子,他会先去比较age的大小,如果age的大小相同,那么比较height的大小,如果height也无法比较大小, 那么就比较weight的大小,最终对这个索引进行排序。
那么根据这个排序我们也可以画出一个B+树,这里就不像上文画的那么详细了,简化一下:
数据:
![](https://static001.geekbang.org/infoq/57/575f5ccbee78eed572d2b23b5e5436b4.png)
B+树:
![](https://static001.geekbang.org/infoq/84/84ede6dbd7c2190a93e4a32c7d321d71.jpeg?x-oss-process=image/resize,p_80/auto-orient,1)
注意:此时由于是非聚簇索引,所以叶子节点不在有数据,而是存了一个主键索引,最终会通过主键索引来回表查询数据。
B+树的结构有了,就可以通过这个来理解最左前缀匹配原则了。
我们先写一个查询语句
![](https://static001.geekbang.org/infoq/79/79b05ff59e7b24fa2593176b15cad6e3.png)
毋庸置疑,这条语句一定会走idx_obj这个索引。
那么我们再看一个语句:
![](https://static001.geekbang.org/infoq/11/1131e11089815f4f3d35f74e2fb32606.png)
思考一下,这条SQL会走索引吗?
上文中我们提到了一个多列的排序原则,是从左到右进行比较然后排序的,而我们的idx_obj这个索引从左到右依次是age,height,weight,所以当我们使用height和weight来作为查询条件时,由于age的缺失,那么就无法从age来进行比较了。
看到这里可能有小伙伴会有疑问,那如果直接用height和weight来进行比较不可以吗?显然是不可以的,可以举个例子,我们把缺失的这一列写作一个问号,那么这条语句的查询条件就变成了?27,那么我们从这课B+树的根节点开始,根节点上有127和365,那么以height和weight来进行比较的话,走的一定是127这一边,如果缺失的列数字是大于3的呢?比如427,527,627,那么如果走索引来查询数据,将会丢失数据,错误查询。所以这种情况下是绝对不会走索引进行查询的。这就是最左前缀匹配原则的成因。
1、最左前缀匹配原则,MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引,d是无法使用索引的,如果建立(a,b,d,c)的索引则都可以使用到,a、b、d的顺序可以任意调整。
2、=和in可以乱序,比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式。
根据我们了解的可以得出结论:
只要无法进行排序比较大小的,就无法走联合索引。
可以再看几个语句:
![](https://static001.geekbang.org/infoq/af/afb5e48bcd0580230f2c48ba00408793.png)
这条语句是可以走idx_obj索引的,因为它可以通过比较 (12?<365)。
![](https://static001.geekbang.org/infoq/7a/7a5d5a6e58d886a6069baaa88d73b908.png)
这条语句也是可以走ind_obj索引的,因为它也可以通过比较(1?7<365),走左子树,但是实际上weight并没有用到索引,因为根据最左匹配原则,如果有两页的age都等于1,那么会去比较height,但是height在这里并不作为查询条件,所以MySQL会将这两页全都加载到内存中进行最后的weight字段的比较,进行扫描查询。
![](https://static001.geekbang.org/infoq/6c/6c147045bb8814d25dbcd18b2bb0f5f5.png)
这条语句不会走索引,但是可以走索引。这句话是什么意思呢?这条SQL很特殊,由于其存在可以比较的索引,所以它走索引也可以查询出结果,但是由于这种情况是范围查询并且是全字段查询,如果走索引,还需要进行回表,MySQL查询优化器就会认为走索引的效率比全表扫描还要低,所以MySQL会去优化它,让他直接进行全表扫描。
![](https://static001.geekbang.org/infoq/06/0665e30f9b099d805d2d3a287439eea2.png)
这条语句是可以走索引的,因为它可以通过age进行比较,但是weight不会用到索引,因为height是范围查找,与第二条语句类似,如果有两页的height都大于2,那么MySQL会将两页的数据都加载进内存,然后再来通过weight匹配正确的数据。
评论