利用shardingJdbc根据时间片动态分表刷新配置

利用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