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

本文由读者小平同志投稿,小平是一位非常朴实认真的猿,现于某上市证券公司做微服务开发,对 MySQL 优化有深入研究,小平的博客地址是https://blog.csdn.net/weixin_41193109。

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

关键字

  • explain:MySQL查看执行计划的关键字,放在sql语句之前。
  • type:访问类型,表示找到所查询数据的方法,常见的有ref、range、index、all等。
  • keys:索引类型,表示MySQL此次查询中使用的索引,多个用逗号分开。
  • rows:遍历行数,表示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');

单表复合索引

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

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

单表复合索引的性能分析

上面表格中,第一行和第二行都走了索引,但是第一行是相连的两列,rows是1,这里我们可以说是使用了( nameage)索引(该索引并发真实存在,只是为了区分效果);第二行是不相连的两列rows是2,然后第四行是使用了复合索引的第一列 name 和非复合索引中的列作为查询条件,rows 同样是2,非相连的两列作为查询条件时,复合索引相当于使用了第一列作为查询条件。

为什么会这样呢?MySQL 在进行查询时,会根据索引筛选出复合索引的行,如果存在查询条件不在索引中的列,会进行二次筛选(即根据筛选出来的行进行二次查询),导致遍历的行数增加。

部分查询条件会导致全表扫描

特殊注意:

  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* 或许性能和指定字段相差不是非常大,但是代码的可读性降低了很多,不推荐使用。

原文发布于微信公众号 - 牧码小子(a_javaboy)

原文发表时间:2019-04-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券