12 普通索引和唯一索引有哪些区别？
更新时间：2019-08-22 11:13:47
衡量一个人的真正品格，是看他在知道没人看见的时候干些什么。

——孟德斯鸠

对于普通索引和唯一索引的区别，也许你已经知道：有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。其实对于 MySQL 来说，不止这一种区别。今天我们就再深入探究一下普通索引和唯一索引的区别。

在讨论两者的区别前，我们首先学习一下 Insert Buffer 和 Change Buffer。

1 Insert Buffer

对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入 Insert Buffer 中，然后再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge 操作。这时通常能将多个插入合并到一个操作中（因为在一个索引页中），就大大提高了非聚集索引的插入性能。

为什么要增加 Insert Buffer？

增加 Insert Buffer 有两个好处：

  • 减少磁盘的离散读取
  • 将多次插入合并为一次操作

但是得注意的是，使用 Insert Buffer 得满足两个条件：

  • 索引是辅助索引
  • 索引不是唯一

2 Change Buffer

InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

影响参数有两个：

  • innodb_change_buffering：确定哪些场景使用 Change Buffer，它的值包含：none、inserts、deletes、changes、purges、all。默认为 all，表示启用所有。
  • innodb_change_buffer_max_size：控制 Change Buffer 最大使用内存占总 buffer pool 的百分比。默认25，表示最多可以使用 buffer pool 的 25%，最大值50。

跟 Insert Buffer 一样，Change Buffer 也得满足这两个条件：

  • 索引是辅助索引
  • 索引不是唯一

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

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

3 普通索引和唯一索引的区别

通过上面对 Insert Buffer 和 Change Buffer 的了解，也许你已经知道了普通索引和唯一索引的另外一种区别：如果对数据有修改操作，则普通索引可以用 Change Buffer，而唯一索引不行。

在上面讲解 Change Buffer 时，也提到了修改唯一索引必须判断是否违反唯一性约束，其实在 RR 隔离级别（事务隔离级别将在第 4 章重点讲解）下，可能会出现一个比较严重的问题：死锁。

那么查询过程两者的区别呢？

对于普通索引，查找到满足条件的第一个记录，还需要查找下一个记录，直到不满足条件。

对于唯一索引来说，查找到第一个记录返回结果就结束了。

但是 InnoDB 是按页从磁盘读取的，所以很大可能根据该普通索引查询的数据都在一个数据页里，因此如果通过普通索引查找到第一条满足条件所在的数据页，再查找后面的记录很大概率都在之前的数据页里，也就是多了几次内存扫描，实际这种消耗可以忽略不计。

这里总结一下普通索引和唯一索引的隐藏区别：

  • 数据修改时，普通索引可以用 Change Buffer，而唯一索引不行。
  • 数据修改时，唯一索引在 RR 隔离级别下，更容易出现死锁。
  • 查询数据是，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况可以忽略不计。

4 普通索引和唯一索引如何选择

上面说了普通索引和唯一索引的区别，那么两者应该如何选择呢？

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

ERROR 1062 (23000): Duplicate entry '1' for key 'f1'

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

5 总结

本节在讲普通索引和唯一索引的区别时，首先提到了 Insert Buffer，其目的是将多次插入合并。InnoDB 从 1.0.x 版本开始，开始支持增删改操作，统一称为 Change Buffer。

最后再总结下普通索引和唯一索引的区别：

  • 有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。
  • 数据修改时，普通索引优于唯一索引，因为普通索引可以用 Change Buffer，并且 RR 隔离级别下，出现死锁的概率比唯一索引低。
  • 查询数据时，两者性能差别不大。

6 参考资料

《MySQL 内核：InnoDB 存储引擎》卷1：第 11 章 Insert Buffer

}