分库分表 springboot+dubbo+mybatisPlus+shardingSphere

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 Spring Boot Starter -->
        <dependency>
            <groupId>${dubbo.groupId}</groupId>
            <artifactId>dubbo-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>${dubbo.groupId}</groupId>
            <artifactId>dubbo</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.zookeeper</groupId>
            <artifactId>zookeeper</artifactId>
        </dependency>
        <dependency>
            <groupId>${dubbo.groupId}</groupId>
            <artifactId>dubbo-configcenter-zookeeper</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>objenesis</artifactId>
                    <groupId>org.objenesis</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>${dubbo.groupId}</groupId>
            <artifactId>dubbo-rpc-rest</artifactId>
            <exclusions>
                <exclusion>
                    <artifactId>jetty-server</artifactId>
                    <groupId>org.eclipse.jetty</groupId>
                </exclusion>
                <exclusion>
                    <artifactId>jetty-servlet</artifactId>
                    <groupId>org.eclipse.jetty</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-logging-juli</artifactId>
        </dependency>

        <dependency>
            <groupId>javax.el</groupId>
            <artifactId>javax.el-api</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.glassfish.web</groupId>
            <artifactId>javax.el</artifactId>
            <version>2.2.6</version>
            <exclusions>
                <exclusion>
                    <artifactId>javax.el-api</artifactId>
                    <groupId>javax.el</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-log4j2</artifactId>
            <version>2.1.4.RELEASE</version>
        </dependency>

        <!-- MyBatis Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.1.5.RELEASE</version>
            <scope>test</scope>
        </dependency>
        
       <!--sharding jdbc springboot-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.0</version>
            <!--<version>4.0.0-RC2</version>-->
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>4.1.0</version>
            <!--<version>4.0.0-RC2</version>-->
        </dependency>

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<Date> {
    /**
     * 按创建时间分库
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        Date value = preciseShardingValue.getValue();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String dataSource = "ds" + sdf.format(value);
        System.out.println("switch datasource:" + dataSource);
        return dataSource;
    }
}