mysql 联合索引的最左匹配深入研究

咱们在背 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*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) 联合索引，假如查询条件是以下这几种，就能够运用联合索引：

• 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 前缀匹配的规模查询，并不会中止匹配。

好了，讲完了