mysql中innodb-内存结构-更改缓存区

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。

1、更改缓存区简介

更改缓存区是一种特殊的数据结构，当二级索引页不在缓存池中时，它将更改缓存到二级索引页。缓存的更改(可能由INSERT、UPDATE或DELETE操作(DML)引起)稍后在其他读操作将页面加载到缓存池时合并。

与聚集索引不同的是，二级索引通常不是惟一的，并且二级索引的插入是以相对随机的顺序进行的。类似地，删除和更新可能会影响索引树中没有邻接位置的二级索引页。稍后，当其他操作将受影响的页读到缓存池中时，合并缓存的更改，可以避免从磁盘将二级索引页读到缓存池中所需的大量随机访问I/O。

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

当有许多受影响的行和许多二级索引需要更新时，更改缓存区合并可能需要几个小时。在此期间，磁盘I/O会增加，这可能导致磁盘绑定查询的显著减速。在事务提交后，甚至在服务器关闭和重启后，更改缓存区合并也可能继续发生(更多信息请参见下面“强制InnoDB恢复”)。

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

更改缓存区中缓存的数据类型由innodb_change_buffering变量控制，详情见下文

如果二级索引包含降序索引列，或者主键包含降序索引列，则不支持更改缓存

2、强制InnoDB恢复

1、简介

要调查数据库页面损坏，您可以使用 SELECT … INTO OUTFILE. 通常，通过这种方式获得的数据大部分是完整的。严重损坏可能会导致SELECT * FROM tbl_nameInnoDB语句或 后台操作意外退出或断言，甚至导致前 滚恢复崩溃。在这种情况下，您可以使用innodb_force_recovery 选项强制启动存储引擎，同时阻止后台操作运行，以便转储您的表。例如，在重新启动服务器之前，你可以在选项文件的[mysqld]部分添加以下一行:

[mysqld]
innodb_force_recovery = 1

只有在紧急情况下才将innodb_force_recovery设置为大于0的值，这样才能启动InnoDB并转储表。在这样做之前，请确保您有数据库的备份副本，以防需要重新创建它。值大于等于4会永久损坏数据文件。只有在成功地在数据库的一个单独物理副本上测试该设置之后，才可以在生产服务器实例上使用innodb_force_recovery设置为4或更大。当强制InnoDB恢复时，你应该总是从innodb_force_recovery=1开始，只在必要时增加这个值。

innodb_force_recovery默认为 0（正常启动，不强制恢复），允许的非零值为 1 ～ 6。较大的值包括较小值的功能。例如，值 3 包括值 1 和 2 的所有功能。

如果您能够转储innodb_force_recovery值为3或更小的表，那么相对安全的是，只有损坏的单个页上的一些数据会丢失。4或更大的值被认为是危险的，因为数据文件可能被永久损坏。值6被认为是极端的，因为数据库页面处于过时的状态，这反过来可能会给B 树 和其他数据库结构引入更多损坏。

当innodb_force_recovery值大于0时，InnoDB会防止INSERT、UPDATE、DELETE操作，这是一种安全措施。innodb_force_recovery设置为4个或更大的位置，InnoDB处于只读模式。

2、innodb_force_recovery介绍

• 1 ( SRV_FORCE_IGNORE_CORRUPT)

让服务器运行，即使它检测到一个损坏的页面。尝试使SELECT * FROM tbl_name跳过损坏的索引记录和页，这有助于转储表。

• 2 ( SRV_FORCE_NO_BACKGROUND)

阻止主线程和任何清除线程运行。如果在清除操作期间发生意外退出，此恢复值会阻止它。

• 3 ( SRV_FORCE_NO_TRX_UNDO)

崩溃恢复后 不运行事务 回滚。

• 4 ( SRV_FORCE_NO_IBUF_MERGE)

防止插入缓存区合并操作。如果它们会导致崩溃，则不执行它们。不计算表 统计信息。此值可能会永久损坏数据文件。使用此值后，请准备删除并重新创建所有二级索引。设置 InnoDB为只读。

• 5 ( SRV_FORCE_NO_UNDO_LOG_SCAN)

启动数据库时 不查看撤消日志： InnoDB即使是未完成的事务也视为已提交。此值可能会永久损坏数据文件。设置InnoDB为只读。

• 6 ( SRV_FORCE_NO_LOG_REDO)

不执行与恢复相关的重做日志前 滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，这反过来可能会给 B 树和其他数据库结构带来更多损坏。设置 InnoDB为只读。

3、总结

innodb_force_recovery值小于等于3时，可以删除或创建表。直到 MySQL 5.7.17，DROP TABLE都支持innodb_force_recovery大于 3的 值。从 MySQL 5.7.18 开始， DROP TABLE不允许innodb_force_recovery大于 4的 值。

如果您知道给定的表导致回滚时意外退出，您可以删除它。如果遇到批量导入失败或ALTER TABLE导致的失控回滚，可以杀死mysqld进程，并将innodb_force_recovery设置为3，以在不进行回滚的情况下启动数据库，然后DROP掉导致失控回滚的表。

如果表数据中的损坏阻止您转储整个表内容，则带有子句的查询可能能够转储损坏部分之后的表部分。 ORDER BY primary_key DESC

如果启动InnoDB需要一个很高的innodb_force_recovery值，可能会有损坏的数据结构，这可能会导致复杂的查询(包含WHERE, ORDER BY或其他子句的查询)失败。在这种情况下，您可能只能运行基本的SELECT * FROM t查询。

3、配置变更缓存

当在表上执行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语句动态更改它，这需要足够的权限来设置全局系统变量。

更改设置会影响新操作的缓存;合并现有的缓存项不受影响

。

4、配置更改缓存区最大大小

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

。

5、监控更改缓存区

详情参阅：https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html

6、变更缓存区的常见问题

关于变更缓存区的常见问题，请参见https://dev.mysql.com/doc/refman/5.7/en/faqs-innodb-change-buffer.html
FAQ:

1. 哪些类型的操作会修改二级索引并导致更改缓存？
2. InnoDB 更改缓存区的好处是什么？
3. 更改缓存区是否支持其他类型的索引？
4. InnoDB 为更改缓存区使用了多少空间？
5. 如何确定更改缓存区的当前大小？
6. 何时发生更改缓存区合并？
7. 何时刷新更改缓存区？
8. 什么时候应该使用更改缓存区？
9. 什么时候不应该使用更改缓存区？
10. 在哪里可以找到有关更改缓存区的其他信息？

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

INSERT、UPDATE、 和 DELETE操作可以修改二级索引。如果受影响的索引页不在缓存池中，则可以在更改缓存区中缓存更改。

2、InnoDB更改缓存区 的好处是什么？

当二级索引页不在缓存池中时缓存二级索引更改可避免昂贵的随机访问 I/O 操作，这些操作需要立即从磁盘读取受影响的索引页。缓存的更改可以稍后分批应用，因为其他读取操作将页面读入缓存池。

3、更改缓存区是否支持其他类型的索引？

不可以。更改缓存区仅支持二级索引。不支持聚集索引、全文索引和空间索引。全文索引有自己的缓存机制。

4、InnoDB更改缓存区使用了 多少空间？

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

在 MySQL 5.6 及更高版本中， innodb_change_buffer_max_size 配置选项将更改缓存区的最大大小定义为总缓存池大小的百分比。默认情况下， innodb_change_buffer_max_size 设置为 25。最大设置为 50。

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

更改缓存页面不需要在缓存池中持久化，并且可能会被 LRU 操作驱逐。

5、如何确定更改缓存区的当前大小？

更改缓存区的当前大小由 标题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
相关数据点包括：

size：更改缓存区中使用的页数。更改缓存区大小等于seg size - (1 + free list len)。该1 +值表示更改缓存区标题页。

seg size：更改缓存区的大小，以页为单位。

详情参阅：有关监视更改缓存区状态的信息。

6、何时发生更改缓存区合并？

• 当页面被读入缓存池时，缓存的更改会在读取完成后合并，然后页面可用。

• 更改缓存区合并作为后台任务执行。该 innodb_io_capacity 参数设置InnoDB后台任务执行的 I/O 活动的上限，例如合并来自更改缓存区的数据。

• 在崩溃恢复期间执行更改缓存区合并。当索引页读入缓存池时，更改从更改缓存区（在系统表空间中）应用到二级索引的叶页。

• 更改缓存区是完全持久的，可以在系统崩溃时幸免于难。重新启动后，更改缓存区合并操作将作为正常操作的一部分恢复。

• 作为缓慢服务器关闭的一部分，可以使用 强制完全合并更改缓存区 --innodb-fast-shutdown=0。

7、何时刷新更改缓存区？

更新的页面由相同的刷新机制刷新，刷新占用缓存池的其他页面。

8、什么时候应该使用更改缓存区？

更改缓存区是一项功能，旨在随着索引变大并且不再适合InnoDB缓存池而减少对二级索引的随机 I/O 。通常，当整个数据集不适合缓存池时，当有大量 DML 活动修改二级索引页面时，或者当有大量二级索引被 DML 活动定期更改时，应该使用更改缓存区。

9、什么时候不应该使用更改缓存区？

如果整个数据集适合InnoDB缓存池，如果二级索引相对较少，或者如果您使用固态存储，其中随机读取与顺序读取一样快，您可能会考虑禁用更改缓存区。在进行配置更改之前，建议您使用具有代表性的工作负载运行测试，以确定禁用更改缓存区是否有任何好处。