MySQL中的change buffer

MySQL的一条语句，大致流程查看内存→读取磁盘数据页→返回数据。

当比如查找一个 a=5的记录的时候，并不是只查找出这一条数据，它所在的整个数据页都会查找出来(每个数据页16KB)。

下次查找a=6的记录的时候，发现该页已经在内存中了，直接返回，不需要磁盘IO。

但是当时增、删、改操作时，并不会每一次操作都进行一次磁盘IO，使用change buffer可以降低磁盘随机IO。

change buffer首先是可持久化的数据。

当更新某个数据页时，该页在内存中，那么直接更新。

如果该页不在内存中，那么先将更新操作记录在change buffer中，这时不需要从磁盘中读出数据页。

将操作记录到redo log中，防止机器意外关闭导致数据丢失。

这时已经可以返回给客户端更新成功了，因为即使机器意外重启，也可以通过redo log找回数据。

change buffer 使用的是 buffer pool里的内存，不能无限增大，可以通过参数 innodb_change_buffer_max_xize来动态设置，这个参数为50的时候，标识change buffer 的大小最多只能占用 buffer pool 的50%。

什么时候将change buffer中的数据更新到磁盘中？

当下一次查询命中这个数据页的时候，会先从磁盘中读取数据页到内存中，然后先执行change buffer的merge操作，保证数据逻辑的正确性。除了查询操作外，系统有后台线程会定期merge，数据库正常关闭(shutdown)的时候，也会进行merge操作。

 

change buffer 和 redo log 对于磁盘的随机IO影响。

redo log是减少 随机写磁盘IO 的消耗。每个操作先记录redo log，系统空闲时或redo log满时进行磁盘IO。

change buffer是减少 随机读磁盘IO 的消耗。更新时如果内存中不存在该数据页，也不需要马上进行磁盘IO，而是先记录在change buffer中，等待时机统一merge。

 

所以根据业务场景选择是否使用change buffer。

当写少读多的情况下：change buffer的利用率不高，因为可能刚一更新完可能就触发merge操作，change buffer没有起到减少随机IO，还多了一个维护change buffer的成本。

当写多读少的情况下：change buffer的提升效果较明显，可能很多条更新后，也没有一个查询线程来触发merge操作，可以大幅减少磁盘的随机IO。

 

唯一索引和 普通索引（在业务逻辑层保证数据唯一）的选择

命中唯一索引和普通索引时MySQL筛选数据的逻辑不同。

比如一个查询语句 select * from table where k=5;先通过B+树从树根开始，按层搜索到叶子节点，也就是数据页。

唯一索引 k：由于索引定义了唯一性，所以在数据页中找到第一个满足条件的记录后，直接返回。

普通索引 k：没有定义索引的唯一性，所以在查找到第一条满足k=5后，还会继续遍历下一条，直到查到的k != 5的时候，结束遍历。

那么两种索引的效率差距有多大呢？

其实是很小，几乎可忽略。普通索引相对于唯一索引多的一步操作就是"查找并判断下一条记录"，因为都存在于同一个数据页，所以内存遍历对于CPU的开销可忽略。一个数据页可以存在上千个key，刚好下一条数据在下一个数据页而触发再次读取数据页的概率很低。所以这两种索引的效率几乎一样。

但是当进行增删改操作时，两种索引的效率就有明显的差距了。

因为唯一索引每个插入或者更新都要判断是否违反唯一约束，所以每次更新都要从内存中找到这个记录对应的数据页，没有的话需要从磁盘中读出，反正都要从磁盘读数据页了，还不如直接更新内存/磁盘，那么也就意味着change buffer失去意义，所以唯一索引使用change buffer 的话反倒会降低效率，所以只有普通索引能使用到change buffer。