利用shardingJdbc根据时间片动态分表刷新配置
> 文章参考 https://blog.csdn.net/u013615903/article/details/108286966
> 分表业务需求
业务场景为记录ai视频巡检的异常事件,产生的数据量在40w条/月,考虑数据的插入和读取速度决定分表实现(未分库)
> 拆分方式
> 方案一
提前创建表,配置文件中配置这些表.这种方案在前期就要提前创建大量表(一年就是12张表),最主要后期还要在再去为当前业务去创建表,同时还要在配置文件中配置这些表
> 方案二
利用定时任务,定时创建和刷新表配置.这样就省去了后期再去创建表,改配置.减少了后期维护的工作 下面就是方案二的详细实现
> 搭建框架
首先引入依赖:
<!-- Druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<!-- shardingJdbc -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
接下来在application.yml中加入配置:
spring:
main:
# 是否允许使用相同名称重新注册不同的bean实现.(spring默认是允许,SpringBoot默认无值即false)
# 不配置此项会报找不到数据源
allow-bean-definition-overriding: true
shardingsphere:
# 数据源 (可配置多数据源)
datasource:
names: g1
g1:
# 数据库驱动,url.........
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://rm-2zeh7r5s96nge1014.mysql.rds.aliyuncs.com:3306/hilife-ai-platform?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC
username: hilife_ai_platform
password: Hopsonlife@2021
sharding:
# shardingJdbc虚拟表(可配置多张表)
tables:
goods:
#对应到真实数据库中的表
actual-data-nodes: g1.goods_template
key-generator:
column: id
type: SNOWFLAKE
table-strategy:
standard:
sharding-column: create_time
# 精确匹配规则(自定义类)
precise-algorithm-class-name: com.hilife.ai.platform.config.shardingjdbc.GoodsShardingConfig
# 范围匹配规则(自定义类)
range-algorithm-class-name: com.hilife.ai.platform.config.shardingjdbc.GoodsShardingConfig
# 打印日志
props:
sql.show: true
# 分表配置
split:
by:
month:
### 动态创建表的表名称 创建时间开始年 表模板名称 #########
tables:
#可配置多组
goods:
startYear: 2021
startMonth: 11
templateTable: "goods_template"
> 代码编写
> 配置类读取配置
@Data
@Component
@ToString
@ConfigurationProperties(prefix = "split.by.month")
public class SplitTableByMonthConfig {
/*
* key 表名 value 分表参数
* */
private HashMap<String, DynamicMonthTableEntity> tables;
}
@Data
@Component
public class DynamicMonthTableEntity {
Integer startYear;
Integer startMonth;
String templateTable;
}
> 分片逻辑
我这里用一个类分别继承了PreciseShardingAlgorithm和RangeShardingAlgorithm
@Component
public class GoodsShardingConfig implements PreciseShardingAlgorithm<Date>, RangeShardingAlgorithm<Date> {
@Override
//精确匹配逻辑
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
Date date = preciseShardingValue.getValue();
//获取分片键的日期格式为"2020_12"
String tableSuffix = ShardingUtils.getSuffixByYearMonth(date);
//匹配表
for (String tableName : availableTargetNames) {
if (tableName.endsWith(tableSuffix)) {
return tableName;
}
}
throw new IllegalArgumentException("未找到匹配的数据表");
}
@Override
//范围匹配逻辑
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Date> rangeShardingValue) {
List<String> list = new ArrayList<>();
Range<Date> valueRange = rangeShardingValue.getValueRange();
//获取上限,下限
Date lowerDate = valueRange.lowerEndpoint();
Date upperDate = valueRange.upperEndpoint();
//获取分片键的日期格式为"2020_12"
String lowerSuffix = ShardingUtils.getSuffixByYearMonth(lowerDate);
String upperSuffix = ShardingUtils.getSuffixByYearMonth(upperDate);
TreeSet<String> suffixList = ShardingUtils.getSuffixListForRange(lowerSuffix, upperSuffix);
for (String tableName : availableTargetNames) {
if (containTableName(suffixList, tableName)) {
list.add(tableName);
}
}
return list;
}
private boolean containTableName(Set<String> suffixList, String tableName) {
boolean flag = false;
for (String s : suffixList) {
if (tableName.endsWith(s)) {
flag = true;
break;
}
}
return flag;
}
}
> 定时任务
定时执行创建表,刷新配置
@Component
@EnableScheduling
public class LogHandler {
@Autowired
private SplitTableByMonthConfig tableConfig;
@Autowired
private DataSource dataSource;
@Autowired
private GoodsMapper goodsMapper;
private static final Logger logger = LoggerFactory.getLogger(LogHandler.class);
/**
* 定时任务(每月最后一天中午12点生成表并刷新配置)
*/
@Scheduled(cron = "0 0 2 26-28 * *")
public void CreateWcTableJobHandler() throws Exception {
//获取当前年月
Integer year = Integer.parseInt(DateUtils.getYear());
Integer month = Integer.parseInt(DateUtils.getMonth());
//12月开始任务时 生成次年一月份的表
if(month > 11){
year += 1 ;
month = 1 ;
}else {
//生成次月表
month +=1 ;
}
// 所有以月份分片的表
HashMap<String, DynamicMonthTableEntity> tables = tableConfig.getTables();
for (String name : tables.keySet()) {
String newTable = name + "_" + year + "_" + month ;
// 这里判断表是否存在,创建表
goodsMapper.createTable(tables.get(name).getTemplateTable(), name);
}
//创建成功之后 刷新 actual-data-nodes
actualTablesRefresh(year , month);
}
/**
* 初始化起始时间到当前时间的表配置
*/
@PostConstruct
private void intData(){
String year = DateUtils.getYear();
String month = DateUtils.getMonth();
actualTablesRefresh(Integer