• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL:优化 InnoDB 事务管理

MySQL SEian.G 5个月前 (07-12) 225次浏览 已收录 0个评论

要优化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

MySQL:优化 InnoDB 事务管理


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL:优化 InnoDB 事务管理
喜欢 (3)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址