mysql8.0插入慢的問題解決方案(一)

huxiaobai_001發表於2020-07-22
MySql

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 協議》，轉載必須註明作者和本文連結

胡軍

相關文章