Mysql分库分表+读写分离（SpringBoot2 + Druid + MybatisPlus + Sharding-JDBC）

开发环境：SpringBoot2 + Druid + MybatisPlus + Sharding-JDBC

一、引入jar包

<!-- Sharding-JDBC for spring boot -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

<!-- Sharding-JDBC for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

<!-- Druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.18</version>
    <scope>compile</scope>
</dependency>

<!--Mybatis plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.2</version>
</dependency>

<!--mysql connector-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.16</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

二、配置文件

分库分表+读写分离：

我们的目标是将user表，按照其中的id字段进行分库，然后按照其中的age字段进行分表。

spring:
  shardingsphere:
    # 定义数据源
    datasource:
      names: master0,slave0,master1,slave1
      master0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.150.27.45:3306/master
        username: test_yinni
        password: test_yinni
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.150.27.45:3306/slave
        username: test_yinni
        password: test_yinni
      master1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.150.27.45:3306/master1
        username: test_yinni
        password: test_yinni
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://10.150.27.45:3306/slave1
        username: test_yinni
        password: test_yinni
    # 定义分库分表策略
    sharding:
      # 按照id字段进行分库
      default-database-strategy:
        inline:
          sharding-column: id
          algorithm-expression: ds$->{id % 2}
      tables:
        user:
          # 实际上的表节点的表达式
          actual-data-nodes: ds$->{0..1}.user$->{0..1}
          # 主键生成策略
          key-generator:
            column: id
            type: SNOWFLAKE
          # 按照age字段，进行分表
          table-strategy:
            inline:
              sharding-column: age
              algorithm-expression: user$->{age % 2}
      # 广播表
      # broadcast-tables:
      # 绑定表
      # binding-tables:
      master-slave-rules:
        ds0:
          master-data-source-name: master0
          slave-data-source-names: slave0
        ds1:
          master-data-source-name: master1
          slave-data-source-names: slave1

三、测试结果

参考我们前面分库分表的测试和读写分离的测试，经过本地测试，可以达到我们想要的效果。