MYSQL 删除百万级数据

MYSQL 刚建立的数据库的时候,没有考虑数据过多会导致后续查询的问题,也没有建立定期删除的存储过程,等到发现资料过大的时候,直接删除已经不可行了,所以不得不去尝试其他的方式。

数据库情况:每天有50W笔资料进去,现在累计下来已经有9000W多笔数据了,计划只保留最后一个月的数据

1. 最简单 ,也是最不可行的方式

delete from tablename where updatedate<'xxxx';

如果数据库的数据在百万级以内,这一条顶多只是慢一点,但是对待千万级别的数据库,即使你已经把日期栏位列为了索引列,也不要轻易尝试这一条,因为在等待很久很久之后,你就会收到MYSQL的报错,这是本人的血泪史,更不要在数据还在进来的情况下(上班时间),那样五分钟内,就会有连环夺命call找上你。

2. 不按时间检索,按主键ID检索

Select @maxData=max(IDColomn) from tablename where updatedate<'xxxx';
delete from tablename where IDColomn<@maxData;

改成这样的好处是,主键ID栏位是int格式,按它检索肯定比按日期或字符串格式检索快,如果你足够幸运碰到了一台超级强大的硬件配置,应该也是有机会的,只是很慢很慢…,当初在数据还没有到2000W笔的时候,我就是这样做的,想起来了就去删一删,也成功了,不过等到发现9000W笔的时候,这一条就卡死了,毕竟要删除的资料辣么多

3. 按索引分段删除,一步不到位,我就两步,两步不到位,我就三步。。。

Select @minData=min(IDColomn) from tablename;  

也可以用order by updatedate asc的方式去找到最小的那一条,记下那个minData,然后在原有基础上加10W,举例加完的数据为 tmpID

delete from tablename where IDColomn<tmpID; 

选择的这个10W也不一定,我有尝试过10W,30W,50W,100W,它的时间并不是按照这个数据的倍数增加的,看你mysql的承受能力,可以自己尝试,我这边100W的时间是 13min,如果是你选10W的,的确每笔很快,2分钟不到,但你算一下你需要这样运行多少次。。。
在这里插入图片描述
4. 可以建立存储过程,让它自己循环去删,但还是那句话,看你MySQL的承受能力,如果因为它服务器挂了,就很不美好了,毕竟我曾经大半夜在保安的注视下进入公司重启那台服务器,还问我"这么大半夜还进去上班吗?"

CREATE DEFINER=`root`@`%` PROCEDURE `DeleteOverDueData`()
BEGIN
select @minData:=min(IDColomn) from tablename ;
select @maxData:=max(IDColomn) from tablename ;
while minData<maxData do
DELETE from tablename where IDColomn<@maxData limit 400000;  ## 这里的400000 可以自己定,就是每次删除多少笔,跟第三项一样,毕竟不能一次全删
call DeleteOverDueData();
end while;
END

这样建立之后mysql只要调用这个存储过程就好了,但是我写完之后对单条指令做了验证,发现前边增加的两步select语句运行起来其实挺慢, 所以总时间会比手动按方法3更长,我最终放弃了这个方案,选择一下午手动操作来删除,不想亲眼见证数据删除过程的可以试试

**最后忠告.:趁着刚删完数据的数据库没那么大的时候,赶紧建立存储过程,定时触发,不然又要浪费几个周末了,下面附上存储过程最关键的两条指令 **

select @maxNum:=max(IDColomn) from  tablename  where updatedate(DataTime)<date(date_sub(now(),interval 30 day));
DELETE from tablename   where IDColomn<@maxNum;

其实明眼人一看我的这些问题,可能就会提说是不是之前的数据库建立的时候没有分区分表呀,或是时间没有按datetime格式啊,等等吧,但是这种问题解决不了现在的困境啊,分区这些不是没有尝试过,但是因为数据量太大,分区也是直接卡死了,所以才不得不这样就删除资料了,也没有办法去责怪当初建数据库的人员,谁不是在摸索中长大呢

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值