SpringBoot高性能清理MySQL历史数据

embedded/2024/9/25 23:18:50/

背景

项目的心跳数据，随着业务的增长及时间的推移，数据库的数据量越来越庞大，不仅查询性能受到了影响，还消耗过多的数据库空间。为了保证查询的性能，需要控制数据量的大小，与业务商讨，仅保留近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">SELECTMIN(id) AS minId,MAX(id) AS maxId,COUNT(id) AS idCountFROM (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);
}