MySQL中普通索引和唯一索引在读写上性能的差别

【饿了么、美团】外卖红包领取地址 >> 能省一点是一点

在MySQL中,某个字段使用普通索引还是唯一索引的判断条件第一要务还是取决于业务。如果业务上这个字段是非唯一的,那肯定使用不了唯一索引;如果这个字段是唯一的,可以使用唯一索引也可以使用普通索引,只是使用普通索引的时候需要通过业务代码来保障数据的唯一性。这里假定使用后一种情况,抛开业务逻辑看看普通索引和唯一索引在读写上性能的差别。

读性能

在查询中,由于唯一索引限定了数据只会有一条,所以查到第一条满足条件的数据之后,唯一索引的情况下就会停止检索,普通索引则要检索到下一条不符合条件的数据才会停止。故而理论上唯一索引在查询时性能会大于普通索引,但是MySQL是以页为单位从磁盘中读取数据到内存的,对于CPU和内存来说,这个差距微乎其微可以忽略不计。

写性能

在数据库中,有一个change buffer。在更新数据时,如果数据页已经在内存中了,则会直接修改内存中的数据,否则会将修改的数据先缓存在change buffer中,等待下一次读取到相关的数据页时再进行一个合并操作merge。虽然change buffer叫buffer,但是他是一个持久化的数据,所以不用担心数据会丢失,且有后台线程会定期的进行merge操作。
有了change buffer可以减少磁盘的读取操作,使得更新语句的执行速度得到明显的提升。

在唯一索引的场景下,由于更新需要先判断数据是否存在,即是否违反唯一性约束,所以必然会有一次数据页的读取,而此时数据页已经在内存中了,所以不需要使用到change buffer。
在普通索引的场景下,更新会直接写入change buffer。

综上,由于有change buffer的加持,普通索引的写性能会大于唯一索引。但是也有反例,假如你在普通索引下写入数据之后立马进行了读取,那么数据库在写完change buffer之后立马需要读取对应的数据页,然后进行merge合并操作,反而增加了多余的磁盘操作,带来副作用。所以change buffer适合在写多读少的场景下使用。

参考资料:《MySQL实战45讲》第9章


觉得内容还不错?打赏个钢镚鼓励鼓励!!👍

汉状元 儿童画画本宝宝涂色书2-3-6岁幼儿园涂鸦填色绘本图画绘画册套装 全套8本共384页【含36色油画棒】
¥43.80
顾影 苹果13手机壳iphone13promax保护套13pro硅胶软壳镜头全包围潮牌皮质防摔男女款 苹果13Pro【黑色潮牌熊】 镜头全包+不沾指纹
¥30.00
Daisy Leaf 日本防滑凳 加厚儿童凳浴室防滑宝宝洗澡凳子换鞋凳 小号蓝色
¥26.90
倍思(Baseus)手机支架 金属指环扣磁吸支架可360度旋转 适用于苹果安卓手机 银色
¥22.00
三分钟趣读漫画谚语 必读经典传统文化
¥21.42
雀巢(Nestle) 雀巢无蔗糖添加咖啡二合一饮品咖啡香浓无糖添加速溶咖啡粉30条装健身运动咖啡 无蔗糖咖啡30条
¥39.90
品胜苹果12mini手机壳iphone12mini液态硅胶保护套全包防摔12mini软壳直边手机壳 蓝色
¥20.80
世一堂 哈药山楂鸡内金益生菌软糖60克 儿童成人酸甜健康小零食
¥39.90