1、使用的框架有

springboot 2.1.1 + apache dubbo 2.7.2+ mybatisPlus 3.1.0 +shardingSphere 4.1.0

数据库连接池：HikariDataSource

jdbc驱动：mysql-connector-java-6.0.6.jar

2、分库分表方案：

分库：根据create_time字段切换不同的数据源big-data或者big-data-2021

分表：根据school_id字段取模，数据保存到student_analysis_0~5的表中

建表SQL语句：

CREATE TABLE `student_analysis_0` (

  `ID` bigint(20) NOT NULL,

  `SCHOOL_ID` bigint(20) NOT NULL COMMENT '学校id',

  `CREATE_TIME` datetime NOT NULL COMMENT '创建时间',

  `UPDATE_TIME` datetime NOT NULL COMMENT '更新时间',

  `CREATE_USER_ID` bigint(20) NOT NULL COMMENT '创建用户主键',

  `UPDATE_USER_ID` bigint(20) NOT NULL COMMENT '更新用户主键',

  `STATUS` int(1) NOT NULL COMMENT '状态 -1:删除,0:停用，1-启用',

  PRIMARY KEY (`ID`)

) ;

3、maven依赖 pom.xml文件：

        
        
            ${dubbo.groupId}
            dubbo-spring-boot-starter
        
        
            ${dubbo.groupId}
            dubbo
        
        
            org.apache.zookeeper
            zookeeper
        
        
            ${dubbo.groupId}
            dubbo-configcenter-zookeeper
            
                
                    objenesis
                    org.objenesis
                
            
        

        
            ${dubbo.groupId}
            dubbo-rpc-rest
            
                
                    jetty-server
                    org.eclipse.jetty
                
                
                    jetty-servlet
                    org.eclipse.jetty
                
            
        

        
            org.apache.tomcat.embed
            tomcat-embed-logging-juli
        

        
            javax.el
            javax.el-api
            3.0.0
        
        
            org.glassfish.web
            javax.el
            2.2.6
            
                
                    javax.el-api
                    javax.el
                
            
        

        
            org.springframework.boot
            spring-boot-starter-log4j2
            2.1.4.RELEASE
        

        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.1.0
        

        
            mysql
            mysql-connector-java
            6.0.6
        
        
            org.springframework.boot
            spring-boot-starter-test
            2.1.5.RELEASE
            test
        
        
       
        
            org.apache.shardingsphere
            sharding-jdbc-spring-boot-starter
            4.1.0
            
        

        
            org.apache.shardingsphere
            sharding-jdbc-spring-namespace
            4.1.0
            
        

4、application.yml配置：

spring:
  shardingsphere:
    datasource:
      # 数据库名称，多个以逗号隔开
      names: ds2020,ds2021
      ds2020:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=true
        username: ${database.mysql.username}
        password: ${database.mysql.password}
        minimum-idle: 1
        maximum-pool-size: 5
        connection-test-query: SELECT 1
        connection-timeout: 6000
      ds2021:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data-2021?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=true
        username: ${database.mysql.username}
        password: ${database.mysql.password}
        minimum-idle: 1
        maximum-pool-size: 5
        connection-test-query: SELECT 1
        connection-timeout: 6000

    sharding:
      #默认库
      default-data-source-name: ds2020
      tables:
        student_analysis:
          #物理表的结点,下面代表的是ds2020.student_analysis_0..10、ds2021.student_analysis_0..1
          actual-data-nodes: ds$->{2020..2021}.student_analysis_$->{0..4}
          #分库策略，按照创建时间的年份分库，如果不用分库的，直接注释掉分库相关的代码
          database-strategy:
            standard:
              sharding-column: create_time
              precise-algorithm-class-name: com.auge.big.data.sharding.CreateTimeShardingDatabaseAlgorithm
          table-strategy:
            #分表策略，根据school_id字段的值模10
            inline:
              sharding-column: school_id
              algorithm-expression: student_analysis_$->{school_id % 5}
    props:
      #是否打印逻辑SQL语句和实际SQL语句，建议调试时打印，在生产环境关闭
      sql:
        show: true

5、分库策略自定义算法类

/**
 * 分库策略自定义算法。
 */
public class CreateTimeShardingDatabaseAlgorithm implements PreciseShardingAlgorithm {
    /**
     * 按创建时间分库
     */
    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        Date value = preciseShardingValue.getValue();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String dataSource = "ds" + sdf.format(value);
        System.out.println("switch datasource:" + dataSource);
        return dataSource;
    }
}