全网都在说一个错误的结论

咱们好，我是小林。
咱们在背MySQL八股文的时分，是不是经常看到这句话。
联合索引的最左匹配准则会一向向右匹配直到遇到规模查询(>、<、between、like)就会中止匹配。
我顺手在网上搜了下，根本全部都是这个定论，好像这个定论咱们都耳濡目染了，应该大多数人都觉得这个定论是正确的吧。
我在昨晚折腾了几个试验，发现这个定论并不全对！去掉「between和like」这个定论就没问题了。
通过试验的证明，我得出的定论是这样的：
联合索引的最左匹配准则，在遇到规模查询（如>、<）的时分，就会中止匹配，也便是规模查询的字段能够用到联合索引，可是在规模查询字段后边的字段无法用到联合索引。可是，关于>=、<=、BETWEEN、like前缀匹配这四种规模查询，并不会中止匹配。
接下来，我会用几个试验比方来阐明这个定论。
B+Tree索引
首要，先来知道下B+Tree索引。
MySQL的InnoDB存储引擎会为每一张数据库表创立一个「聚簇索引」来保存表的数据，聚簇索引默认运用的是B+Tree索引。
为了让咱们理解B+Tree索引的存储和查询的进程，接下来我通过一个简略比方，阐明一下B+Tree索引在存储数据中的详细完成。
假定有一张产品表，表里有这些数据：
这些数据，存储在B+Tree索引时是长什么姿态的？
B+Tree是一种多叉树，叶子节点才寄存数据，非叶子节点只寄存索引，并且每个节点里的数据是按主键值（id）次序寄存的，每一层父节点的索引值都会出现在下层子节点的索引值中，因而在叶子节点中，包含了一切的索引值信息，并且每一个叶子节点都指向下一个叶子节点，构成一个链表，便于规模查询。
聚簇索引的B+Tree如图所示：
假定，履行了select*fromt_productwhereid=5查询句子，该查询句子的条件是找到id（主键）为5的这条记载。由于B+Tree是一个有序的数据结构，所以能够通过二分查找算法快速定位到这条记载，这也便是咱们常说的索引查询，详细进程如下：
从根节点开端，将5与根节点的索引数据(1，10，20)比较，5在1和10之间，依据二分查找算法，找到第二层的索引数据(1，4，7)；
在第二层的索引数据(1，4，7)中进行查找，由于5在4和7之间，依据二分查找算法，找到第三层的索引数据（4，5，6）；
在叶子节点的索引数据（4，5，6）中进行查找，然后咱们找到了索引值为5的这条记载。
聚簇索引只能用于主键字段的快速查询，假如想完成「非主键字段」的快速查询，咱们就要针对「非主键字段」创立索引，这种索引称作为「二级索引」。二级索引相同根据B+Tree完成的，不过二级索引的叶子节点寄存的是主键值，不是实践数据。
我这儿将前面的产品表中的product_no（产品编码）字段设置为二级索引，那么二级索引的B+Tree如下图，其中非叶子的索引值是product_no（图中橙色部分），叶子节点存储的数据是主键值（图中绿色部分）。
假如我用product_no二级索引查询产品，如下查询句子：
select*fromproductwhereproduct_no=’0002′;
会先在二级索引的B+Tree中快速查找到product_no为0002的二级索引记载，然后获取主键值，然后运用主键值在主键索引的B+Tree中快速查询到对应的叶子节点，然后获取完整的记载。这个进程叫「回表」，也便是说要查两个B+Tree才能查到数据。如下图：

不过，当查询的数据是能在二级索引的B+Tree的叶子节点里查询到，这时就不用再查主键索引查，比方下面这条查询句子：
selectidfromproductwhereproduct_no=’0002′;
这种在二级索引的B+Tree就能查询到结果的进程就叫作「覆盖索引」，也便是只需求查一个B+Tree就能找到数据。
什么是联合索引？
前文我将product_no字段设置为了索引，这种二级索引只要一个字段。假如将多个字段组合成一个索引，那么这种二级索引就被称为联合索引。
比方，将产品表中的product_no和name字段组合成联合索引`(product_no,name)“，创立联合索引的方法如下：
CREATEINDEXindex_product_no_nameONproduct(product_no,name);
联合索引“(product_no,name)`的B+Tree示意图如下：
能够看到，联合索引的非叶子节点用两个字段的值作为B+Tree的索引值。
联合索引的B+Tree是先按product_no进行排序，然后再product_no相同的状况再按name字段排序。记住这句话，很重要！
最左匹配准则
运用联合索引时，存在最左匹配准则，也便是依照最左优先的方法进行索引的匹配。
在运用联合索引进行查询的时分，假如不遵从「最左匹配准则」，联合索引会失效，这样就无法运用到索引快速查询的特性了。
比方，假如创立了一个(a,b,c)联合索引，假如查询条件是以下这几种，就能够运用联合索引：
wherea=1；
wherea=1andb=2andc=3；
wherea=1andb=2；
需求留意的是，由于有查询优化器，所以a字段在where子句的次序并不重要。可是，假如查询条件是以下这几种，由于不契合最左匹配准则，所以就无法匹配上联合索引，联合索引就会失效:
whereb=2；
wherec=3；
whereb=2andc=3；
上面这些查询条件之所以会失效，是由于(a,b,c)联合索引，是先按a排序，在a相同的状况再按b排序，在b相同的状况再按c排序。所以，b和c是大局无序，部分相对有序的，这样在没有遵从最左匹配准则的状况下，是无法运用到索引的。
我这儿举联合索引（a，b）的比方，该联合索引的B+Tree如下：
能够看到，a是大局有序的（1,2,2,3,4,5,6,7,8），而b是大局是无序的（12，7，8，2，3，8，10，5，2）。因而，直接履行whereb=2这种查询条件没有办法运用联合索引的，运用索引的前提是索引里的key是有序的。
只要在a相同的状况才，b才是有序的，比方a等于2的时分，b的值为（7，8），这时便是有序的，这个有序状况是部分的，因而，履行wherea=2andb=7这种查询条件时，a和b字段能用到联合索引的，也便是联合索引生效了。
联合索引规模查询
联合索引有一些特殊状况，并不是查询进程运用了联合索引查询，就代表联合索引中的一切字段都用到了联合索引进行索引查询，也便是可能存在部分字段用到联合索引的B+Tree，部分字段没有用到联合索引的B+Tree的状况。
这种特殊状况就发生在规模查询。也便是文章最初的那句话：联合索引的最左匹配准则会一向向右匹配直到遇到「规模查询」就会中止匹配。也便是规模查询的字段能够用到联合索引，可是规模查询字段的后边的字段无法用到联合索引。
规模查询有很多种，那到底是哪些规模查询会导致联合索引的最左匹配准则会中止匹配呢？
接下来，举例几个规模查询的比方，下面的试验事例是根据MySQL8.0做的。
比方一
Q1:select*fromt_tablewherea>1andb=2，联合索引（a,b）哪一个字段用到了联合索引的B+Tree？
由于联合索引（二级索引）是先依照a字段的值排序的，所以契合a>1条件的二级索引记载肯定是相邻的，所以在进行索引扫描的时分，能够定位到契合a>1条件的第一条记载，然后沿着记载地点的链表向后扫描，直到某条记载不契合a>1条件方位。所以a字段能够在联合索引的B+Tree中进行索引查询。
可是在契合a>1条件的二级索引记载的规模里，b字段的值是无序的。
比方，下图的联合索引的B+Tree里：
下面这三条记载的a字段的值都契合a>1查询条件，而b字段的值是无序的：
a字段值为5的记载，该记载的b字段值为8；
a字段值为6的记载，该记载的b字段值为10；
a字段值为7的记载，该记载的b字段值为5；
因而，咱们不能依据查询条件b=2来进一步削减需求扫描的记载数量（b字段无法运用联合索引进行索引查询的意思）。
所以在履行Q1这条查询句子的时分，对应的扫描区间是(2,+∞)，构成该扫描区间的鸿沟条件是a>1，与b=2无关。
因而，Q1这条查询句子只要a字段用到了联合索引进行索引查询，而b字段并没有运用到联合索引。
咱们也能够在履行计划中的key_len知道这一点，在运用联合索引进行查询的时分，通过key_len咱们能够知道优化器详细运用了多少个字段的查询条件来构成扫描区间的鸿沟条件。
举例个比方，a和b都是int类型且不为NULL的字段，那么Q1这条查询句子履行计划如下：
能够看到key_len为4字节（假如字段允许为NULL，就在字段类型占用的字节数上加1，也便是5字节），阐明只要a字段用到了联合索引进行索引查询，并且能够看到，即便b字段没用到联合索引，key为idx_a_b，阐明Q1查询句子运用了idx_a_b联合索引。
通过Q1查询句子咱们能够知道，a字段运用了>进行规模查询，联合索引的最左匹配准则在遇到a字段的规模查询（>）后就中止匹配了，因而b字段并没有运用到联合索引。
比方二
Q2:select*fromt_tablewherea>=1andb=2，联合索引（a,b）哪一个字段用到了联合索引的B+Tree？
Q2和Q1的查询句子很像，唯一的区别便是a字段的查询条件「大于等于」。
由于联合索引（二级索引）是先依照a字段的值排序的，所以契合>=1条件的二级索引记载肯定是相邻，所以在进行索引扫描的时分，能够定位到契合>=1条件的第一条记载，然后沿着记载地点的链表向后扫描，直到某条记载不契合a>=1条件方位。所以a字段能够在联合索引的B+Tree中进行索引查询。
尽管在契合a>=1条件的二级索引记载的规模里，b字段的值是「无序」的，可是关于契合a=1的二级索引记载的规模里，b字段的值是「有序」的（由于关于联合索引，是先依照a字段的值排序，然后在a字段的值相同的状况下，再依照b字段的值进行排序）。
所以，在确定需求扫描的二级索引的规模时，当二级索引记载的a字段值为1时，能够通过b=2条件削减需求扫描的二级索引记载规模（b字段能够运用联合索引进行索引查询的意思）。也便是说，从契合a=1andb=2条件的第一条记载开端扫描，而不需求从第一个a字段值为1的记载开端扫描。
所以，Q2这条查询句子a和b字段都用到了联合索引进行索引查询。
咱们也能够在履行计划中的key_len知道这一点。履行计划如下：
能够看到key_len为8字节，阐明优化器运用了2个字段的查询条件来构成扫描区间的鸿沟条件，也便是a和b字段都用到了联合索引进行索引查询。
通过Q2查询句子咱们能够知道，尽管a字段运用了>=进行规模查询，可是联合索引的最左匹配准则并没有在遇到a字段的规模查询（>=）后就中止匹配了，b字段仍是能够用到了联合索引的。
比方三
Q3:SELECT*FROMt_tableWHEREaBETWEEN2AND8ANDb=2，联合索引（a,b）哪一个字段用到了联合索引的B+Tree？
Q3查询条件中aBETWEEN2AND8的意思是查询a字段的值在2和8之间的记载。
不同的数据库对BETWEEN…AND处理方法是有差异的。在MySQL中，BETWEEN包含了value1和value2鸿沟值，类似于>=and=<。而有的数据库则不包含value1和value2鸿沟值（类似于>and<）。
这儿咱们只讨论MySQL。由于MySQL的BETWEEN包含value1和value2鸿沟值，所以类似于Q2查询句子，因而Q3这条查询句子a和b字段都用到了联合索引进行索引查询。
咱们也能够在履行计划中的key_len知道这一点。履行计划如下：
能够看到key_len为8字节，阐明优化器运用了2个字段的查询条件来构成扫描区间的鸿沟条件，也便是a和b字段都用到了联合索引进行索引查询。
通过Q3查询句子咱们能够知道，尽管a字段运用了BETWEEN进行规模查询，可是联合索引的最左匹配准则并没有在遇到a字段的规模查询（BETWEEN）后就中止匹配了，b字段仍是能够用到了联合索引的。
比方四
Q4:SELECT*FROMt_userWHEREnamelike’j%’andage=22，联合索引（name,age）哪一个字段用到了联合索引的B+Tree？
由于联合索引（二级索引）是先依照name字段的值排序的，所以前缀为‘j’的name字段的二级索引记载都是相邻的，所以在进行索引扫描的时分，能够定位到契合前缀为‘j’的name字段的第一条记载，然后沿着记载地点的链表向后扫描，直到某条记载的name前缀不为‘j’为止。
所以a字段能够在联合索引的B+Tree中进行索引查询，构成的扫描区间是[‘j’,’k’)。留意，j是闭区间。如下图：
尽管在契合前缀为‘j’的name字段的二级索引记载的规模里，age字段的值是「无序」的，可是关于契合name=j的二级索引记载的规模里，age字段的值是「有序」的（由于关于联合索引，是先依照name字段的值排序，然后在name字段的值相同的状况下，再依照age字段的值进行排序）。
所以，在确定需求扫描的二级索引的规模时，当二级索引记载的name字段值为‘j’时，能够通过age=22条件削减需求扫描的二级索引记载规模（age字段能够运用联合索引进行索引查询的意思）。也便是说，从契合name=’j’andage=22条件的第一条记载时开端扫描，而不需求从第一个name为j的记载开端扫描。如下图的右边：
所以，Q4这条查询句子a和b字段都用到了联合索引进行索引查询。
咱们也能够在履行计划中的key_len知道这一点。本次比方中：
name字段的类型是varchar(30)且不为NULL，数据库表运用了utf8mb4字符集，一个字符集为utf8mb4的字符是4个字节，因而name字段的实践数据最多占用的存储空间长度是120字节（30×4），然后由于name是变长类型的字段，需求再加2，也便是name的key_len为122。
age字段的类型是int且不为NULL，key_len为4。
Q4查询句子的履行计划如下：
能够看到key_len为126字节，name的key_len为122，age的key_len为4，阐明优化器运用了2个字段的查询条件来构成扫描区间的鸿沟条件，也便是name和age字段都用到了联合索引进行索引查询。
通过Q4查询句子咱们能够知道，尽管name字段运用了like前缀匹配进行规模查询，可是联合索引的最左匹配准则并没有在遇到name字段的规模查询（like’j%’）后就中止匹配了，age字段仍是能够用到了联合索引的。
小结
网上传来穿去这句话：「联合索引的最左匹配准则会一向向右匹配直到遇到规模查询(>、<、between、like)就会中止匹配」并不是对的。
通过试验的证明，我得出的定论是这样的：
联合索引的最左匹配准则，在遇到规模查询（如>、<）的时分，就会中止匹配，也便是规模查询的字段能够用到联合索引，可是在规模查询字段后边的字段无法用到联合索引。留意，关于>=、<=、BETWEEN、like前缀匹配的规模查询，并不会中止匹配。