一、前言

簡單介紹一下出問題的表。

一張元數據表，提取出重點部分，抽象出來的結構如下，

(id, group, code, name，property1, property2, ...)

主鍵primary key：id

唯一鍵unique key：group + code，

也就是說在該group內，code是唯一的。

此外，我們有一個dataworks離線任務，每天會往該表中寫入記錄，采用insert ignore into的方式，如果遇到重復的group+code，就不寫入。

整體邏輯比較清晰明了。數據量級也比較小，每個group大約幾百上千條數據，總數據量不到10w。

二、問題排查和修復過程

2.1 最初的問題

某天用戶反饋線上產品報錯，迅速排查發現，上述表中新接入了一個業務：在dataworks接入了一個新的group（假設名字叫bad_group），同步任務在當天異常往mysql表里導了千萬量級數據（其中實際有效的只有幾千條，其余為臟數據），導致線上產品查詢緩慢、報錯。定位到問題以后，第一反應是把錯誤的bad_group的數據先全部清掉，保留其他group的數據，恢復上線查詢，然后再慢慢想辦法重新導入正確數據。

順帶一提，以下SQL執行等全程都使用彈內DMS平臺進行操作。

2.2 初步思路

清理錯誤數據v1

DELETE FROM MY_TABLE 
WHERE group = 'bad_group';

直接執行上面這個SQL進行普通數據變更可行嗎？顯示不行，有經驗的同學都知道，在千萬量級下，清理大量數據會超過binlog限制，導致SQL無法被執行。

因此我們直接用的是另一個方案，無鎖數據變更，SQL依舊和上面保持一致，關于無鎖變更的描述可見平臺的介紹：

圖片

本以為用無鎖變更差不多就能解決問題了，然而執行過程中發現由于數據量比較大，無鎖變更分批執行SQL效率非常低，估算大概要2h以上來清空這幾千萬的臟數據，不能接受這個方案，執行了幾分鐘果斷放棄。

2.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留下了一個巨坑，有經驗的同學可能一眼就看出來了??????，如果沒有看出來的話，繼續下文。

2.4 表壞了

當天一切正常。然而好景不長，第二天，有同學往表里導數時發現了問題，在沒有指定id的情況下，灌入的所有行id=0。我一臉黑人問號？

id不是默認主鍵嗎，怎么會這樣，重新打開表結構一看，所有的索引都消失了！

此時心里涼了半截，馬上回想到一定是這個語句有問題：

-- 將正常數據復制到臨時表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';

趕緊問了下GPT：

圖片

圖片

果不其然，create table as 只會復制表的列信息結構和數據，不會復制表索引、主鍵等信息。

也就是說，這張表已經被玩壞了！現在回看這個問題，當時至少有兩種方式避免這個問題：

• 不使用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;

• 使用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;

2.5 我覺得還能搶救一下

情況就是這么個情況，只能看看怎么搶救！

主鍵缺失導致插入了許多條id為0的數據，但應用不依賴mysql的自增id，暫時不影響線上應用查詢結果；group+code的unique key缺失導致可能插入了重復數據，但應用側做了去重兜底邏輯。也就是說不幸中的萬幸，產品側暫時無感，趕緊想辦法挽回。

該表同步數據的方式是：如果唯一鍵沖突則忽略，否則就導入成功。新導入的這批數據由于缺失主鍵和唯一鍵，id全部為0且有重復，但其實只有一部分是需要保留的，另一部分需要根據唯一鍵去重。

此時我需要完成兩件事：

1. 保留原有數據的同時，將表的主鍵、唯一鍵和查詢索引進行重建。
2. 將今天新導入的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源頭就做了限制，防止這類事情再次發生。