mysql(47) : 分区表自动管理(天)

说明
分区字段为int类型,并且为主键,如下示例的date

CREATE TABLE `mytest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '(id)',
  `gmt_create` datetime DEFAULT NULL,
  `gmt_modify` datetime DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL COMMENT '名称',
  `age` int DEFAULT NULL COMMENT '年龄',
  `sdate` int NOT NULL COMMENT '天(分区键,20231129)',
  PRIMARY KEY (`id`,`sdate`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=585 ROW_FORMAT=DYNAMIC COMMENT='';

job

import com.alibaba.gts.cdyj.task.biz.dao.mapper.PartitionManagerDayMapper;
import com.alibaba.gts.flm.common.utils.DateUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
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.ArrayList;
import java.util.List;


@Configuration
@Slf4j
public class PartitionManagerDayJob {

    @Resource
    private PartitionManagerDayMapper partitionManagerMapper;

    private static final String prefix = "p_";

    private static final List<Config> configs = new ArrayList<Config>() {{
        add(new Config("mytest", "sdate", 7));
    }};

    @PostConstruct
    private void init() {
        handle();
    }


    /**
     * 0点和12点执行
     */
    @Scheduled(cron = "0 0 0,12 * * ? ")
    public void task() {
        handle();
    }

    public void handle() {
        log.info("分区管理逻辑开始执行... configs.size:{}", configs.size());
        for (Config config : configs) {
            String table = config.getTable();
            String field = config.getField();
            Integer partitionCount = partitionManagerMapper.selectCountPartition(table);
            log.info("table:{} 分区数量:{}", table, partitionCount);
            if (partitionCount > 0) {
                // 已有分区
                    /// 创建新分区(创建未来3天分区)
                for (int i = 1; i <= 3; i++) {
                    String day = DateUtil.format(System.currentTimeMillis() + 1000 * 60 * 60 * 24 * i, DateUtil.PATTERN_YYYYMMDD);
                    if (!exist(table, prefix + day)) {
                        log.info("table:{} 创建分区:{}", table, prefix + day);
                        partitionManagerMapper.createPartition(table, field, prefix + day, day);
                    } else {
                        log.warn("table:{} 分区{}已存在", table, prefix + day);
                    }
                }

                /// 清理旧分区
                long deleteTime = System.currentTimeMillis() - (config.getExpireDay() * 1000L * 60 * 60 * 24);
                String deleteHour = DateUtil.format(deleteTime, DateUtil.PATTERN_YYYYMMDDHH);
                List<String> expirePartitions = partitionManagerMapper.listExpirePartition(table, Integer.parseInt(deleteHour));
                log.info("清理旧分区, 小于{}的分区数量:{}", deleteHour, expirePartitions.size());
                if (expirePartitions.size() > 0) {
                    for (String expirePartition : expirePartitions) {
                        partitionManagerMapper.deletePartition(table, expirePartition);
                        log.info("table:{} 删除分区:{}", table, prefix + expirePartition);
                    }
                }
            } else {
                // 无分区
                // 初始化分区,初始化当前小时分区
                String currentDay = DateUtil.format(System.currentTimeMillis(), DateUtil.PATTERN_YYYYMMDD);
                log.info("table:{} 初始化分区:{}", table, prefix + currentDay);
                partitionManagerMapper.initPartition(table, field, prefix + currentDay, currentDay);
                // 创建未来4天分区
                for (int i = 0; i < 4; i++) {
                    String day = DateUtil.format(System.currentTimeMillis() + 1000L * 60 * 60 * 24 * i, DateUtil.PATTERN_YYYYMMDD);
                    if (!exist(table, prefix + day)) {
                        partitionManagerMapper.createPartition(table, field, prefix + day, day);
                        log.info("table:{} 创建分区:{}", table, prefix + day);
                    }
                }
            }
        }
        log.info("分区管理逻辑执行完成");
    }

    private Boolean exist(String table, String partitionName) {
        return partitionManagerMapper.selectPartitionIsExist(table, partitionName) > 0;
    }

    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Config {
        /* 表名 */
        private String table;
        /* 分区字段 */
        private String field;
        /* 多少小时后过期 */
        private Integer expireDay;
    }
}

mapper

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;


@Mapper
public interface PartitionManagerDayMapper {

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

    @Update("ALTER TABLE ${tableName} DROP PARTITION ${partitionName};")
    int deletePartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName);

    @Update("ALTER TABLE ${table} partition by range (${timeField})( partition ${partitionName} values less than(${partitionVal} ));")
    int initPartition(@Param("table") String table, @Param("timeField") String timeField, @Param("partitionName") String partitionName, @Param("partitionVal") String partitionVal);

    @Update("ALTER TABLE ${table}  ADD PARTITION ( partition ${partitionName} values less than(${partitionVal} ))")
    int createPartition(@Param("table") String table, @Param("timeField") String timeField, @Param("partitionName") String partitionName, @Param("partitionVal") String partitionVal);

    @Select("SELECT\n" +
            "\tPARTITION_NAME\n" +
            "FROM\n" +
            "\tINFORMATION_SCHEMA.partitions\n" +
            "WHERE\n" +
            "\tTABLE_SCHEMA = schema()\n" +
            "\tAND TABLE_NAME = #{tableName}\n" +
            "\tand PARTITION_NAME is not null\n" +
            "\tand CAST(RIGHT (PARTITION_NAME, 8) AS SIGNED) < #{expireDate}")
    List<String> listExpirePartition(@Param("tableName") String tableName, @Param("expireDate") Integer expireDate);
}