「點(diǎn)擊
圖片獲取最近兩年爆款好文」
作者：不一樣的科技宅 出處：.im/post/前言
into 請(qǐng)慎用。這天xxx接到一個(gè)需求，需要將表A的數(shù)據(jù)遷移到表B中去做一個(gè)備份。本想通過程序先查詢查出來然后批量插入。但xxx覺得這樣有點(diǎn)慢，需要耗費(fèi)大量的網(wǎng)絡(luò)I/O，決定采取別的方法進(jìn)行實(shí)現(xiàn)。通過在Baidu的海洋里遨游，他發(fā)現(xiàn)了可以使用 into 實(shí)現(xiàn)，這樣就可以避免使用網(wǎng)絡(luò)I/O，直接使用SQL依靠數(shù)據(jù)庫I/O完成，這樣簡(jiǎn)直不要太棒了。然后他就被開除了。
事故發(fā)生的經(jīng)過。由于數(shù)據(jù)數(shù)據(jù)庫中數(shù)據(jù)量過大，當(dāng)時(shí)好像有700W了并且每天在以30W的速度增加。所以上司命令xxx將內(nèi)的部分?jǐn)?shù)據(jù)遷移到中，并將中的數(shù)據(jù)刪除。這樣來降低表中的數(shù)據(jù)量。
由于考慮到會(huì)占用數(shù)據(jù)庫I/O，為了不影響業(yè)務(wù)，計(jì)劃是9:00以后開始遷移，但是xxx在8:00的時(shí)候，嘗試遷移了少部分?jǐn)?shù)據(jù)(1000條)，覺得沒啥問題，就開始考慮大批量遷移。
本以為停止遷移就就可以恢復(fù)了，但是并沒有。后面發(fā)生的你們可以腦補(bǔ)一下。
事故還原
在本地建立一個(gè)精簡(jiǎn)版的數(shù)據(jù)庫，并生成了100w的數(shù)據(jù)。模擬線上發(fā)生的情況。
建立表結(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à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ào)'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
訂單記錄表
CREATE?TABLE?order_record?like?order_today;
今日訂單表數(shù)據(jù)
模擬遷移
把8號(hào)之前的數(shù)據(jù)都遷移到表中去。
INSERT?INTO?order_record?SELECT
????*?
FROM
????order_today?
WHERE
????pay_success_time?
在中運(yùn)行遷移的sql,同時(shí)開另個(gè)一個(gè)窗口插入數(shù)據(jù)，模擬下單。
從上面可以發(fā)現(xiàn)一開始能正常插入，但是后面突然就卡住了，并且耗費(fèi)了23s才成功，然后才能繼續(xù)插入。這個(gè)時(shí)候已經(jīng)遷移成功了，所以能正常插入了。
出現(xiàn)的原因
在默認(rèn)的事務(wù)隔離級(jí)別下： into * from 加鎖規(guī)則是：表鎖sql創(chuàng)建數(shù)據(jù)庫失敗，逐步鎖（掃描一個(gè)鎖一個(gè)）。
分析執(zhí)行過程。
通過觀察遷移sql的執(zhí)行情況你會(huì)發(fā)現(xiàn)是全表掃描，也就意味著在執(zhí)行 into from 語句時(shí)，mysql會(huì)從上到下掃描內(nèi)的記錄并且加鎖，這樣一來不就和直接鎖表是一樣了。
這也就可以解釋，為什么一開始只有少量用戶出現(xiàn)支付失敗，后續(xù)大量用戶出現(xiàn)支付失敗，初始化訂單失敗等情況，因?yàn)橐婚_始只鎖定了少部分?jǐn)?shù)據(jù)，沒有被鎖定的數(shù)據(jù)還是可以正常被修改為正常狀態(tài)。由于鎖定的數(shù)據(jù)越來越多，就導(dǎo)致出現(xiàn)了大量支付失敗。最后全部鎖住，導(dǎo)致無法插入訂單，而出現(xiàn)初始化訂單失敗。
解決方案
由于查詢條件會(huì)導(dǎo)致全表掃描sql創(chuàng)建數(shù)據(jù)庫失敗，什么能避免全表掃描呢，很簡(jiǎn)單嘛，給字段添加一個(gè)索引就可以了，由于走索引查詢，就不會(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í)行過程
總結(jié)
使用 into tablA * from 語句時(shí)，一定要確保后面的where，order或者其他條件，都需要有對(duì)應(yīng)的索引，來避免出現(xiàn)全部記錄被鎖定的情況。
福利時(shí)間
今天民工哥技術(shù)之路聯(lián)合【北京大學(xué)出版社】給大家?guī)怼陡咝阅芟到y(tǒng)架構(gòu)實(shí)戰(zhàn)大全》技術(shù)書籍福利，助力大家更好的學(xué)習(xí)數(shù)據(jù)庫技術(shù)。
本書特點(diǎn)：
1、 深入淺出：詳解與 數(shù)據(jù)庫性能相關(guān)的方方面面，涵蓋 的體系架構(gòu)及其背后的運(yùn)行機(jī)制。
2、直擊難點(diǎn)：全面解析 SQL 執(zhí)行計(jì)劃和 SQL 性能分析與優(yōu)化。
3、全新實(shí)戰(zhàn)：真實(shí)有效的實(shí)戰(zhàn)案例再現(xiàn)數(shù)據(jù)庫開發(fā)過程中的問題及解決思路。
4、雙管齊下：先設(shè)定方案，然后從應(yīng)用角度和數(shù)據(jù)庫角度綜合考慮，逐一分析實(shí)現(xiàn)環(huán)境。
送書規(guī)則：留言說說你的學(xué)習(xí)與排障經(jīng)驗(yàn)，或者你對(duì)數(shù)據(jù)庫的性能有何獨(dú)特的見解，精選留言點(diǎn)贊1-5名讀者各送出一本此書。
截止時(shí)間：2020年4月13日12:30，中獎(jiǎng)的讀者需于8小時(shí)內(nèi)主動(dòng)聯(lián)系小編發(fā)送快遞信息，逾期則視為自動(dòng)放棄獲贈(zèng)資格。
備注：本活動(dòng)僅公眾號(hào)讀者參與，短時(shí)間內(nèi)贊數(shù)爆增或有刷贊行為均視為無效。
最新整理的2TB技術(shù)干貨：包括架構(gòu)師實(shí)戰(zhàn)教程、大數(shù)據(jù)、容器、系統(tǒng)運(yùn)維、數(shù)據(jù)庫、redis、、電子書、Java基礎(chǔ)課程、Java實(shí)戰(zhàn)項(xiàng)目、ELK Stack、機(jī)器學(xué)習(xí)、BAT面試精講視頻等。只需在「民工哥技術(shù)之路」微信公眾號(hào)對(duì)話框回復(fù)關(guān)鍵字：1024即可獲取全部資料。
☆ END ☆