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 <= v_maxEndTime THEN
            -- cost 7s
            DELETE FROM t_preview_convert_task WHERE id < v_endId;
        END IF;
        
       END $$

delimiter ;

二、新建临时表，分批次导入，然后替换

1. 拷贝表结构，新建临时表
2. 导入需要保留的数据，如果数据量大，需要分批次 INSERT
3. 将源表重命名为备份表，临时表命名为原表名

-- 步骤1
CREATE TABLE IF NOT EXISTS t_task_tmp like t_task;
-- 步骤2,需要考虑查询和插入的时间
INSERT INTO t_task_tmp SELECT * FROM t_task WHERE id > 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 <= v_maxEndTime THEN
            DELETE FROM t_task WHERE id < v_endId;
        END IF;
        
       END $$

delimiter ;

四、为什么没有采用MySQL分区表

• 分区表技术不是用于提升MySQL数据库的性能，而是方便数据的管理。分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题
• 分区表，分区键设计不太灵活：All columns used in the table's partitioning expression must be part of every unique key that the table may have, including any primary key
• 分区表达式中，仅可以使用部分函数(https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html)
• 有额外的学习成本和潜在的风险

CREATE TABLE `t_device_online_record_partition`  (
    `id` bigint UNSIGNED NOT NULL COMMENT '主键ID',
    `device_id` varchar(64) NOT NULL COMMENT '设备ID',
    `online` bit NOT NULL COMMENT '是否在线,1/在线,0/离线',
    `occur_time` datetime NOT NULL COMMENT '发生时间',
    `create_time` bigint UNSIGNED NOT NULL COMMENT '创建时间',
    `create_by` varchar(64) NULL COMMENT '创建人',
    `update_time` bigint UNSIGNED NULL COMMENT '更新时间',
    `update_by` varchar(64) NULL COMMENT '更新人',
    `update_version` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁',
    PRIMARY KEY (`id`, `occur_time`),
    INDEX `idx_device_id_create_time`(`device_id`, `create_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COMMENT = '设备上下线记录分区表'
PARTITION BY LIST(
MONTH(occur_time)
)(
PARTITION p01 VALUES IN(1),
PARTITION p02 VALUES IN(2),
PARTITION p03 VALUES IN(3),
PARTITION p04 VALUES IN(4),
PARTITION p05 VALUES IN(5),
PARTITION p06 VALUES IN(6),
PARTITION p07 VALUES IN(7),
PARTITION p08 VALUES IN(8),
PARTITION p09 VALUES IN(9),
PARTITION p10 VALUES IN(10),
PARTITION p11 VALUES IN(11),
PARTITION p12 VALUES IN(12)
);

上面的示例中，因为想按月分区，于是分区函数中的 occur_time 必须是表中主键的一部分，需要 id 和 occur_time 共同组成复合主键。另外，刚开始是想用bigint类型的 create_time 字段分区，但是没有可利用的函数从时间戳毫秒值获取月份(分区函数中，UNIX_TIMESTAMP()只用作用在TIMESTAMP字段上)，只能单独维护一个datetime类型的字段，或者在入库时就计算好月份。当然也可用使用 RANGE 分区配合 LESS THAN, 计算好固件月份的时间戳，但这样的分区表后面需要到点扩充分区，不是我想要的。

参考文章

1. Navicat统计的行数竟然和表实际行数不一致：https://cloud.tencent.com/developer/article/1909935
2. MySQL数据清理有技巧：https://cloud.tencent.com/developer/article/1546545
3. Mysql大表数据清理方案：https://blog.csdn.net/zhaobangyu/article/details/122606850
4. MySQL CREATE EVENT文档：https://dev.mysql.com/doc/refman/5.7/en/create-event.html
5. 释放MySQL实例的表空间：https://help.aliyun.com/document_detail/41720.html
6. MySQL 分区表的限制及局限性：https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html