MySQL（六）：InnoDB 变更缓冲区（Change Buffer）

太过爱你忘了你带给我的痛 2023-07-12 06:23 43阅读 0赞

### 文章目录 ###

*   *  1、简述
     *  2、配置变更缓冲区
     *  3、配置变更缓冲区最大大小
     *  4、监视变更缓冲区
     *  5、==变更缓冲区==常见问题
     *   *  5.1、哪些类型的操作会修改二级索引并导致修改缓存？
         *  5.2、InnoDB ==变更缓冲区== 的存在有什么好处？
         *  5.3、==变更缓冲区==是否支持其他类型的索引？
         *  5.4、InnoDB将多少空间用于==变更缓冲区==？
         *  5.5、如何确定 ==变更缓冲区== 的当前大小？
         *  5.6、何时会发生==变更缓冲区== 合并？
         *  5.7、==变更缓冲区==何时刷新？
         *  5.8、什么时候应该使用==变更缓冲区==？
         *  5.9、什么时候不应该使用==变更缓冲区==？
     *  6、参考文献

## 1、简述 ##

变更缓冲区是一种特殊的数据结构，当这些页面不在缓冲池中时，该高速缓存会将更改缓存到辅助索引页面。缓冲的更改（可能由INSERT，UPDATE或DELETE操作（DML）引起）将在以后通过其他读取操作将页面加载到缓冲池中时合并。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70]

与聚簇索引不同，二级索引通常是非唯一的，并且二级索引的插入以相对随机的顺序发生。同样，删除和更新可能会影响索引树中不相邻的二级索引页。稍后，当通过其他操作将受影响的页读入缓冲池时，合并缓存的更改可以避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问I / O。

在系统大部分处于空闲状态或关机期间运行的清除操作会定期将更新的索引页写入磁盘。与每个值立即写入磁盘相比，清除操作可以更有效地将一系列索引值写入磁盘块。

当有许多受影响的行和许多辅助索引要更新时，变更缓冲区合并可能需要几个小时。在此期间，磁盘I / O会增加，这可能会导致磁盘定查询的速度大大降低。提交事务之后，甚至在服务器关闭并重新启动之后，变更缓冲区合并也可能继续发生。

在内存中，变更缓冲区占用了缓冲池的一部分。在磁盘上，变更缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改将存储在其中。

变更缓冲区中缓存的数据类型由 innodb\_change\_buffering变量控制。

如果索引包含降序索引列或主键包含降序索引列，则辅助索引不支持更改缓冲。

## 2、配置变更缓冲区 ##

当对表执行INSERT，UPDATE和DELETE操作时，索引列的值（尤其是辅助键的值）通常处于未排序的顺序，需要大量的I/O才能使辅助索引保持最新状态。当相关页面不在缓冲池中时，变更缓冲区将更改缓存到辅助索引条目，从而避免了立即从磁盘读取页面进而避免了昂贵的I/O操作。  
当页面加载到缓冲池中时，缓冲的更改将合并，更新的页面随后将刷新到磁盘。当服务器接近闲置时以及在关闭期间，InnoDB主线程会合并缓冲的更改。

由于变更缓冲区功能可以减少磁盘读写操作，因此它对于受I/O限制的工作（例如，具有大量DML操作的应用程序，例如批量插入）显得更有价值。

但是，变更缓冲区占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集可以填充缓冲池，或者您的表具有相对较少的二级索引，则禁用变更缓冲区更加合适。如果缓冲池可以完全容纳数据集，则变更缓冲区不会带来额外的开销，因为它仅适用于不在缓冲池中的页面。

您可以使用 innodb\_change\_buffering 配置参数来控制InnoDB执行更改缓冲的程度。您可以为插入，删除操作（将索引记录标记为删除）和清除操作（物理删除索引记录）启用或禁用缓冲。更新操作是插入和删除的组合。默认的innodb\_change\_buffering值为all。

innodb\_change\_buffering 值包括：

*  all ：默认值，缓冲区插入，删除和清除。
 *  none：不缓存任何操作
 *  inserts：缓冲区插入操作。
 *  deletes：缓冲区删除标记操作。
 *  changes：缓冲区插入和删除标记操作。
 *  purges：缓冲区在后台发生的物理删除操作。

可以在MySQL配置文件（my.cnf或my.ini）中设置 innodb\_change\_buffering 参数，也可以使用SET GLOBAL语句动态更改它。更改设置会影响新操作的缓冲；现有缓冲条目的合并不受影响。

## 3、配置变更缓冲区最大大小 ##

*innodb\_change\_buffer\_max\_size* 变量允许将更改缓冲区的最大大小配置为缓冲池总大小的百分比。默认情况下，*innodb\_change\_buffer\_max\_size* 设置为25。最大可设置为50。

在具有大量插入，更新和删除活动的MySQL服务器上，建议增加 *innodb\_change\_buffer\_max\_size*，其中更改缓冲区合并不能跟上新的更改缓冲区条目，从而导致更改缓冲区达到其最大大小限制。

在MySQL服务器上用于存储静态数据，或者如果更改缓冲区消耗了与缓冲池共享的太多内存空间，从而导致页面比期望的更快地退出缓冲池，建议减小 *innodb\_change\_buffer\_max\_size*。

该 *innodb\_change\_buffer\_max\_size* 设置是动态的，它允许修改设置而无需重新启动服务器。

## 4、监视变更缓冲区 ##

以下选项可用于监视*变更缓冲区*：

1、InnoDB Standard Monitor 输出包括更改缓冲区状态信息。要查看监视数据，可以使用 ***SHOW* ENGINE INNODB STATUS** 语句。

mysql> SHOW ENGINE INNODB STATUS

更改缓冲区状态信息位于 INSERT BUFFER 和 ADAPTIVE HASH INDEX 下，类似于以下内容：

-------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    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 4425293, used cells 32, node heap has 1 buffer(s)
    13577.57 hash searches/s, 202.47 non-hash searches/s

2、***INFORMATION\_SCHEMA.INNODB\_METRICS*** 表提供了在InnoDB Standard Monitor 输出中指标信息。要查看 *变更缓冲区* 度量指标，可以使用以下查询语句：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'

3、**INFORMATION\_SCHEMA.*INNODB\_BUFFER\_PAGE*** 表提供有关缓冲池中每个页面的元数据，包括 *变更缓冲区* 索引和 *变更缓冲区* 位图页面。*变更缓冲区* 页面由PAGE\_TYPE标识。 IBUF\_INDEX 是 *变更缓冲区* 索引页面的页面类型，而IBUF\_BITMAP是 *变更缓冲区* 位图页面的页面类型。

> 注意：查询 INNODB\_BUFFER\_PAGE 表可能会产生大的性能开销。为避免影响性能，请在测试实例上进行重现及查询。

例如，您可以查询 INNODB\_BUFFER\_PAGE 表，以确定 IBUF\_INDEX 和 IBUF\_BITMAP 页的大约数量（占缓冲池页总数的百分比）

mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
           WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
           (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
           (SELECT ((change_buffer_pages/total_pages)*100)) 
           AS change_buffer_page_percentage;
    +---------------------+-------------+-------------------------------+
    | change_buffer_pages | total_pages | change_buffer_page_percentage |
    +---------------------+-------------+-------------------------------+
    |                  25 |        8192 |                        0.3052 |
    +---------------------+-------------+-------------------------------+

4、Performance Schema 提供了 *变更缓冲区* 互斥锁等待检测，以进行高级性能监视。要查看 *变更缓冲区* 检测信息，请执行以下查询：

mysql> SELECT * FROM performance_schema.setup_instruments
           WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
    +-------------------------------------------------------+---------+-------+
    | NAME                                                  | ENABLED | TIMED |
    +-------------------------------------------------------+---------+-------+
    | wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
    +-------------------------------------------------------+---------+-------+

## 5、变更缓冲区常见问题 ##

### 5.1、哪些类型的操作会修改二级索引并导致修改缓存？ ###

INSERT、UPDATE、DELETE 操作会导致修改二级索引。如果受影响的索引页不在缓冲池中，则 变更缓冲区修改缓存。

### 5.2、InnoDB 变更缓冲区 的存在有什么好处？ ###

当二级索引页不在缓冲池中时，对二级索引的修改进行缓存可以避免昂贵的随机访问I/O操作，该操作可能需要立即从磁盘中读取受影响的索引页。当页面被其他读取操作读取到缓冲池中时，缓冲的更改可以稍后分批进行。

### 5.3、变更缓冲区是否支持其他类型的索引？ ###

*变更缓冲区* 仅支持二级索引。不支持聚簇索引，全文索引和空间索引。全文索引具有其自己的缓存机制。

### 5.4、InnoDB将多少空间用于变更缓冲区？ ###

在MySQL 5.6中引入配置选项之前，系统表空间中磁盘更改缓冲区的最大大小为InnoDB缓冲池大小的1/3 。

在MySQL 5.6和更高版本中， innodb\_change\_buffer\_max\_size 配置选项将更改缓冲区的最大大小定义为总缓冲池大小的百分比。默认情况下，innodb\_change\_buffer\_max\_size 设置为25。最大设置为50。

如果会导致磁盘上的 变更缓冲区 超过定义的限制，InnoDB 则不会缓冲操作。

变更缓冲区 页面不需要保留在缓冲池中，并且可以由LRU操作逐出。

### 5.5、如何确定 变更缓冲区 的当前大小？ ###

通过 SHOW ENGINE INNODB STATUS 可以查看 *变更缓冲区* 的当前大小。例如：

-------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges

*  size：变更缓冲区 使用的页数。变更缓冲区 大小等于 seg size - (1 + free list len)。(1 + free list len) 表示 变更缓冲区 头页面。
 *  seg size：*变更缓冲区*的大小（以页为单位）。

### 5.6、何时会发生变更缓冲区 合并？ ###

*  将页面读入缓冲池时，在页面可用之前，缓冲的更改会在读取完成后合并。
 *  变更缓冲区 合并作为后台任务执行。 innodb\_io\_capacity 参数设置由InnoDB后台任务执行的I/O活动的上限。
 *  在崩溃恢复期间执行变更缓冲区合并。当将索引页读入缓冲池时，修改将从变更缓冲区（在系统表空间中）应用于辅助索引的叶页。
 *  变更缓冲区是持久性的，可以在系统崩溃后恢复。重新启动后，更改缓冲区合并操作将作为正常操作的一部分恢复
 *  可以使用–innodb-fast-shutdown = 0 强制将变更缓冲区合并作为服务器关闭的一部分。

### 5.7、变更缓冲区何时刷新？ ###

更新的页面将通过与刷新占用缓冲池的其他页面相同的刷新机制进行刷新。

### 5.8、什么时候应该使用变更缓冲区？ ###

变更缓冲区 是一项旨在减少随索引变大而不再适合InnoDB缓冲池的二级索引的随机I/O的功能。通常，当整个数据集都无法容纳到缓冲池中时，当大量的DML活动会修改二级索引页面时，或者当许多二级索引被DML活动定期更改时，应使用变更缓冲区。

### 5.9、什么时候不应该使用变更缓冲区？ ###

如果InnoDB缓冲池可以容纳整个数据集，或者二级索引相对较少，或者您使用的是固态存储（其中随机读取的速度与顺序读取的速度差不多），则可以考虑禁用变更缓冲区。

## 6、参考文献 ##

1.  [《高性能MySQL(第3版)》][MySQL_3]
2.  [《MySQL技术内幕：InnoDB存储引擎(第2版)》][MySQL_InnoDB_2]
3.  [《MySQL源码库》][MySQL]
4.  [《MySQL参考手册》][MySQL 1]
5.  [《MySQL实战45讲》][MySQL_45]
6.  [《数据库内核月报》][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200307173420267.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU=,size_16,color_FFFFFF,t_70
[MySQL_3]: http://e.dangdang.com/products/1900716972.html
[MySQL_InnoDB_2]: http://e.dangdang.com/products/1900432045.html
[MySQL]: https://dev.mysql.com/doc/dev/mysql-server/latest/
[MySQL 1]: https://dev.mysql.com/doc/refman/8.0/en/
[MySQL_45]: https://time.geekbang.org/column/intro/139
[Link 1]: http://mysql.taobao.org/monthly/2020/02/02/