MySQL 存储引擎 InnoDB 内存结构之更改缓冲区

更改缓冲区（Change Buffer）是一种特殊的数据结构，用于缓存不在缓冲池中的二级索引（secondary index）页的更改。可能来自于INSERT、UPDATE或DELETE操作（数据操作语言，DML）的缓冲更改，会在后续通过其他读操作将这些页加载到缓冲池时被合并。

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

在系统大部分处于空闲状态或慢速关闭期间，会运行清理（purge）操作，定期将更新的索引页写入磁盘。与立即将每个值写入磁盘相比，清理操作可以更高效地将索引值批量写入磁盘。

当有大量受影响的行和需要更新的二级索引时，变更缓冲区的合并过程可能需要几个小时。在此期间，磁盘 I/O 会增加，这可能导致磁盘密集型查询明显减慢。提交事务之后，甚至在服务器关闭并重新启动之后，变更缓冲区合并也可能会持续发生(请参阅“第 14.22.2 节“强制 InnoDB 恢复”了解更多信息)。

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

变更缓冲区中缓存的数据类型由innodb_change_buffering变量控制。要了解更多信息，请参阅下文的”配置变更缓冲区“。您还可以配置最大变更缓冲区大小。要了解更多信息，请参阅下文的”配置最大变更缓冲区的大小“。

如果二级索引包含降序索引列，或者主键包含降序索引列，那么变更缓冲区不支持对该二级索引进行缓冲。

有关变更缓冲区的常见问题的解答，请参见第 A.16 节“ MySQL 5.7 FAQ: InnoDB 变更缓冲区”。

配置变更缓冲区

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

由于变更缓冲区可以减少磁盘读写操作，因此它对于 I/O 密集型的工作负载最为有价值。例如，变更缓冲可以给频繁进行 DML 操作（如批量插入）的应用程序带来好处。

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

innodb_change_buffering变量控制着InnoDB执行变更缓冲的程度。您可以启用或禁用插入操作、删除操作（最初将索引记录标记为删除时）和清理操作（当索引记录被物理删除时）的缓冲。更新操作是插入操作和删除操作的组合。innodb_change_buffering的默认值为all。

  • all

    默认值：缓冲区插入，删除标记操作和清除。

  • none

    不缓冲任何操作。

  • inserts

    缓冲插入操作。

  • deletes

    缓冲删除标记操作。

  • changes

    缓冲插入和删除标记操作。

  • purges

    缓冲后台发生的物理删除操作。

您可以在 MySQL 选项文件(my.cnf或my.ini)中设置innodb_change_buffering参数，或使用SET GLOBAL语句动态更改它，该语句需要足够的权限来设置全局系统变量。参见第 5.1.8.1 节“系统变量特权”。更改设置会影响新操作的缓冲；现有缓冲条目的合并不受影响。

您可以在 MySQL 的选项文件（my.cnf或my.ini）中设置innodb_change_buffering参数，或者使用SET GLOBAL语句动态更改它，该语句需要足够的权限来设置全局系统变量。请参阅第 5.1.8.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

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

  • Information Schema的INNODB_METRICS表提供了InnoDB标准监视器输出中的大部分数据点以及其他数据点。要查看变更缓冲区指标及其描述，请执行以下查询：

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

    有关`INNODB_METRICS表用法情况的信息，请参见第 14.16.6 节“ InnoDB INFORMATION_SCHEMA Metrics Table”。

  • Information Schema的INNODB_BUFFER_PAGE表提供了关于缓冲池中每个页面的元数据，包括变更缓冲区索引和变更缓冲区位图页面。变更缓冲区页面通过PAGE_TYPE进行标识。IBUF_INDEX是变更缓冲区索引页的页面类型，IBUF_BITMAP是变更缓冲区位图页的页面类型。

    Waring：查询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表提供的其他数据的信息，请参阅第 24.4.2 节 “INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”。有关相关使用信息，请参阅第 14.16.5 节 “InnoDB INFORMATION_SCHEMA缓冲池表”。

  • 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   |
+-------------------------------------------------------+---------+-------+

    有关监视InnoDB互斥锁等待的信息，请参阅第 14.17.2 节 “使用 Performance Schema 监视 InnoDB 互斥等待”。

注：原文来自 MySQL 5.7 官方文档，阅读 MySQL 中文文档时有些语句理解不顺畅，便结合中文文档使用 ChatGPT 进行了翻译，如有不正请指出。

posted @ 2023-06-20 18:26  风吟echo  阅读(397)  评论(3)  编辑  收藏  举报