Mysql-Insert插入過(guò)慢的原因記錄和解決方案

更新時(shí)間：2023年08月30日 15:34:11 作者：胡安民

這篇文章主要介紹了Mysql-Insert插入過(guò)慢的原因記錄和解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

Mysql Insert插入過(guò)慢的原因

場(chǎng)景

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

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

問(wèn)題: 因?yàn)槲覀儾迦肴罩颈淼臅r(shí)候是每次處理一條這樣就導(dǎo)致了5萬(wàn)條日志數(shù)據(jù)，一個(gè)一個(gè)插入非常耗時(shí)，抱著能提升一點(diǎn)就提升一點(diǎn)的想法，我們看看插入還能優(yōu)化嗎?

先來(lái)說(shuō)一下: Mysql中單條語(yǔ)句原子性的，所以高并發(fā)情況下不會(huì)有安全性問(wèn)題

說(shuō)說(shuō)插入會(huì)慢的影響因素

Mysql插入緩存(bulk_insert_buffer_size 默認(rèn)是8M)
每條數(shù)據(jù)在插入的時(shí)候都要驗(yàn)證是否違反表中的約束條件(主鍵,唯一等)
構(gòu)建新數(shù)據(jù)的索引
innodb中，數(shù)據(jù)通常都是先寫(xiě)緩存，再寫(xiě)事務(wù)日志，再寫(xiě)入數(shù)據(jù)文件
Mysql 每秒最大支持16384并發(fā) ,但是默認(rèn)只有151可以設(shè)置max_connections數(shù)量
服務(wù)器性能,cpu,硬盤(pán),網(wǎng)絡(luò), 網(wǎng)絡(luò)數(shù)據(jù)傳輸 ,代碼解析
數(shù)據(jù)庫(kù)事務(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 ，插入的過(guò)程中我們可以異步，不用等待結(jié)果的返回，通過(guò)上面的方案，插入100萬(wàn)數(shù)據(jù)幾十秒就完事了

為什么要這樣解決呢？

因?yàn)榕坎迦肫鋵?shí)就是訪問(wèn)一次數(shù)據(jù)量告訴數(shù)據(jù)庫(kù)我需要執(zhí)行這1000條的插入，而不是訪問(wèn)1000次數(shù)據(jù)庫(kù)告訴數(shù)據(jù)庫(kù)我要執(zhí)行1條的插入所以使用批量插入大大的縮減客戶端與數(shù)據(jù)庫(kù)之間的連接、關(guān)閉等消耗，所以我們?cè)谏厦娴姆桨钢惺褂靡淮翁峤慌繑?shù)據(jù)的更新的語(yǔ)句，那么還有沒(méi)其他的方式呢？

MySQL 的事務(wù)自動(dòng)提交模式默認(rèn)是開(kāi)啟的，其對(duì) MySQL 的性能也有一定得影響。

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

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