联合索引的最左前缀匹配原则

目录

联合索引

最左前缀匹配原则

最左匹配原则的成因

---

联合索引
 

所谓的联合索引就是指，由两个或以上的字段共同构成一个索引。

本文测试用例的数据表结构如下，一张简简单单的学生信息表 tb_student，仅包含四个字段（student_id、student_name、student_age、student_addr）

那比如说，我们现在的业务需求经常要通过学生的年龄（student_age）和学生的家庭住址（student_addr）来同时筛选学生，如下

SELECT * FROM tb_student WHERE student_age = 20 AND student_addr = '北京';

那此时我们就最好在 student_age 和 student_addr 这两个字段上同时设置索引（注意，这里不是在这两个列上各自设置一个索引），这就是联合索引，我们执行下面的 sql 语句来设置联合索引。

设置索引的方式 ：

ALTER TABLE 表名 ADD INDEX 索引名(列名…)

ALTER TABLE tb_student ADD INDEX test_index(student_age,student_addr);

最左前缀匹配原则
 

最左前缀匹配原则，是一个非常重要的原则，可以通过以下这几个特性来理解。

1. 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
2. = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
3. 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

这么说还看不懂也没关系，下面会我通过四个简单的小例子来帮助你明白。

看例子之前，先要普及一下 explain 这个关键字的用法。

explain 是用来分析 SELECT 查询语句的，开发人员可以通过分析 explain 结果来优化查询语句。文章接下来将会大量使用 explain 来观察索引是否被使用到，我们先简单的看一个 explain 使用的小例子。

就用最简单的，扫描 tb_student 全表。

SELECT * FROM tb_student

我们用 explain 分析一下

EXPLAIN SELECT * FROM tb_student

返回结果

注意我圈红的这仨字段，这是使用 explain 语句需要重点关注的字段

1. type：访问类型，要是显示 ALL ，那你可要小心了，这是全表扫描的意思，性能最差，说明你的查询有很大的优化余地，如果显示的是 index ，说明会使用索引来优化查询。关于 type 的更多解释请参考这个文章 ：mysql中explain的type的解释
2. key：具体使用的索引名，这里没有。
3. rows：扫描的行数。

好了，言归正传，现在开始兑现承诺，举四个小例子了，还记得我们在上面刚给 student_age 和 student_addr 设置完联合索引吗，现在我们使用 explain 分析四种 where 子句的执行情况。
 
一、where student_age = 10 and student_addr = '北京’

explain select * from tb_student where student_age = 10 and student_addr = '北京';

返回结果

OK,使用了索引。
 
二、where student_addr = ‘北京’ and student_age = 10

跟第一种情况相比只是调换了顺序。

explain select * from tb_student where student_addr = '北京' and  student_age = 10;

返回结果

OK,还是正常。
 
三、where student_age = 10

explain select * from tb_student where student_age = 10;

没问题
 
四、where student_addr = '北京’

explain select * from tb_student where student_addr = '北京';

返回结果

无索引，这就是最左匹配原则。

 

最左匹配原则的成因
 

MySQL 建立联合索引的规则是这样的，它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

综上，第一个字段是绝对有序的，从第二个字段开始是无序的，这就解释了为什么直接使用第二字段进行条件判断用不到索引了（从第二个字段开始，无序，无法走 B+ Tree 索引）！这也是 MySQL 在联合索引中强调最左前缀匹配原则的原因。