定时任务分批删除大表数据策略

一、需求背景

由于系统中存在许多临时表，这些临时表主要用于解析附件或者Excel或者合并资源池数据等等业务功能。
随着时间的积累，数据量越来越大，其中最大的一个表数据量达到了22亿，因此需要对这些临时表进行定时清理，节省数据库存储空间和提升查询效率。

二、技术方案

需要保留最近15天的数据且数据量太大，不能直接delete，所以采用定时任务每天凌晨3点分批进行删除策略。由于数据库主键id采用自增方式，所以这里采用代码分批计算最大最小主键id方式进行删除，另外每次循环删除的最大行数、执行间隔通过配置数据字典方式更加方便灵活控制。

1、历史数据处理

(1)新建相同表结构的数据表，新表补充creation_date的索引，利用同步工具同步最新15天的数据到新表。
(2)校验新表和旧表的数据一致性（统计总行数、查询最大id和最小id等）
(3)将新表替换为旧表（可以通过重命名方式）
(4)完成历史数据的清理（需要数据库服务器停机操作）

2、大表定时清理

大表数据可能一天达到几千万条，需要按照时间分段再分批处理。
此步骤前提是完成历史数据处理之后且建立creation_date的索引。
结束时间endDate:当前时间-15天
开始时间startDate:endDate-intervalHours(间隔时间)
最小主键minId:

select id as minId from relation_temp_t where creation_date >= #{startDate} order by creation_date asc limit 1;

最大主键maxId:

select id as maxId from relation_temp_t where creation_date <= #{endDate} order by creation_date desc limit 1;

每次删除的最大数量按照10000条进行限制。
分批执行的最大时间不超过默认配置时间2h，若在2h内还没有执行完成，则终止删除，等下次调度时间再重新发起执行删除操作。
删除脚本为：

delete from relation_temp_t where id between #{minId} and #{maxId} and creation_date between #{startDate} and #{endDate};

三、代码实现

public int newChildJobTask(){
	PeriodDataCleanVo cleanVo = getPeriodDataCleanVo(cleanDataTableEnum.getTableName());
	return deleteData(cleanVo,cleanDataTableEnum.getTableName());
}

private PeriodDataCleanVo getPeriodDataCleanVo(String tableName){ 
    // 定时清理天数 
	int day = 15;
	Date endate = DateUtils.addDays(new Date(),-days);
	int intervalHours = 24；
	Date startDate = DateUtils.addHours(endate,-intervalHours);
	PeriodDataCleanVo cleanVo = relationDao.findData(tableName);
	cleanVo.setStartDate((Date) startDae.clone());
	cleanVo.setEndDate((Date) endate.clone());
	return cleanVo;
}

private int deleteData(PeriodDataCleanVo cleanVo,String tableName){
	if(cleanVo.getTotal() == 0L){
		// 需要删除的数据总数为0，直接返回
		return 0;
	}
	// 每次循环删除的最大行数
	long maxDeleteRows = 10000;
	// 循环删除的最大执行时间600s*1000ms
	int maxExecutionTime = 600* 1000;
	// 当前时间区间最大主键
	long originalMaxId = cleanVo.getMaxId();
	long startId = cleanVo.getMinId();
	// 记录执行删除操作的开始时间戳
	long startTime = system.currentTimeMillis();
	int result = 0;
	long currentTime;
	
	do{
		PeriodDataCleanVo.setMinId(startId);
		PeriodDataCleanVo.setMaxId(startId+maxDeleteRows);
		int delRecords = relationDao.deleteData(cleanVo.getTableName());
		result = result +delRecords;
		startId= startId +maxDeleteRows+1;
		currentTime = System.currentTimeMillis();
		// 当循环数量结束或者循环删除的操作时间超过配置的指定时间未完成时，终止当前删除操作，等待下次定时任务触发执行删除任务
	} while (startId <= originalMaxId && (currentTime-startTime < maxExecutionTime))
	
	log.infoLog(log,"delete records complete");
	return result;
}