作者:xlecho
連結:https://juejin.cn/post/6931890118538199048
血一般的教訓,請慎用 insert into select。同事應用之後,導致公司損失了近10w元,最終被公司開除。
事情的起因
公司的交易量比較大,使用的資料庫是mysql,每天的增量差不多在百萬左右,公司並沒有分庫分表,所以想維持這個表的效能只能考慮做資料遷移。
同事李某接到了這個任務,於是他想出了這兩個方案
- 先透過程式查詢出來,然後插入歷史表,再刪除原表
- 使用insert into select讓資料庫IO來完成所有操作
第一個方案使用的時候發現一次性全部載入,系統直接就OOM了,但是分批次做就過多io和時間長,於是選用了第二種方案,測試的時候沒有任何問題,開開心心上線,然後被開除。
推薦一個開源免費的 Spring Boot 實戰專案:
https://github.com/javastacks/spring-boot-best-practice
到底發生了啥,我們覆盤一下
先來看第一個方案,先看虛擬碼
// 1、查詢對應需要遷移的資料
List<Object> list = selectData();
// 2、將資料插入歷史表
insertData(list);
// 3、刪除原表資料
deleteByIds(ids);
我們可以從這段程式碼中看到,OOM的原因很簡單,我們直接將資料全部載入記憶體,記憶體不爆才怪。
再來看看第二個方案,到底發生了啥。
為了維持表的效能,同時保留有效資料,經過商量定了一個量,保留10天的資料,差不多要在表裡面保留1kw的資料。所以同事就做了一個時間篩選的操作,直接insert into select ... dateTime < (Ten days ago),爽極了,直接就避免了要去分頁查詢資料,這樣就不存在OOM啦。還簡化了很多的程式碼操作,減少了網路問題。
為了測試,還特意建了1kw的資料來模擬,測試環境當然是沒有問題啦,順利透過。考慮到這個表是一個支付流水錶,於是將這個任務做成定時任務,並且定在晚上8點執行。
晚上量也不是很大,自然是沒有什麼問題,但是第二天公司財務上班,開始對賬,發現資金對不上,很多流水都沒有入庫。最終排查發現晚上8點之後,陸陸續續開始出現支付流水插入失敗的問題,很多資料因此丟失。最終定位到了是遷移任務引起的問題,剛開始還不明所以,白天沒有問題,然後想到晚上出現這樣的情況可能是晚上的任務出現了影響,最後停掉該任務的第二次上線,發現沒有了這樣的情況。
覆盤
問題在哪裡?
為什麼停掉遷移的任務之後就好了呢?這個insert into select操作到底做了什麼?我們來看看這個語句的explain。
我們不難從圖中看出,這個查詢語句直接走了全表掃描。這個時候,我們不難猜想到一點點問題。如果全表掃描,我們這個表這麼大,是不是意味著遷移的時間會很長?假若我們這個遷移時間為一個小時,那是不是意味著就解釋了我們白天沒有出現這樣問題的原因了。但是全表掃描是最根本的原因嗎?
我們不妨試試,一邊遷移,一邊做些的操作,還原現場。最終還是會出現這樣的問題。這個時候,我們可以調整一下,大膽假設,如果不全表掃描,是不是就不會出現這樣的問題。當我們將條件修改之後,果然發現沒有走了全表掃描了。
最終再次還原現場,問題解決了
得出結論:全表掃描導致了這次事故的發生。
這樣做就解決了發生的問題,但是做為陸陸續續開始失敗這個就不好解釋了。
原因
在預設的事務隔離級別下:insert into a select b的操作a表示直接鎖表,b表是逐條加鎖。這也就解釋了為什麼出現陸續的失敗的原因。在逐條加鎖的時候,流水錶由於多數是複合記錄,所以最終部分在掃描的時候被鎖定,部分拿不到鎖,最終導致超時或者直接失敗,還有一些在這加鎖的過成功成功了。
為什麼測試沒有問題?
在測試的時候充分的使用了正式環境的資料來測試,但是別忽視一個問題,那就是測試環境畢竟是測試環境,在測試的時候,資料量真實並不代表就是真實的業務場景。比方說,這個情況裡面就少了一個遷移的時候,大量資料的插入這樣的情況。最終導致線上bug
解決辦法
既然我們避免全表掃描就可以解決,我們避免它就行了。想要避免全表掃描,對where後面的條件做索引,讓我們的select查詢都走索引即可
insert into還能用嗎?
可以
總結
使用insert into select的時候請慎重,一定要做好索引。
更多文章推薦:
1.Spring Boot 3.x 教程,太全了!
2.2,000+ 道 Java面試題及答案整理(2024最新版)
3.免費獲取 IDEA 啟用碼的 7 種方式(2024最新版)
覺得不錯,別忘了隨手點贊+轉發哦!