基于Sharding-JDBC的读写分离

1.整体架构

2.详细配置

2.1 pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.18</version><relativePath/></parent><groupId>com.example</groupId><artifactId>my-springboot-project</artifactId><version>1.0.0</version><name>my-springboot-project</name><description>Spring Boot project with MyBatis Plus and ShardingJDBC</description><properties><java.version>17</java.version><mybatis-plus.version>3.5.3.1</mybatis-plus.version><maven.compiler.source>17</maven.compiler.source><maven.compiler.target>17</maven.compiler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><repositories><repository><id>maven-central</id><url>https://repo.maven.apache.org/maven2</url></repository><repository><id>spring-milestones</id><name>Spring Milestones</name><url>https://repo.spring.io/milestone</url></repository></repositories><dependencies><!-- spring boot 相关配置 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- db相关配置 --><!-- mysql connector --><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.0.33</version></dependency><!-- mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatis-plus.version}</version></dependency><!-- shrding jdbc --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId><version>5.0.0</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build>
</project>

2.2 yml文件

# Spring Boot配置
spring:
application:name: my-springboot-project
# ShardingSphere读写分离配置：一主两从
shardingsphere:mode:type: Standalonerepository:type: Fileprops:path: ./shardingspheredatasource:names: master,slave1,slave2# 主库配置：用于写操作master:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://service1:3311/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000username: rootpassword: 123456minimum-idle: 5maximum-pool-size: 20connection-timeout: 30000idle-timeout: 600000max-lifetime: 1800000# 从库1配置：用于读操作slave1:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://service2:3312/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000username: rootpassword: 123456minimum-idle: 5maximum-pool-size: 20connection-timeout: 30000idle-timeout: 600000max-lifetime: 1800000# 从库2配置：用于读操作slave2:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://service3:3313/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000username: rootpassword: 123456minimum-idle: 5maximum-pool-size: 20connection-timeout: 30000idle-timeout: 600000max-lifetime: 1800000rules:readwrite-splitting:data-sources:user-ds:write-data-source-name: masterread-data-source-names: slave1,slave2load-balancer-name: randomload-balancers:random:type: RANDOMprops:sql-show: truesql-simple: false
logging:level:root: INFO# MyBatis Plus配置
mybatis-plus:
configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.example.entity
global-config:db-config:logic-delete-field: statuslogic-delete-value: 0logic-not-delete-value: 1

3.测试

执行单一读操作，走从库。

执行单一写操作，走主库。

MySQL所有语句均以事务为单位执行。
一个事务中，包含任一写操作，走主库。
一个事务中，没有任何写操作，走从库。

例如
读写操作，包含在同一个事务中，这个事务存在写操作，整个事务的读写都走主库。
读写操作，未包含在同一个事务中，读写都是单独的事务，读走从库，写走主库。