mysql8.0插入慢的问题解决方案(一)

mysql8.0的安装就不说了,上网搜索一大堆的教程,再写到博文里面就有点多余了哦
咱们来说说5.6升级到8.0之后插入数据慢的问题
写一个存储过程循环往表里面插入3000条数据
先来看5.6的表现:
1.4s还是可以接受的吧
mysql8.0插入慢的问题解决方案
再来看8.0的表现:
9.07s 比5.6慢了好多好多 难以接受吧
mysql8.0插入慢的问题解决方案
并且我的还报错了
具体错误:

[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比起来还是不行啊 差着好几倍呢
mysql8.0插入慢的问题解决方案

很多人到这就戛然而止了 我相信mysql8.0尽然推出来就不可能比mysql5.6 5.7差!要不人家推8.0干啥?总不能越搞越差吧?
再牛逼的梦想抵不过傻逼似的坚持!死磕到底!


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

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

然后看效果:

mysql8.0插入慢的问题解决方案
是不是感觉好神奇啊!卧槽!优化完竟然比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 协议》,转载必须注明作者和本文链接

胡军

讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!