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

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

mysql:
利用 spring boot 构建了 200w 的数据记录

select * from t_student WHERE size='size10001'

耗时：0.747s

新增索引

ALTER TABLE `t_student` ADD INDEX `index1` (`size`) USING BTREE ;

耗时：0.248s

 

多列索引和联合索引参考：

多个单列索引和联合索引的区别详解_深寒色的猫丶的博客-CSDN博客_联合索引和单个索引区别

创建测试表(表记录数为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'
可以通过key看到，联合索引有效

2.查询条件为 mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
可以看到联合索引无效

3.查询条件为 billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
联合索引无效

4.查询条件为 userid and mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
联合索引有效

5.查询条件为 mobile and userid
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND userid='2222'
在4的基础上调换了查询条件的顺序，发现联合索引依旧有效

6.查询条件为 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
把 and 换成 or，发现联合所索引无效！

7.查询条件为 userid and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'
这两个条件分别位于联合索引位置的第一和第三，测试联合索引依旧有效！

8.查询条件为 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
这两个条件分别位于联合索引位置的第二和第三，发现联合索引无效！

9.查询条件为 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
所有条件一起查询，联合索引有效！（当然，这才是最正统的用法啊！）

二、单列索引测试
创建三个单列索引：
1.查询条件为 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
我们发现三个单列索引只有 userid 有效（位置为查询条件第一个），其他两个都没有用上

2.查询条件为 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
我们发现此处两个查询条件只有 mobile 有效（位置也为查询条件第一个），后面的无效

3.查询条件为 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'
这次把 and 换成 or，发现两个查询条件都用上索引了！

我们在网上可能常常看到有人说or会导致索引失效，其实这并不准确。
我们首先需要判断用的是哪个数据库哪个版本，什么引擎？
比如我用的是mysql5.7版本，innodb引擎，在这个环境下我们再去讨论索引的具体问题。
关于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组合语句求结果
————————————————
原文链接：https://blog.csdn.net/Abysscarry/article/details/80792876


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

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

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

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

同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？
这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；
有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。
网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

 

 

结论：

(1) 联合索引本质：

当创建(a,b,c)联合索引时，相当于创建了 (a) 单列索引，(a,b) 联合索引  以及 (a,b,c) 联合索引
想要索引生效的话,只能使用 a 和 a,b 和 a,b,c 三种组合；

当然，a,c 组合也可以，但实际上只用到了 a 的索引，c 并没有用到！
注：这个可以结合上边的 电话簿例子 来思考！

(2) 多个单列索引：(不建议使用)

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

(3) 其他知识点：

1、需要加索引的字段，要在 where 条件中
2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）
3、如果 where 条件中是 OR 关系，加索引不起作用
4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。
另外建立多列索引时，顺序也是需要注意的，应该将严格的索引(字段值不容易发生改变的)放在前面，这样筛选的力度会更大，效率更高。

————————————————
原文链接：https://blog.csdn.net/weixin_43291055/article/details/92617039