要优化InnoDB
事务处理,请在事务功能的性能开销和服务器的工作负载之间找到理想的平衡点。例如,如果应用程序每秒提交数千次,则可能会遇到性能问题;如果它仅每 2-3 小时提交一次,则可能会遇到不同的性能问题。
默认的 MySQL 设置
AUTOCOMMIT=1
会对繁忙的数据库服务器施加性能限制。在可行的情况下,将几个相关的数据更改操作包装到一个事务中,方法是发布SET AUTOCOMMIT=0
或START TRANSACTION
声明,然后COMMIT
在进行所有更改后发布声明。InnoDB
如果事务对数据库进行了修改,则必须在每次事务提交时将日志刷新到磁盘。当每次更改后都进行提交时(与默认的自动提交设置一样),存储设备的 I/O 吞吐量会限制每秒的潜在操作数。或者,对于仅包含单个
SELECT
语句的事务,打开AUTOCOMMIT
有助于InnoDB
识别只读事务并优化它们。有关要求,请参阅 第 8.5.3 节,“优化 InnoDB 只读事务”。避免在插入、更新或删除大量行后执行回滚。如果一个大事务正在降低服务器性能,将其回滚会使问题变得更糟,执行时间可能是原始数据更改操作的数倍。终止数据库进程没有帮助,因为回滚在服务器启动时再次开始。
为了尽量减少发生此问题的可能性:
增加 缓冲池的大小,这样所有的数据变化变化都可以缓存起来而不是立即写入磁盘。
设置
innodb_change_buffering=all
以便除了插入之外还缓冲更新和删除操作。考虑
COMMIT
在大数据更改操作期间定期发布语句,可能会将单个删除或更新分解为多个对较少行数进行操作的语句。
要在失控回滚发生后摆脱它,请增加缓冲池以使回滚受 CPU 限制并快速运行,或者终止服务器并重新启动
innodb_force_recovery=3
,如第 14.18.2 节“InnoDB 恢复”中所述。默认设置下预计不会出现此问题,默认设置
innodb_change_buffering=all
允许将更新和删除操作缓存在内存中,从而使它们首先执行得更快,并且在需要时回滚也更快。确保在处理具有大量插入、更新或删除的长时间运行的事务的服务器上使用此参数设置。如果您可以承受意外退出时丢失一些最新提交的事务,则可以将
innodb_flush_log_at_trx_commit
参数设置为 0。InnoDB
尽管不能保证一定会刷新,但无论如何都会尝试每秒刷新一次日志。此外,将 的值设置innodb_support_xa
为 0,这样可以减少由于在磁盘数据和二进制日志上同步而导致的磁盘刷新次数。当行被修改或删除时,行和关联的 撤消日志不会立即物理删除,甚至不会在事务提交后立即删除。旧数据会一直保留到较早开始或同时开始的事务完成,以便这些事务可以访问修改或删除行的先前状态。因此,长时间运行的事务可以防止
InnoDB
清除由不同事务更改的数据。当在长时间运行的事务中修改或删除行时,如果其他使用
READ COMMITTED
和REPEATABLE READ
隔离级别的事务读取这些相同的行,则它们必须做更多的工作来重建旧数据。当一个长时间运行的事务修改一个表时,其他事务对该表的查询不使用覆盖索引技术。通常可以从二级索引中检索所有结果列的查询,而不是从表数据中查找适当的值。
如果发现二级索引页有一个
PAGE_MAX_TRX_ID
太新的,或者如果二级索引中的记录被删除标记,InnoDB
可能需要使用聚集索引来查找记录。