最近线上突然发现一张表每天会产生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
操作存储数据碎片的问题,MyISAM
和InnoDB
引擎还是有区别的,后面的文章会详细介绍。
方法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时间都会较长,而且还有丢失数据的风险。
后面准备针对这个大表做定期数据规定或者写脚本做定期删除操作,但是删除后表优化的情况我们后面讨论。
本文由 空心菜 创作,采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
最后编辑时间为: Apr 17, 2018 at 01:06 pm