无分区初始化分区及创建明日分区,定时删除旧分区
注意 : 分区字段必须为主键字段,联合主键,字段类型为datetime
job
import com.alibaba.bkbox.pull.image.repository.mapper.PartitionManagerMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Scheduled;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
/**
* @Author: liyue
* @Date: 2022/03/02/17:51
* @Description: mysql分区管理
*/
@Configuration
@Slf4j
public class PartitionManagerTask {
private ExecutorService executorService = new ThreadPoolExecutor(5, 10,
3000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<>(10));
@Resource
private PartitionManagerMapper partitionManagerMapper;
private static final Map<String, String> tables = new HashMap<String, String>() {{
put("test1", "create_time");
put("test2", "create_time");
}};
@PostConstruct
private void init() {
handle();
}
/**
* 下次执行时间
* 2022-03-01 10:00:00
* 2022-03-01 16:00:00
* 2022-03-01 22:00:00
* 2022-03-02 10:00:00
* 2022-03-02 16:00:00
*/
@Scheduled(cron = "0 0 10,16,22 * * ? ")
public void task() {
executorService.submit(new Runnable() {
@Override
public void run() {
handle();
}
});
}
public void handle() {
String date = partitionManagerMapper.queryDate();
String days = partitionManagerMapper.queryDays();
String tomorrowDate = partitionManagerMapper.queryTomorrowDate();
String tomorrowDays = partitionManagerMapper.queryTomorrowDays();
for (Map.Entry<String, String> entry : tables.entrySet()) {
String table = entry.getKey();
String timeField = entry.getValue();
Integer partitionCount = partitionManagerMapper.selectCountPartition(table);
if (partitionCount > 0) {
// 已有分区,创建明日分区
Integer partitionIsExist = partitionManagerMapper.selectPartitionIsExist(table, tomorrowDate);
if (partitionIsExist == 0) {
partitionManagerMapper.createPartition(table, timeField, tomorrowDate, tomorrowDays);
}
// 删除旧分区
List<String> partitions = partitionManagerMapper.selectDaysAgoPartition(table, 3);
for (String partition : partitions) {
partitionManagerMapper.deletePartition(table, partition);
}
} else {
// 无分区,新建今日分区和明日分区
partitionManagerMapper.initPartition(table, timeField, date, days);
partitionManagerMapper.createPartition(table, timeField, tomorrowDate, tomorrowDays);
}
}
}
/**
* 删除分区
* 每天4点执行
*/
@Scheduled(cron = "0 0 4 * * ? ")
public void clean() {
tables.forEach((k, v) -> cl(k));
}
private void cl(String table) {
List<String> list = partitionManagerMapper.queryPartition("'" + table + "'", 1);
if (list.size() == 0) {
return;
}
String ps = String.join(",", list);
partitionManagerMapper.deletePartition(table, ps);
}
}
mapper
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
/**
* @Author: liyue
* @Date: 2022/03/02/17:52
* @Description:
*/
public interface PartitionManagerMapper {
@Select("SELECT concat('p',DATE_FORMAT(date_add(now(), interval 1 day),'%Y%m%d'))")
String queryTomorrowDate();
@Select("SELECT TO_DAYS(date_add(now(), interval 1 day) )")
String queryTomorrowDays();
@Select("SELECT concat('p',DATE_FORMAT(now(),'%Y%m%d'))")
String queryDate();
@Select("SELECT TO_DAYS(now())")
String queryDays();
@Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=#{tableName} and PARTITION_NAME is not null;")
Integer selectCountPartition(@Param("tableName") String tableName);
@Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=#{tableName} AND partition_name = #{partitionName};")
Integer selectPartitionIsExist(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
@Select("SELECT partition_name\n" +
" FROM INFORMATION_SCHEMA.PARTITIONS\n" +
" WHERE TABLE_NAME = #{tableName} AND TABLE_SCHEMA = schema() AND partition_description <= to_days(date_sub(curdate(), INTERVAL ${day} DAY))")
List<String> selectDaysAgoPartition(@Param("tableName") String tableName, @Param("day") Integer day);
@Update("ALTER TABLE ${tableName} DROP PARTITION ${partitionName};")
int deletePartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
@Update("ALTER TABLE ${table} partition by range (TO_DAYS(${timeField}))( partition ${partitionName} values less than(${day} ));")
int initPartition(@Param("table") String table, @Param("timeField") String timeField,
@Param("partitionName") String partitionName, @Param("day") String day);
@Update("ALTER TABLE ${table} ADD PARTITION ( partition ${partitionName} values less than(${day} ))")
int createPartition(@Param("table") String table, @Param("timeField") String timeField,
@Param("partitionName") String partitionName, @Param("day") String day);
@Select("\n" +
"select partition_name\n" +
" from information_schema.partitions\n" +
" where table_schema= schema() and table_name= ${table} \n" +
" and partition_description < (to_days(now())-${day});\n")
List<String> queryPartition(@Param("table") String table, @Param("day") Integer day);
}