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章