因用了Insert into select語句，同事被開除了！

作者：不一樣的科技宅

https://juejin.im/post/5e670f0151882549274a65ef

某天 xxx 接到一個需求，需要將表 A 的數(shù)據(jù)遷移到表 B 中去做一個備份。他本想通過程序先查詢查出來然后批量插入，但 xxx 覺得這樣有點慢，需要耗費大量的網(wǎng)絡(luò) I/O，決定采取別的方法進行實現(xiàn)。

通過在某度的海洋里遨游，他發(fā)現(xiàn)了可以使用 insert into select 實現(xiàn)，這樣就可以避免使用網(wǎng)絡(luò) I/O，直接使用 SQL 依靠數(shù)據(jù)庫 I/O 完成，這樣簡直不要太棒，然后他就被開除了。

由于數(shù)據(jù)數(shù)據(jù)庫中 order_today 數(shù)據(jù)量過大，當(dāng)時好像有 700W 了，并且每天在以 30W 的速度增加。

所以上司命令 xxx 將 order_today 內(nèi)的部分數(shù)據(jù)遷移到 order_record 中，并將 order_today 中的數(shù)據(jù)刪除，這樣來降低 order_today 表中的數(shù)據(jù)量。

由于考慮到會占用數(shù)據(jù)庫 I/O，為了不影響業(yè)務(wù)，計劃是 9：00 以后開始遷移，但是 xxx 在 8：00 的時候，嘗試遷移了少部分數(shù)據(jù)（1000 條），覺得沒啥問題，就開始考慮大批量遷移。

在本地建立一個精簡版的數(shù)據(jù)庫，并生成了 100w 的數(shù)據(jù)。模擬線上發(fā)生的情況。

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 '支付狀態(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)建時間',
`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;

INSERT INTO order_record SELECT
    * 
FROM
    order_today 
WHERE
    pay_success_time < '2020-03-08 00:00:00';

從上面可以發(fā)現(xiàn)一開始能正常插入，但是后面突然就卡住了，并且耗費了 23s 才成功，然后才能繼續(xù)插入。這個時候已經(jīng)遷移成功了，所以能正常插入了。

在默認的事務(wù)隔離級別下：insert into order_record select * from order_today 加鎖規(guī)則是：order_record 表鎖，order_today 逐步鎖（掃描一個鎖一個）。

通過觀察遷移 SQL 的執(zhí)行情況你會發(fā)現(xiàn) order_today 是全表掃描，也就意味著在執(zhí)行 insert into select from 語句時，MySQL 會從上到下掃描 order_today 內(nèi)的記錄并且加鎖，這樣一來不就和直接鎖表是一樣了。

這也就可以解釋，為什么一開始只有少量用戶出現(xiàn)支付失敗，后續(xù)大量用戶出現(xiàn)支付失敗，初始化訂單失敗等情況，因為一開始只鎖定了少部分數(shù)據(jù)，沒有被鎖定的數(shù)據(jù)還是可以正常被修改為正常狀態(tài)。

由于鎖定的數(shù)據(jù)越來越多，就導(dǎo)致出現(xiàn)了大量支付失敗。最后全部鎖住，導(dǎo)致無法插入訂單，而出現(xiàn)初始化訂單失敗。

由于查詢條件會導(dǎo)致 order_today 全表掃描，什么能避免全表掃描呢，很簡單嘛，給 pay_success_time 字段添加一個 idx_pay_suc_time 索引就可以了。

由于走索引查詢，就不會出現(xiàn)掃描全表的情況而鎖表了，只會鎖定符合條件的記錄。

最終的 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 或者其他條件，都需要有對應(yīng)的索引，來避免出現(xiàn) tableB 全部記錄被鎖定的情況。