你们在背MySQL定型的时候是不是经常看到这句话？联合索引的最左匹配原则会一直向右匹配，直到遇到范围查询（>、<、between、like）才停止匹配。随便在网上搜了一下，基本都是这个结论。看来大家已经被这个结论给迷住了。大多数人可能认为这个结论是正确的。昨晚折腾了几个实验，发现这个结论不太对！去掉“betweenandlike”的结论是没有问题的。经过实验证明，我得出的结论是联合索引的最左匹配原则在遇到范围查询（如>、<）时会停止匹配，即范围查询的字段可以使用联合索引，但是范围查询字段后面的字段不能使用联合索引。但是对于>=、<=、BETWEEN、like前缀匹配这四个范围查询，匹配不会停止。接下来，我将用几个实验例子来说明这个结论。B+Tree索引首先，我们来认识一下B+Tree索引。MySQL的InnoDB存储引擎会为每个数据库表创建一个“聚集索引”来保存表中的数据。聚簇索引默认使用B+Tree索引。为了让大家了解B+Tree索引的存储和查询过程，下面我用一个简单的例子来说明B+Tree索引在存储数据上的具体实现。假设有一个产品表，里面有这些数据：这个数据存储在B+Tree索引中是什么样子的？B+Tree是一棵多叉树，叶子节点只存放数据，非叶子节点只存放索引，每个节点中的数据按照主键值（id）的顺序存放，每个节点的索引值父节点会出现在下层子节点的索引值中，所以在叶子节点中，包含了所有的索引值信息，每个叶子节点指向下一个叶子节点，形成一个链表，方便range询问。聚簇索引的B+Tree如图所示：假设，执行select*fromt_productwhereid=5查询语句，查询语句的条件是查找id（主键）为5的记录.因为B+Tree是一种有序的数据结构，通过二分查找算法可以快速定位到这条记录，也就是我们常说的索引查询。具体过程如下：从根节点开始，结合5和Index数据(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表中的product_no（商品代码）字段设置为二级索引，那么二级索引的B+Tree如下图，其中非叶子索引值为product_no（橙色部分图中），叶子节点存储的数据就是主键值（图中绿色部分）。如果我使用product_no二级索引查询商品，如下查询语句：select*fromproductwhereproduct_no='0002';会先在二级索引的B+Tree中快速找到product_no为0002的二级索引记录，然后得到主键值，然后利用主键值在索引的B+Tree中快速查询对应的叶子节点通过主键，得到完整的记录。这个过程叫做“还表”，也就是说需要查两棵B+树才能找到数据。如下图所示：但是，当查询到的数据可以在二级索引的B+Tree的叶子节点中查询到时，那么就不需要去查主键索引了。例如下面的查询语句：selectidfromproductwhereproduct_no='0002';这种在二级索引的B+Tree中查询结果的过程称为“覆盖索引”，即只需要查一个B+Tree就可以找到数据。什么是联合指数？前面我把product_no字段设置为索引，这个二级索引只有一个字段。如果将多个字段组合成一个索引，那么这个二级索引就称为联合索引。例如将product表中的product_no和name字段组合成一个联合索引(product_no,name)`，创建联合索引的方法如下：CREATEINDEXindex_product_no_nameONproduct(product_no,name);联合索引的B`(product_no,name)+Tree的示意图如下：可以看出联合索引的非叶子节点使用两个字段的值作为索引值B+树。联合索引的B+Tree先按product_no排序，如果product_no相同再按name字段排序。记住这句话，很重要！最左匹配原则在使用联合索引时，有一个最左匹配原则，即按照最左优先的方式进行索引匹配。在使用联合索引进行查询时，如果不遵循“最左匹配原则”，联合索引就会失败，从而无法使用索引的快速查询特性。比如创建一个(a,b,c)联合索引，如果查询条件是下面的，就可以使用联合索引：wherea=1;其中a=1且b=2且c=3；其中a=1和b=2；需要注意的是，由于查询优化器的存在，where子句中a字段的顺序并不重要。但是，如果查询条件是以下几种，由于不符合最左匹配原则，无法匹配到联合索引，联合索引就会失败：whereb=2;其中c=3；whereb=2andc=3.上述查询条件之所以失败是因为(a,b,c)联合索引先按a排序，当a相同时再按b排序，再按c排序当b相同时。因此，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。例1Q1：select*fromt_tablewherea>1andb=2，联合索引(a,b)的哪个字段使用了联合索引的B+Tree？由于联合索引（二级索引）首先根据a字段的值进行排序，满足a>1条件的二级索引记录必然是相邻的，所以在进行索引扫描时，可以定位到满足a的记录conditionofa>1条件的第一条记录，然后沿着记录所在的链表向后扫描，直到有一条记录不满足a>1的条件。所以可以在a中索引查询a字段联合索引的B+Tree。但是在满足a>1条件的二级索引记录范围内，字段b的值是无序的。比如下图中联合索引的B+Tree中：下面3条记录的字段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不使用联合索引.例2Q2：select*fromt_tablewherea>=1andb=2，联合索引(a,b)的哪个字段使用了联合索引的B+Tree？Q2和Q1的查询语句非常相似，唯一不同的是字段a的查询条件是“大于等于”。由于联合索引（二级索引）首先根据a字段的值进行排序，满足>=1条件的二级索引记录一定是相邻的，所以在进行索引扫描时，可以定位到>=1条件第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不满足a>=1的条件。所以可以在联合索引的B+Tree中对a字段进行索引查询.虽然在满足条件a>=1的二级索引记录范围内，字段b的值为“无序”，但是对于满足a=1的二级索引记录范围，字段b的值为“有序”.order”（因为对于联合索引来说，是先按照字段a的值排序，如果字段a的值相同，再按照字段b的值排序）。因此，在确定二级索引的扫描范围时，当二级索引记录的a字段值为1时，可以使用b=2条件来缩小二级索引记录的扫描范围（b字段可以使用联合索引来索引查询意义）。即从第一条满足a=1和b=2条件的记录开始扫描，而不是从第一条字段值为1的记录开始扫描。因此，Q2的查询语句的a和b字段都是使用联合索引进行索引查询。我们也可以在执行计划中的key_len中知道这一点。执行计划如下：可以看到key_len为8字节，说明优化器使用2个字段的查询条件构成扫描区间的边界条件，即字段a和b都使用联合索引进行索引查询。通过Q2查询语句我们可以知道，虽然a字段使用>=进行范围查询，但是联合索引的最左匹配原则在遇到a字段的范围查询（>=）后并不会停止匹配，而b字段仍然可以使用联合索引。例3Q3：SELECT*FROMt_tableWHEREaBETWEEN2AND8ANDb=2，联合索引(a,b)的哪个字段使用了联合索引的B+Tree？Q3的查询条件中，aBETWEEN2AND8表示查询字段a的值为2到8之间的记录，不同的数据库对BETWEEN...AND的处理不同。在MySQL中，BETWEEN包括value1和value2的边界值，类似于>=和=<。其他数据库不包括value1和value2边界值（类似于>和<）。这里我们只讨论MySQL。由于MySQL的BETWEEN包含value1和value2边界值，与Q2的查询语句类似，所以Q3的查询语句使用联合索引对a和b两个字段进行索引查询。我们也可以在执行计划中的key_len中知道这一点。执行计划如下：可以看到key_len为8字节，说明优化器使用2个字段的查询条件构成扫描区间的边界条件，即字段a和b都使用联合索引进行索引查询。从Q3查询语句可以知道，虽然字段a使用BETWEEN进行范围查询，但是联合索引的最左匹配原则在遇到字段a的范围查询（BETWEEN）后并没有停止匹配，字段b仍然可以使用到联合指数。Example4Q4:SELECT*FROMt_userWHEREnamelike'j%'andage=22，联合索引(name,age)的哪个字段使用了联合索引的B+Tree？由于联合索引（二级索引）首先根据name字段的值进行排序，name字段以'j'为前缀的二级索引记录都是相邻的，所以在进行索引扫描时，可以定位到匹配的首先记录name字段前缀为'j'的记录，然后沿着该记录所在的链表向后扫描，直到一条记录的name前缀不为'j'。因此，可以在联合索引的B+Tree中对a字段进行索引查询，形成的扫描区间为['j','k')。请注意，j是闭区间。如下图所示：虽然age字段的值在匹配name字段前缀为'j'的二级索引记录范围内是“无序”的，但是对于匹配name=j的二级索引记录范围，age字段的值是“有序”的（因为对于一个联合索引来说，先按照name字段的值排序，如果name字段的值不一致，再按照age字段的值排序是相同的）。因此，在确定要扫描的二级索引范围时，当二级索引记录的name字段的值为'j'时，可以使用age=22的条件来缩小二级索引记录的范围为scanned（age字段可以结合Index表示索引查询）。也就是说，从第一条满足name='j'和age=22条件的记录开始扫描，而不是从name为j的第一条记录开始扫描。下图右侧：因此Q4查询语句的a和b字段都使用了联合索引进行索引查询。我们也可以在执行计划中的key_len中知道这一点。本例中：name字段的类型为varchar(30)且不为NULL，数据库表使用utf8mb4字符集，一个字符集为utf8mb4的字符为4字节，所以name字段的实际数据占用最多存储空间长度为120字节（30x4），并且由于name是变长字段，所以需要加2，即name的key_len为122。age字段的类型为int而不是NULL，key_len为4。Q4查询语句的执行计划如下：可以看到key_len为126字节，name的key_len为122，age的key_len为4，说明优化器使用两个字段的查询条件构成扫描区间的边界条件，即name和age字段都使用联合索引进行索引查询。从Q4查询语句可以知道，虽然name字段使用like前缀匹配进行范围查询，但是联合索引的最左匹配原则在遇到name字段的范围查询（like'j%'）后并没有停止匹配,age字段仍然可以使用联合索引。总结网上流传的一句话：“联合索引的最左匹配原则会一直向右匹配，直到遇到范围查询（>,<,between,like）才停止匹配”是不正确。经过实验证明，我得出的结论是联合索引的最左匹配原则在遇到范围查询（如>、<）时会停止匹配，即范围查询的字段可以使用联合索引，但是范围查询字段后面的字段不能使用联合索引。注意对于>=,<=,BETWEEN等前缀匹配的范围查询，不会停止匹配。好了，我说完了，怎么样，我是不是又假装了？
