Mysql：最左前缀匹配原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。比如我们创建了一个联合索引 (col1, col2, col3)，当执行查询时，如果查询条件能够从联合索引的最左侧字段开始，并且连续匹配后续字段，那么就可以利用这个联合索引来加速查询。

使用

索引成功使用

例如，对于索引 (col1, col2, col3) ：

• WHERE col1 = 'value1' ：可以使用索引。
• WHERE col1 = 'value1' AND col2 = 'value2' ：也可以使用索引，并且匹配效果更好。
• WHERE col1 = 'value1' AND col2 = 'value2' AND col3 = 'value3' ：能充分利用索引，效率最高。

需要注意的是，如果查询的前缀字段都用上了，但是顺序不同，如 col2= value2 and col1 ＝value1，这样也是能够命中索引的，因为的Mysql的查询引擎会自动为我们优化为匹配联合索引的顺序。

比如，这里有张学生表，学生表上有联合索引（student_name，age，sex），在查询时便符合最左前缀匹配原则：

image.png

索引失效

然而，如果查询条件跳过了最左侧的字段，或者不连续匹配后续字段，索引的使用可能就不那么有效或者完全无法使用。

例如：

• WHERE col2 = 'value2' ：无法使用索引。
• WHERE col1 = 'value1' AND col3 = 'value3' ：无法使用索引。

比如，如果只查学生的年龄和性别，跳过了姓名的匹配便无法使用索引：

image.png

原理

非联合索引

我们都明白，在 MySQL 的 InnoDB 引擎中，索引通过 B+树来完成构建。不管是非联合索引，亦或是联合索引，都得构造 B+树的索引结构。 就非联合索引来说，其存储结构表现为：在 B+树的每个非叶子节点上存有索引的值，在 B+树的叶子节点上，则存有索引的值和聚簇索引（即主键索引）的值。

例如，假设我们有一个包含大量用户年龄信息的表，年龄字段创建了普通索引。B+树的非叶子节点可能会存储一些年龄区间，如 10 - 20、21 - 30 等。 当进行查询，比如查找年龄为 25 的用户时，通过非叶子节点的指引，可以迅速缩小搜索范围到特定的子树。 而叶子节点不仅记录了索引的值（即年龄 25），还记录了聚簇索引（主键索引）的值。这是因为通过主键可以直接获取完整的用户记录。比如说，通过年龄 25 定位到叶子节点后，同时获取到对应的主键值，如用户 ID 为 1001，然后就可以根据这个主键值快速找到包含用户所有详细信息的完整记录。

image.png

联合索引

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

在存储进程中，正如上述所说，当 age 存在差异时，依照 age 来进行排序；当 age 相同的时候，就依照 name 来排序。

image.png

总结

在明晰了索引的存储结构以后，我们就能轻松地领会最左前缀匹配的原理：由于索引的底层是一棵 B+树，如果是联合索引，在构建 B+树时，会率先依据左边的键进行排序，当左边的键相同的时候，再依次依照右边的键进行排序。

故而，在通过索引进行查询时，也必须遵循最左前缀匹配的原则，也就是说需要从联合索引的最左侧开始进行匹配。这就规定查询语句的 WHERE 条件里要包含最左边的索引值。