8.5.2 优化 InnoDB 事务 Management

要优化InnoDB事务处理，请在事务功能的性能开销与服务器的工作负载之间找到理想的平衡。例如，如果一个应用程序每秒提交数千次，则可能会遇到性能问题；如果仅每 2-3 小时提交一次，则可能会遇到不同的性能问题。

• 默认的 MySQL 设置AUTOCOMMIT=1可能会对繁忙的数据库服务器造成性能限制。在可行的情况下，通过发出SET AUTOCOMMIT=0或START TRANSACTION语句，然后在进行所有更改后再发出COMMIT语句，将多个相关的数据更改操作包装到单个事务中。

如果该事务对数据库进行了修改，则InnoDB必须在每次事务提交时将日志刷新到磁盘。在每次更改之后都进行一次提交(与默认的自动提交设置一样)时，存储设备的 I/O 吞吐量将限制每秒可能进行的操作数。

• 另外，对于仅包含一个SELECT语句的事务，打开AUTOCOMMIT可以帮助InnoDB识别只读事务并对其进行优化。有关要求，请参见第 8.5.3 节“优化 InnoDB 只读事务”。

• 避免在插入，更新或删除大量行之后执行回滚。如果大事务减慢了服务器性能，则回滚它会使问题变得更糟，执行时间可能是原始数据更改操作的几倍。终止数据库进程无济于事，因为回滚会在服务器启动时再次开始。

为了最大程度地减少发生此问题的可能性：

• 增大buffer pool的大小，以便可以缓存所有数据更改更改，而不是立即将它们写入磁盘。

  • 设置innodb_change_buffering=all，以便除了插入操作外还缓冲更新和删除操作。

  • 考虑在大数据更改操作期间定期发出COMMIT条语句，可能将单个删除或更新分解为对较少行数进行操作的多个语句。

要消除发生的回滚，请增加缓冲池，以使回滚成为 CPU 绑定并快速运行，或者终止服务器并使用innodb_force_recovery=3重新启动，如第 14.19.2 节“ InnoDB 恢复”中所述。

默认设置innodb_change_buffering=all预计不会出现此问题，默认设置innodb_change_buffering=all允许将更新和删除操作缓存在内存中，从而使它们首先可以更快地执行，并且在需要时可以更快地回滚。确保在处理具有许多插入，更新或删除操作的长期事务的服务器上使用此参数设置。

• 如果发生崩溃时，如果您可以承担一些最近提交的事务的损失，可以将innodb_flush_log_at_trx_commit参数设置为 0.InnoDB仍然尝试每秒刷新一次日志，尽管不能保证刷新。另外，将innodb_support_xa的值设置为 0，这将减少由于磁盘数据和二进制日志同步而导致的磁盘刷新次数。

• 当修改或删除行时，不会立即或什至在事务提交后立即物理删除行和关联的undo logs。保留旧数据，直到更早或同时开始的事务完成为止，以便那些事务可以访问已修改或已删除行的先前状态。因此，长时间运行的事务可以防止InnoDB清除由其他事务更改的数据。

• 在长时间运行的事务中修改或删除行时，如果其他事务使用READ COMMITTED和REPEATABLE READ隔离级别，则它们必须做更多的工作才能重建较旧的数据(如果它们读取了相同的行)。

• 当长时间运行的事务修改 table 时，来自其他事务的对该 table 的查询将不使用covering index技术。通常可以从二级索引检索所有结果列，而从 table 数据中查找适当值的查询。

如果发现二级索引页面的PAGE_MAX_TRX_ID太新，或者二级索引中的记录被删除标记，则InnoDB可能需要使用聚集索引来查找记录。