sharding-jdbc分库分表的使用

1、数据分片

0. 效果：
   order_no为偶数存入库ds0，为奇数存入库ds1；user_id为偶数存入表t_order_0,奇数存入表t_order_1。

1. 引入maven依赖

   <dependency>
       <groupId>io.shardingsphere</groupId>
       <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
       <version>${shardingsphere.version}</version>
   </dependency>
   <!-- for spring namespace -->
   <dependency>
       <groupId>io.shardingsphere</groupId>
       <artifactId>sharding-jdbc-spring-namespace</artifactId>
       <version>${shardingsphere.version}</version>
   </dependency>
   

2. 创建数据库ds0，ds1，分别创建表t_order_0,t_order1。sql语句如下

   drop table if exists t_order_0;
   drop table if exists t_order_1;
   
   create table t_order_0 (
     id bigint primary key auto_increment,
     order_no int not null ,
     user_id int not null
   );
   
   create table t_order_1 (
     id bigint primary key auto_increment,
     order_no int not null ,
     user_id int not null
   );
   

3. 配置文件application-sharding.properties

   sharding.jdbc.datasource.names=ds0,ds1
   # 配置该数据源之后，无需配置springboot数据源
   sharding.jdbc.datasource.ds0.type=org.apache.commons.dbcp.BasicDataSource
   sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
   sharding.jdbc.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&useSSL=false
   sharding.jdbc.datasource.ds0.username=root
   sharding.jdbc.datasource.ds0.password=Huawei_123
   
   sharding.jdbc.datasource.ds1.type=org.apache.commons.dbcp.BasicDataSource
   sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
   sharding.jdbc.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&useSSL=false
   sharding.jdbc.datasource.ds1.username=root
   sharding.jdbc.datasource.ds1.password=Huawei_123
   
   # 分库策略
   sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=order_no
   # 分库算法表达式(order_no偶数存入库ds0，奇数存入ds1)
   sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{order_no % 2}
   # 分片表（ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1）
   sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{0..1}
   # 分表策略
   sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.sharding-column=user_id
   # 分表规则(user_id偶数存入表t_order_0,奇数存入t_order_1)
   sharding.jdbc.config.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{user_id % 2}
   # 主键生成列名
   sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=id
   

2、读写分离

1. server:
     port: 8080
   
   mybatis:
     mapper-locations: classpath:mapper/**/*Mapper.xml
     configuration:
       map-underscore-to-camel-case: true
   sharding:
     jdbc:
       datasource:
         names: db0,db1,db2
         db0:
           type: org.apache.commons.dbcp.BasicDataSource
           driverClassName: com.mysql.cj.jdbc.Driver
           url: jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&useAffectedRows=true
           username: root
           password: Huawei_123
         db1:
           type: org.apache.commons.dbcp.BasicDataSource
           driverClassName: com.mysql.cj.jdbc.Driver
           url: jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&useAffectedRows=true
           username: root
           password: Huawei_123
         db2:
           type: org.apache.commons.dbcp.BasicDataSource
           driverClassName: com.mysql.cj.jdbc.Driver
           url: jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&useAffectedRows=true
           username: root
           password: Huawei_123
       config:
         masterslave:
           name: ds_ms
           master-data-source-name: db1
           slave-data-source-names: db0,db2
           load-balance-algorithm-type: round_robin
         props:
           sql.show: true
   

官网https://shardingsphere.apache.org/document/legacy/3.x/document/cn/overview/