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.1PartitionTableService
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.2PartitionTableServiceImpl
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.3PartitionTableMapper
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