选择唯一索引还是普通索引?

一叶而不知秋

向管中窥豹寻知外,坐井观天又出来。

我们都有这样的经验,数据库中一般唯一性的字段需要设置唯一索引约束。但是如果业务可以保证数据唯一性,该如何选择,它们之间有什么差异?下面我们从两种索引除了唯一约束,性能上有什么差别。

查询性能差异

普通索引查询流程:

根据索引找到第一条满足查询条件的记录

找到下一行记录

判断下一行是否满足查询条件

满足重复步骤2,不满足返回满足条件的结果集

唯一索引查询流程:

根据索引找到第一条满足查询条件的记录

返回该行记录

可以看到,普通索引和唯一索引的差异就是“是否读取并判断下一行记录”。InnoDB中,数据是按页为单位来读写,读取一行记录会将包含该行的整个页读到内存,因此这个差异只是一次指针寻找和一次比较判断,对性能的影响微乎其微。

更新性能差异

首先我们介绍一下更新语句的执行流程。假如我们要执行:

上图中,redo log有连个状态,分别是:prepare和commit状态。这就是经常说的两阶段提交。MySQL的日志为什么要使用两阶段提交呢?我们不妨假设不用两阶段提交,执行一条语句A,看看会有什么问题。

先写redo log,binlog还没写入服务器异常重启。恢复的时候,主库重放redo log中的A语句,从库重放的binlog中A语句没有写入,结果主从不一致。

先写binlog,redo log还没写入服务器异常重启。恢复的时候,主库重放redo log中A语句没有写入,而从库中的binlog写入了A语句,结果主从不一致。

还有一个地方需要注意:数据页不在内存中时,需要将磁盘数据读入内存,磁盘读取涉及到随机IO,这是MySQL中成本最高的操作之一。为了解决这个问题,引入了change buffer机制。

change buffer

如上所述,change buffer是为了解决数据页不在内存时随机IO的问题,将更新操作缓存到change buffer中。change buffer使用的是buffer pool中的内存,配置项设置最多占用buffer pool的比例。

内存中的数据在断电后会清空,因此change buffer中数据需要持久化,这就需要通过merge操作,有三种情况会触发merge操作:

将原数据页读入内存时

定时任务merge

数据库正常关闭

通过前面的介绍,可以知道,数据页不在内存才会用到change buffer机制。而唯一索引需要校验数据的唯一性,校验操作需要在内存中进行。因此只有change buffer只是用于普通索引。

还可以得出change buffer的适用场景:写多读少,并且写完立马访问到的概率小。如果写完立马访问,会将数据页加载到内存,然后执行merge操作;这样还不如直接将数据加载到内存,然后更新内存,省去了修改change buffer和merge操作。

当然,前面的讨论是基于业务保证不重复的前提下,将唯一索引改为普通索引可以提升效率,而在业务不能保证的情况下,还是以业务为先,在数据库增加唯一约束。

你如果想学技术 | 屯干货 | 聊职场

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181227G0Y3G100?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券