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

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 17 InnoDB 存储引擎 / 17.5 InnoDB 内存结构 / 17.5.2 更改缓冲区

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

17.5.2 变更缓冲17.5.2 Change Buffer

更改缓冲区是一种特殊的数据结构，当这些页面不在缓冲池中时，它会缓存对二级索引页面的更改。被缓冲的更改可能来自INSERT、UPDATE或DELETE操作（DML），当页面被其他读取操作加载到缓冲池时，这些更改会被随后合并。The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

图 17.3 更改缓冲区Figure 17.3 Change Buffer

Content is described in the surrounding text.

与聚簇索引不同，二级索引通常是非唯一的，并且插入到二级索引的操作顺序相对随机。类似地，删除和更新可能会影响在索引树中位置不相邻的二级索引页面。当受影响的页面被其他操作读取到缓冲池中时，稍后合并缓存的更改可以避免从磁盘读取二级索引页面到缓冲池所需的大量随机访问 I/O。Unlike clustered indexes, secondary indexes are usually nonunique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read secondary index pages into the buffer pool from disk.

系统大部分处于空闲状态时，或者在缓慢关闭期间运行的清理操作，会周期性地将更新后的索引页面写入磁盘。与每个值立即写入磁盘相比，清理操作可以更高效地为一系列索引值写入磁盘块。Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

当受影响的行数很多且需要更新的二级索引众多时，变更缓冲合并可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能会显著减慢依赖磁盘的查询速度。变更缓冲合并也可能在事务提交后继续进行，甚至在服务器关闭和重启后继续进行（有关更多信息，请参见第 17.20.3 节，“强制 InnoDB 恢复”）。Change buffer merging may take several hours when there are many affected rows and numerous secondary indexes to update. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed, and even after a server shutdown and restart (see Section 17.20.3, “Forcing InnoDB Recovery” for more information).

在内存中，变更缓冲占用缓冲池的一部分。在磁盘上，变更缓冲是系统表空间的一部分，当数据库服务器关闭时，索引更改会被缓冲到其中。In memory, the change buffer occupies part of the buffer pool. On disk, the change buffer is part of the system tablespace, where index changes are buffered when the database server is shut down.

缓存到更改缓冲区的数据类型由 innodb_change_buffering 变量控制。更多信息请参见配置更改缓冲。您还可以配置最大更改缓冲区大小。更多信息请参见配置更改缓冲最大大小。The type of data cached in the change buffer is governed by the innodb_change_buffering variable. For more information, see Configuring Change Buffering. You can also configure the maximum change buffer size. For more information, see Configuring the Change Buffer Maximum Size.

如果二级索引包含降序索引列，或者主键包含降序索引列，则不支持更改缓冲。Change buffering is not supported for a secondary index if the index contains a descending index column or if the primary key includes a descending index column.

有关更改缓冲区的常见问题解答，请参阅第 A.16 节，“MySQL 8.4 FAQ：InnoDB 更改缓冲”。For answers to frequently asked questions about the change buffer, see Section A.16, “MySQL 8.4 FAQ: InnoDB Change Buffer”.

配置更改缓冲Configuring Change Buffering

当对表执行INSERT、UPDATE和DELETE操作时，被索引列的值（尤其是二级索引键的值）通常是无序的，这需要大量 I/O 操作来更新二级索引。变更缓冲区在相关页不在缓冲池中时，会缓存对二级索引条目的更改，从而通过不立即从磁盘读取页面来避免昂贵的 I/O 操作。当页面加载到缓冲池中时，缓冲的更改会被合并，并且更新后的页面随后会被刷新到磁盘中。InnoDB 主线程在服务器接近空闲时以及在慢速关闭期间合并缓冲的更改。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. The change buffer 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 into 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 密集型的工作负载最有价值；例如，具有大量 DML 操作的应用程序（如批量插入）可以从变更缓冲中受益。Because it can result in fewer disk reads and writes, change buffering is most valuable for workloads that are I/O-bound; for example, applications with a high volume of DML operations such as bulk inserts benefit from change buffering.

然而，变更缓冲会占用缓冲池的一部分，减少用于缓存数据页的内存。如果工作集几乎可以完全放入缓冲池，或者您的表的二级索引相对较少，则禁用变更缓冲可能是有用的。如果工作数据集完全适合缓冲池，变更缓冲不会带来额外开销，因为它只应用于不在缓冲池中的页面。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 data set fits entirely within the buffer pool, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.

innodb_change_buffering 变量控制 InnoDB 执行变更缓冲的程度。您可以为插入操作、删除操作（当索引记录最初被标记为删除时）和清理操作（当索引记录被物理删除时）启用或禁用缓冲。更新操作是插入和删除的组合。默认的 innodb_change_buffering 值为 none，允许的值在 innodb_change_buffering 文档中有描述。The innodb_change_buffering variable controls the extent to which InnoDB performs change buffering. 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. The default innodb_change_buffering value is none, and permitted values are described within the innodb_change_buffering documentation.

您可以在 MySQL 配置文件（my.cnf 或 my.ini）中设置innodb_change_buffering变量，或者使用SET GLOBAL语句动态更改它，这需要具有足够权限来设置全局系统变量。参见第 7.1.9.1 节，“系统变量权限”。更改此设置会影响新操作的缓冲，但已缓冲条目的合并不会受到影响。You can set the innodb_change_buffering variable in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL statement, which requires privileges sufficient to set global system variables. See Section 7.1.9.1, “System Variable Privileges”. Changing the setting affects the buffering of new operations; the merging of existing buffered entries is not affected.

配置 Change Buffer 最大大小Configuring the Change Buffer Maximum Size

innodb_change_buffer_max_size 变量允许将更改缓冲区的最大尺寸配置为缓冲池总大小的百分比。默认情况下，innodb_change_buffer_max_size 设置为 25。最大设置值为 50。The innodb_change_buffer_max_size variable permits configuring the maximum size of the change buffer as a percentage of the total size of the buffer pool. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50.

考虑在具有大量插入、更新和删除操作的 MySQL 服务器上增加 innodb_change_buffer_max_size，当更改缓冲区合并跟不上新的更改缓冲区条目时，可能导致更改缓冲区达到其最大尺寸限制。Consider increasing innodb_change_buffer_max_size on a MySQL server with heavy insert, update, and delete activity, where change buffer merging does not keep pace with new change buffer entries, causing the change buffer to reach its maximum size limit.

考虑在用于报表的静态数据的 MySQL 服务器上减少 innodb_change_buffer_max_size，或者当更改缓冲区占用了与缓冲池共享的过多内存空间，导致页面比预期更早地从缓冲池中淘汰时，也应减少该值。Consider decreasing innodb_change_buffer_max_size on a MySQL server with static data used for reporting, or if the change buffer consumes too much of the memory space shared with the buffer pool, causing pages to age out of the buffer pool sooner than desired.

使用具有代表性的工作负载测试不同设置，以确定最佳配置。innodb_change_buffer_max_size 变量是动态的，可以在不重启服务器的情况下修改设置。Test different settings with a representative workload to determine an optimal configuration. The innodb_change_buffer_max_size variable is dynamic, which permits modifying the setting without restarting the server.

监控更改缓冲区Monitoring the Change Buffer

可用于更改缓冲区监控的选项如下：The following options are available for change buffer monitoring:

InnoDB 标准监视器输出包括更改缓冲状态信息。要查看监视器数据，请执行 SHOW ENGINE INNODB STATUS 语句。InnoDB Standard Monitor output includes change buffer status information. To view monitor data, issue the SHOW ENGINE INNODB STATUS statement.
```
mysql> SHOW ENGINE INNODB STATUS\G
```
更改缓冲状态信息位于 INSERT BUFFER AND ADAPTIVE HASH INDEX 标题下，并显示类似如下：Change buffer status information is located under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading and appears similar to the following:
```
-------------------------------------
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 标准监视器和锁监视器输出”。For more information, see Section 17.17.3, “InnoDB Standard Monitor and Lock Monitor Output”.
信息模式 INNODB_METRICS 表提供了大部分在 InnoDB 标准监控器输出中找到的数据点以及其他数据点。要查看更改缓冲区指标及其描述，请执行以下查询：The Information Schema INNODB_METRICS table provides most of the data points found in InnoDB Standard Monitor output plus other data points. To view change buffer metrics and a description of each, issue the following query:
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
参见第 17.15.6 节，“InnoDB INFORMATION_SCHEMA Metrics 表”。See Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
信息模式 INNODB_BUFFER_PAGE 表提供缓冲池中每个页面的元数据，包括更改缓冲索引页和更改缓冲位图页。更改缓冲页面通过 PAGE_TYPE 进行标识。IBUF_INDEX 是更改缓冲索引页的页面类型，IBUF_BITMAP 是更改缓冲位图页的页面类型。The Information Schema INNODB_BUFFER_PAGE table provides metadata about each page in the buffer pool, including change buffer index and change buffer bitmap pages. Change buffer pages are identified by PAGE_TYPE. IBUF_INDEX is the page type for change buffer index pages, and IBUF_BITMAP is the page type for change buffer bitmap pages.
Warning
查询 INNODB_BUFFER_PAGE 表可能会引入显著的性能开销。为了避免影响性能，请在测试实例上复现您要调查的问题，并在测试实例上运行您的查询。Querying the INNODB_BUFFER_PAGE table can introduce significant performance overhead. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and run your queries on the test instance.
例如，您可以查询 INNODB_BUFFER_PAGE 表，以确定 IBUF_INDEX 和 IBUF_BITMAP 页面约占缓冲池总页面的百分比。For example, you can query the INNODB_BUFFER_PAGE table to determine the approximate number of IBUF_INDEX and IBUF_BITMAP pages as a percentage of total buffer pool pages.
```
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 节，“INNODB_BUFFER_PAGE 表”。有关相关的使用信息，请参见第 17.15.5 节，“InnoDB INFORMATION_SCHEMA 缓冲池表”。For information about other data provided by the INNODB_BUFFER_PAGE table, see Section 28.4.2, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. For related usage information, see Section 17.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

性能模式提供用于高级性能监控的更改缓冲互斥等待器的检测。要查看更改缓冲检测，请执行以下查询：Performance Schema provides change buffer mutex wait instrumentation for advanced performance monitoring. To view change buffer instrumentation, issue the following query:

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 节，“使用 Performance Schema 监控 InnoDB 互斥等待”。For information about monitoring InnoDB mutex waits, see Section 17.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

首页上一级下一节