普通索引和唯一索引对更新语句性能的影响这个问题，得了解一下change buffer。

MySQL中的change buffer信息如下，默认值是25，代表它的大小最大只能占用到innodb buffer pool的25%大小，如下：

show variables like "%change%";

 

当我们需要更新一条数据记录的时候，如果这个记录所在的数据页A在内存中，那么直接更新内存中的数据页A即可，如果数据页B不在内存中，这个时候，Innodb会将这些更新操作缓存在change buffer中，当下次需要访问磁盘上的数据页B时，将数据页B从磁盘上加载到内存里面，然后应用change buffer中与这个数据页有关的操作。通过这种方式，有两个好处：

第一、将原本2次的磁盘访问，整合成1次磁盘访问，并且能够保证数据的一致性。

第二、数据页读入内存是需要占用内存空间的，这种方式能够避免内存的使用，提高内存的利用率

应用change buffer中与该数据页相关的操作的这个过程，我们称之为数据页的merge操作，merge操作在以下的场景下会触发：

1、访问数据页的时候

2、后台线程每秒都会merge

3、数据库正常关闭的情况下

上面说普通索引和唯一索引在更新上的区别的时候，我们说了，普通索引可以使用change buffer，而唯一索引不能使用change buffer，原因是唯一索引在做insert或者update的时候，需要判断索引记录的唯一性，而判断唯一性必须要在内存中判断，所以数据页会被加载到内存中，如果数据页已经加载到了内存中，那么当然是直接更新内存更快了。

上面我们说过，更新的时候，唯一索引不能使用change buffer而普通索引能够使用change buffer.

这里我们详细看看在更新记录的时候，这俩分别干了什么事情。以update table where k=4举例子：

普通索引k会找到索引记录等于3和5的位置，然后在中间插入k=4的记录；

唯一索引k会找到索引记录等于3和5的位置，然后判断没有冲突，插入k=4的记录；

可以看出，当记录在内存中的时候，这俩差别不大，仅仅是唯一索引多了一步判断唯一性的操作。这个判断，仅仅会消耗很小一部分cpu的资源。

唯一索引需要将数据页加载到内存中，判断这个值没有冲突，然后插入这个新值；

普通索引则是将更新记录在change buffer，语句执行就结束了。

可以看到，普通索引利用了change buffer，减少了磁盘上的随机访问，对性能的提升比较明显。

在一些写多读少的业务中，change buffer能够发挥很好的作用。它可以将多次对磁盘的操作，合并成一次merge操作，从而提高MySQL的性能，一次性merge的操作越多，收益就越大。

但是需要注意，如果你的数据写入之后。立马会读取(也就意味着需要从磁盘读取到内存)，那么建议不要使用change buffer，因为在这种情况下，使用change buffer不会减低IO次数，反而多了change buffer的维护开销。

下面的示意图表示了change buffer和redo log的合作过程。

当我们要更新一条普通索引记录的时候：

1、如果这条记录在内存中，那么直接更新内存；

2、如果该记录没有在内存中，那么就需要更新change buffer

3、更新完change buffer之后，MySQL会在redo log中记录下change buffer的修改，

4、事务就算完成了，后续binlog落盘，redo log commit

5、当需要读取不在内存中的记录时，会将该数据页从磁盘加载到内存，然后应用change buffer中的修改，也就是merge操作

 

3.索引的选择

普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。

在实际使用中，会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优

化还是很明显的。特别地，在使用机械硬盘时代，change buffer这个机制的收效是非常显著的。所以，当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。