什么是最左前缀匹配？为什么要遵守？-51CTO.COM

在 MySQL 中，最左前缀匹配指的是在查询时利用索引的最左边部分进行匹配。当你执行查询时，如果查询条件涉及到组合索引的前几个列，MySQL 就能够利用该复合索引来进行匹配。

组合索引即由多个字段组成的联合索引，比如 idx_col1_col2_col3 (col1,col2,col3)。

假设我们创建了一个组合索引 (col1, col2, col3)，如果查询条件是针对 col1、(col1, col2) 或者 (col1, col2, col3)，那么 MySQL 就能利用该复合索引进行最左前缀匹配。

然而，如果查询条件只涉及到 col2、只涉及到 col3 或者只涉及到 col2 和 col3，也就是没有包含 col1，那么通常情况下（不考虑索引跳跃扫描等其他优化），就无法利用该索引进行最左前缀匹配。

值得注意的是，最左前缀匹配与查询条件的顺序无关。无论你写的是 where col1 = "Paidaxing" and col2 = "666" 还是 where col2 = "666" and col1 = "Paidaxing"，对结果都没有影响，命中的结果仍然一样。

此外，需要大家注意的是，许多人可能会误以为创建一个组合索引 (col1, col2, col3) 时，数据库会创建三个索引 (col1)、(col1, col2) 和 (col1, col2, col3)，这样的理解其实是不正确的。实际上，数据库只会创建一棵 B+树，只不过在这颗树中，首先按照 col1 进行排序，然后在 col1 相同时再按照 col2 排序，col2 相同再按照 col3 排序。

另外，如果没有涉及到联合索引，单个字段的索引也需要遵守最左前缀原则。即当一个字段的值为"abc"时，当我们使用 like 进行模糊匹配时，like "ab%" 是可以利用索引的，而 "%bc"则不行，因为后者不符合最左前缀匹配的原则。

为什么要遵循最左前缀匹配

我们都了解，在 MySQL 的 InnoDB 引擎中，索引是通过 B+树来实现的。不论是普通索引还是联合索引，都必须构建 B+树的索引结构。

针对普通索引，其存储结构是在 B+树的每个非叶子节点上记录索引的值，而在 B+树的叶子节点上，则记录了索引的值和聚簇索引（主键索引）的值。

如：

图片

在联合索引中，比如联合索引 (age, name)，同样也是构建了一棵 B+树。在这棵 B+树中，非叶子节点中记录的是 name 和 age 两个字段的值，而在叶子节点中记录的是 name、age 两个字段以及主键 id 的值。

图片

在存储过程中，如上所述，当 age 不同时，按照 age 排序；当 age 相同时，则按照 name 排序。

因此，了解了索引的存储结构之后，我们就很容易理解最左前缀匹配了：由于索引底层是一棵 B+树，如果是联合索引的话，在构造 B+树时，会先按照左边的键进行排序，当左边的键相同时，再依次按照右边的键进行排序。

因此，在通过索引查询时，也需要遵守最左前缀匹配的原则，即需要从联合索引的最左边开始进行匹配。这就要求查询语句的 WHERE 条件中包含最左边的索引值。

MySQL 索引一定遵循最左前缀匹配吗？

因为索引底层是一个 B+树，如果是联合索引的话，在构造 B+树的过程中，会先按照左边的键进行排序。当左边的键相同时，再依次按照右边的键排序。

因此，在通过索引进行查询时，也需要遵守最左前缀匹配的原则，即需要从联合索引的最左边开始进行匹配。这就要求查询语句的 WHERE 条件中包含最左边的索引值。这就是最左前缀匹配的概念。

在 MySQL 之前的版本中，一直都是遵循最左前缀匹配的原则，这句话在以前是正确的，没有任何问题。但是在 MySQL 8.0 中，情况就有所不同了。因为在 8.0.13 中引入了索引跳跃扫描的特性。

补充知识

索引跳跃扫描

MySQL 8.0.13 版本引入了索引跳跃扫描（Index Skip Scan）优化，对于 range 查询提供了支持。即使不符合组合索引最左前缀原则的条件下，SQL 依然能够使用组合索引，从而减少不必要的扫描。

让我们通过一个例子来解释一下。首先，我们有下面这样一张表（参考了 MySQL 官网的例子，但经过了一些改动和优化）：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL);
CREATE INDEX idx_t on t1(f1,f2);
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;

通过以下 SQL 语句，先创建一张名为 t1 的表，并将字段 f1 和 f2 设置为联合索引。然后向其中插入一些记录。

接着，分别在 MySQL 5.7.9 和 MySQL 8.0.30 上执行EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40;。

图片

可以看到，主要有以下几个区别：

MySQL 5.7 中，type = index，rows=160，extra=Using where;Using index

MySQL 8.0 中，type = range，rows=16，extra=Using where;Using index for skip scan

type 字段表示扫描方式，其中 range 表示范围扫描，而 index 表示索引树扫描。通常情况下，范围扫描要比索引树扫描快得多。

通过 rows 字段也能够观察到这一点，使用索引树扫描的方式共扫描了 160 行，而范围扫描方式只扫描了 16 行。

然后，关键在于为什么 MySQL 8.0 中的扫描方式更快呢？这主要是因为采用了"Using index for skip scan"的技术。

换句话说，尽管我们的 SQL 没有遵循最左前缀原则，仅仅使用了 f2 作为查询条件，但经过 MySQL 8.0 的优化，仍然通过索引跳跃扫描的方式利用了索引。

优化原理

那么他是怎么优化的呢？在 MySQL 8.0.13 及以后的版本中，执行SELECT f1, f2 FROM t1 WHERE f2 = 40;的过程如下：

获取 f1 字段的第一个唯一值，即 f1=1。
构造条件f1=1 and f2=40，进行范围查询。
获取 f1 字段的第二个唯一值，即 f1=2。
构造条件f1=2 and f2=40，进行范围查询。
重复上述步骤，直到扫描完 f1 字段的所有唯一值。
最后将结果合并并返回。

换句话说，最终执行的 SQL 语句类似于下面的形式：

SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 = 40
UNION
SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 = 40;

即，MySQL 的优化器帮我们把联合索引中的 f1 字段作为查询条件进行查询了。

限制条件

在了解了索引跳跃扫描的执行过程后，一些聪明的读者可能会意识到，这种查询优化更适用于具有较少取值范围和低区分度的字段（比如性别），而当字段的区分度特别高时（比如出生年月日），这种查询可能会变得更慢。

因此，是否使用索引跳跃扫描，实际上取决于 MySQL 优化器经过成本预估后做出的决定。

通常情况下，这种优化技术适用于联合索引中第一个字段的区分度较低的情况。但需要注意的是，并非绝对如此。尽管一般情况下我们不太会将区分度较低的字段放在联合索引的左边，但 MySQL 提供了这样的优化方案，这说明确实存在这样的需求。

然而，我们不应该过度依赖这种优化。在建立索引时，仍然应优先考虑将区分度高且频繁查询的字段放置在联合索引的左边。

此外，在 MySQL 官网中还提到了索引跳跃扫描的其他一些限制条件：

表 T 必须至少有一个联合索引，但对于联合索引(A,B,C,D)，A 和 D 可以为空，但 B 和 C 必须非空。
查询只能依赖于单张表，不能进行多表连接。
查询中不能使用 GROUP BY 或 DISTINCT 语句。
查询的字段必须是索引中的列。