一、前言

簡(jiǎn)單介紹一下出問(wèn)題的表。

一張?jiān)獢?shù)據(jù)表，提取出重點(diǎn)部分，抽象出來(lái)的結(jié)構(gòu)如下，

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

主鍵primary key：id

唯一鍵unique key：group + code，

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

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

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

二、問(wèn)題排查和修復(fù)過(guò)程

2.1 最初的問(wèn)題

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

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

2.2 初步思路

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

DELETE FROM MY_TABLE 
WHERE group = 'bad_group';

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

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

圖片

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

2.3 另辟蹊徑

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

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

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

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

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


-- 刪除原表
DROP TABLE MY_TABLE;


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

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

2.4 表壞了

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

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

此時(shí)心里涼了半截，馬上回想到一定是這個(gè)語(yǔ)句有問(wèn)題：

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

趕緊問(wèn)了下GPT：

圖片

圖片

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

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

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

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

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


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


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

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

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

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


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


-- 刪除原表
DROP TABLE MY_TABLE;


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

2.5 我覺(jué)得還能搶救一下

情況就是這么個(gè)情況，只能看看怎么搶救！

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

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

此時(shí)我需要完成兩件事：

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

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

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

重建表

-- 1.復(fù)制id=0的數(shù)據(jù)到臨時(shí)表，
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是沒(méi)有問(wèn)題的，因?yàn)檫@張臨時(shí)表并不重要。DELETE由于數(shù)據(jù)量不大也沒(méi)有性能問(wèn)題。出于謹(jǐn)慎，上述4個(gè)SQL也是通過(guò)4個(gè)工單一個(gè)個(gè)提交執(zhí)行的，便于中間過(guò)程觀察。思路清晰，這次應(yīng)該ok！

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

圖片

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

圖片

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

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

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

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

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

我有點(diǎn)汗流浹背了。

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

ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001;

然而仍然報(bào)這個(gè)錯(cuò)誤。

絕望。

此時(shí)已經(jīng)夜里快十點(diǎn)，周圍沒(méi)有什么人了，本來(lái)空調(diào)澎湃吹動(dòng)熱氣的聲音也不知不覺(jué)趨于安靜，我望向?qū)γ鏄菞?，燈光明滅可?jiàn)。一月小寒的夜晚有些冷，我突然想起李清照的那句“冷冷清清，凄凄慘慘戚戚”，不就在描繪這個(gè)場(chǎng)景嗎？

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

圖片

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

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

至此問(wèn)題解決。

三、總結(jié)

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

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

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

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