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

雨点打透心脏的1/2处 2021-10-15 12:47 415阅读 0赞

本文由读者小平同志投稿，小平是一位非常朴实认真的猿，现于某上市证券公司做微服务开发，对 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');

# 单表复合索引 #

![640?wx\_fmt=png][640_wx_fmt_png]

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

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

# 单表复合索引的性能分析 #

![640?wx\_fmt=png][640_wx_fmt_png 1]

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

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

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

![640?wx\_fmt=png][640_wx_fmt_png 2]

特殊注意：

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

![640][] ![640][640 1]关注牧码小子，后台回复 Java ，领取松哥为你精心准备的Java干货！ ![640][]

往期文章一览

[**1、****工作之余，你是怎么提高技术的？**][1]

[**2、两年了，我写了这些干货！**][2]

[**3、想和大家谈一点合作**][3]

[**4、一个Java程序猿眼中的前后端分离以及Vue.js入门**][4_Java_Vue.js]

[**5、跟着平台混了四年，现在要单飞了！**][5]

![640?wx\_fmt=png][640_wx_fmt_png 3]你点的每个在看，我都认真当成了喜欢

[640_wx_fmt_png]: /images/20211015/dc925e3eef6a4dc19784f84ed9d77bb9.png?wx_fmt=png
[640_wx_fmt_png 1]: /images/20211015/c42d23ed6bd54be69e5bba8fe33bb8b9.png?wx_fmt=png
[640_wx_fmt_png 2]: /images/20211015/a8a5b8342d0345feafde92ac10842f35.png?wx_fmt=png
[640]: /images/20211015/1cc7061cb3314c26be7e337a14dde381.png
[640 1]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/b96CibCt70iabafnkvWicibyhC6PNrGbF0hoYBmuP8giamnzN5zYXH5efYYfnjj5QT3uUNIWm0KvflL71PcRtKEZuLA/640
[1]: http://mp.weixin.qq.com/s?__biz=MzI1NDY0MTkzNQ==&mid=2247485322&idx=1&sn=7c576db5e2f187cbba25d57767a22092&chksm=e9c355eadeb4dcfcdc04112d020ce9c493ec8a7ac414ae6c39f9e3932a39c7504b830e8531da&scene=21#wechat_redirect
[2]: http://mp.weixin.qq.com/s?__biz=MzI1NDY0MTkzNQ==&mid=2247485183&idx=1&sn=d514a417534ba5dc4ec04e94ff738fc4&chksm=e9c3549fdeb4dd89e67c3c65566be3b953dc01f4885674f81fd50fb6f144d51e34bfc73e5852&scene=21#wechat_redirect
[3]: http://mp.weixin.qq.com/s?__biz=MzI1NDY0MTkzNQ==&mid=2247485337&idx=1&sn=9b3bc8fdb37748fda6f6154235c991cf&chksm=e9c355f9deb4dcef0b35489e256bc90b1bb8d2867448c0d1e45a09e60d1ec0920919a8d1159a&scene=21#wechat_redirect
[4_Java_Vue.js]: http://mp.weixin.qq.com/s?__biz=MzI1NDY0MTkzNQ==&mid=2247485303&idx=1&sn=0b04836496f2d22dd2dcb116041570e7&chksm=e9c35517deb4dc01815c284cc9ecc2ba7cc732c50eaae5d23b6c90aee59fe7fe154d69305d90&scene=21#wechat_redirect
[5]: http://mp.weixin.qq.com/s?__biz=MzI1NDY0MTkzNQ==&mid=2247485227&idx=1&sn=58f054101d345d48b3e117028e044b17&chksm=e9c3554bdeb4dc5de839f7e1ebe4a66141bf2c5eef6a27234cfef8b3e2d18dd0423030368bfd&scene=21#wechat_redirect
[640_wx_fmt_png 3]: /images/20211015/897e1f903d7d4f5caea558ad7e672762.png?wx_fmt=png