作者：温粥

一、前言

谁懂啊家人们，作为一名 java 开发，原来以为 mysql 这东西，写写 CRUD，不是有手就行吗；你说 DDL 啊，不就是设计个表结构，搞几个索引吗。

键盘撒一把冻干，我家猫也能来上班。——粥师傅

结果一次线上出问题了，一环接一环，不仅猫上不了班，晚上还得等我加班回家，眼巴巴吃不到冻干。

1.1 表结构

简单介绍一下出问题的表。

一张元数据表，提取出重点部分，抽象出来的结构如下，

(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 只会复制表的列信息结构和数据，不会复制表索引、主键等信息。

也就是说，这张表已经被玩坏了！现在回看这个问题，当时至少有两种方式避免这个问题，

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;

1. 使用 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 源头就做了限制，防止这类事情再次发生。