mysql中誤用insert into select實例分析

本篇內容介紹了“mysql中誤用insert into select實例分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

公司的交易量比較大，使用的數據庫是 MySQL，每天的增量差不多在百萬左右，公司并沒有分庫分表，所以想維持這個表的性能只能考慮做數據遷移。

同事李某接到了這個任務，于是他想出了這兩個方案：

• 先通過程序查詢出來，然后插入歷史表，再刪除原表。
  

• 使用 insert into select 讓數據庫 IO 來完成所有操作。

第一個方案使用的時候發現一次性全部加載，系統直接就 OOM 了，但是分批次做就過多 IO 和時間長，于是選用了第二種方案，測試的時候沒有任何問題，開開心心上線，然后被開除。

到底發生了啥？我們復盤一下。

先來看第一個方案，先看偽代碼：

// 1、查詢對應需要遷移的數據
List<Object> list = selectData();

// 2、將數據插入歷史表
insertData(list);

// 3、刪除原表數據
deleteByIds(ids);

再來看看第二個方案，到底發生了啥？

為了維持表的性能，同時保留有效數據，經過商量定了一個量，保留 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 還能用嗎？回答是：當然可以。

“mysql中誤用insert into select實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！