mysql学习(9):联合索引和多个单列索引的使用区别详解

前言
为了提高数据库的查询效率,建索引是最常用的手段。那么问题来了,如果查询条件为2个及以上,我们是创建多个单列索引好呢,还是建一个联合索引比较好?

正文
我们首先来建一张表,并且指定其中的3个字段(学号、姓名、电话)为联合索引:

CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` varchar(20) DEFAULT NULL COMMENT '学号',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`phone` varchar(30) DEFAULT NULL COMMENT '电话',
`address` varchar(30) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`),
KEY `联合索引` (`stu_id`,`name`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='学生表';
1
2
3
4
5
6
7
8
9
接下来我们使用Explain查询来查看接下来的SQL查询语句的执行计划,这次我们只关注key和key_len列。其中key列显示MySQL实际决定使用的索引,如果没有使用到索引则显示NULL。key_len列显示索引中使用的字节数。

一、联合索引验证
1、针对联合索引中的字段分别进行单个查询
1、针对索引字段stu_id进行查询,结果联合索引有效:

EXPLAIN
SELECT * from student where stu_id = '001';
1
2

2、针对索引字段name进行查询,结果联合索引无效:

EXPLAIN
SELECT * from student where name = '小明';
1
2

3、针对索引字段phone进行查询,结果联合索引无效:

EXPLAIN
SELECT * from student where phone = '123';
1
2


2、针对联合索引中的字段进行两两组合查询
1、对索引排列中的前两个(stu_id、name)进行查询,结果联合索引有效:

EXPLAIN
SELECT * from student where stu_id = '001' AND name= '小明';
1
2

2、对索引排列中的第一个和第三个(stu_id、phone)进行查询,结果联合索引有效:(注意这里 1,3  也是有效的)

EXPLAIN
SELECT * from student where stu_id = '004' AND phone = '14785454554';
1
2

3、在第2点的基础上,调换一下stu_id、phone的位置,结果联合索引仍有效( 即使换了顺序也是有效)

EXPLAIN
SELECT * from student where phone = '14785454554' AND stu_id = '004';
1
2


4、对索引排列中的第二个和第三个(name、phone)进行查询,结果联合索引无效:

EXPLAIN
SELECT * from student where stu_id = '004' OR name = '小军';
1
2


3、针对联合索引中的字段进行全组合查询
1、按照索引的定义顺序进行查询,结果联合索引有效:

EXPLAIN
SELECT * from student where stu_id = '004' AND name = '小明' AND phone = '123';
1
2

2、打乱3个查询条件的顺序,结果联合索引仍有效:

EXPLAIN
SELECT * from student where phone = '123' AND name = '小明' AND stu_id = '004';
1
2

3、如果where条件出现OR,结果联合索引无效:

EXPLAIN
SELECT * from student where phone = '123' AND name = '小明' OR stu_id = '004';
1
2


二、多个单列索引验证
1、联合索引和单列索引同时存在
再分别对那3个字段建一个索引,同时保留联合索引:

create index index_stu_id on student(stu_id);
create index index_name on student(name);
create index index_phone on student(phone);
1
2
3
1、再来查询单个字段stu_id,结果显示使用了联合索引:

EXPLAIN
SELECT * from student where stu_id = '004';
1
2

2、再来查单个字段phone,结果使用了单例索引:

EXPLAIN
SELECT * from student where phone = '14785454554';
1
2

其实这里涉及到了mysql优化器的优化策略,当有多种索引存在时,优化器会评估哪个条件的索引效率最高,它会选择最佳的索引去使用。

2、只有单列索引
现在去掉联合索引,只剩下3个单列索引,再来进行查询

drop index 联合索引 on student;
1
1、单个字段查询,结果使用了对应字段的索引:

EXPLAIN
SELECT * from student where phone = '123';
1
2

2、多个字段查询,结果使用的是优化器选择出来的索引:

EXPLAIN
SELECT * from student where phone = '123' AND stu_id = '004';
1
2

3、多个字段查询,where条件使用OR,结果索引失效:

EXPLAIN
SELECT * from student where phone = '123' AND name = '小明' OR stu_id = '004';
1
2


总结
利用联合索引,我们可以缩小搜索的范围。但是使用联合索引不同于使用多个单独的索引。联合索引的结构其实与通讯录类似,人的名字由姓氏和名字组成,通讯录通常首先按照姓氏进行排序,然后按名字对有相同姓氏的人进行排序。如果我们知道姓氏,那么通过通讯录查找将会很有用;如果我们同时知道姓氏和名字,那么查通讯录会更加有效;但是如果我们只知道姓名,不知道姓氏,那么通讯录将没有用处。

所以我们在创建联合索引时,应该仔细考虑下列的顺序。对索引中的所有列执行查询,或者对前几列进行查询,联合索引将会非常有效;但是仅对后面的任意列进行搜索时,联合索引将没有用处。

多个单列索引在多个条件查询时,优化器会选择最优索引策列,可能只会用到其中一个索引,也可能用到多个索引。但是多个单列索引底层会建立多个B树索引,比较占用空间,也会浪费一定的搜索效率,所以只要有多个条件的联合查询,最好时建立联合索引。

最左前缀原则:
以索引定义的最左边为起点,任何连续的索引都能匹配上,
注:如果第一个字段是范围查询,则需要单独建一个索引。
注:在创建联合索引时,要根据业务的要求,where子句中使用最频繁的一列放在最左边。这样的扩展性比较好,比如stu_id经常作为查询条件,而phone不常用,则需要把stu_id放在联合索引的第一位,即最左边。

同时存在联合索引和单列索引(字段有重复)时,这时mysql查询会怎么使用索引呢?这个涉及到mysql本身的查询优化器策略了,当一个表有多条索引可以走时,mysql会根据查询语句的成本来选择走哪条索引。

联合索引的本质理解:
当创建了(a,b,c)这个联合索引时,相当于创建了下面这3条索引:
(a)这个单列索引;
(a,b)这个联合索引;
(a,b,c)这个联合索引。
想要索引生效的话,只能使用a,ab,abc这3种查询方式。上面我们测试过,ac组合也可以,但实际上只用到了a的索引,c并没有用到。
————————————————
版权声明:本文为CSDN博主「name_s_Jimmy」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

参考:mysql学习(9):联合索引和多个单列索引的使用区别详解

posted @ 2022-11-16 18:09  aspirant  阅读(213)  评论(0编辑  收藏  举报