血一般的教訓(xùn)，請慎用insert into select
血一般的教訓(xùn)，請慎用 insert into select。同事應(yīng)用之后，導(dǎo)致公司損失了近 10w 元，最終被公司開除。
事情的起因
公司的交易量比較大，使用的數(shù)據(jù)庫是 MySQL，每天的增量差不多在百萬左右，公司并沒有分庫分表，所以想維持這個(gè)表的性能只能考慮做數(shù)據(jù)遷移。
同事李某接到了這個(gè)任務(wù)，于是他想出了這兩個(gè)方案：
先通過程序查詢出來，然后插入歷史表，再刪除原表。
使用 insert into select 讓數(shù)據(jù)庫 IO 來完成所有操作。
第一個(gè)方案使用的時(shí)候發(fā)現(xiàn)一次性全部加載，系統(tǒng)直接就 OOM 了，但是分批次做就過多 IO 和時(shí)間長，于是選用了第二種方案，測試的時(shí)候沒有任何問題，開開心心上線，然后被開除。
到底發(fā)生了啥？我們復(fù)盤一下。
先來看第一個(gè)方案，先看偽代碼：
// 1、查詢對應(yīng)需要遷移的數(shù)據(jù)
List<Object> list = selectData();
// 2、將數(shù)據(jù)插入歷史表
insertData(list);
// 3、刪除原表數(shù)據(jù)
deleteByIds(ids);
再來看看第二個(gè)方案，到底發(fā)生了啥？
為了維持表的性能，同時(shí)保留有效數(shù)據(jù)，經(jīng)過商量定了一個(gè)量，保留 10 天的數(shù)據(jù)，差不多要在表里面保留 1kw 的數(shù)據(jù)。
所以同事就做了一個(gè)時(shí)間篩選的操作，直接 insert into select ... dateTime < (Ten days ago)...
爽極了，直接就避免了要去分頁查詢數(shù)據(jù)，這樣就不存在 OOM 啦。還簡化了很多的代碼操作，減少了網(wǎng)絡(luò)問題。
為了測試，還特意建了 1kw 的數(shù)據(jù)來模擬，測試環(huán)境當(dāng)然是沒有問題啦，順利通過。
考慮到這個(gè)表是一個(gè)支付流水表，于是將這個(gè)任務(wù)做成定時(shí)任務(wù)，并且定在晚上 8 點(diǎn)執(zhí)行。
晚上量也不是很大，自然是沒有什么問題，但是第二天公司財(cái)務(wù)上班，開始對賬，發(fā)現(xiàn)資金對不上，很多流水都沒有入庫。
最終排查發(fā)現(xiàn)晚上 8 點(diǎn)之后，陸陸續(xù)續(xù)開始出現(xiàn)支付流水插入失敗的問題，很多數(shù)據(jù)因此丟失。
最終定位到了是遷移任務(wù)引起的問題，剛開始還不明所以，白天沒有問題，然后想到晚上出現(xiàn)這樣的情況可能是晚上的任務(wù)出現(xiàn)了影響，最后停掉該任務(wù)的第二次上線，發(fā)現(xiàn)沒有了這樣的情況。
復(fù)盤
問題在哪里？為什么停掉遷移的任務(wù)之后就好了呢？這個(gè) insert into select 操作到底做了什么？
我們來看看這個(gè)語句的 explain：
如果全表掃描，我們這個(gè)表這么大，是不是意味著遷移的時(shí)間會(huì)很長？假若我們這個(gè)遷移時(shí)間為一個(gè)小時(shí)，那是不是意味著就解釋了我們白天沒有出現(xiàn)這樣問題的原因了。但是全表掃描是最根本的原因嗎？
我們不妨試試，一邊遷移，一邊做些的操作，還原現(xiàn)場。最終還是會(huì)出現(xiàn)這樣的問題。
這個(gè)時(shí)候，我們可以調(diào)整一下，大膽假設(shè)，如果不全表掃描，是不是就不會(huì)出現(xiàn)這樣的問題。當(dāng)我們將條件修改之后，果然發(fā)現(xiàn)沒有走了全表掃描了。
得出結(jié)論：全表掃描導(dǎo)致了這次事故的發(fā)生。這樣做就解決了發(fā)生的問題，但是做為陸陸續(xù)續(xù)開始失敗這個(gè)就不好解釋了。
原因
在默認(rèn)的事務(wù)隔離級別下：insert into a select b 的操作 a 表示直接鎖表，b 表是逐條加鎖。這也就解釋了為什么出現(xiàn)陸續(xù)的失敗的原因。
在逐條加鎖的時(shí)候，流水表由于多數(shù)是復(fù)合記錄，所以最終部分在掃描的時(shí)候被鎖定，部分拿不到鎖，最終導(dǎo)致超時(shí)或者直接失敗，還有一些在這加鎖的過成功成功了。
為什么測試沒有問題？
在測試的時(shí)候充分的使用了正式環(huán)境的數(shù)據(jù)來測試，但是別忽視一個(gè)問題，那就是測試環(huán)境畢竟是測試環(huán)境，在測試的時(shí)候，數(shù)據(jù)量真實(shí)并不代表就是真實(shí)的業(yè)務(wù)場景。
比方說，這個(gè)情況里面就少了一個(gè)遷移的時(shí)候，大量數(shù)據(jù)的插入這樣的情況。最終導(dǎo)致線上 Bug。
解決辦法
既然我們避免全表掃描就可以解決，我們避免它就行了。想要避免全表掃描，對 where 后面的條件做索引，讓我們的 select 查詢都走索引即可。
insert into 還能用嗎？回答是：當(dāng)然可以。
總結(jié)
使用 insert into select 的時(shí)候請慎重，一定要做好索引。
編輯：陶家龍
出處：https://juejin.cn/post/6931890118538199048
你們要的最強(qiáng) Nginx 的學(xué)習(xí)手冊 ( 建議收藏 )
程序員過關(guān)斬將--領(lǐng)導(dǎo)說我的類的職責(zé)不單一
面試官扎心一問：數(shù)據(jù)量很大，分頁查詢很慢，有什么優(yōu)化方案？