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

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 索引、（ name， age）索引以及( name, age, nickname) 索引（注意，后面两个索引不能再按复合索引算，只是为了解释说明）。

4. where 条件后面的顺序不影响复合索引的触发如 age=21andname='test1' 一样会触发复合索引(MySQL会对查询条件顺序进行优化，我们无需担心顺序问题，但是为了更好理解，建议合理安排顺序)。

单表复合索引的性能分析

===========

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

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

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

特殊注意：

1. 使用MySQL的CONCAT函数拼接条件一样会使用索引。

2. 在使用 in 时，如果只有一个值，则等价于使用 =符号，会触发索引，包含两个或多个值，则索引失效。

3. 在使用 not in 时，无论多少个值，索引都会失效。

4. 使用 null 关键字查询时，无论值是否有为空的，都会触发索引。

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Android开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
都将为你打开新的学习之门！**

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！