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

作者：温粥

一、前言

谁懂啊家人们，作为一名 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 留下了一个巨坑，有经验的同学可能一眼就看出来了