前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL优化 InnoDB 事务管理

MySQL优化 InnoDB 事务管理

作者头像
SEian.G
发布2021-07-29 14:26:21
5150
发布2021-07-29 14:26:21
举报
文章被收录于专栏:SEian.G学习记录

今天我们来聊聊如何优化InnoDB事务管理?

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

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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-07-13,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA的辛酸事儿 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档