基于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: Standalone
    repository:
      type: File
      props:
        path: ./shardingsphere
  datasource:
    names: master,slave1,slave2
    # 主库配置：用于写操作
    master:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://service1:3311/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000
      username: root
      password: 123456
      minimum-idle: 5
      maximum-pool-size: 20
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
    # 从库1配置：用于读操作
    slave1:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://service2:3312/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000
      username: root
      password: 123456
      minimum-idle: 5
      maximum-pool-size: 20
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
    # 从库2配置：用于读操作
    slave2:
      type: com.zaxxer.hikari.HikariDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://service3:3313/test?useSSL=false&serverTimezone=Asia/Shanghai&failOverReadOnly=false&connectTimeout=10000&socketTimeout=60000
      username: root
      password: 123456
      minimum-idle: 5
      maximum-pool-size: 20
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
  rules:
    readwrite-splitting:
      data-sources:
        user-ds:
          write-data-source-name: master
          read-data-source-names: slave1,slave2
          load-balancer-name: random
      load-balancers:
        random:
          type: RANDOM
  props:
    sql-show: true
    sql-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: status
    logic-delete-value: 0
    logic-not-delete-value: 1

3.测试

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

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

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

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