SpringBoot高性能清理MySQL历史数据

背景

项目的心跳数据，随着业务的    增长及时间的推移，数据库的数据量越来越庞大，不仅查询性能受到了影响，还消耗过多的数据库空间。为了保证查询的性能，需要控制数据量的大小，与业务商讨，仅保留近60天的数据即可。

清理历史数据需要考虑的地方

•   1、不能一次性将历史数据删除，消耗时间长，失败风险高，而且还会带来锁的问题
•   2、查询语句需要索引，不然删除操作也大打折扣
•   3、根据id范围删除数据(id字段有主键索引)
•   4、打印一些关键的日志记录一下删除进度及耗时

清理历史数据的主要步骤

说明：项目中引入xxl-job做调度中心，但该篇文章不包含xxl-job的引入及调用，真的有需要，可在评论区回复，可考虑出一篇，建议先从xxl-job官网了解该调度框架的英姿https://www.xuxueli.com/xxl-job

创建craete_time索引

# 创建creat_time索引 
ALTER TABLE xxx ADD INDEX idx_ctime (create_time);

需要的DTO对象

@Data
public class MinMaxIdDTO {
    /**
     * 最小Id
     */
    private Long minId;
    /**
     * 最大Id
     */
    private Long maxId;
    /**
     * 总条数
     */
    private Long idCount;
}

编写对应的Mapper接口

    /**
     * 查询ID范围
     * @param dateTime
     * @return
     */
    MinMaxIdDTO queryIdRange(@Param("dateTime") LocalDateTime dateTime);

    /**
     * 根据ID范围清理数据
     */
    void deleteByIdRange(MinMaxIdDTO minMaxIdDTO);

编写对应的Mapper.xml文件

<select id="queryIdRange" resultType="com.xxx.xxx.xxx.dto.MinMaxIdDTO">
    SELECT
        MIN(id) AS minId,
        MAX(id) AS maxId,
        COUNT(id) AS idCount
    FROM (
    SELECT id FROM `xxx` WHERE create_time <![CDATA[ <= ]]> #{dateTime} ORDER BY id LIMIT 0,5000
    ) a
</select>


<delete id="deleteByIdRange">
    DELETE FROM `xxx` WHERE id BETWEEN #{minId} AND #{maxId}
</delete>

编写业务逻辑代码

public void cleanHistoryData() {
    // 当前时间的前两个月
    LocalDateTime before60DateTime = LocalDateTime.now().minusMonths(2);
    // 异步执行
    CompletableFuture.runAsync(() -> {
        try {
            long l = System.currentTimeMillis();
            long total = 0;
            log.info("开始清理xxx表时间[{}]之前的历史数据", before60DateTime);
            do {
                MinMaxIdDTO minMaxIdDTO = xxxMapper.queryIdRange(before60DateTime);
                if (minMaxIdDTO.getIdCount() == 0) {
                    log.info("xxx表已经清理完毕或找不到合适的数据");
                    break;
                }
                total += minMaxIdDTO.getIdCount();
                log.info("xxx表清除ID范围{}-{}的数据", minMaxIdDTO.getMinId(), minMaxIdDTO.getMaxId());
                xxxMapper.deleteByIdRange(minMaxIdDTO);
            } while (true);
            log.info("结束清理xxx表时间[{}]之前的历史数据,共清理:{}条数据,共耗时:{}毫秒", before60DateTime,
                    total, System.currentTimeMillis() - l);
        } catch (Exception e) {
            log.error("清理xxx表历史数据发生异常,停止任务", e);
            throw e;
        }
	// asyncExecutor线程池可加可不加
    }, asyncExecutor);
}

总结

1、添加需要的索引
2、引入xxl-job框架(本篇文章忽略)
3、编写对应的Mapper接口
4、编写对应的Mapper.xml文件
5、编写清理历史数据的逻辑