一、前言

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

一張元數(shù)據(jù)表，提取出重點部分，抽象出來的結(jié)構(gòu)如下，

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

主鍵primary key：id

唯一鍵unique key：group + code，

也就是說在該group內(nèi)，code是唯一的。

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

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

二、問題排查和修復(fù)過程

2.1 最初的問題

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

順帶一提，以下SQL執(zhí)行等全程都使用彈內(nèi)DMS平臺進行操作。

2.2 初步思路

清理錯誤數(shù)據(jù)v1

DELETE FROM MY_TABLE 
WHERE group = 'bad_group';

直接執(zhí)行上面這個SQL進行普通數(shù)據(jù)變更可行嗎？顯示不行，有經(jīng)驗的同學(xué)都知道，在千萬量級下，清理大量數(shù)據(jù)會超過binlog限制，導(dǎo)致SQL無法被執(zhí)行。

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

圖片

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

2.3 另辟蹊徑

于是只能換一種方式。重新考慮這個問題，我們需要保留的數(shù)據(jù)僅僅只有千萬中的不到10萬條非bad_group的數(shù)據(jù)，因此除了刪除bad_group數(shù)據(jù)這種方法，更簡單的是將有效數(shù)據(jù)先copy到一張臨時表中，然后drop原表，再重新創(chuàng)建表，將臨時表中數(shù)據(jù)拷貝回來。為什么drop表會比delete數(shù)據(jù)快呢，這也是一個重要知識點。

舉個不那么恰當(dāng)?shù)睦樱帽确繓|把房子租給別人，到期后發(fā)現(xiàn)房子里全都是垃圾，DELETE語句是將這些垃圾一件一件清理出來，只保留原來干凈的家具。TRUNCATE相當(dāng)于一把火把房子里所有東西都燒了，DROP語句就是房子直接不要了。

這里drop和truncate的方案都可以選擇，我們采用了房子不要了的方案，直接drop表：

清理錯誤數(shù)據(jù)v2

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


-- 刪除原表
DROP TABLE MY_TABLE;


-- 將臨時表重命名為原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;

執(zhí)行成功后，count(*)了一把數(shù)據(jù)量級，發(fā)現(xiàn)確實回到正常水準(zhǔn)，于是問題就那么初步解決了。然而如果問題那么容易就解決了，那就不會記錄在ATA。上面的SQL留下了一個巨坑，有經(jīng)驗的同學(xué)可能一眼就看出來了??????，如果沒有看出來的話，繼續(xù)下文。

2.4 表壞了

當(dāng)天一切正常。然而好景不長，第二天，有同學(xué)往表里導(dǎo)數(shù)時發(fā)現(xiàn)了問題，在沒有指定id的情況下，灌入的所有行id=0。我一臉黑人問號？

id不是默認(rèn)主鍵嗎，怎么會這樣，重新打開表結(jié)構(gòu)一看，所有的索引都消失了！

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

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

趕緊問了下GPT：

圖片

圖片

果不其然，create table as 只會復(fù)制表的列信息結(jié)構(gòu)和數(shù)據(jù)，不會復(fù)制表索引、主鍵等信息。

也就是說，這張表已經(jīng)被玩壞了！現(xiàn)在回看這個問題，當(dāng)時至少有兩種方式避免這個問題：

• 不使用drop語句。使用truncate語句，保留原表結(jié)構(gòu)。

清理錯誤數(shù)據(jù)v3

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


-- 清空原表數(shù)據(jù)，但不刪除表
TRUNCATE TABLE MY_TABLE;


-- 將臨時表數(shù)據(jù)插入到原表
INSERT INTO MY_TABLE SELECT * FROM TEMP_TABLE;

• 使用CREATE TABLE LIKE 語句創(chuàng)建臨時表，復(fù)制原表結(jié)構(gòu)。

清理錯誤數(shù)據(jù)v4

-- 創(chuàng)建和原表結(jié)構(gòu)一樣的臨時表
CREATE TABLE TEMP_TABLE LIKE MY_TABLE;


-- 將正常數(shù)據(jù)復(fù)制到臨時表
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 我覺得還能搶救一下

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

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

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

此時我需要完成兩件事：

1. 保留原有數(shù)據(jù)的同時，將表的主鍵、唯一鍵和查詢索引進行重建。
2. 將今天新導(dǎo)入的id=0的數(shù)據(jù)根據(jù)原唯一鍵的規(guī)則重新導(dǎo)入。

但我們知道，執(zhí)行添加唯一鍵的語句時，會檢查此時表里是否有不滿足唯一的數(shù)據(jù)，如果有的話該語句會被拒絕執(zhí)行。因此這批帶有重復(fù)的新數(shù)據(jù)的干擾，不能直接alter table add unique key。

靈機一動，采取和昨日一樣的臨時表方案，即先將id=0的數(shù)據(jù)復(fù)制到臨時表，刪除原表中所有id=0的數(shù)據(jù)，然后重建索引，再將id=0的數(shù)據(jù)使用insert ignore into語句導(dǎo)回來。對應(yīng)的SQL：

重建表

-- 1.復(fù)制id=0的數(shù)據(jù)到臨時表，
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.導(dǎo)回id=0的新數(shù)據(jù)
INSERT IGNORE INTO MY_TABLE SELECT * FROM TEMP_TABLE;

仔細(xì)思考，這次使用CREATE TABLE AS是沒有問題的，因為這張臨時表并不重要。DELETE由于數(shù)據(jù)量不大也沒有性能問題。出于謹(jǐn)慎，上述4個SQL也是通過4個工單一個個提交執(zhí)行的，便于中間過程觀察。思路清晰，這次應(yīng)該ok！

當(dāng)執(zhí)行完上面第2條語句，刪除id=0的數(shù)據(jù)后，執(zhí)行了select count(*)簡單確認(rèn)了一下，沒想到這一確認(rèn)還真出了問題，delete過后數(shù)據(jù)條數(shù)沒有變？！經(jīng)過緊張的思考??，新機子哇伊自摸一刀子：猜測大概率是主備沒有實時同步。關(guān)于這一點，我們線上用的MYSQL是主庫，工單執(zhí)行的SQL也是在主庫執(zhí)行，但DMS控制臺為了不影響線上正常使用，是在備庫進行查詢，正常情況下主備庫會實時同步。但當(dāng)一些耗時SQL執(zhí)行時，就會出現(xiàn)同步延遲。為了驗證這一點，可以在主庫select count(*)，DMS也提供了切換選項，只是默認(rèn)會選備庫。

圖片

這張截圖是后來我咨詢了DBA后幫忙查詢到的結(jié)果，確實是有延遲。

圖片

繼續(xù)重建索引，包括主鍵primary key、唯一鍵unique key、普通索引key。沒有問題。

最后一步，將id=0的數(shù)據(jù)從臨時表導(dǎo)回原表，就可以回家喂??了，然而工單一直執(zhí)行報錯。

[ERROR] Duplicate entry '0' for key 'PRIMARY'【解決方法】：https://help.aliyun.com/document_detail/198139.html
TraceId : 0b8464d617047224212725080d867f

百思不得其解，按理想情況，重新導(dǎo)回數(shù)據(jù)后，id應(yīng)該是從此刻的最大id開始自增才對（假設(shè)表中有10000條數(shù)據(jù)，那么新插入的數(shù)據(jù)理應(yīng)id=10001），為什么還是0，并且還重復(fù)了？難道是之前的CREATE TABLE AS語句導(dǎo)致auto increment被清為0了？

按照這個思路，回憶起之前在日常環(huán)境寫假數(shù)據(jù)的時候，如果指定了一個比較大的id，那么后續(xù)所有新數(shù)據(jù)都會在這個id基礎(chǔ)上生成（比如當(dāng)前表中只有10條記錄，id=10，插入一條id=100的數(shù)據(jù)，后續(xù)數(shù)據(jù)就會接著id=101繼續(xù)生成。）嘗試過后發(fā)現(xiàn)依舊報錯。

我有點汗流浹背了。

為什么不管用？又用GPT查詢了設(shè)置表auto increment值的方法：

ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001;

然而仍然報這個錯誤。

絕望。

此時已經(jīng)夜里快十點，周圍沒有什么人了，本來空調(diào)澎湃吹動熱氣的聲音也不知不覺趨于安靜，我望向?qū)γ鏄菞潱瑹艄饷鳒缈梢姟Ｒ辉滦『囊雇碛行├洌彝蝗幌肫鹄钋逭盏哪蔷洹袄淅淝迩澹嗥鄳K慘戚戚”，不就在描繪這個場景嗎？

最后的最后，再次對比日常庫的正常表結(jié)構(gòu)，發(fā)現(xiàn)原來是id的auto increment也消失了。原來還是create table as 留下來的坑，難怪之前重新設(shè)置auto increment也不生效。為什么沒有第一時間發(fā)現(xiàn)到這一點，因為按上面gpt的回答，該語句對"列結(jié)構(gòu)"是可以正常復(fù)制的，只有索引、主鍵等信息會丟失，原以為"AUTO_INCREMENT"是屬于id這一列的列信息，看起來并不是。

圖片

重新設(shè)置id使用自增：

MODIFY COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID';

至此問題解決。

三、總結(jié)

一切的問題源自對create table as這個語句的不熟悉，這個語句建表導(dǎo)致的表主鍵、索引、auto_increment的丟失。

不熟悉的SQL不能亂跑??????

后面也在反思在線上使用drop和truncate有些激進。不過當(dāng)時考慮到是內(nèi)部應(yīng)用并且查詢已經(jīng)不可用了。也歡迎讀者同學(xué)們思考和反饋，針對這樣的場景是否有更好處理建議。 

順便說明：后續(xù)我們針對odps導(dǎo)入mysql源頭就做了限制，防止這類事情再次發(fā)生。