多个单列索引和联合索引的区别详解

向右看齐 2023-01-14 01:39 106阅读 0赞

***背景：***  
*为了提高数据库效率，建索引是家常便饭；那么当查询条件为2个及以上时，我们是创建**多个单列索引**还是创建**一个联合索引**好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。*

--------------------

## 一、联合索引测试 ##

*注：Mysql版本为 5.7.20*

**创建测试表(表记录数为63188)：**

CREATE TABLE `t_mobilesms_11` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id，创建任务时的userid',
      `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
      `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
      `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
      `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
      `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
      `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
      `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
      `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
      `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
      `createTime` datetime DEFAULT NULL COMMENT '创建时间',
      `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
      PRIMARY KEY (`id`),
      KEY `联合索引` (`userId`,`mobile`,`billMonth`)
    ) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'

我们为`userId`, `mobile`, `billMonth`三个字段添加上联合索引！

我们选择 `explain` 查看执行计划来观察索引利用情况：

--------------------

**1.查询条件为 `userid`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'

![这里写图片描述][70]

可以通过`key`看到，联合索引**有效**

--------------------

**2.查询条件为 `mobile`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'

![这里写图片描述][70 1]  
可以看到联合索引**无效**

--------------------

**3.查询条件为 `billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'

![这里写图片描述][70 2]  
联合索引**无效**

--------------------

**4.查询条件为 `userid``and``mobile`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'

![这里写图片描述][70 3]  
联合索引**有效**

--------------------

**5.查询条件为 `mobile and userid`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222'

![这里写图片描述][70 4]  
在4的基础上调换了查询条件的顺序，发现联合索引依旧**有效**

--------------------

**6.查询条件为 `userid or mobile`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'

![这里写图片描述][70 5]  
把 `and` 换成 `or`，发现联合所索引**无效**！

--------------------

**7.查询条件为 `userid and billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'

![这里写图片描述][70 6]  
这两个条件分别位于联合索引位置的**第一和第三**，测试联合索引依旧**有效**！

--------------------

**8.查询条件为 `mobile and billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'

![这里写图片描述][70 7]  
这两个条件分别位于联合索引位置的**第二和第三**，发现联合索引**无效**！

--------------------

**9.查询条件为 `userid and mobile and billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'

![这里写图片描述][70 8]  
所有条件一起查询，联合索引**有效**！（当然，这才是最正统的用法啊！）

--------------------

## 二、单列索引测试 ##

*创建三个单列索引：*  
![这里写图片描述][70 9]

**1.查询条件为 `userid and mobile and billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'

![这里写图片描述][70 10]  
我们发现三个单列索引**只有 `userid` 有效（位置为查询条件第一个）**，其他两个都没有用上。

那么为什么没有用上呢？按照我们的理解，三个字段都加索引了，无论怎么排列组合查询，应该都能利用到这三个索引才对！

其实这里其实涉及到了mysql优化器的**优化策略**！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引去使用，也就是说，此处`userid 、mobile 、billMonth`这三个索引列都能用，只不过**优化器判断使用`userid`这一个索引能最高效完成本次查询**，故最终explain展示的key为userid。

--------------------

注意：如果user\_id索引出来的数据还是很多，mysql有可能会使用多个索引，也就是**mysql执行查询，是可以同时使用多个索引的**

另外的例子

CREATE TABLE `te_exam_user_wrong` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `exam_user_id` bigint(20) DEFAULT NULL COMMENT '用户考试记录id',
      `exam_id` bigint(20) NOT NULL COMMENT '考试id',
      `user_id` bigint(20) NOT NULL COMMENT '用户id',
      `paper_id` bigint(20) NOT NULL COMMENT '试卷id',
      `group_id` bigint(20) NOT NULL COMMENT '大题id',
      `question_id` bigint(20) NOT NULL COMMENT '试题id',
      `answer` text COMMENT '答案，json格式',
      `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除，0表示正常，1表示删除',
      `rent_id` bigint(20) NOT NULL COMMENT '租户标识',
      `create_by` varchar(100) NOT NULL COMMENT '创建人id',
      `gmt_create` datetime NOT NULL COMMENT '创建时间',
      `last_modified_by` varchar(100) DEFAULT NULL COMMENT '最后修改人id',
      `gmt_modified` datetime DEFAULT NULL COMMENT '最后修改时间',
      `version` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '乐观锁版本号，每次修改+1',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `in_exam_user_answer_union` (`exam_user_id`) USING BTREE,
      KEY `in_exam_user_answer_exam` (`exam_id`) USING BTREE,
      KEY `in_exam_user_answer_paper` (`paper_id`) USING BTREE,
      KEY `in_exam_user_answer_group` (`group_id`) USING BTREE,
      KEY `in_exam_user_answer_question` (`question_id`) USING BTREE,
    
    ) ENGINE=InnoDB AUTO_INCREMENT=108149 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户错题本信息，存放以往答错的题'

该表对exam\_id和question\_id都建了单索引，但仅仅通过exam\_id的索引出来的数据还是很大，所以mysql这里使用了2个索引进行检索

![20210419170226969.png][]

--------------------

**2.查询条件为 `mobile and billMonth`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'

![这里写图片描述][70 11]  
我们发现此处两个查询条件**只有 `mobile` 生效（位置也为查询条件第一个）**

--------------------

**3.查询条件为 `userid or mobile`**

EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972'

![这里写图片描述][70 12]  
这次把 `and` 换成 `or`，发现两个查询条件都用上索引了！

我们在网上可能常常看到有人说or会导致索引失效，其实这**并不准确**。而且我们首先需要**判断用的是哪个数据库哪个版本，什么引擎？**

比如我用的是mysql5.7版本，innodb引擎，在**这个环境下**我们再去讨论索引的具体问题。

**关于or查询的真相是：**  
所谓的索引失效指的是：假如or连接的**俩个查询条件字段中有一个没有索引**的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法，没啥问题。

此刻需要注意**type**类型为`index_merge`。  
我查资料说mysql 5.0 版本之前 使用or**只会用到一个索引**（即使如上我给userid和mobile都建立的单列索引），但自从5.0版本开始引入了**index\_merge索引合并优化**！也就是说，我们现在可以利用上多个索引去优化or查询了。

**index\_merge作用：**  
1、索引合并是把几个索引的范围扫描合并成一个索引。

2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。  
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

**index\_merge应用场景：**

1.对OR语句求并集，如查询`SELECT * FROM TB1 WHERE``c1="xxx" OR c2=""xxx"`时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果

2.对AND语句求交集，如查询`SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"`时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果

3.对AND和OR组合语句求结果

--------------------

## 三、结论 ##

***通俗理解：***  
利用索引中的附加列，您可以缩小搜索的范围，但使用**一个具有两列的索引 不同于使用两个单独的索引**。复合索引的结构与**电话簿**类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。**如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处**。

所以说创建复合索引时，应该仔细考虑**列的顺序**。对索引中的**所有列**执行搜索或仅对**前几列**执行搜索时，**复合索引非常有用**；**仅对后面的任意列**执行搜索时，**复合索引则没有用处。**

--------------------

***重点：***

**多个单列索引**在**多条件查询**时优化器会选择**最优索引策略**，**可能只用一个索引，也可能将多个索引全用上！** 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有**多条件联合查询时最好建联合索引！**

--------------------

***最左前缀原则：***

顾名思义是最左优先，以**最左边的为起点**任何连续的索引都能匹配上，  
注：如果第一个字段是**范围查询需要单独建一个索引**

注：在创建联合索引时，要根据业务需求，where子句中**使用最频繁的一列放在最左边**。这样的话扩展性较好，比如 `userid` 经常需要作为查询条件，而 `mobile` 不常常用，则需要把 `userid` 放在联合索引的**第一位置，即最左边**

--------------------

**同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？**

这个涉及到mysql本身的**查询优化器策略**了，当一个表有多条索引可走时, Mysql 根据**查询语句的成本**来选择走哪条索引；

--------------------

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，**mysql执行优化器会对其进行优化**，**当不考虑索引时，where条件顺序对效率没有影响**，**真正有影响的是是否用到了索引**！

--------------------

***联合索引本质：***

当创建\*\*(a,b,c)联合索引**时，相当于创建了**(a)单列索引\*\*，**(a,b)联合索引**以及\*\*(a,b,c)联合索引\*\*

想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，**a,c组合也可以，但实际上只用到了a的索引，c并没有用到！**  
注：这个可以结合上边的 *通俗理解* 来思考！

--------------------

***其他知识点：***

1、需要加索引的字段，**要在where条件中**  
2、**数据量少的字段不需要加索引**；因为**建索引有一定开销**，如果数据量小则没必要建索引（速度反而慢）

3、避免在where子句中使用**or**来连接条件,因为如果**俩个字段中有一个没有索引**的话,引擎会放弃索引而产生全表扫描  
4、**联合索引比对每个列分别建索引更有优势**，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，**应该将严格的索引放在前面，这样筛选的力度会更大，效率更高**。

[70]: /images/20221022/7a3babbdb00c41bb9e366f0383554584.png
[70 1]: /images/20221022/1f577003877248f694288baa495cc880.png
[70 2]: /images/20221022/ed2e17994a6c43d0bd35c187f0d75481.png
[70 3]: /images/20221022/16014e9bb3ce48b3879ef6f6eb41e152.png
[70 4]: /images/20221022/3d5672edfbd349be989fa7112b0a28f1.png
[70 5]: /images/20221022/b1a184f3f5fb49d4b288db1d8ee61359.png
[70 6]: /images/20221022/6e34c75b64994af88079ab11ef163fde.png
[70 7]: /images/20221022/d2ed92516c5146f3a73dc3e190b8e16f.png
[70 8]: /images/20221022/57cb29ea9a5840e598b257cde0f0f046.png
[70 9]: /images/20221022/42a7d640d618417bab11cfcd5115847b.png
[70 10]: /images/20221022/430c9231aba943b28ea05d7b67afc790.png
[20210419170226969.png]: /images/20221022/26b59630234d47a3a31b6e0ce29b6e95.png
[70 11]: /images/20221022/c9098abf63574d2dbe9d8b5960cdd95c.png
[70 12]: /images/20221022/f1451d16e3414ed8ba55f3248e080828.png