mysql索引详解，以及多个单列索引和联合索引的区别详解

旧城等待， 2023-02-17 05:19 39阅读 0赞

**温故而知新，可以为师矣**

网上看到一些文章，觉得很不错，这里记录一下。

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

# 一、key 、index 、primary key 、unique key 的区别 #

## 1、Key ##

**1.1 key 是数据库的物理结构，它包含两层意义和作用**

*  一是约束：偏重于约束和规范数据库的结构完整性
 *  二是索引：辅助查询用的

key包括primary key, unique key, foreign key 等。

**1.2 primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引**

primary key 约束：唯一标识数据库表中的每条记录

*  主键必须包含唯一的值；
 *  主键列不能包含 NULL 值；
 *  每个表都应该有一个主键，并且每个表只能有一个主键。（primary key 拥有自动定义的 unique 约束）

**1.3 unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引**

unique 约束：唯一标识数据库表中的每条记录。

*  unique 和 primary key 约束均为列或列集合提供了唯一性的保证。  
    \-每个表可以有多个 unique 约束，但是每个表只能有一个primary key 约束.

**1.4 foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index**

可见，MySQL的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别（至少在oracle上建立外键，不会自动建立index）。

**1.5 因此创建key也有如下几种方式：**  
（1）在字段级以key方式建立， 如 create table t (id int not null primary key);  
（2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk\_t\_id PRIMARY key (id));  
（3）在表级以key方式建立，如create table t(id int, primary key (id));

其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。

## 2、Index ##

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等。因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。我们说索引分类，分为：

*  主键索引（必须指定为“PRIMARY KEY”，没有PRIMARY Index）
 *  唯一索引（unique index，一般写成unique key）
 *  普通索引(index，只有这一种才是纯粹的index)

## 3、Index 与Key 的区别 ##

Key即键值，是关系模型理论中的一部份，比如有主键（Primary Key)，外键（Foreign Key）等，用于数据完整性检否与唯一性约束等。而Index则处于实现层面，比如可以对表的任意列建立索引，那么当建立索引的列处于SQL语句中的Where条件中时，就可以得到快速的数据定位，从而快速检索。至于Unique Index，则只是属于Index中的一种而已，建立了Unique Index表示此列数据不可重复。  
在设计表的时候，Key只是处于模型层面的，而当需要进行查询优化，则对相关列建立索引即可。另外，在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique Index，无需重复再在上面建立索引了。

## 4、UNIQUE KEY和PRIMARY KEY有什么区别 ##

*  Primary key的1个或多个列必须为NOT NULL，如果列为NULL，在增加PRIMARY KEY时，列自动更改为NOT NULL。而UNIQUE KEY 对列没有此要求。
 *  一个表只能有一个PRIMARY KEY，但可以有多个UNIQUE KEY
 *  主键和唯一键约束是通过参考索引实施的，如果插入的值均为NULL，则根据索引的原理，全NULL值不被记录在索引上，所以插入全NULL值时，可以有重复的，而其他的则不能插入重复值。

# 二、联合索引测试 #

注：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'  
![20200612171428571.png][]  
可以通过key看到，联合索引有效

## 2.查询条件为 mobile ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE mobile='13281899972'  
![20200612171438866.png][]

可以看到联合索引**无效**

## 3.查询条件为 billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE billMonth='2018-04'  
![20200612171451242.png][]

联合索引**无效**

## 4.查询条件为 userid and mobile ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE userid='2222' AND mobile='13281899972'  
![20200612171501648.png][]

联合索引**有效**

## 5.查询条件为 mobile and userid ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE  mobile='13281899972' AND userid='2222'   
![20200612171515597.png][]

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

## 6.查询条件为 userid or mobile ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE userid='2222' OR mobile='13281899972'  
![2020061217152553.png][]

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

## 7.查询条件为 userid and billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE userid='2222' AND billMonth='2018-04'  
![20200612171540471.png][]

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

## 8.查询条件为 mobile and billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE mobile='13281899972' AND billMonth='2018-04'  
![20200612171556383.png][]

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

## 9.查询条件为 userid and mobile and billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'  
![20200612171558833.png][]

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

# 三、单列索引测试 #

创建三个单列索引：

## ![20200612171610338.png][]   1.查询条件为 userid and mobile and billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'  
![20200612171629982.png][]

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

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

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

当然，如果优化器判断本次查询非要全使用三个索引才能效率最高，那么explain的key就会是userid 、mobile 、billMonth，都会生效！

## 2.查询条件为 mobile and billMonth ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE mobile='13281899972' AND billMonth='2018-04'  
![20200612171634385.png][]

我们发现此处两个查询条件只有 mobile 生效（位置也为查询条件第一个）

## 3.查询条件为 userid or mobile ##

EXPLAIN SELECT \* FROM \`t\_mobilesms\_11\` WHERE  userid='2222' OR mobile='13281899972'   
![20200612171638227.png][]

这次把 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、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

## 最后的说明： ##

网上关于索引优化等文章太多了，针对各个数据库各个版本各种引擎都可能存在不一样的说法！

我们的SQL引擎自带的优化也越来越强大，说不定你的某个SQL优化认知，其SQL引擎在某次升级中早就自优化了。

所以要么跟进官方文档，要么关注数据库大牛的最新文章，要么在现有数据库环境下自己去亲手测试！

数据库领域的水很深。。大家加油。。共勉 ~

转自：

[https://www.jianshu.com/p/19fff558c182][https_www.jianshu.com_p_19fff558c182]

[https://blog.csdn.net/Abysscarry/article/details/80792876][https_blog.csdn.net_Abysscarry_article_details_80792876]

[20200612171428571.png]: https://img-blog.csdnimg.cn/20200612171428571.png
[20200612171438866.png]: https://img-blog.csdnimg.cn/20200612171438866.png
[20200612171451242.png]: https://img-blog.csdnimg.cn/20200612171451242.png
[20200612171501648.png]: https://img-blog.csdnimg.cn/20200612171501648.png
[20200612171515597.png]: https://img-blog.csdnimg.cn/20200612171515597.png
[2020061217152553.png]: https://img-blog.csdnimg.cn/2020061217152553.png
[20200612171540471.png]: https://img-blog.csdnimg.cn/20200612171540471.png
[20200612171556383.png]: https://img-blog.csdnimg.cn/20200612171556383.png
[20200612171558833.png]: https://img-blog.csdnimg.cn/20200612171558833.png
[20200612171610338.png]: https://img-blog.csdnimg.cn/20200612171610338.png
[20200612171629982.png]: https://img-blog.csdnimg.cn/20200612171629982.png
[20200612171634385.png]: https://img-blog.csdnimg.cn/20200612171634385.png
[20200612171638227.png]: https://img-blog.csdnimg.cn/20200612171638227.png
[https_www.jianshu.com_p_19fff558c182]: https://www.jianshu.com/p/19fff558c182
[https_blog.csdn.net_Abysscarry_article_details_80792876]: https://blog.csdn.net/Abysscarry/article/details/80792876