温故而知新,可以为师矣
网上看到一些文章,觉得很不错,这里记录一下。
背景:
为了提高数据库效率,建索引是家常便饭;那么当查询条件为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'
可以通过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 有效(位置为查询条件第一个),其他两个都没有用上。
那么为什么没有用上呢?按照我们的理解,三个字段都加索引了,无论怎么排列组合查询,应该都能利用到这三个索引才对!
其实这里其实涉及到了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'
我们发现此处两个查询条件只有 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连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从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引擎在某次升级中早就自优化了。
所以要么跟进官方文档,要么关注数据库大牛的最新文章,要么在现有数据库环境下自己去亲手测试!
数据库领域的水很深。。大家加油。。共勉 ~
转自: