來源：https://juejin.im/post/5e670f0151882549274a65ef

前言

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*? FROMorder_today? WHEREpay_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*? FROMorder_today?FORCE?INDEX?(idx_pay_suc_time) WHEREpay_success_time?<=?'2020-03-08?00:00:00';

執行過程

總結

使用insert into tablA select * from tableB語句時，一定要確保tableB后面的where，order或者其他條件，都需要有對應的索引，來避免出現tableB全部記錄被鎖定的情況。

? ???精 彩 文 章?代碼也浪漫：用Python放一場煙花秀！你們曾經下過哪些成人影片，都被這個站記下了25個常用Matplotlib圖的Python代碼，收藏收藏！超級棒的170+款web前端開發工具匯總，千萬要收藏好！END來和小伙伴們一起向上生長呀~~~掃描下方二維碼，添加小詹微信，可領取千元大禮包并申請加入 Python學習交流群，群內僅供學術交流，日常互動，如果是想發推文、廣告、砍價小程序的敬請繞道！一定記得備注「交流學習」，我會盡快通過好友申請哦！（添加人數較多，請耐心等待） （掃碼回復 1024? 即可領取IT資料包）

總結

以上是生活随笔為你收集整理的同事给我埋了个坑：Insert into select语句把生产服务器炸了的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。