Sharding JDBC分库分表及读写分离

1 引入POM

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>


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

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


         <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>

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

       <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.11</version>
        </dependency>

</dependencies>

2 分库分表配置

spring.shardingsphere.datasource.names=m1

spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://url:3306/info_center?characterEncoding=utf-8
spring.shardingsphere.datasource.m1.username=username
spring.shardingsphere.datasource.m1.password=password
#真实表分布，m1库的course_1或course_2的表
spring.shardingsphere.sharding.binding-tables=tb_user
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=m1.tb_user_$->{1..2}
#主键生成策略
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=cid
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE

#用表的cid列作为分片规则
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=cid
#cid列的值%2+1，要么等于1就选course_1表，等于2就选course_2表
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user_$->{cid%2+1}

spring.shardingsphere.props.sql.show = true
spring.main.allow-bean-definition-overriding=true

 

 

3 分库分表加读写分离

spring.shardingsphere.datasource.names=m0,s0

spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://url:3306/info_center?characterEncoding=utf-8
spring.shardingsphere.datasource.m0.username=username
spring.shardingsphere.datasource.m0.password=password

spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://url:3306/data-nut?characterEncoding=utf-8
spring.shardingsphere.datasource.s0.username=username
spring.shardingsphere.datasource.s0.password=password
#读写分离规则， m0 主库，s0 从库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names[0]=s0

#基于读写分离的表分片
#真实表分布，m1库的course_1或course_2的表
spring.shardingsphere.sharding.binding-tables=tb_user
spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=ds0.tb_user_$->{1..2}
#主键生成策略
spring.shardingsphere.sharding.tables.tb_user.key-generator.column=cid
spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE

#用表的cid列作为分片规则
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=cid
#cid列的值%2+1，要么等于1就选course_1表，等于2就选course_2表
spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user_$->{cid%2+1}

注：sharding-jdbc不支持主从同步，需要靠DB本身实现复制与同步

4 原理

读写分离原理：

与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据 SQL 语义的分析，将读操

作和写操作分别路由至主库与从库。

分库分表执行过程