Mysql-Insert插入過慢的原因記錄和解決方案
Mysql Insert插入過慢的原因
場景
先來說下業(yè)務(wù)場景, A單位每天下發(fā)全量數(shù)據(jù)5萬數(shù)據(jù)左右,我們需要將這5萬條數(shù)據(jù)處理到主表中以及日志表中
我們現(xiàn)在的邏輯是,先將數(shù)據(jù)每次100條批量修改或者插入到主表中, 然后我們通過事件監(jiān)聽將每次插入或者修改的動作都記錄到日志表中
問題: 因為我們插入日志表的時候是每次處理一條這樣就導(dǎo)致了5萬條日志數(shù)據(jù),一個一個插入非常耗時,抱著能提升一點就提升一點的想法,我們看看插入還能優(yōu)化嗎?
先來說一下: Mysql中單條語句原子性的,所以高并發(fā)情況下不會有安全性問題
說說插入會慢的影響因素
- Mysql插入緩存(bulk_insert_buffer_size 默認(rèn)是8M)
- 每條數(shù)據(jù)在插入的時候都要驗證是否違反表中的約束條件(主鍵,唯一等)
- 構(gòu)建新數(shù)據(jù)的索引
- innodb中,數(shù)據(jù)通常都是先寫緩存,再寫事務(wù)日志,再寫入數(shù)據(jù)文件
- Mysql 每秒最大支持16384并發(fā) ,但是默認(rèn)只有151可以設(shè)置max_connections數(shù)量
- 服務(wù)器性能,cpu,硬盤,網(wǎng)絡(luò), 網(wǎng)絡(luò)數(shù)據(jù)傳輸 ,代碼解析
- 數(shù)據(jù)庫事務(wù)影響
解決辦法
先設(shè)置mysql的插入緩存bulk_insert_buffer_size=100M,確保一次能插入1000條數(shù)據(jù)
然后使用List方式記錄需要插入的數(shù)據(jù)當(dāng)達(dá)到1000條之后我們批量一次性插入
insert into tablename values('xxx','xxx'),('yyy','yyy'),('zzz','zzz')......
然后在清空List ,插入的過程中我們可以異步,不用等待結(jié)果的返回 ,通過上面的方案, 插入100萬數(shù)據(jù)幾十秒就完事了
為什么要這樣解決呢?
因為批量插入其實就是訪問一次數(shù)據(jù)量告訴數(shù)據(jù)庫我需要執(zhí)行這1000條的插入,而不是訪問1000次數(shù)據(jù)庫告訴數(shù)據(jù)庫我要執(zhí)行1條的插入所以使用批量插入大大的縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗 , 所以我們在上面的方案中使用一次提交批量數(shù)據(jù)的更新的語句,那么還有沒其他的方式呢?
MySQL 的事務(wù)自動提交模式默認(rèn)是開啟的,其對 MySQL 的性能也有一定得影響。
也就是說如果你執(zhí)行1000 條插入語句,MySQL 就會提交 1000 次事物,這大大影響了插入數(shù)據(jù)的速度。
我們還可以在代碼中,手動開啟事物也就是相當(dāng)于希望事物內(nèi)執(zhí)行的語句要作為一個“不可分割”的整體去執(zhí)行的任務(wù)當(dāng)所有任務(wù)完畢后在提交事物,當(dāng)然這樣是沒有批量插入快的但是也能節(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.表中有過多索引,當(dāng)數(shù)據(jù)insert時,索引會重排產(chǎn)生太多的io操作。導(dǎo)致緩慢,有必然要的只保留主鍵。
3.表的數(shù)據(jù)庫引擎,默認(rèn)InnerDB,若數(shù)據(jù)不重要,可以使用MyISAM
4.注意字段text使用,內(nèi)存過大會引起表膨脹,影響插入和查詢效率
總結(jié)
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
MySQL 億級數(shù)據(jù)導(dǎo)入導(dǎo)出及遷移筆記
上周被安排做了一個MySQL億級數(shù)據(jù)的遷移,特此記錄一下,導(dǎo)入和導(dǎo)出是兩個過程,本文想詳細(xì)的介紹一下具體的使用,感興趣的小伙伴們可以參考一下2021-06-06mysql獲取字符串長度函數(shù)(CHAR_LENGTH)
本文介紹一下關(guān)于mysql獲取字符串長度的方法,希望此教程對各位同學(xué)會有所幫助哦。2013-11-11MySQL中ON DUPLICATE KEY UPDATE語句的使用
INSERT INTO ... ON DUPLICATE KEY UPDATE?是一個強大的SQL語句,它結(jié)合了插入新記錄和更新已存在記錄的功能于一體,本文就來介紹一下MySQL中ON DUPLICATE KEY UPDATE語句的使用,感興趣的可以了解一下2024-08-08