目录
联合索引
所谓的联合索引就是指,由两个或以上的字段共同构成一个索引。
本文测试用例的数据表结构如下,一张简简单单的学生信息表 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);
最左前缀匹配原则
最左前缀匹配原则,是一个非常重要的原则,可以通过以下这几个特性来理解。
- 对于联合索引,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 的顺序可以随意调换。
- = 和 in 可以乱序,比如 a = 3 and b = 4 and c = 5 建立 (a,b,c)索引可以任意顺序。
- 如果建立的索引顺序是 (a,b)那么直接采用 where b = 5 这种查询条件是无法利用到索引的,这一条最能体现最左匹配的特性。
这么说还看不懂也没关系,下面会我通过四个简单的小例子来帮助你明白。
看例子之前,先要普及一下 explain 这个关键字的用法。
explain 是用来分析 SELECT 查询语句的,开发人员可以通过分析 explain 结果来优化查询语句。文章接下来将会大量使用 explain 来观察索引是否被使用到,我们先简单的看一个 explain 使用的小例子。
就用最简单的,扫描 tb_student 全表。
SELECT * FROM tb_student
我们用 explain 分析一下
EXPLAIN SELECT * FROM tb_student
返回结果
注意我圈红的这仨字段,这是使用 explain 语句需要重点关注的字段
- type:访问类型,要是显示 ALL ,那你可要小心了,这是全表扫描的意思,性能最差,说明你的查询有很大的优化余地,如果显示的是 index ,说明会使用索引来优化查询。关于 type 的更多解释请参考这个文章 :mysql中explain的type的解释
- key:具体使用的索引名,这里没有。
- 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 在联合索引中强调最左前缀匹配原则的原因。