记一次大数据删除优化
需求:
从50亿数据中,删除满足条件的用户的交易数据(物理删除),其中大约是有40亿左右。
背景:
两张表:t_account(账户表,50亿左右数据),t_pay_record(交易记录表,500万左右数据,2022年1月1日前占大概70%)【心里骂了一万遍原来的开发,为啥没做分表】
删除条件:1、用户的账号类型为"1",2、2022年1月1日前的用户的交易数据
SQL优化记录:
第一版SQL:简单暴力,放着就下班了
delete from t_pay_record pr where pr.user_id in ( select id from t_user u where u.register_date < '2022-01-01' and type= '1');
结果:可想而知,第二天上班,服务器瘫了。
第二版SQL:通过代码分批删除
delete from t_pay_record pr where pr.user_id in ( select id from t_user u where u.register_date < '2022-01-01' and type= '1') limit 1000;
结果:能删除数据,但每次执行需要大概十几秒。算下来得跑一个月,还是得继续优化。
第三版SQL:因为符号条件查出来的用户占多数,比对次数偏多
delete from t_pay_record pr where pr.user_id not in ( select id from t_user u where u.register_date < '2022-01-01' and type= '1') limit 1000;
结果:没啥优化。
第四版SQL:先按照下列SQL将最小id出来,这边使用limit 1,然后传入下列SQL。每删除100次,就重新执行查询获取最小id。
delete from t_pay_record pr where id >= #{id} and pr.user_id not in ( select id from t_user u where u.register_date < '2022-01-01' and type= '1') order by id limit 1000;
结果:查询需要十几秒,之后100次删除基本30秒左右执行完。
结束语:
大家有更好的优化方式,也可以提出来哈。