MySQL :: MySQL 9.0 参考手册 :: 17.5.2 更改缓冲区

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 更改缓冲区

17.5.2 更改缓冲区

更改缓冲区是一种特殊的数据结构，当辅助索引页不在缓冲池中时，它会缓存对这些页的更改。缓冲的更改可能是由 INSERT、UPDATE 或 DELETE 操作（DML）导致的，稍后当其他读取操作将页面加载到缓冲池中时，这些更改将被合并。

图 17.3 更改缓冲区

Content is described in the surrounding text.

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

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

当有许多受影响的行和大量辅助索引需要更新时，更改缓冲区合并可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能会导致磁盘绑定查询显着减慢。即使在事务提交之后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能会继续发生（有关更多信息，请参阅第 17.20.3 节，“强制 InnoDB 恢复”）。

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

更改缓冲区中缓存的数据类型由 innodb_change_buffering 变量控制。有关更多信息，请参阅配置更改缓冲。您还可以配置最大更改缓冲区大小。有关更多信息，请参阅配置更改缓冲区最大大小。

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

有关更改缓冲区的常见问题解答，请参阅第 A.16 节，“MySQL 9.0 常见问题解答：InnoDB 更改缓冲区”。

配置更改缓冲

当在表上执行 INSERT、UPDATE 和 DELETE 操作时，索引列（尤其是辅助键的值）的值通常是无序的，需要大量的 I/O 才能使辅助索引保持最新。当相关的页不在缓冲池中时，更改缓冲区会缓存对辅助索引条目的更改，从而通过不立即从磁盘读取页面来避免昂贵的 I/O 操作。当页面加载到缓冲池中时，缓冲的更改将被合并，并且更新后的页面稍后将刷新到磁盘。当服务器几乎空闲时，以及在缓慢关闭期间，InnoDB 主线程会合并缓冲的更改。

由于更改缓冲可以减少磁盘读写次数，因此对于 I/O 密集型工作负载最有价值；例如，具有大量 DML 操作的应用程序（如批量插入）将受益于更改缓冲。

但是，更改缓冲区会占用缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集几乎适合缓冲池，或者如果表中只有相对较少的二级索引，则禁用更改缓冲可能很有用。如果工作数据集完全适合缓冲池，则更改缓冲不会产生额外的开销，因为它仅适用于不在缓冲池中的页面。

innodb_change_buffering 变量控制 InnoDB 执行更改缓冲的程度。您可以为插入、删除操作（最初标记要删除索引记录时）和清除操作（物理删除索引记录时）启用或禁用缓冲。更新操作是插入和删除的组合。默认 innodb_change_buffering 值为 none，允许的值在 innodb_change_buffering 文档中进行了描述。

您可以在 MySQL 选项文件（my.cnf 或 my.ini）中设置 innodb_change_buffering 变量，也可以使用 SET GLOBAL 语句动态更改它，这需要具有足以设置全局系统变量的权限。请参阅第 7.1.9.1 节“系统变量权限”。更改设置会影响新操作的缓冲；不会影响现有缓冲条目的合并。

配置更改缓冲区最大大小

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 变量是动态的，允许在不重新启动服务器的情况下修改设置。

监控更改缓冲区

以下选项可用于更改缓冲区监控

InnoDB 标准监视器输出包括更改缓冲区状态信息。要查看监视器数据，请发出 SHOW ENGINE INNODB STATUS 语句。

mysql> SHOW ENGINE INNODB STATUS\G

更改缓冲区状态信息位于 INSERT BUFFER AND 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

有关更多信息，请参阅第 17.17.3 节“InnoDB 标准监视器和锁监视器输出”。

信息架构 INNODB_METRICS 表提供了在 InnoDB 标准监视器输出中找到的大多数数据点以及其他数据点。要查看更改缓冲区指标和每个指标的描述，请发出以下查询
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
请参阅第 17.15.6 节“InnoDB 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 |
+---------------------+-------------+-------------------------------+
```
有关 INNODB_BUFFER_PAGE 表提供的其他数据的详细信息，请参阅第 28.4.2 节“INFORMATION_SCHEMA INNODB_BUFFER_PAGE 表”。有关相关使用信息，请参阅第 17.15.5 节“InnoDB INFORMATION_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   |
+-------------------------------------------------------+---------+-------+

有关监控 InnoDB 互斥锁等待的信息，请参阅第 17.16.2 节“使用性能架构监控 InnoDB 互斥锁等待”。