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='学生表';

接下来我们使用Explain查询来查看接下来的SQL查询语句的执行计划，这次我们只关注key和key_len列。其中key列显示MySQL实际决定使用的索引，如果没有使用到索引则显示NULL。key_len列显示索引中使用的字节数。

一、联合索引验证

1、针对联合索引中的字段分别进行单个查询

1、针对索引字段stu_id进行查询，结果联合索引有效：

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

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

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

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

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

2、针对联合索引中的字段进行两两组合查询

1、对索引排列中的前两个（stu_id、name）进行查询，结果联合索引有效：

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

2、对索引排列中的第一个和第三个（stu_id、phone）进行查询，结果联合索引有效：

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

3、在第2点的基础上，调换一下stu_id、phone的位置，结果联合索引仍有效：

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

4、对索引排列中的第二个和第三个（name、phone）进行查询，结果联合索引无效：

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

3、针对联合索引中的字段进行全组合查询

1、按照索引的定义顺序进行查询，结果联合索引有效：

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

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

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

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

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

二、多个单列索引验证

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、再来查询单个字段stu_id，结果显示使用了联合索引：

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

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

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

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

2、只有单列索引

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

drop index 联合索引 on student;

1、单个字段查询，结果使用了对应字段的索引：

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

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

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

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

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

总结

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

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

多个单列索引在多个条件查询时，优化器会选择最优索引策列，可能只会用到其中一个索引，也可能用到多个索引。但是多个单列索引底层会建立多个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并没有用到。