破防了，谁懂啊家人们：记一次MySQL问题排查

一、前言

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

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

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