一张元数据表,提取出重点部分,抽象出来的结构如下,
(id, group, code, name,property1, property2, ...)
id | group | code | name | property |
1 | 业务1 | 事件1 | 吃冻干 | |
2 | 业务1 | 事件2 | 喂猫粮 | |
3 | 业务2 | 事件1 | 睡觉 | |
4 | 业务3 | 事件10086 | 下班 | |
... | ... | ... | ... |
主键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数据快呢,这也是一个重要知识点。
DROP | TRUNCATE | DELETE | |
删除内容 | 删除整张表数据,表结构以及表的索引、约束和触发器 | 删除全部数据 | 删除部分数据(可带where条件) |
语句类型 | DDL | DDL | DML |
效率 | 最高 | 较高 | 较低 |
回滚 | 无法回滚 | 无法回滚 | 可以回滚 |
自增值 | - | 重置 | 不重置 |
举个不那么恰当的例子,好比房东把房子租给别人,到期后发现房子里全都是垃圾,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 | group | code | name | property |
1 | 业务1 | 事件1 | 吃冻干 | |
2 | 业务1 | 事件2 | 喂猫粮 | |
3 | 业务2 | 事件1 | 睡觉 | |
4 | 业务3 | 事件10086 | 下班 | |
... | ... | ... | ... | |
0(新导入) | 业务1(重复数据) | 事件1(重复数据) | 吃冻干 | |
0(新导入) | 业务1(重复数据) | 事件2(重复数据) | 喂猫粮 | |
0(新导入) | 业务1 | 事件3 | 吃罐头 | |
... | ... | ... | ... |
主键缺失导致插入了许多条id为0的数据,但应用不依赖mysql的自增id,暂时不影响线上应用查询结果;group+code的unique key缺失导致可能插入了重复数据,但应用侧做了去重兜底逻辑。也就是说不幸中的万幸,产品侧暂时无感,赶紧想办法挽回。
该表同步数据的方式是:如果唯一键冲突则忽略,否则就导入成功。新导入的这批数据由于缺失主键和唯一键,id全部为0且有重复,但其实只有一部分是需要保留的,另一部分需要根据唯一键去重。
此时我需要完成两件事:
- 保留原有数据的同时,将表的主键、唯一键和查询索引进行重建。
- 将今天新导入的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源头就做了限制,防止这类事情再次发生。