MySQL第6讲 普通索引和唯一索引，应该怎么选择？

普通索引和唯一索引的查询、更新过程有何区别，应该如何选取？

1. 举个栗子

2. 查询/搜索过程

3. 更新过程

1) change buffer

2）唯一索引和普通索引怎么使用change buffer？

3）change buffer的适用场景

4）你是不是觉得change buffer和redo log很像

如果普通索引和唯一索引都能保证业务正确性，那应该选取普通索引还是唯一索引呢？为什么要这样选呢？选择的依据是什么呢？

1. 举个栗子

以下例子摘抄自极客时间作者原话：

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以，你一定会考虑在id_card字段上建索引。

由于身份证号字段比较大，我不建议你把身份证号当做主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。

现在我要问你的是，从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

建立一个索引，无非是在查询和更新的时候可以更快找到符合条件的数据，所以我们从查询过程和更新过程来分别分析普通索引和唯一索引的性能影响。

2. 查询/搜索过程

描述：假设执行一条普通的查询语句：select id from T where k=5，首先从B+树的根节点出发，按层搜索一直到叶子节点，然后在叶子节点内再通过二分法来定位记录（每个节点内部也是有序的）。

• 如果是普通索引，当查找到满足条件的第一个满足k=5的记录时，会再往下继续查找，直到找到第一个不满足k=5的记录时停止查找。

• 如果是唯一索引，由于唯一性，当查找到第一个满足k=5的记录时，就停止继续查找。

看到这里你可能会觉得使用唯一索引效率更高，因为可以少查一次。但是这点差异其实是很小的，因为InnoDB按页将数据读取内存，所以当找到k=5的记录时，它所在的数据页已经在内存里了，而唯一索引只不过是多做了一次寻址和计算而已，所以这一点性能差异其实是可以忽略不计的。

偶尔可能会出现k=5的下一条记录刚好在下一页上，这就需要将下一页再读入内存，花销会更多，但是对于整型字段来说，一个数据页可以存放接近千个索引，所以这种跨页读取出现的概率其实是很小的，从而平均性能差异其实还是很小。

3. 更新过程

1) change buffer

change buffer是什么呢？试想一下，当执行某条更新语句的时候，如果要更新的数据不在内存中，那还需要将数据页先加载进内存，要知道磁盘读入内存是涉及随机IO访问的，这个速度是很慢的，MySQL这么优秀，这么能容忍如此低效的事情时常发生。如果在更新磁盘的时候先将更新操作缓存在内存中，下次需要访问这个数据页的时候在读入内存，然后将缓存中与此的内容刷新到数据页中。这个缓存就是change buffer。

将change buffer中的操作应用到原数据页的过程称为merge，访问这个数据页会触发merge，并且系统后台会定期merge，数据库正常关闭过程中，也会执行merge。

2）唯一索引和普通索引怎么使用change buffer？

经过前面的介绍我们知道change buffer是在更新数据的时候用作更新操作的缓存使用。那么设想一下，假设要更新的目标数据页不在内存中，那么对普通索引来说，只需要将更新记录在change buffer中即可，等到需要访问的时候再加载进内存进行merge。而对于唯一索引来说，多了一个判断，唯一索引需要保证更新数据后的索引的唯一性，所以对于唯一来说还需要将数据页读入内存，先判断有无冲突，再插入相应的值。

由此可见如果使用普通索引，那么change buffer的使用减少了随机磁盘访问的次数，在更新不在内存中的数据页时只需要写change buffer，不需要读磁盘。而唯一索引并没有用到change buffer，磁盘随机访问次数会比普通索引要多。

如果更新的数据页在内存中，那普通索引和唯一索引都只需要执行更新即可，唯一索引同样需要判断有无冲突，此时都用不到change buffer。

所以，在满足业务正确的前提下，尽量使用普通索引，利用change buffer减少读磁盘次数。

以下例子摘抄自极客时间作者原话：

之前我就碰到过一件事儿，有个DBA的同学跟我反馈说，他负责的某个业务的库内存命中率突然从99%降低到了75%，整个系统处于阻塞状态，更新语句全部堵住。而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。

3）change buffer的适用场景

我们指定change buffer执行merge的时候才是更新真正数据页的时候，也就是有访问该数据页的操作时就会触发merge，那如果业务中频繁出现写完马上查询的操作，那么change buffer反而起到了副作用。因为频繁更新后访问，这意味着一直在读磁盘然后执行merge，这并没有减少磁盘IO次数。

所以在写多读少的情景下，change buffer的使用效果最好。

4）你是不是觉得change buffer和redo log很像

确实是有点像，但是还是不一样的两个玩意。

对redo log来说只要与更新有关的操作都会记录，而change buffer只是在更新的数据页不在内存中时才记录到change buffer中。

redo log是减少了写磁盘的消耗，因为更新操作只需要追加到日志即可。change buffer是减少了读磁盘的消耗，每次更新不在内存中的数据页时只需要写在change buffer中，不用读磁盘。

redo log还作为change buffer的“兜底逻辑”，如果机器异常崩溃了，在事务提交的时候buffer中修改的内容已经写入了redo log，所以重启之后依然可以恢复change buffer的内容。

文末ps：是否使用唯一索引，以及要不要替换成普通索引，还是要以业务正确性优先。