mysql复合索引和单列索引的单表查询分析

mysql复合索引和单列索引的单表查询分析

前言

MySql的索引对查询速度的提高非常明显，但是索引种类很多，如复合索引、单列索引，那它们有什么区别和联系呢?下面我会对两者进行分析。

数据库创建语句

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` int(3) DEFAULT NULL,
  `nickname` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `testKey` (`name`,`age`,`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', '20', 'test1', '1', 'ntest1');
INSERT INTO `user` VALUES ('2', '21', 'test2', '0', 'ntest2');
INSERT INTO `user` VALUES ('3', '24', 'test1', '1', 'ntest3');
INSERT INTO `user` VALUES ('4', '23', 'test4', '0', 'ntest4');
INSERT INTO `user` VALUES ('5', '24', 'test5', '1', 'ntest5');
INSERT INTO `user` VALUES ('6', '25', 'test6', '0', 'ntest6');

部分关键字说明

explain：mysql查看执行计划的关键字，放在sql语句之前。
type：访问类型，表示找到所查询数据的方法，常见的有ref、range、index、all等。
keys：索引类型，表示mysql此次查询中使用的索引，多个用逗号分开。
rows：遍历行数，表示mysql此次查询遍历的行数大小，该值越小，查询速度会越快，是一个估计值，非绝对正确的。

单表复合索引(name, age, nickname)触发条件

执行SQL	type	查询条件	keys	rows
explain select * from user where name='test1'	ref	name=‘test1’	testKey	2
explain select * from user where age='21'	ALL	age=‘21’	/	6
explain select * from user where nickname='ntest1'	ALL	nickname=‘ntest1’	/	6
explain select * from user where name='test1' and age='21'	ref	name=‘test1’ and age=‘21’	testKey	1
explain select * from user where name='test1' and nickname='ntest1'	ref	name=‘test1’ and nickname=‘ntest1’	testKey	2
explain select * from user where age='21' and nickname='ntest1'	ALL	age=‘21’ and nickname=‘ntest1’	/	6
explain select * from user where name='test1' and age='21' and nickname='ntest1'	ref	name=‘test1’ and age=‘21’ and nickname=‘ntest1’	testKey	1

通过上面表格，我们会发现，复合索引(name,age,nickname)和它们三列的单个索引是有区别的（该案例不做复合索引和单列索引的性能分析）主要区别有以下几点：

1. 复合索引中，只有最左边的一列单独使用才会触发索引，其他的列单个使用无法触发索引。
2. 复合索引中，从最左边开始，相连的两个或多个会触发索引（相连和不相连的性能不同），如果没有最左边的列，后面的无论是否相连都不会触发索引
3. 通过分析我们可以发现，几个列的复合索引，就相当与有几个索引，如复合索引(name,age,nickname)相当与name索引，（name，age）索引，(name,age,nickname)索引（注意，后面两个索引不能再按复合索引算，只是为了解释说明）
4. where条件后面的顺序不影响复合索引的触发如age=‘21’ and name=‘test1’一样会触发复合索引–mysql会对查询条件顺序进行优化，我们无需担心顺序问题，但是为了更好理解，建议合理安排顺序

单表复合索引的性能分析

执行SQL	type	查询条件	keys	rows
explain select * from user where name='test1' and age='21'	ref	name=‘test1’ and age=‘21’	testKey	1
explain select * from user where name='test1' and nickname='ntest1'	ref	name=‘test1’ and nickname=‘ntest1’	testKey	2
explain select * from user where name='test1' and age='21' and nickname='ntest1'	ref	name=‘test1’ and age=‘21’ and nickname=‘ntest1’	testKey	1
explain select * from user where name='test1' and sex=1	ref	name=‘test1’ and sex=1	testKey	2

上面表格中，第一行和第二行都走了索引，但是第一行是相连的两列，rows是1，这里我们可以说是使用了（name，age）索引–该索引并发真实存在，只是为了区分效果；第二行是不相连的两列rows是2，然后第四行是使用了复合索引的第一列name和非复合索引中的列作为查询条件，rows同样是2，非相连的两列作为查询条件时，复合索引相当与使用了第一列作为查询条件。产生的原因：mysql在进行查询时，会根据索引筛选出复合索引的行，如果存在查询条件不在索引中的列，会进行二次筛选（即根据筛选出来的行进行二次查询），导致遍历的行数增加

这里指出部分查询条件会导致全表扫描

执行SQL	原因
explain select * from user where name='test1'+'1';	字符串拼接
explain select * from user where name!='test1'	!=
explain select * from user where name in('test1','test2')	in
explain select * from user where name is not null	not null
explain select * from user where name like 'test%'	like
explain select * from user where name='test1' or name='test2'	or
explain select * from user where age BETWEEN 21 and 24;	between value1 and value2

特殊注意：

1. 使用Mysql的CONCAT函数拼接条件一样会使用索引。
2. 在使用in时，如果只有一个值，则等价于使用 =符号，会触发索引，包含两个或多个值，则索引失效。
3. 在使用not in时，无论多少个值，索引都会失效。
4. 使用null关键字查询时，无论值是否有为空的，都会触发索引。
5. 在使用like关键字时，只要使用了%号进行模糊匹配，就会使索引失效。
6. 网上说使用is null会使索引失效，我测试的结果是，使用is null，无论是复合索引还是单列索引都能触发索引。

总结

在我们使用单列索引和复合索引时，需要注意以下几点：

1. 常用的字段放在第一列，经常和第一列一起使用的字段放在第二列，如用户表的电话和姓名，身份证表的身份照号和姓名，如果超过两列，则注意其顺序。
2. 条件查询时，尽可能所有字段都有索引（如sex这种情况例外，因为sex的值只有三个，冗余性太高，定位比较差，不如全表检索快），这样能提高很多效率。
3. 查询时避免会使索引失效的情况发生，如or条件，可以使用union或者union all来达到相同效果。
4. 索引能提高查询效率，但是过多的索引，同样会降低我们的修改操作效率，对此，我们创建索引需要合理，在使用频率较低的情况下，尽量不要创建索引。
5. select *或许性能和指定字段相差不是非常大，但是代码的可读性降低了很多，不推荐使用。