【MySQL】唯一索引与普通索引的区别

2022.04.02

唯一索引上的字段的数据只能是唯一的，而普通索引可以有重复数据。

以 SELECT * FROM user WHERE k=5 为例，

对于普通索引来说，k 是普通索引的话，会在普通索引上面找出满足条件的值，找到之后会继续往下找。
对于唯一索引来说，会在索引上面找出满足条件的值后就停止不继续往下，因为这行数据是唯一的。
从字面上看的话，这两者之间差距很大，因为唯一索引每次都是第一次就确定要的数据，但其实在内部的设计中，这两者之间差距并不是很大。

为什么呢？

因为 MySQL 是有个缓冲池的，它是通过预读 (Read-Ahead) 将磁盘里面的数据一页页的存入内存中，每页数据大概有 16KB。

也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。一个数据页可以放近千个 key，在计算平均性能差异时，可以认为“查找和判断下一条记录”操作成本对于CPU 来说可以忽略不计。

那么如何选择普通索引与唯一索引呢？

如果业务要求某个字段唯一，但是代码不能完全保证写入唯一值，则添加唯一索引，让这个字段唯一，该字段新增重复数据时，将报 Duplicate entry 的错；

如果代码确定某个字段不会有重复的数据写入，则可以选择添加普通索引。 因为普通索引可以使用 Change Buffer，并且出现死锁的概率比唯一索引低。

建议尽量选择普通索引。如果所有的更新后面都马上伴随着对这个记录的查询，那么就关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

Change Buffer

change buffer 减少了随机磁盘访问，所以对更新性能的提升是会明显的。

为什么唯一索引的更新不使用 Change Buffer ?

原因：唯一索引必须要将数据页读入内存才能判断是否违反唯一性约束。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 Change Buffer 了。