MySQL:优化 InnoDB 事务管理

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

• 默认的MySQL设置AUTOCOMMIT=1 可能会对繁忙的数据库服务器造成性能限制。在可行的情况下，通过发出SET AUTOCOMMIT=0或START TRANSACTION声明，将多个相关的数据更改操作包装到单个事务中 ，然后在进行所有更改后再添加一个 COMMIT语句。
• InnoDB如果该事务对数据库进行了修改，则必须在每次事务提交时将日志刷新到磁盘。在每次更改之后都进行一次提交（与默认的自动提交设置一样）时，存储设备的I / O吞吐量将限制每秒可能进行的操作的数量。
• 对于仅包含一条SELECT语句的事务，打开AUTOCOMMIT有助于 InnoDB识别只读事务并对其进行优化。
• 避免在插入，更新或删除大量行之后执行回滚。如果大事务减慢了服务器性能，则回滚它会使问题变得更糟，执行时间可能是原始数据更改操作的几倍。终止数据库进程无济于事，因为回滚会在服务器启动时再次开始。
• 为了最大程度地减少发生此问题的可能性，请执行以下操作：

1. 增加缓冲池的大小， 以便可以缓存所有数据更改更改，而不是立即将它们写入磁盘。
2. 进行设置， innodb_change_buffering=all 以便除了插入操作外还缓冲更新和删除操作。
3. 考虑COMMIT在大数据更改操作期间定期发布语句，可能将单个删除或更新分解为对较少行数进行操作的多个语句。

要消除发生的回滚，请增加缓冲池，以使回滚成为CPU约束并快速运行，或者终止服务器并重新启动 innodb_force_recovery=3。

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

• 如果可以承受因意外退出而导致的一些最新提交事务的丢失，可以将innodb_flush_log_at_trx_commit 参数设置 为0。InnoDB尽管不能保证刷新，但还是尝试每秒刷新一次日志。
• 修改或删除行时，不会立即删除行和关联的 撤消日志，甚至不会在事务提交后立即删除。保留旧数据，直到更早或同时开始的事务完成为止，以便那些事务可以访问已修改或已删除行的先前状态。因此，长时间运行的事务可以防止InnoDB清除由其他事务更改的数据。
• 如果在长时间运行的事务中修改或删除行，则使用READ COMMITTED和 REPEATABLE READ隔离级别的其他事务 必须读取旧的数据，才能做更多工作来重建较旧的数据。
• 当长时间运行的事务修改表时，来自其他事务的对该表的查询不会使用覆盖索引技术。通常可以从二级索引检索所有结果列，而从表数据中查找适当值的查询。如果发现二级索引页面的索引 PAGE_MAX_TRX_ID太新，或者二级索引中的记录被删除标记，则 InnoDB可能需要使用聚集索引来查找记录。

官方文档链接：https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-transaction-management.html