共计 6226 个字符，预计需要花费 16 分钟才能阅读完成。
大家在背 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 * from t_product where id = 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 * from product where product_no = '0002';会先在二级索引的 B+Tree 中疾速查找到 product_no 为 0002 的二级索引记录，而后获取主键值，而后利用主键值在主键索引的 B+Tree 中疾速查问到对应的叶子节点，而后获取残缺的记录。这个过程叫「回表」，也就是说要查两个 B+Tree 能力查到数据。如下图：
不过，当查问的数据是能在二级索引的 B+Tree 的叶子节点里查问到，这时就不必再查主键索引查，比方上面这条查问语句：
select id from product where product_no = '0002';这种在二级索引的 B+Tree 就能查问到后果的过程就叫作「笼罩索引」，也就是只须要查一个 B+Tree 就能找到数据。
什么是联结索引？
前文我将 product_no 字段设置为了索引，这种二级索引只有一个字段。如果将多个字段组合成一个索引，那么这种二级索引就被称为 联结索引。
比方，将商品表中的 product_no 和 name 字段组合成联结索引(product_no, name)`，创立联结索引的形式如下：
CREATE INDEX index_product_no_name ON product(product_no, name);
联结索引 `(product_no, name) 的 B+Tree 示意图如下：
能够看到，联结索引的非叶子节点用两个字段的值作为 B+Tree 的索引值。
联结索引的 B+Tree 是先按 product_no 进行排序，而后再 product_no 雷同的状况再按 name 字段排序。记住这句话，很重要！
最左匹配准则
应用联结索引时，存在 最左匹配准则，也就是依照最左优先的形式进行索引的匹配。
在应用联结索引进行查问的时候，如果不遵循「最左匹配准则」，联结索引会生效，这样就无奈利用到索引疾速查问的个性了。
比方，如果创立了一个 (a, b, c) 联结索引，如果查问条件是以下这几种，就能够利用联结索引：
- where a=1；
- where a=1 and b=2 and c=3；
- where a=1 and b=2；
须要留神的是，因为有查问优化器，所以 a 字段在 where 子句的程序并不重要。然而，如果查问条件是以下这几种，因为不合乎最左匹配准则，所以就无奈匹配上联结索引，联结索引就会生效:
- where b=2；
- where c=3；
- where b=2 and c=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）。因而，间接执行 where b = 2 这种查问条件没有方法利用联结索引的，利用索引的前提是索引里的 key 是有序的。
只有在 a 雷同的状况才，b 才是有序的，比方 a 等于 2 的时候，b 的值为（7，8），这时就是有序的，这个有序状态是部分的，因而，执行 where a = 2 and b = 7 这种查问条件时，a 和 b 字段能用到联结索引的，也就是联结索引失效了。
联结索引范畴查问
联结索引有一些非凡状况，并不是查问过程应用了联结索引查问，就代表联结索引中的所有字段都用到了联结索引进行索引查问，也就是可能存在局部字段用到联结索引的 B+Tree，局部字段没有用到联结索引的 B+Tree 的状况。
这种非凡状况就产生在范畴查问。也就是文章结尾的那句话：联结索引的最左匹配准则会始终向右匹配直到遇到「范畴查问」就会进行匹配。也就是范畴查问的字段能够用到联结索引，然而范畴查问字段的前面的字段无奈用到联结索引。
范畴查问有很多种，那到底是哪些范畴查问会导致联结索引的最左匹配准则会进行匹配呢？
接下来，举例几个范畴查问的例子，上面的试验案例是基于 MySQL 8.0 做的。
例子一
Q1: select * from t_table where a > 1 and b = 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 * from t_table where a >= 1 and b = 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 = 1 and b = 2 条件的第一条记录开始扫描，而不须要从第一个 a 字段值为 1 的记录开始扫描。
所以，Q2 这条查问语句 a 和 b 字段都用到了联结索引进行索引查问。
咱们也能够在执行打算中的 key_len 晓得这一点。执行打算如下：
能够看到 key_len 为 8 字节，阐明优化器应用了 2 个字段的查问条件来造成扫描区间的边界条件，也就是 a 和 b 字段都用到了联结索引进行索引查问。
通过 Q2 查问语句咱们能够晓得，尽管 a 字段应用了 >= 进行范畴查问，然而联结索引的最左匹配准则并没有在遇到 a 字段的范畴查问（>=）后就进行匹配了，b 字段还是能够用到了联结索引的。
例子三
Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联结索引（a, b）哪一个字段用到了联结索引的 B+Tree？
Q3 查问条件中 a BETWEEN 2 AND 8 的意思是查问 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 * FROM t_user WHERE name like ‘j%’ and age = 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’ and age = 22 条件的第一条记录时开始扫描，而不须要从第一个 name 为 j 的记录开始扫描。如下图的左边：
所以，Q4 这条查问语句 a 和 b 字段都用到了联结索引进行索引查问。
咱们也能够在执行打算中的 key_len 晓得这一点。本次例子中：
- name 字段的类型是 varchar(30) 且不为 NULL，数据库表应用了 utf8mb4 字符集，一个字符集为 utf8mb4 的字符是 4 个字节，因而 name 字段的理论数据最多占用的存储空间长度是 120 字节（30 x 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 前缀匹配的范畴查问，并不会进行匹配。
好了，讲完了，怎么样，是不是又被我装到了
