點擊上方?web項目開發，選擇?設為星標
優質文章，及時送達
--Insert into select請慎用。這天xxx接到一個需求，需要將表A的數據遷移到表B中去做一個備份。本想通過程序先查詢查出來然后批量插入。但xxx覺得這樣有點慢，需要耗費大量的網絡I/O，決定采取別的方法進行實現。通過在Baidu的海洋里遨游，她發現了可以使用insert into select實現，這樣就可以避免使用網絡I/O，直接使用SQL依靠數據庫I/O完成，這樣簡直不要太棒了。然后她就被開除了。
事故發生的經過。
由于數據數據庫中order_today數據量過大，當時好像有700W了并且每天在以30W的速度增加。所以上司命令xxx將order_today內的部分數據遷移到order_record中，并將order_today中的數據刪除。這樣來降低order_today表中的數據量。
由于考慮到會占用數據庫I/O，為了不影響業務，計劃是9:00以后開始遷移，但是xxx在8:00的時候，嘗試遷移了少部分數據(1000條)，覺得沒啥問題，就開始考慮大批量遷移。
在遷移的過程中，應急群是先反應有小部分用戶出現支付失敗，隨后反應大批用戶出現支付失敗的情況，以及初始化訂單失敗的情況，同時騰訊也開始報警。
然后xxx就慌了，立即停止了遷移。
本以為停止遷移就就可以恢復了，但是并沒有。后面發生的你們可以腦補一下。
# 事故還原
在本地建立一個精簡版的數據庫，并生成了100w的數據。模擬線上發生的情況。
# 建立表結構
訂單表
CREATE TABLE `order_today` (`id` varchar(32) NOT NULL COMMENT '主鍵',`merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商戶編號',`amount` decimal(15,2) NOT NULL COMMENT '訂單金額',`pay_success_time` datetime NOT NULL COMMENT '支付成功時間',`order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付狀態 S：支付成功、F：訂單支付失敗',`remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '備注',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間 -- 修改時自動更新', PRIMARY KEY (`id`) USING BTREE,KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商戶編號') ENGINE=InnoDB DEFAULT CHARSET=utf8;訂單記錄表
CREATE TABLE order_record like order_today;今日訂單表數據
#?模擬遷移
把8號之前的數據都遷移到order_record表中去。
INSERT INTO order_record SELECT * FROM order_today WHERE pay_success_time < '2020-03-08 00:00:00';在navicat中運行遷移的sql,同時開另個一個窗口插入數據，模擬下單。
從上面可以發現一開始能正常插入，但是后面突然就卡住了，并且耗費了23s才成功，然后才能繼續插入。這個時候已經遷移成功了，所以能正常插入了。
# 出現的原因
在默認的事務隔離級別下：insert into order_record select * from order_today 加鎖規則是：order_record表鎖，order_today逐步鎖(掃描一個鎖一個)。
分析執行過程。
通過觀察遷移sql的執行情況你會發現order_today是全表掃描，也就意味著在執行insert into select from 語句時，mysql會從上到下掃描order_today內的記錄并且加鎖，這樣一來不就和直接鎖表是一樣了。
這也就可以解釋，為什么一開始只有少量用戶出現支付失敗，后續大量用戶出現支付失敗，初始化訂單失敗等情況，因為一開始只鎖定了少部分數據，沒有被鎖定的數據還是可以正常被修改為正常狀態。由于鎖定的數據越來越多，就導致出現了大量支付失敗。最后全部鎖住，導致無法插入訂單，而出現初始化訂單失敗。
# 解決方案
由于查詢條件會導致order_today全表掃描，什么能避免全表掃描呢，很簡單嘛，給pay_success_time字段添加一個idx_pay_suc_time索引就可以了，由于走索引查詢，就不會出現掃描全表的情況而鎖表了，只會鎖定符合條件的記錄。
最終的sql
INSERT INTO order_record SELECT * FROM order_today FORCE INDEX (idx_pay_suc_time)WHERE pay_success_time <= '2020-03-08 00:00:00';執行過程
# 總結
使用insert into tablA select * from tableB語句時，一定要確保tableB后面的where，order或者其他條件，都需要有對應的索引，來避免出現tableB全部記錄被鎖定的情況。
# 參考
https://blog.csdn.net/asdfsadfasdfsa/article/details/83030011
作者：不一樣的科技宅來源：juejin.im/post/5e670f0151882549274a65ef
--完--
推薦案例
Springboot+Vue前后端分離實現Excle文件導入并在前端頁面回顯功能
Springboot+Vue實現從數據庫中獲取數據生成樹狀圖在前端頁面展示功能
Springboot+Vue實現從數據庫中獲取數據生成餅狀圖并在前端頁面展示功能
Springboot+Vue實現批量文件上傳(pdf、word、excel)并支持在線預覽功能
Springboot+Vue實現滑動驗證成功后登錄功能
Springboot+Vue實現從數據庫中獲取數據生成折線圖并在前端頁面展示功能
Springboot＋Vue實現網頁內容生成圖片功能
Springboot＋Vue實現信息批量修改功能
查看全部案例...
溫暖提示
為了方便大家更好的學習，本公眾號經常分享一些完整的單個功能案例代碼給大家去練習，如果本公眾號沒有你要學習的功能案例，你可以聯系小編(微信：xxf960513)提供你的小需求給我，我安排我們這邊的開發團隊免費幫你完成你的案例。注意：只能提單個功能的需求不能要求功能太多，比如要求用什么技術，有幾個頁面，頁面要求怎么樣？請長按識別二維碼
想學習更多的java功能案例請關注
Java項目開發
如果你覺得這個案例以及我們的分享思路不錯，對你有幫助，請分享給身邊更多需要學習的朋友。別忘了《留言+點在看》給作者一個鼓勵哦！
