因用了Insert into select語(yǔ)句，同事被開(kāi)除了！
某天 xxx 接到一個(gè)需求，需要將表 A 的數(shù)據(jù)遷移到表 B 中去做一個(gè)備份。他本想通過(guò)程序先查詢(xún)查出來(lái)然后批量插入，但 xxx 覺(jué)得這樣有點(diǎn)慢，需要耗費(fèi)大量的網(wǎng)絡(luò) I/O，決定采取別的方法進(jìn)行實(shí)現(xiàn)。
通過(guò)在某度的海洋里遨游，他發(fā)現(xiàn)了可以使用 insert into select 實(shí)現(xiàn)，這樣就可以避免使用網(wǎng)絡(luò) I/O，直接使用 SQL 依靠數(shù)據(jù)庫(kù) I/O 完成，這樣簡(jiǎn)直不要太棒，然后他就被開(kāi)除了。
事故發(fā)生的經(jīng)過(guò)
由于數(shù)據(jù)數(shù)據(jù)庫(kù)中 order_today 數(shù)據(jù)量過(guò)大，當(dāng)時(shí)好像有 700W 了，并且每天在以 30W 的速度增加。
所以上司命令 xxx 將 order_today 內(nèi)的部分?jǐn)?shù)據(jù)遷移到 order_record 中，并將 order_today 中的數(shù)據(jù)刪除，這樣來(lái)降低 order_today 表中的數(shù)據(jù)量。
由于考慮到會(huì)占用數(shù)據(jù)庫(kù) I/O，為了不影響業(yè)務(wù)，計(jì)劃是 9：00 以后開(kāi)始遷移，但是 xxx 在 8：00 的時(shí)候，嘗試遷移了少部分?jǐn)?shù)據(jù)(1000 條)，覺(jué)得沒(méi)啥問(wèn)題，就開(kāi)始考慮大批量遷移。
在遷移的過(guò)程中，應(yīng)急群是先反應(yīng)有小部分用戶(hù)出現(xiàn)支付失敗，隨后反應(yīng)大批用戶(hù)出現(xiàn)支付失敗的情況，以及初始化訂單失敗的情況，同時(shí)騰訊也開(kāi)始報(bào)警。
然后 xxx 就慌了，立即停止了遷移。本以為停止遷移就就可以恢復(fù)了，但是并沒(méi)有。
后面發(fā)生的你們可以腦補(bǔ)一下，當(dāng)時(shí)整個(gè)支付系統(tǒng)癱瘓了快一個(gè)小時(shí)，客服電話(huà)都被打爆。
事故還原
在本地建立一個(gè)精簡(jiǎn)版的數(shù)據(jù)庫(kù)，并生成了 100w 的數(shù)據(jù)。模擬線(xiàn)上發(fā)生的情況。
1. 建立表結(jié)構(gòu)
訂單表如下：
- CREATE TABLE `order_today` (
- `id` varchar(32) NOT NULL COMMENT '主鍵',
- `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商戶(hù)編號(hào)',
- `amount` decimal(15,2) NOT NULL COMMENT '訂單金額',
- `pay_success_time` datetime NOT NULL COMMENT '支付成功時(shí)間',
- `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付狀態(tài) S：支付成功、F：訂單支付失敗',
- `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '備注',
- `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
- `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間 -- 修改時(shí)自動(dòng)更新',
- PRIMARY KEY (`id`) USING BTREE,
- KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商戶(hù)編號(hào)'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
訂單記錄表如下：
- CREATE TABLE order_record like order_today;
今日訂單表數(shù)據(jù)如下：
2. 模擬遷移
把 8 號(hào)之前的數(shù)據(jù)都遷移到 order_record 表中去：
- INSERT INTO order_record SELECT
- *
- FROM
- order_today
- WHERE
- pay_success_time < '2020-03-08 00:00:00';
在 Navicat 中運(yùn)行遷移的 SQL，同時(shí)開(kāi)另個(gè)一個(gè)窗口插入數(shù)據(jù)，模擬下單：
從上面可以發(fā)現(xiàn)一開(kāi)始能正常插入，但是后面突然就卡住了，并且耗費(fèi)了 23s 才成功，然后才能繼續(xù)插入。這個(gè)時(shí)候已經(jīng)遷移成功了，所以能正常插入了。
出現(xiàn)的原因
在默認(rèn)的事務(wù)隔離級(jí)別下：insert into order_record select * from order_today 加鎖規(guī)則是：order_record 表鎖，order_today 逐步鎖(掃描一個(gè)鎖一個(gè))。
分析執(zhí)行過(guò)程：
通過(guò)觀察遷移 SQL 的執(zhí)行情況你會(huì)發(fā)現(xiàn) order_today 是全表掃描，也就意味著在執(zhí)行 insert into select from 語(yǔ)句時(shí)，MySQL 會(huì)從上到下掃描 order_today 內(nèi)的記錄并且加鎖，這樣一來(lái)不就和直接鎖表是一樣了。
這也就可以解釋?zhuān)瑸槭裁匆婚_(kāi)始只有少量用戶(hù)出現(xiàn)支付失敗，后續(xù)大量用戶(hù)出現(xiàn)支付失敗，初始化訂單失敗等情況，因?yàn)橐婚_(kāi)始只鎖定了少部分?jǐn)?shù)據(jù)，沒(méi)有被鎖定的數(shù)據(jù)還是可以正常被修改為正常狀態(tài)。
由于鎖定的數(shù)據(jù)越來(lái)越多，就導(dǎo)致出現(xiàn)了大量支付失敗。最后全部鎖住，導(dǎo)致無(wú)法插入訂單，而出現(xiàn)初始化訂單失敗。
解決方案
由于查詢(xún)條件會(huì)導(dǎo)致 order_today 全表掃描，什么能避免全表掃描呢，很簡(jiǎn)單嘛，給 pay_success_time 字段添加一個(gè) idx_pay_suc_time 索引就可以了。
由于走索引查詢(xún)，就不會(huì)出現(xiàn)掃描全表的情況而鎖表了，只會(huì)鎖定符合條件的記錄。
最終的 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';
執(zhí)行過(guò)程如下：
總結(jié)
使用 insert into tablA select * from tableB 語(yǔ)句時(shí)，一定要確保 tableB 后面的 where，order 或者其他條件，都需要有對(duì)應(yīng)的索引，來(lái)避免出現(xiàn) tableB 全部記錄被鎖定的情況。
作者：不一樣的科技宅
編輯：陶家龍
出處：https://juejin.im/post/5e670f0151882549274a65ef
