MySQL表历史数据清理总结 | 您所在的位置:网站首页 › mysql清理数据库根据什么清理最快 › MySQL表历史数据清理总结 |
最近工作中在处理线上几个数据表的清理工作,工作虽然简单,但操作起来注意的地方还是挺多的。现将整个过程进行简单的总结,以便作为日后的参考。 首先确定下我们清理数据的期望: 期望清理过程不能对线上业务产生影响,最多只能对线上产生微小、短暂的影响 期望清理过程最好可复用,整个过程在下次需要清理数据时可以重复使用 期望能够在监控下进行,如果影响在可控范围内,希望能在任意时刻进行对于待清理的历史数据可分为两类,一类是确认没有实际价值的数据,这部分数据可以直接删除,采用定时器的方式定时清理就可以了;另一类的是未来可能有价值的数据,这部分数据一般不会直接删除,而是定时将历史业务数据的归档。 针对第一类数据,定时清理比较简单,只需要在低峰期定时按条件删除即可 delimiter $$ CREATE EVENT IF NOT EXISTS event_records_interval_clear ON SCHEDULE EVERY 1 DAY STARTS '2022-06-10 01:05:00' DO BEGIN DECLARE v_time BIGINT(20) DEFAULT 0; SET v_time = (SELECT UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 15 DAY)) * 1000); DELETE FROM t_record WHERE create_time < v_time; END $$ delimiter ;在实际工程实践中,可能大部分还是第二类数据,定时归档数据,这里主要需要考虑数据迁移的时间,是否能够用上索引等问题。这部分暂且不表,等后面实际操作了再结合一个案例进行说明。 当然如果前期已经考虑了数据清理的方案,一切都会比较简单,表不会变得很大,也没有没有在大表中删除数据的需求。但是如果和我们一样,是等到数据量堆积很大了才着手考虑清理,就会遇到在一张在线业务大表中,删除大量历史数据的情况。接下来就对遇到的这种情况进行总结,网上查找的资料,方式主要有两种。 一、分批清理按一定的查询条件,分批次的删除数据,该方法操作比较简单,但需要注意的地方也比较多 注意点: 分批次的查询和删除要控制好数据量、执行耗时,保证每批次耗时不要太长 进行删除前,预留好足够的存储空间,必要时需要先扩容再清理。在删除后存储空间可能不会降低,还可能会增长,因为实际操作中产生了大量binlog日志。至少预留与删除数据量同等大小的空间 删除数据后,需要额外对碎片空间进行清理,该操作也比较耗时,需要低峰执行实测中,删除 1400w 数据,日志空间增长近10G,与删除数据占用存储空间接近 delimiter $$ CREATE EVENT IF NOT EXISTS event_task_clear ON SCHEDULE EVERY 10 MINUTE STARTS '2022-06-20 22:30:00' ENDS '2022-06-21 03:10:00' DO BEGIN DECLARE v_endId BIGINT(20) DEFAULT 0; DECLARE v_endCreateTime BIGINT(20) DEFAULT 0; DECLARE v_maxEndTime BIGINT(20) DEFAULT 0; SET v_maxEndTime = (SELECT UNIX_TIMESTAMP('2022-05-01 00:00:00') * 1000); -- cost 2.5s SELECT ID, create_time INTO v_endId, v_endCreateTime FROM ttask ORDER BY ID ASC LIMIT 500000,1; IF v_endCreateTime 6868326044778799105; -- 步骤3,重命名,替换原表 RENAME TABLE t_task TO t_task_bak, t_task_tmp TO t_task;实际操作中注意以下几点: 步骤2如果数据过多,需要分批进行 步骤2和步骤3可能会导致一小部分数据丢失,如果最近数据是热点数据,该方法不适用,或选择在低峰期进行 如果数据表主键是自增ID,需要更新替换后的表的自增ID 三、案例我们线上有个任务表 t_task,目前数据量在 2000w ,存储空间在 18G 。计划先将 2 个月以前的数据进行清理,在清理历史数据后再考虑分月归档。 表的 create_time 字段没有索引,主键 ID 采用雪花算法生成。因为表中数据最近数据为热点数据,所有没有采用方案(二),使用的是分批删除的方式。 在实测中发现,分批删除如果根据创建时间做查询删除,耗时不太理想,需要选择在低峰段进行。考虑到我们的数据表主键是趋势递增的,我们可以批量删除前 50w 条数据,直到第50w条记录超出了需要清理的日期,然后再手动将剩余的一小部分数据删除(如果不要求特别精确,其实也可以不删)。实际测试中,采用这种方式每执行一次大约 10s 左右。这样做的好处是耗时短,可以在任意时间段进行,不好的地方是,最后还是需要手动做一次清理。 delimiter $$ CREATE EVENT IF NOT EXISTS event_task_clear ON SCHEDULE EVERY 10 MINUTE STARTS '2022-03-01 00:00:00' ENDS '2022-03-01 06:00:00' DO BEGIN DECLARE v_endId BIGINT(20) DEFAULT 0; DECLARE v_endCreateTime BIGINT(20) DEFAULT 0; DECLARE v_maxEndTime BIGINT(20) DEFAULT 0; SET v_maxEndTime = (SELECT UNIX_TIMESTAMP('2022-01-01 00:00:00') * 1000); SELECT ID, create_time INTO v_endId, v_endCreateTime FROM t_task ORDER BY ID ASC LIMIT 500000,1; IF v_endCreateTime |
CopyRight 2018-2019 实验室设备网 版权所有 |