mysql分区自动维护(SpringBoot+MybatisPlus)

丿似锦

已于 2024-08-06 09:42:08 修改

阅读量723

点赞数 22

文章标签： mysql spring boot 数据库

于 2024-08-06 09:36:42 首次发布

本文链接：https://blog.csdn.net/zk_tww/article/details/140945704

版权

1.环境

SpringBoot + MybatisPlus + MySQL

2.简介

通过定时器@Scheduled每日触发，查询当前库中所有分区表（这里以时间段进行分区）
判断剩余分区是否小于自定义预留分区（无自定义预留分区则取默认分区配置），若小于预留分区时，自动创建分区至配置分区数
判断已有分区是否大于自定义保留分区（无自定义保留分区则取默认分区配置），若大于保留分区时，自动删除分区至配置分区数

主要目的为解决每日自动创建/删除分区运维工作
这里分区表主要用于存储存在大文本字段的日志记录数据
分区判断以当前时间为基准判断所需预留/保留数量
通过分区做自动清理，可避免delete带来的数据库碎片率持续升高问题

3.分区表介绍

MySQL的分区表是一种把大表拆分成多个小表的技术，在逻辑上看是一个表，但在物理上实际上是多个独立的表。每个小表被称为一个分区，可以分布在不同的物理位置上，并且可以使用不同的存储引擎。

3.1.分区表的几种方式

MySQL的分区表可以通过以下几种方式来进行分区：

范围分区（RANGE partitioning）：根据某个列的取值范围将数据进行分区。例如，可以根据日期范围或者数值范围进行分区。
列表分区（LIST partitioning）：根据某个列的取值列表将数据进行分区。例如，可以根据国家或者地区的列表进行分区。
散列分区（HASH partitioning）：根据某个列的散列值将数据进行分区。例如，可以根据用户ID的散列值进行分区。
键值分区（KEY partitioning）：根据某个列的键值将数据进行分区。这是一种基于哈希或者范围的分区方式。

在创建分区表时，需要指定分区键（partition key），即用来进行分区的列。分区键可以是一个或多个列的组合，分区的方式也可以是组合的。分区键可以是任何数据类型，包括整型、日期、字符串等。

3.2.分区表的优点：

提高查询性能：可以并行处理查询，提高查询效率。
提高维护性和可用性：可以独立对某个分区进行维护、优化或修复，不会影响其他分区的正常运行。
改善分布式数据库的水平扩展能力：可以将数据分散在不同的节点上，提高系统的可扩展性和负载均衡能力。

3.3.分区表的缺点：

管理和维护相对复杂：需要考虑分区的设计和划分策略，以及数据迁移、备份和恢复等操作。
查询性能可能受限于分区键：如果查询条件涉及到分区键，只有涉及到的分区才会被查询，其他分区不会被利用，可能导致查询性能不佳。
可能增加额外的存储空间和IO开销：因为每个分区都是一个独立的表，可能会有一定的冗余和重复的存储空间和IO开销。

总的来说，MySQL的分区表可以提高查询性能、维护性和可用性，但需要考虑合适的分区键和划分策略，并且需要投入额外的管理和维护工作。

4.分区表管理

4.1.创建分区表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
PARTITION BY RANGE(partition_column) (
    PARTITION p1 VALUES LESS THAN (value1),
    PARTITION p2 VALUES LESS THAN (value2),
    ...
);

4.2.修改分区表

ALTER TABLE table_name 
ADD PARTITION (PARTITION partition_name VALUES LESS THAN (value));

4.3.删除分区表

ALTER TABLE table_name
DROP PARTITION partition_name;

4.4.查询分区表

select
	TABLE_NAME as tableName
from
	INFORMATION_SCHEMA.PARTITIONS
where
	TABLE_SCHEMA = database()
	and PARTITION_EXPRESSION is not null
group by
	TABLE_NAME ;

5.自动运维功能实现

通过定时器每日自动增加/删除分区表的分区

5.1.配置文件

# 分区新增配置
table.partition.config.addEnable=true
table.partition.config.addMap.default=10
table.partition.config.addMap.zk_user_log_ext=10

# 分区清理配置
table.partition.config.cleanEnable=true
table.partition.config.cleanMap.default=3
table.partition.config.cleanMap.zk_user_log_ext=3

5.2.主入口

定时器PartitionManager

package com.zk.app.manager;

import com.zk.app.entity.PartitionTable;
import com.zk.app.service.PartitionTableService;
import com.zk.app.utils.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * @program: ZK
 * @description: 分区表管理器
 * @author: zk
 * @create: 2024-07-24 17:58
 **/
@Component
@ConfigurationProperties(prefix = "table.partition.config")
@Slf4j
public class PartitionManager {

    private static final String PARTITION_NAME_PREFIX = "p";

    private static final String DEFAULT_KEY = "default";

    @Autowired
    private PartitionTableService partitionTableService;

    @Resource(name = "table-executor")
    private ThreadPoolExecutor threadPoolExecutor;
    /**
     * 是否开启分区表自动创建
     */
    private boolean addEnable;

    /**
     * 分区表自动创建配置
     */
    private Map<String, Integer> addMap;

    /**
     * 是否开启分区表自动清理
     */
    private boolean cleanEnable;

    /**
     * 分区表自动清理配置
     */
    private Map<String, Integer> cleanMap;


    @Scheduled(cron = "0 1 0 * * ?")
    public void todo() {
        long startTime = System.currentTimeMillis();
        log.info("table partition add and clen start...");
        Date now = new Date();
        List<PartitionTable> partitionTableList = partitionTableService.selectPartitionTableList();
        for (PartitionTable partitionTable : partitionTableList) {
            threadPoolExecutor.execute(() -> {
                try {
                    if (addEnable) {
                        addPartition(partitionTable, now);
                    }
                    if (cleanEnable) {
                        cleanPartiton(partitionTable, now);
                    }
                } catch (ParseException e) {
                    throw new RuntimeException(e);
                }
            });
        }
        log.info("table partition add and clen end,cost time:{}", System.currentTimeMillis() - startTime);
    }

    /**
     * 添加分区
     *
     * @param partitionTable 分区表
     * @param now            当前时间
     */
    private void addPartition(PartitionTable partitionTable, Date now) throws ParseException {
        Integer addDays = addMap.get(partitionTable.getTableName());
        if (Objects.isNull(addDays)) {
            addDays = addMap.get(DEFAULT_KEY);
        }
        Date maxPartitionDate = DateUtil.parseDateDefaultDateNoInterval(partitionTable.getMaxPartition());
        long days = DateUtil.daysBetween(now, maxPartitionDate);
        if (days < addDays) {
            int addPartition = addDays - (int) days;
            for (int i = 1; i <= addPartition; i++) {
                Date addDate = DateUtil.addDays(maxPartitionDate, i);
                String partitionName = PARTITION_NAME_PREFIX + DateUtil.formatDateDefaultDateNoInterval(addDate);
                partitionTableService.addPartition(partitionTable.getTableName(),
                        partitionName, DateUtil.formatDateDefaultDate(DateUtil.addDays(addDate, 1)));
                log.info("{} add partition {} success!!", partitionTable.getTableName(), partitionName);
            }
        }
    }

    /**
     * 清理分区
     *
     * @param partitionTable 分区表
     * @param now            当前时间
     */
    private void cleanPartiton(PartitionTable partitionTable, Date now) throws ParseException {
        Integer cleanDays = cleanMap.get(partitionTable.getTableName());
        if (Objects.isNull(cleanDays)) {
            cleanDays = cleanMap.get(DEFAULT_KEY);
        }
        Date minPartitionDate = DateUtil.parseDateDefaultDateNoInterval(partitionTable.getMinPartition());
        long days = DateUtil.daysBetween(minPartitionDate, now);
        if (days > cleanDays) {
            int cleanPartition = (int) days - cleanDays;
            for (int i = 0; i < cleanPartition; i++) {
                Date cleanDate = DateUtil.addDays(minPartitionDate, i);
                String partitionName = PARTITION_NAME_PREFIX + DateUtil.formatDateDefaultDateNoInterval(cleanDate);
                partitionTableService.cleanPartition(partitionTable.getTableName(), partitionName);
                log.info("{} drop partition {} success!!", partitionTable.getTableName(), partitionName);
            }
        }
    }

    public boolean isAddEnable() {
        return addEnable;
    }

    public void setAddEnable(boolean addEnable) {
        this.addEnable = addEnable;
    }

    public Map<String, Integer> getAddMap() {
        return addMap;
    }

    public void setAddMap(Map<String, Integer> addMap) {
        this.addMap = addMap;
    }

    public boolean isCleanEnable() {
        return cleanEnable;
    }

    public void setCleanEnable(boolean cleanEnable) {
        this.cleanEnable = cleanEnable;
    }

    public Map<String, Integer> getCleanMap() {
        return cleanMap;
    }

    public void setCleanMap(Map<String, Integer> cleanMap) {
        this.cleanMap = cleanMap;
    }
}

5.3.分区表操作实现

5.3.1.`PartitionTableService`

package com.zk.app.service;

import com.zk.app.entity.PartitionTable;

import java.util.List;

/**
 * @program: ZK
 * @description: 分区表操作
 * @author: zk
 * @create: 2024-07-24 18:04
 **/
public interface PartitionTableService{

    /**
     * 获取所有分区表信息
     *
     * @return
     */
    List<PartitionTable> selectPartitionTableList();

    /**
     * 添加分区
     *
     * @param tableName     表名
     * @param partitionName 分区名
     * @param partitionDate 分区日期
     */
    boolean addPartition(String tableName, String partitionName, String partitionDate);

    /**
     * 清理分区
     *
     * @param tableName     表名
     * @param partitionName 分区名
     */
    boolean cleanPartition(String tableName, String partitionName);
}

5.3.2.`PartitionTableServiceImpl`

package com.zk.app.service.impl;

import com.zk.app.entity.PartitionTable;
import com.zk.app.mapper.PartitionTableMapper;
import com.zk.app.service.PartitionTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.retry.annotation.Backoff;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @program: ZK
 * @description:
 * @author: zk
 * @create: 2024-07-24 18:35
 **/
@Service
public class PartitionTableServiceImpl implements PartitionTableService {

    @Autowired
    private PartitionTableMapper partitionTableMapper;

    @Override
    public List<PartitionTable> selectPartitionTableList() {
        return partitionTableMapper.selectPartitionTableList();
    }

    @Override
    @Retryable(value = {Exception.class}, maxAttempts = 3, backoff = @Backoff(delay = 1000, multiplier = 2))
    public boolean addPartition(String tableName, String partitionName, String partitionDate) {
        partitionTableMapper.addPartition(tableName, partitionName, partitionDate);
        return true;
    }

    @Override
    @Retryable(value = {Exception.class}, maxAttempts = 3, backoff = @Backoff(delay = 1000, multiplier = 2))
    public boolean cleanPartition(String tableName, String partitionName) {
        partitionTableMapper.cleanPartition(tableName, partitionName);
        return true;
    }
}

@Retryable为重试框架，避免新增/删除分区时，无法获取表锁而失败

        <dependency>
            <groupId>org.springframework.retry</groupId>
            <artifactId>spring-retry</artifactId>
        </dependency>

5.3.3.`PartitionTableMapper`

package com.zk.app.mapper;

import com.zk.app.entity.PartitionTable;
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;

/**
 * @program: ZK
 * @description: 分区表mapper
 * @author: zk
 * @create: 2024-07-24 18:27
 **/
@Mapper
public interface PartitionTableMapper {

    /**
     * 获取所有分区表信息
     *
     * @return
     */
    @Select("SELECT TABLE_NAME AS tableName, max(right(PARTITION_NAME,8)) AS maxPartition ,min(right(PARTITION_NAME,8)) AS minPartition " +
            "FROM INFORMATION_SCHEMA.PARTITIONS " +
            "WHERE TABLE_SCHEMA = DATABASE() AND PARTITION_EXPRESSION is not null " +
            "group by TABLE_NAME ;")
    List<PartitionTable> selectPartitionTableList();

    /**
     * 添加分区
     *
     * @param tableName     表名
     * @param partitionName 分区名
     * @param partitionDate 分区日期
     */
    @Update("ALTER TABLE ${tableName} ADD PARTITION (PARTITION ${partitionName} VALUES LESS THAN (#{partitionDate}));")
    void addPartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName,
                      @Param("partitionDate") String partitionDate);

    /**
     * 清理分区
     *
     * @param tableName     表名
     * @param partitionName 分区名
     */
    @Update("ALTER TABLE ${tableName} DROP PARTITION ${partitionName};")
    void cleanPartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
}

5.3.4.实体类

PartitionTable

package com.zk.app.entity;

import lombok.Data;

/**
 * @program: ZK
 * @description: 分区表
 * @author: zk
 * @create: 2024-07-24 18:20
 **/
@Data
public class PartitionTable {

    /**
     * 表名
     */
    private String tableName;

    /**
     * 最大分区
     */
    private String maxPartition;

    /**
     * 最小分区
     */
    private String minPartition;
}

5.3.5.线程池配置

ThreadConfig

package com.zk.app.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @program: ZK
 * @description: 线程池配置
 * @author: zk
 * @create: 2024-07-25 10:35
 **/
@Configuration
public class ThreadConfig {

    @Bean("table-executor")
    public ThreadPoolExecutor tablePoolExecutor() {
        return new ThreadPoolExecutor(10, 20, 10,
                java.util.concurrent.TimeUnit.SECONDS,
                new ArrayBlockingQueue<>(100),
                new ZKThreadFactory("table-executor"),
                new ThreadPoolExecutor.CallerRunsPolicy());
    }

    /**
     * 线程工厂类
     * 线程名称赋值
     */
    public class ZKThreadFactory implements ThreadFactory {
        private String name;

        private final AtomicInteger threadNumber = new AtomicInteger(1);

        public ZKThreadFactory(String name) {
            this.name = name;
        }

        @Override
        public Thread newThread(Runnable r) {
            Thread thread = new Thread(r);
            thread.setName(name + "-" + threadNumber.getAndIncrement());
            return thread;
        }
    }
}

5.3.6.日期转换工具类

DateUtil

package com.zk.app.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.Duration;
import java.time.Period;

/**
 * @program: ZK
 * @description: 时间工具类
 * @author: zk
 * @create: 2024-07-24 17:24
 **/
public class DateUtil {

    private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd";

    private static final String DEFAULT_DATE_FORMAT_NO_INTERVAL = "yyyyMMdd";

    private static final String DEFAULT_DATETIME_FORMAT = "yyyy-MM-dd HH:mm:ss";

    /**
     * 将字符串转换为 Date 对象
     *
     * @param dateString 日期字符串
     * @param format     日期格式
     * @return Date 对象
     */
    public static Date parseDate(String dateString, String format) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.parse(dateString);
    }

    /**
     * 将字符串转换为 Date 对象，使用默认的日期格式
     *
     * @param dateString 日期字符串
     * @return Date 对象
     */
    public static Date parseDateDefaultDate(String dateString) throws ParseException {
        return parseDate(dateString, DEFAULT_DATE_FORMAT);
    }

    /**
     * 将字符串转换为 Date 对象，使用默认的日期格式，不包含间隔
     *
     * @param dateString 日期字符串
     * @return Date 对象
     */
    public static Date parseDateDefaultDateNoInterval(String dateString) throws ParseException {
        return parseDate(dateString, DEFAULT_DATE_FORMAT_NO_INTERVAL);
    }

    /**
     * 将字符串转换为 Date 对象，使用默认的日期时间格式
     *
     * @param dateString 日期字符串
     * @return Date 对象
     */
    public static Date parseDateDefaultDateTime(String dateString) throws ParseException {
        return parseDate(dateString, DEFAULT_DATETIME_FORMAT);
    }

    /**
     * 将 Date 对象转换为字符串
     *
     * @param date   Date 对象
     * @param format 日期格式
     * @return 日期字符串
     */
    public static String formatDate(Date date, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }

    /**
     * 将 Date 对象转换为字符串，使用默认的日期格式
     *
     * @param date Date 对象
     * @return 日期字符串
     */
    public static String formatDateDefaultDate(Date date) {
        return formatDate(date, DEFAULT_DATE_FORMAT);
    }

    /**
     * 将 Date 对象转换为字符串，使用默认的日期格式，不包含间隔
     *
     * @param date Date 对象
     * @return 日期字符串
     */
    public static String formatDateDefaultDateNoInterval(Date date) {
        return formatDate(date, DEFAULT_DATE_FORMAT_NO_INTERVAL);
    }

    /**
     * 将 Date 对象转换为字符串，使用默认的日期时间格式
     *
     * @param date Date 对象
     * @return 日期字符串
     */
    public static String formatDateDefaultDateTime(Date date) {
        return formatDate(date, DEFAULT_DATETIME_FORMAT);
    }

    /**
     * 向 Date 对象添加指定的天数
     *
     * @param date Date 对象
     * @param days 要添加的天数
     * @return 新的 Date 对象
     */
    public static Date addDays(Date date, int days) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, days);
        return calendar.getTime();
    }

    /**
     * 向 Date 对象添加指定的小时数
     *
     * @param date  Date 对象
     * @param hours 要添加的小时数
     * @return 新的 Date 对象
     */
    public static Date addHours(Date date, int hours) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.HOUR_OF_DAY, hours);
        return calendar.getTime();
    }

    /**
     * 计算两个日期之间的天数差
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 天数差
     */
    public static long daysBetween(Date startDate, Date endDate) {
        LocalDate start = startDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        LocalDate end = endDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        return Period.between(start, end).getDays();
    }

    /**
     * 计算两个日期之间的时间差（以秒为单位）
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 时间差（秒）
     */
    public static long secondsBetween(Date startDate, Date endDate) {
        LocalDateTime start = startDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        LocalDateTime end = endDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        return Duration.between(start, end).getSeconds();
    }

}

5.4.分区表示例

zk_user_log_ext


CREATE TABLE `zk_user_log_ext` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `log_id` bigint NOT NULL COMMENT '操作日志id',
  `log_content` mediumtext NOT NULL COMMENT '日志详细信息',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_date` date NOT NULL COMMENT '创建日期',
  `create_user_id` bigint NOT NULL DEFAULT '0' COMMENT '创建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_user_id` bigint NOT NULL DEFAULT '0' COMMENT '修改人',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除:0否;1是',
  `remarks` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  `version` bigint NOT NULL DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='操作日志扩展表'
PARTITION BY RANGE  COLUMNS(create_date)
(PARTITION p20240722 VALUES LESS THAN ('2024-07-23') ENGINE = InnoDB,
 PARTITION p20240723 VALUES LESS THAN ('2024-07-24') ENGINE = InnoDB,
 PARTITION p20240724 VALUES LESS THAN ('2024-07-25') ENGINE = InnoDB,
 PARTITION p20240725 VALUES LESS THAN ('2024-07-26') ENGINE = InnoDB,
 PARTITION p20240726 VALUES LESS THAN ('2024-07-27') ENGINE = InnoDB,
 PARTITION p20240727 VALUES LESS THAN ('2024-07-28') ENGINE = InnoDB,
 PARTITION p20240728 VALUES LESS THAN ('2024-07-29') ENGINE = InnoDB,
 PARTITION p20240729 VALUES LESS THAN ('2024-07-30') ENGINE = InnoDB,
 PARTITION p20240730 VALUES LESS THAN ('2024-07-31') ENGINE = InnoDB,
 PARTITION p20240731 VALUES LESS THAN ('2024-08-01') ENGINE = InnoDB,
 PARTITION p20240801 VALUES LESS THAN ('2024-08-02') ENGINE = InnoDB,
 PARTITION p20240802 VALUES LESS THAN ('2024-08-03') ENGINE = InnoDB,
 PARTITION p20240803 VALUES LESS THAN ('2024-08-04') ENGINE = InnoDB,
 PARTITION p20240804 VALUES LESS THAN ('2024-08-05') ENGINE = InnoDB);

这里采用范围分区
分区键：create_date

6.待办

代码开源地址补充

丿似锦

关注

22
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
mysql分区自动维护(SpringBoot+MybatisPlus)

通过定时器@Scheduled每日触发，查询当前库中所有分区表（这里以时间段进行分区）判断剩余分区是否小于自定义预留分区（无自定义预留分区则取默认分区配置），若小于预留分区时，自动创建分区至配置分区数判断已有分区是否大于自定义保留分区（无自定义保留分区则取默认分区配置），若大于保留分区时，自动删除分区至配置分区数主要目的为解决每日自动创建/删除分区运维工作这里分区表主要用于存储存在大文本字段的日志记录数据分区判断以当前时间为基准判断所需预留/保留数量通过分区做自动清理，可避免。
复制链接

扫一扫