[大数据]mysql(29) : mybatis分区管理

无分区初始化分区及创建明日分区,定时删除旧分区

注意 : 分区字段必须为主键字段

job

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 PartitionManagerJob {


    private ExecutorService executorService = new ThreadPoolExecutor(5, 10,
            3000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<>(10));
    @Resource
    private CustomizeMapper customizeMapper;

    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 = customizeMapper.queryDate();
        String days = customizeMapper.queryDays();

        String tomorrowDate = customizeMapper.queryTomorrowDate();
        String tomorrowDays = customizeMapper.queryTomorrowDays();

        for (Map.Entry<String, String> entry : tables.entrySet()) {
            String table = entry.getKey();
            String timeField = entry.getValue();
            Integer partitionCount = customizeMapper.selectCountPartition(table);
            if (partitionCount > 0) {
                // 已有分区,创建明日分区
                Integer partitionIsExist = customizeMapper.selectPartitionIsExist(table, tomorrowDate);
                if (partitionIsExist == 0) {
                    customizeMapper.createPartition(table, timeField, tomorrowDate, tomorrowDays);
                }
                // 删除旧分区
                List<String> partitions = customizeMapper.selectDaysAgoPartition(table, 3);
                for (String partition : partitions) {
                    customizeMapper.deletePartition(table, partition);
                }
            } else {
                // 无分区,新建今日分区和明日分区
                customizeMapper.initPartition(table, timeField, date, days);
                customizeMapper.createPartition(table, timeField, tomorrowDate, tomorrowDays);
            }
        }
    }
}

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 CustomizeMapper {

    @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);

}