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

利用shardingJdbc根据时间片动态分表刷新配置 利用shardingJdbc根据时间片动态分表刷新配置 > 文章参考 https://blog.csdn.net/u013615903/article/details/108286966 > 分表业务需求

业务场景为记录ai视频巡检的异常事件,产生的数据量在40w条/月,考虑数据的插入和读取速度决定分表实现(未分库)

> 拆分方式 > 方案一

​ 提前创建表,配置文件中配置这些表.这种方案在前期就要提前创建大量表(一年就是12张表),最主要后期还要在再去为当前业务去创建表,同时还要在配置文件中配置这些表

> 方案二

​ 利用定时任务,定时创建和刷新表配置.这样就省去了后期再去创建表,改配置.减少了后期维护的工作 下面就是方案二的详细实现

> 搭建框架

首先引入依赖：

        
            com.alibaba
            druid
            1.1.20
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.0.0-RC1
        

接下来在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 {

    
    private HashMap tables;

}

@Data
@Component
public class DynamicMonthTableEntity {
    Integer startYear;

    Integer startMonth;

    String templateTable;
}

​

> 分片逻辑

我这里用一个类分别继承了PreciseShardingAlgorithm和RangeShardingAlgorithm

@Component
public class GoodsShardingConfig implements PreciseShardingAlgorithm, RangeShardingAlgorithm {

    @Override
    //精确匹配逻辑
    public String doSharding(Collection availableTargetNames, PreciseShardingValue 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 doSharding(Collection availableTargetNames, RangeShardingValue rangeShardingValue) {
        List list = new ArrayList<>();
        Range valueRange = rangeShardingValue.getValueRange();
        //获取上限,下限
        Date lowerDate = valueRange.lowerEndpoint();
        Date upperDate = valueRange.upperEndpoint();
        //获取分片键的日期格式为"2020_12"
        String lowerSuffix = ShardingUtils.getSuffixByYearMonth(lowerDate);
        String upperSuffix = ShardingUtils.getSuffixByYearMonth(upperDate);
        TreeSet suffixList = ShardingUtils.getSuffixListForRange(lowerSuffix, upperSuffix);
        for (String tableName : availableTargetNames) {
            if (containTableName(suffixList, tableName)) {
                list.add(tableName);
            }
        }
        return list;
    }

    private boolean containTableName(Set 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);



    @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 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.parseInt(year),Integer.parseInt(month));
    }



    public void actualTablesRefresh(Integer year , Integer month)  {
        try {
            //获取shadingJdbc配置
            ShardingDataSource dataSource = (ShardingDataSource) this.dataSource;
            HashMap tables = tableConfig.getTables();
            final Set names = tables.keySet();
            //判断是否需要动态刷新配置
            if (names == null ||  names.size() == 0) {
                logger.error("没有动态分表配置");
                return;
            }
            //为每张动态表刷新配置
            for (String name : names) {
                TableRule tableRule = null;
                try {
                    //获取配置规则
                    tableRule = dataSource.getShardingContext().getShardingRule().getTableRule(name);
                } catch (ShardingConfigurationException e) {
                    logger.error("报错啦" +   e.getMessage());

                }
                //配置sharding表对应的实际表节点
                List dataNodes = tableRule.getActualDataNodes();
                Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes");
                Field modifiersField = Field.class.getDeclaredField("modifiers");
                modifiersField.setAccessible(true);
                modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL);
                actualDataNodesField.setAccessible(true);
                //新的表节点集合
                List newDataNodes = new ArrayList<>();
                //起始时间点
                DynamicMonthTableEntity dynamicMonthTableEntity = tables.get(name);
                int yearTime = dynamicMonthTableEntity.getStartYear();
                int monthTime = dynamicMonthTableEntity.getStartMonth();
                //获取sharding表名
                String dataSourceName = dataNodes.get(0).getDataSourceName();
                //往表节点中更新数据
                while (true) {
                    int tempTime = yearTime ;
                    //起始年小于当前年
                    if (yearTime < year) {
                        while (yearTime <= year) {
                            //起始年份的年份配置节点生成
                            if (tempTime == yearTime) {
                                for (int j = monthTime; j < 13; j++) {
                                    DataNode dataNode = new DataNode(dataSourceName + "." + name + "_" + yearTime + "_" + j);
                                    newDataNodes.add(dataNode);
                                }
                                yearTime++;
                                //中间年份的年份配置节点生成
                            }else if(yearTime > tempTime && yearTime != year){
                                for (int j = 1; j < 13; j++) {
                                    DataNode dataNode = new DataNode(dataSourceName + "." + name + "_" + year + "_" + j);
                                    newDataNodes.add(dataNode);
                                }
                                yearTime++;
                            }else {
                                break;
                            }
                        }
                        //当前年份的年份配置节点生成
                        if (yearTime == year) {
                            for (int j = 1; j < month + 2; j++) {
                                DataNode dataNode = new DataNode(dataSourceName + "." + name + "_" + yearTime + "_" + j);
                                newDataNodes.add(dataNode);
                            }
                            yearTime++;
                            break;
                        }
                    }

                    //起始年等于当前年
                    if (yearTime == year) {
                        for (int j = monthTime; j < month+2; j++) {
                            DataNode dataNode = new DataNode(dataSourceName + "." + name + "_" + yearTime + "_" + j);
                            newDataNodes.add(dataNode);
                        }
                        yearTime++;
                    }

                    if (yearTime > year) {
                        break;
                    }
                }
                actualDataNodesField.set(tableRule, newDataNodes);
            }
        }catch (Exception e){
            e.printStackTrace();
            logger.error("初始化 动态表单失败" + e.getMessage());
        }
    }
}

> 工具类

public class DateUtils {


    
    public static String getYear() {
        return formatDate(new Date(), "yyyy");
    }

    
    public static String getYear(Date date) {
        return formatDate(date, "yyyy");
    }


    
    public static String getMonth() {
        return formatDate(new Date(), "MM");
    }
    
    public static String getMonth(Date date) {
        return formatDate(date, "MM");
    }


    
    public static String getDay() {
        return formatDate(new Date(), "yyyy-MM-dd");
    }

    
    public static String getDay(Date date) {
        return formatDate(date, "yyyy-MM-dd");
    }

    
    public static String getDays() {
        return formatDate(new Date(), "yyyyMMdd");
    }

    
    public static String getDays(Date date) {
        return formatDate(date, "yyyyMMdd");
    }

    
    public static String getTime() {
        return formatDate(new Date(), "yyyy-MM-dd HH:mm:ss");
    }

    
    public static String getMsTime() {
        return formatDate(new Date(), "yyyy-MM-dd HH:mm:ss.SSS");
    }

    
    public static String getAllTime() {
        return formatDate(new Date(), "yyyyMMddHHmmss");
    }

    
    public static String getTime(Date date) {
        return formatDate(date, "yyyy-MM-dd HH:mm:ss");
    }

    public static String formatDate(Date date, String pattern) {
        String formatDate = null;
        if (StringUtils.isNotBlank(pattern)) {
            formatDate = DateFormatUtils.format(date, pattern);
        } else {
            formatDate = DateFormatUtils.format(date, "yyyy-MM-dd");
        }
        return formatDate;
    }

    
    public static boolean compareDate(String s, String e) {
        if (parseDate(s) == null || parseDate(e) == null) {
            return false;
        }
        return parseDate(s).getTime() >= parseDate(e).getTime();
    }

    
    public static Date parseDate(String date) {
        return parse(date, "yyyy-MM-dd");
    }

    
    public static Date parseTime(String date) {
        return parse(date, "yyyy-MM-dd HH:mm:ss");
    }

    public static Date parseMsTime(String date) {
        return parse(date, "yyyy-MM-dd HH:mm:ss.SSS");
    }

    
    public static Date parse(String date, String pattern) {
        try {
            return org.apache.commons.lang3.time.DateUtils.parseDate(date, pattern);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
    }

    
    public static String format(Date date, String pattern) {
        return DateFormatUtils.format(date, pattern);
    }

    
    public static Timestamp format(Date date) {
        return new Timestamp(date.getTime());
    }

    
    public static boolean isValidDate(String s) {
        return parse(s, "yyyy-MM-dd HH:mm:ss") != null;
    }

    
    public static boolean isValidDate(String s, String pattern) {
        return parse(s, pattern) != null;
    }

    public static int getDiffYear(String startTime, String endTime) {
        DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            int years = (int) (((fmt.parse(endTime).getTime() - fmt.parse(
                    startTime).getTime()) / (1000 * 60 * 60 * 24)) / 365);
            return years;
        } catch (Exception e) {
            // 如果throw java.text.ParseException或者NullPointerException，就说明格式不对
            return 0;
        }
    }

    
    public static long getDaySub(String beginDateStr, String endDateStr) {
        long day = 0;
        SimpleDateFormat format = new SimpleDateFormat(
                "yyyy-MM-dd");
        Date beginDate = null;
        Date endDate = null;

        try {
            beginDate = format.parse(beginDateStr);
            endDate = format.parse(endDateStr);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        day = (endDate.getTime() - beginDate.getTime()) / (24 * 60 * 60 * 1000);
        // System.out.println("相隔的天数="+day);

        return day;
    }

    
    public static String getAfterDayDate(String days) {
        int daysInt = Integer.parseInt(days);

        Calendar canlendar = Calendar.getInstance(); // java.util包
        canlendar.add(Calendar.DATE, daysInt); // 日期减 如果不够减会将月变动
        Date date = canlendar.getTime();

        SimpleDateFormat sdfd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr = sdfd.format(date);

        return dateStr;
    }

    
    public static String getAfterDayWeek(String days) {
        int daysInt = Integer.parseInt(days);

        Calendar canlendar = Calendar.getInstance(); // java.util包
        canlendar.add(Calendar.DATE, daysInt); // 日期减 如果不够减会将月变动
        Date date = canlendar.getTime();

        SimpleDateFormat sdf = new SimpleDateFormat("E");
        String dateStr = sdf.format(date);

        return dateStr;
    }

    
    public static String formatDateTime_今天昨天前天(Date date, String pattern) {
        SimpleDateFormat format = new SimpleDateFormat(pattern);
        if (date == null) {
            return "";
        }
        String time;
        try {
            time = format.format(date);
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }

        Calendar current = Calendar.getInstance();

        Calendar thisTime = Calendar.getInstance();
        thisTime.setTime(date);

        if (current.get(Calendar.YEAR) != thisTime.get(Calendar.YEAR)) {
            //不是同一年了

            return DateUtils.formatDate(date, pattern);
        }

        Calendar today = Calendar.getInstance();    //今天
        today.set(Calendar.YEAR, current.get(Calendar.YEAR));
        today.set(Calendar.MONTH, current.get(Calendar.MONTH));
        today.set(Calendar.DAY_OF_MONTH, current.get(Calendar.DAY_OF_MONTH));
        today.set(Calendar.HOUR_OF_DAY, 0);
        today.set(Calendar.MINUTE, 0);
        today.set(Calendar.SECOND, 0);

        Calendar yesterday = Calendar.getInstance();    //昨天

        yesterday.set(Calendar.YEAR, current.get(Calendar.YEAR));
        yesterday.set(Calendar.MONTH, current.get(Calendar.MONTH));
        yesterday.set(Calendar.DAY_OF_MONTH, current.get(Calendar.DAY_OF_MONTH) - 1);
        yesterday.set(Calendar.HOUR_OF_DAY, 0);
        yesterday.set(Calendar.MINUTE, 0);
        yesterday.set(Calendar.SECOND, 0);

        Calendar beforeYesterday = Calendar.getInstance();    //前天

        beforeYesterday.set(Calendar.YEAR, current.get(Calendar.YEAR));
        beforeYesterday.set(Calendar.MONTH, current.get(Calendar.MONTH));
        beforeYesterday.set(Calendar.DAY_OF_MONTH, current.get(Calendar.DAY_OF_MONTH) - 2);
        beforeYesterday.set(Calendar.HOUR_OF_DAY, 0);
        beforeYesterday.set(Calendar.MINUTE, 0);
        beforeYesterday.set(Calendar.SECOND, 0);

        if (thisTime.after(today)) {
            return time.split(" ")[1];
        } else if (thisTime.after(yesterday)) {
            return "昨天" + time.split(" ")[1];
        } else if (thisTime.after(beforeYesterday)) {
            return "前天" + time.split(" ")[1];
        } else {
            int index = time.indexOf("-") + 1;
            return time.substring(index, time.length());
        }
    }


    
    public static Date getWeekStartNew() {
        Calendar cal = Calendar.getInstance();
        int dayWeek = cal.get(Calendar.DAY_OF_WEEK);// 获得当前日期是一个星期的第几天
        if (1 == dayWeek) {
            cal.add(Calendar.DAY_OF_MONTH, -1);
        }
        cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
        cal.set(Calendar.HOUR_OF_DAY, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        Date date = cal.getTime();
        return date;
    }

    
    public static String getNextDay(String date, int day) {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        try {
            calendar.setTime(format.parse(date));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        calendar.add(Calendar.DAY_OF_MONTH, day);
        // Date date1 = calendar.getTime();
        Date time = calendar.getTime();
        return format.format(time);
    }

    public static Map getMonthRange(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        //获取某月最大天数
        int lastDay = calendar.getActualMaximum(Calendar.DATE);
        //设置日历中月份的最大天数
        calendar.set(Calendar.DAY_OF_MONTH, lastDay);
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        Date edtm = calendar.getTime();
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        Date bgtm = calendar.getTime();
        Map map = new HashMap<>();
        map.put("bgtm", bgtm);
        map.put("edtm", edtm);
        return map;
    }

    public static Date getMongoDate(Date date) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        cal.add(Calendar.HOUR_OF_DAY, 8);
        return cal.getTime();
    }

    public static String appendLastSecondOfDay(String yyyyMMdd) {
        return yyyyMMdd + " 23:59:59";
    }

    public static Date getEndTimeOfDay(String edtm) {
        return parseTime(appendLastSecondOfDay(edtm));
    }

}

public class ShardingUtils {

    public static TreeSet getSuffixListForRange(String lowerSuffix, String upperSuffix) {
        TreeSet suffixList = new TreeSet<>();

        if (lowerSuffix.equals(upperSuffix)) {
            //上下界在同一张表
            suffixList.add(lowerSuffix);
        } else {
            //上下界不在同一张表  计算间隔的所有表
            String tempSuffix = lowerSuffix;
            while (!tempSuffix.equals(upperSuffix)) {
                suffixList.add(tempSuffix);
                String[] ym = tempSuffix.split("_");
                Date tempDate = DateUtils.parse(ym[0] + (ym[1].length() == 1 ? "0" + ym[1] : ym[1]), "yyyyMM");
                Calendar cal = Calendar.getInstance();
                cal.setTime(tempDate);
                cal.add(Calendar.MONTH, 1);
                tempSuffix = ShardingUtils.getSuffixByYearMonth(cal.getTime());
            }
            suffixList.add(tempSuffix);
        }
        return suffixList;
    }

    public static HashSet getSuffixListForRange1(String lowerSuffix, String upperSuffix) {
        HashSet suffixList = new HashSet<>();
        if (lowerSuffix.equals(upperSuffix)) { //上下界在同一张表
            String[] split = upperSuffix.split("_");
            String year = split[0] ;
            String month = split[1] ;
            //按年月,分表  上半年-下半年
            StringBuffer tableSuffix = new StringBuffer();
            tableSuffix.append(year);
            tableSuffix.append("_");
            if(Integer.parseInt(month) > 6){
                tableSuffix.append(2);
            }else {
                tableSuffix.append(1);
            }
            suffixList.add(tableSuffix.toString());
        } else {  //上下界不在同一张表  计算间隔的所有表
            String tempSuffix = lowerSuffix;
            while (!tempSuffix.equals(upperSuffix)) {
                suffixList.add(tempSuffix);
                String[] ym = tempSuffix.split("_");
                Date tempDate = DateUtils.parse(ym[0] + (ym[1].length() == 1 ? "0" + ym[1] : ym[1]), "yyyyMM");
                Calendar cal = Calendar.getInstance();
                cal.setTime(tempDate);
                cal.add(Calendar.MONTH, 1);
                tempSuffix = ShardingUtils.getSuffixByYearMonth(cal.getTime());
            }
            String[] split = tempSuffix.split("_");
            String year = split[0] ;
            String month = split[1] ;
            //按年月,分表  上半年-下半年
            StringBuffer tableSuffix = new StringBuffer();
            tableSuffix.append(year);
            if(Integer.parseInt(month) > 6){
                tableSuffix.append(2);
                tableSuffix.append("_");
            }else {
                tableSuffix.append(1);
            }
            suffixList.add(tableSuffix.toString());
        }
        return suffixList;
    }

    public static String getSuffixByYearMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.YEAR) + "_" + (calendar.get(Calendar.MONTH) + 1);
    }

    public static String getPrevSuffix(String suffix) {
        if (StringUtils.isBlank(suffix)) {
            return getSuffixByYearMonth(new Date());
        }
        String[] arr = suffix.split("_");
        if ("1".equals(arr[1])) {
            return (Integer.valueOf(arr[0]) - 1) + "_12";
        } else {
            return arr[0] + "_" + (Integer.valueOf(arr[1]) - 1);
        }
    }

}