change buffer是在别的数据库查询中沒有的一个定义,简言之便是一块系统软件表空间分派的室内空间,对于的目标是辅助索引的叶子节点(为何并不是主键索引?由于主键索引是集聚索引,在磁盘上的排序是井然有序的,磁盘的次序IO的特性很高,而任意IO的特性却很低)。当辅助索引的值有升级时,将这种升级先缓存文件起來,当有别的运用对同样的页做升级操作后,对该页开展融合,最终将融合后的值一起升级到磁盘文档中,降低了磁盘的I/O. change buffer是由InnoDB的系统软件表空间分派的,尽管叫buffer,但和double write buffer一样,全是表空间的室内空间,能够 和别的数据信息页一样缓存文件在buffer pool中。
下列是change buffer有关文本文档的汉语翻译,来源于官方网文本文档。
14.9.4 Configuring InnoDB Change Buffering
When INSERT, UPDATE, and DELETE operations are performed on a table, the values of indexed columns (particularly the values of secondary keys) are often in an unsorted order, requiring substantial I/O to bring secondary indexes up to date. InnoDB has a change buffer that caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded to the buffer pool, and the updated page is later flushed to disk. The InnoDB main thread merges buffered changes when the server is nearly idle, and during a slow shutdown.
当对表开展删改改操作的情况下,因为索引列(尤其是拷贝索引列)的值一直混乱的,升级辅助索引通常必须很多的任意I/O操作。InnoDB的change buffer会储放一些辅助索引内容,当有DML操作对辅助索引改动时,会先对这种索引页开展融合,以后一块更新到磁盘上。InnoDB的主线任务程会在系统软件I/O空余时或关闭设备融合这种索引页。
Because it can result in fewer disk reads and writes, the change buffer feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.
由于change buffer能够 降低磁盘的读写能力,在有很多读写能力关联的操作上面更反映它的使用价值,例如针对批量插入操作的运用而言。
However, the change buffer occupies a part of the buffer pool, reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working set fits entirely within the buffer, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.
殊不知,change buffer会占有buffer pool的存储空间,那样便会降低数据信息页在运行内存中的缓存文件。因而针对有辅助索引有关的表操作的情况下,change buffer很有可能会有效,针对工作中集都会缓存池中的操作,change buffer就不容易起功效,因为它只运用于索引页不彻底在运行内存中的状况。
You can control the extent to which InnoDB performs change buffering using the innodb_change_buffering configuration parameter. You can enable or disable buffering for inserts, delete operations (when index records are initially marked for deletion) and purge operations (when index records are physically deleted). An update operation is a combination of an insert and a delete. In MySQL 5.5 and higher, the default innodb_change_buffering value is changed from inserts to all.
能够 应用主要参数innodb_change_buffering来对是不是应用change buffer来开展操纵,5.5及之上的版本号中初始值是all.
Permitted innodb_change_buffering values include:

The default value: buffer inserts, delete-marking operations, and purges.


Do not buffer any operations.


Buffer insert operations.


Buffer delete-marking operations.


Buffer both inserts and delete-marking operations.


Buffer the physical deletion operations that happen in the background.

You can set the innodb_change_buffering parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege. Changing the setting affects the buffering of new operations; the merging of existing buffered entries is not affected.
innodb_change_buffering主要参数的值能够 在环境变量(my.cnf or my.ini)中设定,或是具备SUPER管理权限的客户应用SET GLOBAL指令动态性的改动,改动只对之后的操作起效。

能够 根据下列指令来监管change buffer。

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1 buffer(s)
15.81 hash searches/s, 46.33 non-hash searches/s

seg size 指segment总的分派尺寸,以页为企业;
free list 指空余数据信息页;
size 指早已合拼数据信息页总数。