MySQL学习笔记 - 3 - 关于change buffer的补充

在《MySQL学习笔记 - 2 - 索引相关 - 掘金》文章中对于change buffer的描述，我当时的理解是change buffer是用于缓存数据页的修改，但是实际上，change buffer是用来缓存对于非主键索引的数据页的修改,也就是说，对于表数据的数据页的修改是不会缓存在change buffer中。果然还是得看看官方得文档?，下面是官方文档中关于change buffer得介绍，我翻译了一下，然后发出来，有兴趣可以看一看?

change buffer是一种特殊的数据结构，用于缓存不在buffer pool中的非主键索引的数据页的修改。缓存起来的修改（包括增删改）会在数据页通过其他读操作被加载进buffer pool的时候合并。

跟主键索引不一样，非主键索引通常不是唯一的，并且插入非主键索引的顺序是相对随机的。类似地，删除和更新可能会影响不在索引树中邻接的非主键索引页。当受影响的数据页被其他操作读入buffer pool后，在稍后的时间合并缓存的更改，这避免了大量的随机IO。

purge operation会定期在系统空闲的时候，或者在正常关闭数据库的过程中，将更新后的非索引数据页写到磁盘。purge operation可以为一系列的修改后索引值写入磁盘块，这比每个索引值在更改后立即写入磁盘中更有效。

当有很多受影响的行和很多非主键索引需要更新的时候，change buffer的merge操作可能需要花费好几个小时。在此期间，磁盘的IO会增加，这会导致磁盘的查询明显变慢。change buffer的合并操作可能会在一个事务提交后继续执行，也可能会在MySQL关闭重启后执行。

在内存中，change buffer占用一部分的buffer pool。在磁盘中，change buffer是系统表空间的一部分，当MySQL关闭的时候，对于索引页的修改的操作将会持久化到磁盘的系统表空间。

缓存在change buffer的数据类型由参数innodb_change_buffering参数控制。change buffer的大小同样也是可以设置的。

change buffer并不适用于包含倒序索引列或包含主键包含倒序索引列的非主键索引。

配置change buffer

当新增，更新和删除操作在表中执行，索引列的值（尤其是非主键索引列的值）通常是无序的，如果需要立即更新这些非主键索引会要求大量的随机IO。当跟这些操作（增删改）相关的非主键索引数据页不在内存中的时候，change buffer会将相关的修改缓存起来，从而避免了昂贵的随机IO。当数据页加载进buffer pool后，change buffer缓存的修改将会合并到数据页中，随后会刷新到磁盘中。当MySQL空闲或者正常关闭的过程中，InnoDB的main线程会合并change buffer的更改。

由于change buffer可以减少磁盘IO，所以change buffer适合于频繁增删改的场景

然而，change buffer占用的是buffer pool的空间，这会相对减少缓存数据页的空间。如果数据页基本都在buffer pool中，或者表的非主键索引比较少，那么建议关闭change buffer。

通过参数innodb_change_buffering可以控制InnoDBchange buffer缓存的类型

value	description
all	默认值，缓存插入、删除标记操作和清除。
none	不缓存任何操作
inserts	缓存插入操作
deletes	缓存删除操作
changes	缓存插入和删除操作
purages	缓存发生在后台的物理删除操作

可以通过MySQL配置文件（my.cnf或my.ini）或使用set global语句动态设置innodb_change_buffering，这要求MySQL账户拥有足够的权限（root用户）。修改参数后不会影响change buffer中已缓存的修改。

配置change buffer的最大值

通过参数innodb_change_buffer_max_size参数，可以设置change buffer在buffer pool的最大值（以百分比的形式）。默认情况下，innodb_change_buffer_max_size的值为25，最大设置的值是50。

当MySQL需要承受大量的新增、更新或删除操作的时候，可以考虑增大change_buffer在buffer pool中的比例。

当MySQL主要用于静态数据报表之类，或者change buffer占用buffer pool的空间过大导致内存命中率下降的时候，可以考虑减少change_buffer在buffer pool中的比例。

参数innodb_change_buffer_max_size得设置是动态的，修改后不需要重启MySQL。