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

淩亂°似流年 2021-09-18 10:10 541阅读 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`看到，联合索引**有效**

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

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

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

![70 1][]

联合索引**无效**

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

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

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

![70 2][]

联合索引**有效**

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

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

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

![70 3][]

在4的基础上调换了查询条件的顺序，发现联合索引依旧**有效**

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

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

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

![70 4][]

把 `and` 换成 `or`，发现联合所索引**无效**！

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

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

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

![70 5][]

这两个条件分别位于联合索引位置的**第一和第三**，测试联合索引依旧**有效**！

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

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

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

![70 6][]

这两个条件分别位于联合索引位置的**第二和第三**，发现联合索引**无效**！

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

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

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

![70 7][]

所有条件一起查询，联合索引**有效**！（当然，这才是最正统的用法啊！）

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

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

*创建三个单列索引：*

![70 8][]

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

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

![70 9][]

我们发现三个单列索引**只有 `userid` 有效（位置为查询条件第一个）**，其他两个都没有用上

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

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

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

![70 10][]

我们发现此处两个查询条件**只有 `mobile` 有效（位置也为查询条件第一个）**，后面的无效

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

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

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

![70 11][]

这次把 `and` 换成 `or`，发现两个查询条件都用上索引了！

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

## 三、结论 ##

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

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

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

***重点：***

**多个单列索引**在**多条件查询**时只会生效**第一个**索引！所以**多条件联合查询时最好建联合索引！**

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

最左前缀原则：

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，   
注：如果第一个字段是范围查询需要单独建一个索引   
注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的**第一位置**，**即最左边**

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

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

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

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

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上[百度][Link 1]过，很多都是这种说法，但是据我研究，**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、**联合索引比对每个列分别建索引更有优势**，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

\---------------------   
作者：袁杰丶   
来源：CSDN   
原文：https://blog.csdn.net/Abysscarry/article/details/80792876   
版权声明：本文为博主原创文章，转载请附上博文链接！

[70]: /images/20210720/a92724d200814408924971bd4a86a134.png
[70 1]: /images/20210720/9b8ed6c297194c90bbe13051601a3478.png
[70 2]: /images/20210720/a5256ac4b83c4f5591ee0b32bf8d5b80.png
[70 3]: /images/20210720/26d4f752e82b4f61b644b76075ddfdfa.png
[70 4]: /images/20210720/70728121c4504414853f72bf1980fd39.png
[70 5]: /images/20210720/0f0db251bc9246e79caba68965a7b176.png
[70 6]: /images/20210720/0e446a7358af4b03abe11d0a9d54ecc7.png
[70 7]: /images/20210720/bf24d69fc64e403ba93959e5c230f22e.png
[70 8]: /images/20210720/39af993664d8448ab5dfeb7ca5b138ee.png
[70 9]: /images/20210720/9a93fee2acc24f1d96c73926629cdfd0.png
[70 10]: /images/20210720/660d34e490854321b2b8eec1115e1486.png
[70 11]: /images/20210720/c10df348acf9409e938d189f2df876ad.png
[Link 1]: https://www.baidu.com/s?wd=%E7%99%BE%E5%BA%A6&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd