MySQL 遵循最左前缀匹配原则！面试官：回去等通知吧

我们都知道，MySQL 的 Innodb 引擎中，索引是通过 B+树来实现的。不管是普通索引还是联合索引，都需要构造一个 B+树的索引结构。

那么，我们都知道普通索引的存储结构中在 B+树的每个非节点上记录的索引的值，而这棵 B+树的叶子节点上记录的是聚簇索引（主键索引）的值。

如：

那么，如果是联合索引的话，这棵 B+树又是如何存储的呢？

在联合索引中，联合索引(name,age)也是一个 B+树，非叶子节点中记录的是 name,age 两个字段的值，叶子节点中记录的是 name，age 两个字段以及主键 id 的值。

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

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

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

在了解了最左前缀匹配之后，日常我们在工作中，经常在简历索引以及查询的时候，都会基于这个默认的约定进行索引的设计和 SQL 的优化。

大家都默认 MySQL 一定是遵循最左前缀匹配的。会认为当一个 age,name 的联合索引存在时，如果查询语句中不包含 age 作为条件，就一定不走索引。

MySQL 一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在 MySQL 8.0 中，就不一定了。

索引跳跃扫描

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

通过一个例子给大家解释一下，首先有下面这样一张表（参考了 MySQL 官网的例子，但是我做了些改动和优化）：

通过上面的 SQL，先创建一张 t1 表，并把 f1,f2 两个字段设置为联合索引。之后再向其中插入一些记录。

分别在 MySQL 5.7.9 和 MySQL 8.0.30 上执行：

执行结果如下：

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

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 表示的是索引树扫描，通常情况下，range 要比 index 快得多。

从 rows 上也能看得出来，使用 index 的扫描方式共扫描了 160 行，而使用 range 的扫描方式只扫描了 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;SQL 执行过程如下：

• 获取 f1 字段第一个唯一值，也就是 f1=1

• 构造 f1=1 and f2 = 40，进行范围查询

• 获取 f1 字段第二个唯一值，也就是 f1=2

• 构造 f1=2 and f2 = 40，进行范围查询

• 一直扫描完 f1 字段所有的唯一值，最后将结果合并返回

也就是说，最终执行的 SQL 语句是像下面这样的：

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

限制条件

在知道了索引跳跃扫描的执行过程之后，很多聪明的读者其实就会发现，这种查询优化比较适合于 f1 的取值范围比较少，区分度不高的情况，一旦 f1 的区分度特别高的话，这种查询可能会更慢。

所以，真正要不要走索引跳跃扫描，还是要经过 MySQL 的优化器进行成本预估之后做决定的。

所以，这种优化一般用于那种联合索引中第一个字段区分度不高的情况。但是话又说回来了，我们一般不太会把区分度不高的字段放在联合索引的左边，不过事无绝对，既然 MySQL 给了一个优化的方案，就说明还是有这样的诉求的。

但是，我们不能依赖他这个优化，建立索引的时候，还是优先把区分度高的，查询频繁的字段放到联合索引的左边。

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

• 表 T 至少有一个联合索引，但是对于联合索引(A,B,C,D)来说，A 和 D 可以是空的，但 B 和 C 必须是非空的。

• 查询必须只能依赖一张表，不能多表 JOIN

• 查询中不能使用 GROUP BY 或 DISTINCT 语句

• 查询的字段必须是索引中的列

来源：https://mp.weixin.qq.com/s/IYRTE00_3bXD6y3YBW9P6Q

作者：Hollis