因用了Insert into select語句,同事被開除了!

2020-12-23 51CTO

Insert into select 請慎用,同事因為使用了 Insert into select 語句引發了重大生產事故,最後被開除。

某天 xxx 接到一個需求,需要將表 A 的數據遷移到表 B 中去做一個備份。他本想通過程序先查詢查出來然後批量插入,但 xxx 覺得這樣有點慢,需要耗費大量的網絡 I/O,決定採取別的方法進行實現。

通過在某度的海洋裡遨遊,他發現了可以使用 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;

訂單記錄表如下:

CREATETABLE order_record like order_today;

今日訂單表數據如下:

模擬遷移

把 8 號之前的數據都遷移到 order_record 表中去:

INSERTINTO 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:

INSERTINTO order_record SELECT * FROM order_today FORCEINDEX (idx_pay_suc_time)WHERE pay_success_time <= '2020-03-08 00:00:00';

執行過程如下:

總結

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

相關焦點

  • SELECT語句的基本介紹
    SELECT 語句的作用是從資料庫中返回信息一、SELECT基本語句的編寫1.SELECT 語句作用列選擇(投影操作) :能夠使用 SELECT 語句的列選擇功能選擇表中的列,這些列是我們想要用查詢返回 的。當我們查詢時,可在選擇查詢的表中指定的列。
  • 工作中,我們經常用到哪些SQL語句呢?
    工作中我們基本上每天都要與資料庫打交道,資料庫的知識點呢也特別多,全部記住呢也是不可能的,也沒必要把所有的記住(有些語句命令可能我們一輩子都用不到)。所以呢在工作之餘,把工作中經常用到的一些語句整理出來,忘記的時候可以當做字典來查。個人在工作中用Oracle資料庫比較多,就以關係型資料庫Oracle為例進行整理,後面可能會整理一些非關係型資料庫,如mogodb之類的。
  • MySQL聊聊SELECT必須知道的基礎知識
    一、前言select語句可以說是mysql中最常用的語句了,除了select還有insert、delete、update等關鍵詞,這些關鍵詞是mysql的保留詞,我們在定義表名,欄位名,變量名的時候不要使用這些保留詞。select的作用就是從一張表或者多張表中查詢我們想要的數據。這一章我們會講select的基礎知識。
  • 「打聽同事工資」被開除,冤不冤?
    記者採訪發現,有不少公司將「禁止私下交流工資獎金」寫入勞動合同,甚至還有公司讓員工籤訂收入保密協議,明確違反者將被開除。但將薪酬保密寫入勞動合同,甚至以開除作為懲戒來約束員工,顯然有些小題大做,有侵犯勞動者合法權益之嫌。眾所周知,同工同酬是勞動者的基本權利,勞動者對自己的工資高低亦享有知情權。而只有與同事對比之後,員工本人方可知悉自己是否享受到了同工同酬待遇。將打聽同事工資者開除,是變相侵犯勞動者的同工同酬權益。
  • 員工打聽同事工資被開除,法院判令公司支付被開除人賠償金
    前不久,同事間工資應不應該公開的話題引發熱議,有媒體就「你會打聽同事工資嗎」發起網上投票,結果顯示,表示會打聽的人佔多數。打聽同事工資僅僅是同事間茶餘飯後的消遣行為嗎?記者採訪了解到,遠不止於此。有不少公司將「禁止私下交流工資獎金」寫入合同,甚至還有公司讓員工籤訂收入保密協議,明確違反者將被開除。如此一來,打聽同事工資由「會不會」變成了「能不能」。
  • insert鍵在哪裡?insert鍵的功能?
    第一種就是按下了鍵盤上的insert鍵。當您不小心按下了鍵盤上,如下圖的insert鍵的時候,您再打字的話就會覆蓋掉光標後面的已經存在的文字。插入鍵(Insert key,縮寫INS)是電腦鍵盤的一個鍵,主要用於在文字處理器切換文本輸入的模式。一種為覆蓋模式,光標位置新輸入字會替代原來的字;另一種為插入模式,新輸入的字插入到光標位置,原來的字相應後移。
  • 使用explain和show profile來分析SQL語句實現優化SQL語句
    SQL語句優化是建立在慢查詢分析的基礎上,通過慢查詢定位有問題的SQL語句,關於慢查詢的介紹及其分析工具,可以參考[mysql慢查詢及慢查詢日誌分析工具]一、通過explain查詢1 用法:explain sql2 作用:用於分析sql語句(1)、id:執行explain的一個編號(沒有實際意義)(2)、