Mysql-Insert插入過慢的原因記錄和解決方案
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)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關文章
mysql獲取字符串長度函數(shù)(CHAR_LENGTH)
本文介紹一下關于mysql獲取字符串長度的方法,希望此教程對各位同學會有所幫助哦。2013-11-11MySQL中ON DUPLICATE KEY UPDATE語句的使用
INSERT INTO ... ON DUPLICATE KEY UPDATE?是一個強大的SQL語句,它結合了插入新記錄和更新已存在記錄的功能于一體,本文就來介紹一下MySQL中ON DUPLICATE KEY UPDATE語句的使用,感興趣的可以了解一下2024-08-08