一、前言
誰懂啊家人們，作為一名java開發，原來以為MySQL這東西，寫寫CRUD，不是有手就行嗎；你說DDL啊，不就是設計個表結構，搞幾個索引嗎。
鍵盤撒一把凍幹，我家貓也能來上班。——粥師傅
結果一次線上出問題了，一環接一環，不僅貓上不了班，晚上還得等我加班回家，眼巴巴吃不到凍幹。
（你看我錘不錘你就完事了）
1.表結構
簡單介紹一下出問題的表。
一張中繼資料表，提取出重點部分，抽象出來的結構如下，
(id, group, code, name，property1, property2, ...)
主鍵primary key：id
唯一鍵unique key：group + code，
也就是說在該group内，code是唯一的。
此外，我們有一個dataworks離線任務，每天會往該表中寫入記錄，采用insert ignore into的方式，如果遇到重複的group+code，就不寫入。
整體邏輯比較清晰明了。資料量級也比較小，每個group大約幾百上千條資料，總資料量不到10w。
二、問題排查和修複過程
1.最初的問題
某天使用者回報線上産品報錯，迅速排查發現，上述表中新接入了一個業務：在dataworks接入了一個新的group（假設名字叫bad_group），同步任務在當天異常往mysql表裡導了千萬量級資料（其中實際有效的隻有幾千條，其餘為髒資料），導緻線上産品查詢緩慢、報錯。定位到問題以後，第一反應是把錯誤的bad_group的資料先全部清掉，保留其他group的資料，恢複上線查詢，然後再慢慢想辦法重新導入正确資料。
順帶一提，以下SQL執行等全程都使用彈内DMS平台進行操作。
2.初步思路
清理錯誤資料v1
DELETE FROM MY_TABLE
WHERE group = 'bad_group'; 直接執行上面這個SQL進行普通資料變更可行嗎？顯示不行，有經驗的同學都知道，在千萬量級下，清理大量資料會超過binlog限制，導緻SQL無法被執行。
是以我們直接用的是另一個方案，無鎖資料變更，SQL依舊和上面保持一緻，關于無鎖變更的描述可見平台的介紹：
本以為用無鎖變更差不多就能解決問題了，然而執行過程中發現由于資料量比較大，無鎖變更分批執行SQL效率非常低，估算大概要2h以上來清空這幾千萬的髒資料，不能接受這個方案，執行了幾分鐘果斷放棄。
3.另辟蹊徑
于是隻能換一種方式。重新考慮這個問題，我們需要保留的資料僅僅隻有千萬中的不到10萬條非bad_group的資料，是以除了删除bad_group資料這種方法，更簡單的是将有效資料先copy到一張臨時表中，然後drop原表，再重新建立表，将臨時表中資料拷貝回來。為什麼drop表會比delete資料快呢，這也是一個重要知識點。
舉個不那麼恰當的例子，好比房東把房子租給别人，到期後發現房子裡全都是垃圾，DELETE語句是将這些垃圾一件一件清理出來，隻保留原來幹淨的家具。TRUNCATE相當于一把火把房子裡所有東西都燒了，DROP語句就是房子直接不要了。
這裡drop和truncate的方案都可以選擇，我們采用了房子不要了的方案，直接drop表：
清理錯誤資料v2
-- 将正常資料複制到臨時表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
-- 删除原表
DROP TABLE MY_TABLE;
-- 将臨時表重命名為原表
RENAME TABLE TEMP_TABLE TO MY_TABLE; 執行成功後，count(*)了一把資料量級，發現确實回到正常水準，于是問題就那麼初步解決了。然而如果問題那麼容易就解決了，那就不會記錄在ATA。上面的SQL留下了一個巨坑，有經驗的同學可能一眼就看出來了，如果沒有看出來的話，繼續下文。
4.表壞了
當天一切正常。然而好景不長，第二天，有同學往表裡導數時發現了問題，在沒有指定id的情況下，灌入的所有行id=0。我一臉黑人問号？
id不是預設主鍵嗎，怎麼會這樣，重新打開表結構一看，所有的索引都消失了！
此時心裡涼了半截，馬上回想到一定是這個語句有問題：
-- 将正常資料複制到臨時表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group'; 趕緊問了下GPT：
果不其然，create table as 隻會複制表的列資訊結構和資料，不會複制表索引、主鍵等資訊。
也就是說，這張表已經被玩壞了！現在回看這個問題，當時至少有兩種方式避免這個問題：
1）不使用drop語句。使用truncate語句，保留原表結構。
清理錯誤資料v3
-- 将正常資料複制到臨時表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
-- 清空原表資料，但不删除表
TRUNCATE TABLE MY_TABLE;
-- 将臨時表資料插入到原表
INSERT INTO MY_TABLE SELECT * FROM TEMP_TABLE; 2）使用CREATE TABLE LIKE 語句建立臨時表，複制原表結構。
清理錯誤資料v4
-- 建立和原表結構一樣的臨時表
CREATE TABLE TEMP_TABLE LIKE MY_TABLE;
-- 将正常資料複制到臨時表
INSERT INTO TEMP_TABLE SELECT * FROM MY_TABLE WHERE group <> 'bad_group';
-- 删除原表
DROP TABLE MY_TABLE;
-- 将臨時表重命名為原表
RENAME TABLE TEMP_TABLE TO MY_TABLE; 5.我覺得還能搶救一下
情況就是這麼個情況，隻能看看怎麼搶救！
主鍵缺失導緻插入了許多條id為0的資料，但應用不依賴mysql的自增id，暫時不影響線上應用查詢結果；group+code的unique key缺失導緻可能插入了重複資料，但應用側做了去重兜底邏輯。也就是說不幸中的萬幸，産品側暫時無感，趕緊想辦法挽回。
該表同步資料的方式是：如果唯一鍵沖突則忽略，否則就導入成功。新導入的這批資料由于缺失主鍵和唯一鍵，id全部為0且有重複，但其實隻有一部分是需要保留的，另一部分需要根據唯一鍵去重。
此時我需要完成兩件事：
- 保留原有資料的同時，将表的主鍵、唯一鍵和查詢索引進行重建。
- 将今天新導入的id=0的資料根據原唯一鍵的規則重新導入。
但我們知道，執行添加唯一鍵的語句時，會檢查此時表裡是否有不滿足唯一的資料，如果有的話該語句會被拒絕執行。是以這批帶有重複的新資料的幹擾，不能直接alter table add unique key。
靈機一動，采取和昨日一樣的臨時表方案，即先将id=0的資料複制到臨時表，删除原表中所有id=0的資料，然後重建索引，再将id=0的資料使用insert ignore into語句導回來。對應的SQL：
重建表
-- 1.複制id=0的資料到臨時表，
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE id = 0;
-- 2.删除源表中id=0的記錄
DELETE FROM MY_TABLE WHERE id = 0;
-- 3.重建索引
ALTER TABLE MY_TABLE ADD INDEX ...;
-- 4.導回id=0的新資料
INSERT IGNORE INTO MY_TABLE SELECT * FROM TEMP_TABLE; 仔細思考，這次使用CREATE TABLE AS是沒有問題的，因為這張臨時表并不重要。DELETE由于資料量不大也沒有性能問題。出于謹慎，上述4個SQL也是通過4個工單一個個送出執行的，便于中間過程觀察。思路清晰，這次應該ok！
當執行完上面第2條語句，删除id=0的資料後，執行了select count(*)簡單确認了一下，沒想到這一确認還真出了問題，delete過後資料條數沒有變？！經過緊張的思考，新機子哇伊自摸一刀子：猜測大機率是主備沒有實時同步。關于這一點，我們線上用的MYSQL是主庫，工單執行的SQL也是在主庫執行，但DMS控制台為了不影響線上正常使用，是在備庫進行查詢，正常情況下主備庫會實時同步。但當一些耗時SQL執行時，就會出現同步延遲。為了驗證這一點，可以在主庫select count(*)，DMS也提供了切換選項，隻是預設會選備庫。
這張截圖是後來我咨詢了DBA後幫忙查詢到的結果，确實是有延遲。
繼續重建索引，包括主鍵primary key、唯一鍵unique key、普通索引key。沒有問題。
最後一步，将id=0的資料從臨時表導回原表，就可以回家喂貓了，然而工單一直執行報錯。
[ERROR] Duplicate entry '0' for key 'PRIMARY'【解決方法】：https://help.aliyun.com/document_detail/198139.html
TraceId : 0b8464d617047224212725080d867f 百思不得其解，按理想情況，重新導回資料後，id應該是從此刻的最大id開始自增才對（假設表中有10000條資料，那麼新插入的資料理應id=10001），為什麼還是0，并且還重複了？難道是之前的CREATE TABLE AS語句導緻auto increment被清為0了？
按照這個思路，回憶起之前在日常環境寫假資料的時候，如果指定了一個比較大的id，那麼後續所有新資料都會在這個id基礎上生成（比如目前表中隻有10條記錄，id=10，插入一條id=100的資料，後續資料就會接着id=101繼續生成。）嘗試過後發現依舊報錯。
我有點汗流浃背了。
為什麼不管用？又用GPT查詢了設定表auto increment值的方法：
ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001; 然而仍然報這個錯誤。
絕望。
此時已經夜裡快十點，周圍沒有什麼人了，本來空調澎湃吹動熱氣的聲音也不知不覺趨于安靜，我望向對面樓棟，燈光明滅可見。一月小寒的夜晚有些冷，我突然想起李清照的那句“冷冷清清，凄凄慘慘戚戚”，不就在描繪這個場景嗎？
最後的最後，再次對比日常庫的正常表結構，發現原來是id的auto increment也消失了。原來還是create table as 留下來的坑，難怪之前重新設定auto increment也不生效。為什麼沒有第一時間發現到這一點，因為按上面gpt的回答，該語句對"列結構"是可以正常複制的，隻有索引、主鍵等資訊會丢失，原以為"AUTO_INCREMENT"是屬于id這一列的列資訊，看起來并不是。
重新設定id使用自增：
MODIFY COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID'; 至此問題解決。
三、總結
一切的問題源自對create table as這個語句的不熟悉，這個語句建表導緻的表主鍵、索引、auto_increment的丢失。
不熟悉的SQL不能亂跑。
後面也在反思線上上使用drop和truncate有些激進。不過當時考慮到是内部應用并且查詢已經不可用了。也歡迎讀者同學們思考和回報，針對這樣的場景是否有更好處理建議。
順便說明：後續我們針對odps導入mysql源頭就做了限制，防止這類事情再次發生。
作者丨溫粥
來源丨公衆号：阿裡技術（ID：gh_438062fa21b1）
dbaplus社群歡迎廣大技術人員投稿，投稿郵箱：[email protected]