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 _use_info _use_info;
insert _use_info select * _use_info;
第⼆步：删除 ustatus=2 的记录
delete _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 p_dup_user select max(use_info_id),uuser,ucode _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 p_dup_user_uniq1 select max(use_info_id),uuser,ucode _use_info group by uuser,ucode having count(*)=1;
mysql删除重复的数据保留一条3)delete_id仅存储需要删除的冗余数据id
create table delete_id (use_info_id bigint primary key);
insert into delete_id select use_info_id _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 _use_info where use_info_id in (select use_info_id from delete_id);
5)快速验证，按照预期，输出结果应该是0条
select use_info_id _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 _use_info add unique key idx_uuser_ucode(uuser,ucode);
第五步：清理线上数据，建⽴唯⼀索引 (uuser ucode)
1)正式备份线上数据
create _use_info_bak _use_info;
insert _use_info_bak select * _use_info;
2)清理线上的业务数据
delete _use_info where ustatus=2;
3)按照delete_id清理冗余数据
delete _use_info where use_info_id in (select use_info_id from test.delete_id);
4)提前唯⼀性索引
alter _use_info add unique key idx_uuser_ucode(uuser,ucode);
整个过程有很多改进之处，相对来说，每⼀步都可以衡量，⽽且可以根据整个推演的过程计算出应该变更的数据量，整个过程就像是做⼀道计算题。
⼀旦某个业务的数据按照现有逻辑清理存在问题，也可以及时进⾏追溯和调整。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。