MySQL 普通索引和唯一索引的区别详解
1概念区分
- 普通索引和唯一索引
普通索引可重复,唯一索引和主键一样不能重复。唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键)
- 主键和唯一索引
主键保证数据库里面的每一行都是唯一的,比如身份证,学号等,在表中要求唯一,不重复。唯一索引的作用跟主键的作用一样。不同的是,在一张表里面只能有一个主键,主键不能为空,唯一索引可以有多个,唯一索引可以有一条记录为空,即保证跟别人不一样就行。比如学生表,在学校里面一般用学号做主键,身份证则弄成唯一索引;而到了教育局,他们就把身份证号弄成主键,学号换成了唯一索引。选谁做表的主键,要看实际应用,主键不能为空。
2案例引入
某居民系统,每人有唯一身份证号。如果系统需要按身份证号查姓名,就会执行类似如下SQL:
selectnamefromCUserwhereid_card='ooxx';
然后你肯定会在id_card字段建索引。但id_card字段较大,不推荐将其做主键。于是现有俩选择:
- 给id_card字段创建唯一索引
- 创建一个普通索引
假定业务代码已保证不会写入重复的身份证号,这两个选择逻辑上都正确。但从性能角度考虑,唯一索引还是普通索引呢?
再看如下案例:假设字段k上的值都不重复。
- InnoDB的索引组织结构:
接下来分析性能。
3查询性能
selectidfromTwherek=4
通过B+树从树根开始层序遍历到叶节点,可认为数据页内部是通过二分法搜索。
- 普通索引,查找到满足条件的第一个记录(4,400)后,需查找下个记录,直到碰到第一个不满足k=4的记录
- 唯一索引,由于索引具备唯一性,查找到第一个满足条件的记录后,就会停止检索
看起来性能差距很微小。
InnoDB数据按数据页单位读写。即读一条记录时,并非将该一个记录从磁盘读出,而以页为单位,将其整体读入内存。
因此普通索引,要多做一次“查找和判断下一条记录”的操作,也就一次指针寻找和一次计算。如果k=4记录恰为该数据页最后一个记录,那么要取下个记录,还得读取下个数据页,操作稍微复杂。对整型字段,一个数据页可存近千key,因此这种情况概率其实也很低。因此计算平均性能差异时,可认为该操作成本对现在CPU开销忽略不计。
我们知道MySQL有changebuffer。
4 更新性能
现在来看往表中插入一个新记录(4,400),InnoDB会做什么?
需要区分该记录要更新的目标页是否在内存:
4.1在内存
- 唯一索引
找到3和5之间位置,判断到没有冲突,插入值,语句执行结束。
- 普通索引
找到3和5之间位置,插入值,语句执行结束。
普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,耗费微小CPU时间。
4.2不在内存
- 唯一索引
需将数据页读入内存,判断到没有冲突,插入值,语句执行结束。
- 普通索引
将更新记录在changebuffer,语句执行结束。
将数据从磁盘读入内存涉及随机IO访问,是数据库里面成本最高操作之一。而changebuffer减少随机磁盘访问,所以更新性能提升明显。
5 实践中的索引选择
普通索引和唯一索引究竟如何抉择?这两类索引在查询性能上没差别,主要考虑对更新性能影响。所以,推荐尽量选择普通索引。
如果所有更新后面,都紧跟对该记录的查询,那么该关闭changebuffer。而在其他情况下,changebuffer都能提升更新性能。普通索引和changebuffer的配合使用,对于数据量大的表的更新优化还是很明显的。
在使用机械硬盘时,changebuffer机制的收效非常显著。所以,当你有一个类似“历史数据”的库,并且出于成本考虑用机械硬盘时,应该关注这些表里的索引,尽量使用普通索引,把changebuffer开大,确保“历史数据”表的数据写速度。
6changebuffer和redolog
WAL提升性能的核心机制,也是尽量减少随机读写,这两个概念易混淆。所以,这里我把它们放到了同一个流程里来说明区分。
6.1插入流程
insertintot(id,k)values(id1,k1),(id2,k2);
假设当前k索引树的状态,查找到位置后,k1所在数据页在内存(InnoDBbufferpool),k2数据页不在内存。
- 带changebuffer的更新流程图,图中两个箭头都是后台操作,不影响更新响应。
该更新做了如下操作:
- Page1在内存,直接更新内存
- Page2不在内存,就在changebuffer区,缓存下“往Page2插一行记录”的信息
- 将前两个动作记入redolog
之后事务完成。执行该更新语句成本很低,只写两处内存,然后写一处磁盘(前两次操作合在一起写了一次磁盘),还是顺序写。
6.2怎么处理之后的读请求?
select*fromtwherekin(k1,k2);
读语句紧随更新语句,内存中的数据都还在,此时这俩读操作就与系统表空间和redolog无关。所以在图中就没画这俩。
- 带changebuffer的读过程
读Page1时,直接从内存返回。WAL之后如果读数据,是不是一定要读盘,是不是一定要从redolog里面把数据更新以后才可以返回?其实不用。看上图状态,虽然磁盘上还是之前数据,但这里直接从内存返回结果,结果正确。
要读Page2时,需把Page2从磁盘读入内存,然后应用changebuffer里面的操作日志,生成一个正确版本并返回结果。可见直到需读Page2时,该数据页才被读入内存。
所以,要简单对比这俩机制对更新性能影响
- redolog主要节省随机写磁盘的IO消耗(转成顺序写)
- changebuffer主要节省随机读磁盘的IO消耗
7 总结
由于唯一索引用不了changebuffer的优化机制,因此如果业务可以接受,从性能角度,推荐优先考虑非唯一索引。
7.1关于到底是否使用唯一索引
主要纠结在“业务可能无法确保”。本文前提是“业务代码已经保证不会写入重复数据”下,讨论性能问题。
如果业务不能保证,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。这种情况下,本文意义在于,如果碰上大量插入数据慢、内存命中率低时,多提供一个排查思路。
然后,在一些“归档库”的场景,可考虑使用唯一索引的。比如,线上数据只需保留半年,然后历史数据保存在归档库。此时,归档数据已是确保没有唯一键冲突。要提高归档效率,可考虑把表的唯一索引改普通索引。
7.2如果某次写入使用changebuffer,之后主机异常重启,是否会丢失changebuffer的数据?
不会丢失。虽然是只更新内存,但在事务提交时,我们把changebuffer的操作也记录到redolog,所以崩溃恢复时,changebuffer也能找回。
7.3merge的过程是否会把数据直接写回磁盘?
merge执行流程
- 从磁盘读入数据页到内存(老版本数据页)
- 从changebuffer找出该数据页的changebuffer记录(可能有多个),依次应用,得到新版数据页
- 写redolog
该redolog包含数据的变更和changebuffer的变更
至此merge过程结束。这时,数据页和内存中changebuffer对应磁盘位置都尚未修改,是脏页,之后各自刷回自己物理数据,就是另外一过程。
问题思考
在构造第一个例子的过程,通过sessionA的配合,让sessionB删除数据后又重新插入一遍数据,然后就发现explain结果中,rows字段从10001变成37000多。而如果没有sessionA的配合,只是单独执行deletefromt、callidata()、explain这三句话,会看到rows字段其实还是10000左右。这是什么原因呢?
如果没有复现,检查
- 隔离级别是不是RR(RepeatableRead,可重复读)
- 创建的表t是不是InnoDB引擎
为什么经过这个操作序列,explain的结果就不对了?delete语句删掉了所有的数据,然后再通过callidata()插入了10万行数据,看上去是覆盖了原来10万行。但是,sessionA开启了事务并没有提交,所以之前插入的10万行数据是不能删除的。这样,之前的数据每行数据都有两个版本,旧版本是delete之前数据,新版本是标记deleted的数据。这样,索引a上的数据其实有两份。
然后你会说,不对啊,主键上的数据也不能删,那没有使用forceindex的语句,使用explain命令看到的扫描行数为什么还是100000左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段a作为索引更合适)是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是showtablestatus的值。大家的机器如果IO能力比较差的话,做这个验证的时候,可以把innodb_flush_log_at_trx_commit和sync_binlog都设置成0。
以上就是MySQL普通索引和唯一索引的区别详解的详细内容,更多关于MySQL普通索引和唯一索引的资料请关注毛票票其它相关文章!
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。