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

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

2024-04-10 15:30JAVA日知录 Mysql

一切的问题源自对create table as这个语句的不熟悉，这个语句建表导致的表主键、索引、auto_increment的丢失。

一、前言

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

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

(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依旧和上面保持一致，关于无锁变更的描述可见平台的介绍：

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

本以为用无锁变更差不多就能解决问题了，然而执行过程中发现由于数据量比较大，无锁变更分批执行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留下了一个巨坑，有经验的同学可能一眼就看出来了