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);
整个过程有很多改进之处,相对来说,每一步都可以衡量,而且可以根据整个推演的过程计算出应该变更的数据量,整个过程就像是做一道计算题。
一旦某个业务的数据按照现有逻辑清理存在问题,也可以及时进行追溯和调整。
在争辩中获胜的唯一秘诀是不要争辩人生在世,如果想以争辩来制止争辩,最终不能制止争辩,只有忍耐才能制止争辩,此法最可尊贵。当争辩开始时,双方考虑的是自己的面子自己的观点自己的利益,根本没有兴趣听对方说什么,无论争辩
真爱你的男人,不管城府有多深,也会有这四个表现城府,新华字典里的解释是,比喻令人难以揣测的深远用心。有的人说现在人心不古,一个男人没有点城府怎么在社会上混啊。所以,城府也是人的一种功力,内敛而不外露的功力。一个有城府的男人,会
一个女人越来越美的6个好习惯一个女人最宝贵的财富不是青春貌美,而是内心强大一个女人最大的幸福不是嫁入豪门,而是内心有座优雅丰富的殿堂生活不易,做人艰难,做女人更是。真正优秀的女人,不仅要有能力,更要有能量。一
无论是夫妻还是情人,如果从不问你这五件事,说明他心里没有你无论是夫妻还是情人,如果对方从不问你这五件事,说明他心里根本没有你。女人对待爱情的态度,往往都会通过自己第六感去决定的,不会理智去选择伴侣。当女人真的爱了的时候会付出自己的全部,但
聊天这样结尾的人,一定很爱你最近有一句话火了我回他消息是秒回,他回我消息是轮回判断一个人爱不爱你的细节除了愿意为你花钱愿意花时间陪你还有他和你说话的方式愿意留有余地跟你聊天的人肯定是愿意跟你长久地交往如果谈恋
男人最爱的,永远是这种女人看过一句话回首来路三千,一晃数载流年。才知我若是游子,你便是人间。回不去的从前,看不清的未来,退不出,进不得,心若游子,无处可依。的确,人生在世,有太多的茫然无措,尤其在面对感情时
不管跟谁交往,都要记住这15条忠告1hr没有爽快答应,就是委婉拒绝找人帮忙时,如果对方没有爽快答应,实际上就是不愿意,只是不好意思直接拒绝,或者还没想好拒绝你的借口罢了。这个时候,干脆点离开,日后还是好朋友。如果非
深爱一个人,却无法做夫妻,往往是这几个原因听闻爱情,十有九悲。在这尘世间,有情人终成眷属,是每个人都渴望的结局。然而,失望和遗憾却是感情里的大多数,有多少深情,最后只能止于唇齿,掩于岁月。正所谓,缘在天定,在一起终究要讲缘
凡是让你不舒服的关系,都是错的匆匆一生,我们会遇见数不清的人。命运决定了哪些人会出现在我们的生命里,而我们自己则决定了哪些人会停留在我们的生命中。一段关系能否维持得长久,标准很简单,只需要两个字舒服。一段舒适的
男人想和你发生关系,会用三句话套路你,女人一认真就输了跟异性交往,在没有确定关系之前,女人要小心男人这几个套路。不管男人已婚还是单身,如果他们想骗你,会用三句话套路你,女人一认真就输了。01我带你去个地方吃饭男人跟你说这句话,都会选择
时间面前,一切终将释怀时光荏苒,岁月匆匆,弹指一挥间,人生已过半。回首过往,有快乐和满足,也有悲伤和事与愿违。曾经以为,有些人,永远不会原谅有些遗憾,永远不会释怀。不曾想,在时间的推移下,很多事情,不知