mysql 定期清理表数据_MySQL冗余数据清理的一些总结

这是学习笔记的第 2278篇文章

今天提前下班了，突然有个开发同学急冲冲找过来说有个业务需求需要紧急支持下。

大体的背景是有一张表中的数据目前存在一些冗余的记录，从业务层面来看这些看起来冗余的数据是某些虚拟福利会被重复领取，所以需要马上做下限制，根据用户的基本属性(比如userid,usercode)进行唯一性标识。

因为这个服务的使用率不是很高，重复领取的情况确实存在，但是频率相对不高，如果活动大量推送后期会有很大的业务损失，所以修复这个潜在问题迫在眉睫。

所以直接的操作就是alter table xxxx add unique key(xxx)

但是显然会失败，因为表中存在冗余数据，需要先完成数据清理的工作才可行。

和开发同学沟通后，发现实际的数据清理需求比想象的要略复杂一些，一方面要按照业务特点删除一些已有的数据，然后才按照冗余数据的写入情况清理，数据表为rc_user_info,数据量不是很大，大概是30万左右，转换为数据操作大体如下：

1)按照业务属性删除部分数据，删除ustatus=2的数据

2)按照字段uuser，ucode组合清理冗余数据，只保留最新的数据记录(字段use_info_id是流水号)即可。

这个操作时间紧，数据质量要求高，而且需要保证整个过程可以追溯和回退，于是我快速设计了如下的操作方式。

在test数据库中复制数据，模拟整个数据清理和创建唯一性索引的完整过程，待验证确认后，在线上环境进行数据清理和变更。

比如样例数据如下:

经过一通清理之后，需要保留的数据仅为1条，即use_info_id=61543的数据记录，其中红色框住的数据是ustatus=2的数据，需要清理，然后按照use_info_id进行排序，取最新的值即可。

我和开发同学讨论后，整理的大体的步骤如下：

第一步：备份 rc_use_info

create table test.rc_use_info like userdb.rc_use_info;

insert into test.rc_use_info select * from userdb.rc_use_info;

第二步：删除 ustatus=2 的记录

delete from test.rc_use_info where ustatus=2;

第三步：删除 ustatus=1 的 (uuser ucode) 存在多条记录的数据，保留一条

1)在此创建了3张临时表，tmp_dup_user是冗余的数据，直接提取max(use_info_id)

create table tmp_dup_user(use_info_id bigint,uuser varchar(100),ucode varchar(100));

insert into test.tmp_dup_user select max(use_info_id),uuser,ucode from test.rc_use_info group by uuser,ucode having count(*)>=2;

3978 rows in set (0.34 sec)

2)tmp_dup_user_uniq1是当前数据中没有重复记录的用户信息

create table tmp_dup_user_uniq1(use_info_id bigint,uuser varchar(100),ucode varchar(100));

insert into test.tmp_dup_user_uniq1 select max(use_info_id),uuser,ucode from test.rc_use_info group by uuser,ucode having count(*)=1;

3)delete_id仅存储需要删除的冗余数据id

create table delete_id (use_info_id bigint primary key);

insert into delete_id select use_info_id from test.rc_use_info where use_info_id not in (select use_info_id from tmp_dup_user_uniq1)

and use_info_id not in (select use_info_id from tmp_dup_user)

4)按照id清理冗余数据

delete from test.rc_use_info where use_info_id in (select use_info_id from delete_id);

5)快速验证，按照预期，输出结果应该是0条

select use_info_id from test.rc_use_info where use_info_id not in (select use_info_id from tmp_dup_user_uniq1)

and use_info_id not in (select use_info_id from tmp_dup_user)

第四步：建立唯一索引 (uuser ucode)

alter table test.rc_use_info add unique key idx_uuser_ucode(uuser,ucode);

第五步：清理线上数据，建立唯一索引 (uuser ucode)

1)正式备份线上数据

create table test.rc_use_info_bak like userdb.rc_use_info;

insert into test.rc_use_info_bak select * from userdb.rc_use_info;

2)清理线上的业务数据

delete from userdb.rc_use_info where ustatus=2;

3)按照delete_id清理冗余数据

delete from userdb.rc_use_info where use_info_id in (select use_info_id from test.delete_id);

4)提前唯一性索引

alter table userdb.rc_use_info add unique key idx_uuser_ucode(uuser,ucode);

整个过程有很多改进之处，相对来说，每一步都可以衡量，而且可以根据整个推演的过程计算出应该变更的数据量，整个过程就像是做一道计算题。

一旦某个业务的数据按照现有逻辑清理存在问题，也可以及时进行追溯和调整。