mysql8更新更慢_mysql8.0插入慢的问题解决方案(一) | MySQL 技术论坛

从 MySQL 5.6 升级到 8.0 后,发现插入数据速度显著变慢。文章通过对比 5.6 和 8.0 的插入性能,分析了 8.0 中的 only_full_group_by 错误并提供了解决方案。然后,文章探讨了 8.0 默认开启的 log-bin 功能导致的性能下降,并给出了关闭该功能的方法。通过调整 innodb_flush_log_at_trx_commit 和 sync_binlog 参数,成功优化了插入性能,甚至超越了 MySQL 5.7 的表现。建议在遇到类似问题时,优先考虑调优而不是回退到旧版本。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

mysql8.0 的安装就不说了,上网搜索一大堆的教程,再写到博文里面就有点多余了哦

咱们来说说 5.6 升级到 8.0 之后插入数据慢的问题

写一个存储过程循环往表里面插入 3000 条数据

先来看 5.6 的表现:

1.4s 还是可以接受的吧

212020713_1_20210104104418666

再来看 8.0 的表现:

9.07s 比 5.6 慢了好多好多 难以接受吧

212020713_2_20210104104419416

并且我的还报错了

具体错误:

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

为什么会报这个错误?

only_full_group_by :使用这个就是使用和 oracle 一样的 group 规则,select 的列都要在 group 中,或者本身是聚合列 (SUM,AVG,MAX,MIN) 才行。5.6 版本中没有这约束。更高版本就有这个问题了。去掉就可以了。

执行命令(这是临时的做法重启 mysql 服务之后就失效了 永久的改变你可以去修改 my.cnf 配置文件 懒得去搞了能解决问题就行啊):

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

然后重启 mysqld 服务

这还没到重点,上边只是做了对比和报错的解决,实质性的慢的问题还没解决

插入慢一方面的原因是因为 mysql8.0 一些设置是默认开启的(5.7 是默认关闭的),而这些设置可能会严重影响数据库性能

影响插入效率的因素之一是 8.0 默认开启了 log-bin 功能,导致性能严重下降,在确定不用该功能的前提下只要关闭了该配置即可:

vim /etc/my.cnf#将这句话放进去就完事 或者写 skip-log-bin 作用是一样的看看哪个管用吧disable_log_bin

然后我们再来看 mysql8.0 的插入效果:

提升到了 4.327s 但是效果仍然差强人意 和 5.6 比起来还是不行啊 差着好几倍呢

212020713_3_20210104104419588

很多人到这就戛然而止了 我相信 mysql8.0 尽然推出来就不可能比 mysql5.6 5.7 差!要不人家推 8.0 干啥?总不能越搞越差吧?

再牛逼的梦想抵不过傻逼似的坚持!死磕到底!

我们先来说解决方式再去解释,执行命令:

set global innodb_flush_log_at_trx_commit = 2;set global sync_binlog = 100000;

然后看效果:

212020713_4_20210104104419682

是不是感觉好神奇啊!卧槽!优化完竟然比 5.7 还快?为什么呢?

解释一下:

mysql 这么多年了也经受住了考验,不要怀疑人家,就算是卡也是我们自己的问题,所以调优才是最主要的,甭想着还他妈的用 5.6 吧,调优调优调优,包括 nginx 也是如何进行 nginx 调优 多说几句 你的 nginx 无法抵御洪水攻击你难不成还怪 nginx 性能不行?还是调优调优调优!innodb_flush_log_at_trx_commit 参数

安全性考虑,这个参数默认是 1

innodb_flush_log_at_trx_commit 默认值为 1,可设置为 0、1、2

innodb_flush_log_at_trx_commit 设置为 0,log buffer 将每秒一次地写入 log file 中,并且 log file 的 flush (刷到磁盘) 操作同时进行。该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。

innodb_flush_log_at_trx_commit 设置为 1,每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file,并且 flush (刷到磁盘) 中去。

innodb_flush_log_at_trx_commit 设置为 2,每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file. 但是 flush (刷到磁盘) 操作并不会同时进行。该模式下,MySQL 会每秒执行一次 flush (刷到磁盘) 操作。sync_binlog 参数

sync_binlog 默认值为 1,可设置为 [0,N)

当 sync_binlog =0,像操作系统刷其他文件的机制一样,MySQL 不会同步到磁盘中去而是依赖操作系统来刷新 binary log。

当 sync_binlog =N (N>0) ,MySQL 在每写 N 次 二进制日志 binary log 时,会使用 fdatasync () 函数将它的写二进制日志 binary log 同步到磁盘中去。

综上两条命令搞定:

set global innodb_flush_log_at_trx_commit = 2;set global sync_binlog = 2000;

如果你想让他变回来变的更慢那就:

set global innodb_flush_log_at_trx_commit = 1;set global sync_binlog = 1; 本作品采用《CC 协议》,转载必须注明作者和本文链接

胡军

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值