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

最新推荐文章于 2024-04-08 11:02:11 发布

沈临白

最新推荐文章于 2024-04-08 11:02:11 发布

阅读量3.6k

点赞数 1

文章标签： mysql8更新更慢

本文链接：https://blog.csdn.net/weixin_42122881/article/details/113941793

版权

从 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 协议》，转载必须注明作者和本文链接

胡军