Mysql Insert插入過慢的原因

場景

先來說下業(yè)務場景， A單位每天下發(fā)全量數(shù)據(jù)5萬數(shù)據(jù)左右，我們需要將這5萬條數(shù)據(jù)處理到主表中以及日志表中

我們現(xiàn)在的邏輯是，先將數(shù)據(jù)每次100條批量修改或者插入到主表中， 然后我們通過事件監(jiān)聽將每次插入或者修改的動作都記錄到日志表中

問題: 因為我們插入日志表的時候是每次處理一條這樣就導致了5萬條日志數(shù)據(jù)，一個一個插入非常耗時，抱著能提升一點就提升一點的想法，我們看看插入還能優(yōu)化嗎?

先來說一下: Mysql中單條語句原子性的，所以高并發(fā)情況下不會有安全性問題

說說插入會慢的影響因素

• Mysql插入緩存(bulk_insert_buffer_size 默認是8M)
• 每條數(shù)據(jù)在插入的時候都要驗證是否違反表中的約束條件(主鍵,唯一等)
• 構建新數(shù)據(jù)的索引
• innodb中，數(shù)據(jù)通常都是先寫緩存，再寫事務日志，再寫入數(shù)據(jù)文件
• Mysql 每秒最大支持16384并發(fā) ,但是默認只有151可以設置max_connections數(shù)量
• 服務器性能,cpu,硬盤,網(wǎng)絡, 網(wǎng)絡數(shù)據(jù)傳輸 ,代碼解析
• 數(shù)據(jù)庫事務影響

解決辦法

先設置mysql的插入緩存bulk_insert_buffer_size=100M，確保一次能插入1000條數(shù)據(jù)

然后使用List方式記錄需要插入的數(shù)據(jù)當達到1000條之后我們批量一次性插入

insert into tablename values('xxx','xxx'),('yyy','yyy'),('zzz','zzz')...... 

然后在清空List ，插入的過程中我們可以異步，不用等待結果的返回 ，通過上面的方案， 插入100萬數(shù)據(jù)幾十秒就完事了

為什么要這樣解決呢？

因為批量插入其實就是訪問一次數(shù)據(jù)量告訴數(shù)據(jù)庫我需要執(zhí)行這1000條的插入，而不是訪問1000次數(shù)據(jù)庫告訴數(shù)據(jù)庫我要執(zhí)行1條的插入所以使用批量插入大大的縮減客戶端與數(shù)據(jù)庫之間的連接、關閉等消耗 ， 所以我們在上面的方案中使用一次提交批量數(shù)據(jù)的更新的語句，那么還有沒其他的方式呢？

MySQL 的事務自動提交模式默認是開啟的，其對 MySQL 的性能也有一定得影響。

也就是說如果你執(zhí)行1000 條插入語句，MySQL 就會提交 1000 次事物，這大大影響了插入數(shù)據(jù)的速度。

我們還可以在代碼中，手動開啟事物也就是相當于希望事物內執(zhí)行的語句要作為一個“不可分割”的整體去執(zhí)行的任務當所有任務完畢后在提交事物，當然這樣是沒有批量插入快的但是也能節(jié)約不少時間的

MySQL數(shù)據(jù)庫Insert語句慢SQL處理

問題描述

insert into …普通的插入語句，經(jīng)常出現(xiàn)耗時2s以上

數(shù)據(jù)狀態(tài)

1.表數(shù)據(jù)量大，每天產(chǎn)生200萬條數(shù)據(jù)

2.高并發(fā)插入

問題解決

1.由于表中數(shù)據(jù)量龐大，建議數(shù)據(jù)歸檔處理，冷熱處理

2.表中有過多索引，當數(shù)據(jù)insert時，索引會重排產(chǎn)生太多的io操作。導致緩慢，有必然要的只保留主鍵。

3.表的數(shù)據(jù)庫引擎，默認InnerDB，若數(shù)據(jù)不重要，可以使用MyISAM

4.注意字段text使用，內存過大會引起表膨脹，影響插入和查詢效率

總結

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論