mysql(43) : 分区表自动管理(小时)

说明

分区字段为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 '小时(分区键)',
  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.flm.common.utils.DateUtil;
import com.alibaba.gts.flm.module.abnormal.recognition.biz.dao.mapper.PartitionManagerHourMapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
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;

/**
 * @Author: liyue
 * @Date: 2022/03/02/17:51
 * @Description: mysql分区管理
 */
@Configuration
@Slf4j
public class PartitionManagerHourJob {

    @Resource
    private PartitionManagerHourMapper partitionManagerMapper;

    @Value("${spring.profiles.active}")
    private String active;

    private static final String prefix = "p";

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

    @PostConstruct
    private void init() {
        if (!active.contains("prod")) {
            return;
        }
        handle();
    }


    /**
     * 每小时执行一次
     */
    @Scheduled(cron = "0 0 0/1 * * ? ")
    public void task() {
        if (!active.contains("prod")) {
            return;
        }
        handle();
    }


    public void handle() {
        log.info("分区管理逻辑开始执行...");
        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 hour = DateUtil.format(System.currentTimeMillis() + 1000 * 60 * 60 * i, DateUtil.PATTERN_YYYYMMDDHH);
                    if (!exist(table, prefix + hour)) {
                        partitionManagerMapper.createPartition(table, field, prefix + hour, hour);
                        log.info("table:{} 创建分区:{}", table, prefix + hour);
                    } else {
                        log.warn("table:{} 分区{}已存在", table, prefix + hour);
                    }
                }

                /// 清理旧分区
                long deleteTime = System.currentTimeMillis() - (config.getExpireHour() * 1000 * 60 * 60);
                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 currentHour = DateUtil.format(System.currentTimeMillis(), DateUtil.PATTERN_YYYYMMDDHH);
                log.info("table:{} 初始化分区:{}", table, prefix + currentHour);
                partitionManagerMapper.initPartition(table, field, prefix + currentHour, currentHour);
                // 创建未来4小时分区
                for (int i = 0; i < 4; i++) {
                    String hour = DateUtil.format(System.currentTimeMillis() + 1000 * 60 * 60 * i, DateUtil.PATTERN_YYYYMMDDHH);
                    if (!exist(table, prefix + hour)) {
                        partitionManagerMapper.createPartition(table, field, prefix + hour, hour);
                        log.info("table:{} 创建分区:{}", table, prefix + hour);
                    }
                }
            }
        }
        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 expireHour;
    }
}

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;

import java.util.List;

@Mapper
public interface PartitionManagerHourMapper {

    @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, 10) AS SIGNED) < #{expireDate}")
    List<String> listExpirePartition(@Param("tableName") String tableName, @Param("expireDate") Integer expireDate);
}