MYSQL删除大数据表经验总结

in 互联网技术 with 0 comment  访问: 5,144 次

最近线上突然发现一张表每天会产生500w条的数据,一个月下来发现已经接近8000w条的数据,达到90G之大的数据,之前在系统没有升级之前一年才产生100w左右的记录,估计开发的程序或者逻辑出现问题了,不管怎么样,作为运维发生问题,第一时间先以解决问题为第一位,所以这里总结一下删除大表数据的经验。

方法1:重建表

数据较大直接通过where条件进行delete操作肯定不行,加索引的话也不行。MYSQL上delete加low_priorty,quick,ignore估计也帮助不大。

重建表过程如下:

1、把需要保留的数据insert到一张新表

INSERT INTO new_table SELECT * FROM source_table WHERE MODIFIEDTIME > date_sub(now(), interval 30 day);

2、使用RENAME表以原子方式移动原始表,并将副本重命名为原始名称

RENAME TABLE source_table TO source_table_old, new_table TO source_table;

3、删除原表

DROP TABLE source_table_old;

如果按照如上方式,INSERT 操作根据表大小不同,周期会很长以至N小时都完成不了,如果你线上是MYSQL是在线服务的,这种方法就不可取,会造成INSERT漫长时间过程中丢失的数据。

参考:

https://my.oschina.net/zimingforever/blog/91287
https://dev.mysql.com/doc/refman/5.6/en/delete.html

方法2:拆分SQL执行

拆分SQL执行就是把需要删除的数据做在线删除,单独按照条件做删除的话会造成锁表的情况,会导致数据丢失的情况,所以我们可以把需要删除的数据把ID查询出来,然后循环ID列表逐渐删除,操作如下。

1、查询需要删除数据的ID定向到文件中

mysql -u'xxx' -p'ooo' db_name -Bse "select id from source_table" >> /data/delete_id.txt

2、遍历ID文件做删除操作

#!/bin/bash
# Create Date: 2018-03-29 18:51:39
# Last Modified: 2018-03-29 20:06:10
# Author: nock
for id in $(cat /data/delete_id.txt);do 
   mysql -u'xxx' -p'ooo' db_name -Bse "delete from source_table where ID='${id}';"

   if [ $? -eq 0 ];then echo $id >> /data/tag-id.txt ;fi
done

但是delete操作存储数据碎片的问题,MyISAMInnoDB引擎还是有区别的,后面的文章会详细介绍。

方法3:切换主从

主从切换执行过程如下:

1、暂停主从同步

stop slave;

2、删除需要删除的数据

delete from source_table where MODIFIEDTIME < date_sub(now(), interval 30 day);

3、重启开启主从同步

start slave;

4、切换IP
把业务上连接MYSQL的应用程序IP地址都更换,然后按照业务逻辑,按照优先级重启程序。

总结

如上三种方式切换主从是我最推荐的,我线上最终也是决定切换主从来解决大数据表数据删除的问题,因为数据越大方法1、2时间都会较长,而且还有丢失数据的风险。

后面准备针对这个大表做定期数据规定或者写脚本做定期删除操作,但是删除后表优化的情况我们后面讨论。

WeZan