Springboot 整合ShardingJdbc实现分库分表逻辑

        最近需要处理一月1000多万的数据，全部数据都需要随时 保证可参与业务运算，也就是没办法将数据做归档了，因为每天都需要用数据处理相关业务，所以和同事一起搞了个分表分库，原来没有太多接触过这块，如果有问题及其他风险，请大神们指出。

pom文件新增： 

      <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
  
         <dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-core-common</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-namespace</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>

数据源及分库分表配置文件：

  shardingsphere:
    datasource:
      names: ds0,ds2020,ds2021,ds2022
      ds0: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.120:3306/test_base?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        initialSize: 5
        minIdle: 10
        maxActive: 20 
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000 
        minEvictableIdleTimeMillis: 300000
        maxEvictableIdleTimeMillis: 900000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        filter:
          stat:
            enabled: true
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
      ds2020: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.120:3306/test_2020?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        initialSize: 5 
        minIdle: 10 
        maxActive: 20 
        maxWait: 60000 
        timeBetweenEvictionRunsMillis: 60000 
        minEvictableIdleTimeMillis: 300000
        maxEvictableIdleTimeMillis: 900000 
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        filter:
          stat:
            enabled: true
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
      ds2021: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.120:3306/test_2021?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        initialSize: 5
        minIdle: 10
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        maxEvictableIdleTimeMillis: 900000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        filter:
          stat:
            enabled: true
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
      ds2022: 
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.1.120:3306/test_2022?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
        username: root
        password: 123456
        initialSize: 5
        minIdle: 10 
        maxActive: 20 
        maxWait: 60000 
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000 
        maxEvictableIdleTimeMillis: 900000 
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        filter:
          stat:
            enabled: true
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
    sharding:
      default-data-source-name: ds0
      tables:
        tb_test_item:
          actual-data-nodes: ds$->{2020..2022}.tb_test_item
          database-strategy:
            inline:
              sharding-column: batch
              algorithm-expression: ds$->{batch}
        tb_intermediate:
          key-generator:
             column: id 
             type: SNOWFLAKE
          actual-data-nodes: ds$->{2020..2022}.tb_test_$->{1..12}
          database-strategy:
            standard:
              sharding-column: create_date
              precise-algorithm-class-name: com.org.test.sharding.DbPreciseShardingAlgorithm
          table-strategy:
            standard:
              sharding-column: create_date
              precise-algorithm-class-name: com.org.test.sharding.TbPreciseShardingAlgorithm

分库分表逻辑：

/**
 * 分库策略
 * @author  
 *
 */
@Slf4j
public class DbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
    	String transDate = DateUtils.parseDateToStr(DateUtils.YYYYMMDD, shardingValue.getValue());
    	String db = transDate.substring(0, 4);
        for (String dbName : availableTargetNames) {
            if (dbName.endsWith(db)) {
                return dbName;
            }
        }
        throw new NoDbTbException("根据分库字段未找到符合规则的库");
    }
}

分表策略

/**
 * 分表策略
 *
 */
@Slf4j
public class TbPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {
    	String transDate = DateUtils.parseDateToStr(DateUtils.YYYYMMDD, shardingValue.getValue());
    	String tb = transDate.substring(4,6);
    	if(tb.startsWith("0")) {
    		tb = tb.replaceFirst("0", "");
    	}
        for (String tbName : availableTargetNames) {
            if (tbName.endsWith(tb)) {
                return tbName;
            }
        }
        throw new NoDbTbException("根据分表字段未找到符合规则的表");
    }
}