Spring-boot + Sharding-jdbc 读写分离 MySQL在现代应用中，随着数据量的增长和并发请求的增

本文源代码 -> Github | Gitee

前言

在现代应用中，随着数据量的增长和并发请求的增加，数据库的读写分离成为一种常用的架构模式。通过将读操作和写操作分离到不同的数据库实例，可以有效提高系统性能和可扩展性。本篇博文将介绍如何使用 Spring Boot 和 Sharding-JDBC 实现 MySQL 数据库的读写分离，从而优化数据访问效率。

环境

mysql 8.0.39
Java 8
springboot 2.7.18

mysql集群搭建

搭建一主二从MySQL集群

编写docker-compose.yml文件。

version: '2.1'

services:
  mysql_master:
    image: bitnami/mysql:8.0.39
    container_name: mysql_master
    restart: always
    ports:
      - "3310:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=Password@2024
      - MYSQL_REPLICATION_MODE=master
      - MYSQL_REPLICATION_USER=repl_user
      - MYSQL_REPLICATION_PASSWORD=Password@2024
      - MYSQL_USER=lbs
      - MYSQL_PASSWORD=Password@2024
      - MYSQL_DATABASE=test
      - TZ=Asia/Shanghai
    volumes:
      - master_data:/bitnami/mysql/data
      - master_logs:/opt/bitnami/mysql/logs
    mem_limit: 8g

  mysql_slave1:
    image: bitnami/mysql:8.0.39
    container_name: mysql_slave1
    restart: always
    ports:
      - "3311:3306"
    environment:
      - MYSQL_REPLICATION_MODE=slave
      - MYSQL_REPLICATION_USER=repl_user
      - MYSQL_REPLICATION_PASSWORD=Password@2024
      - MYSQL_MASTER_HOST=mysql_master
      - MYSQL_MASTER_PORT_NUMBER=3306
      - MYSQL_MASTER_ROOT_PASSWORD=Password@2024
      - TZ=Asia/Shanghai
    volumes:
      - slave1_data:/bitnami/mysql/data
      - slave1_logs:/opt/bitnami/mysql/logs
    mem_limit: 8g

  mysql_slave2:
    image: bitnami/mysql:8.0.39
    container_name: mysql_slave2
    restart: always
    ports:
      - "3312:3306"
    environment:
      - MYSQL_REPLICATION_MODE=slave
      - MYSQL_REPLICATION_USER=repl_user
      - MYSQL_REPLICATION_PASSWORD=Password@2024
      - MYSQL_MASTER_HOST=mysql_master
      - MYSQL_MASTER_PORT_NUMBER=3306
      - MYSQL_MASTER_ROOT_PASSWORD=Password@2024
      - TZ=Asia/Shanghai
    volumes:
      - slave2_data:/bitnami/mysql/data
      - slave2_logs:/opt/bitnami/mysql/logs
    mem_limit: 8g

volumes:
  master_data:
  slave1_data:
  slave2_data:
  master_logs:
  slave1_logs:
  slave2_logs:

执行下面命令启动mysql集群

docker-compose up -d

使用lbs用户登陆主节点，执行下面sql，创建表。

CREATE TABLE `users`
(
    `id`       bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户编号',
    `username` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
    PRIMARY KEY (`id`),
    UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into users values (1, 'lbs','lbs@2024');

代码编写

通过Sharding-jdbc实现写操作访问主库，读操作访问从库

编写pom.xml配置文件

<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>

    <groupId>com.liboshuai.starlink.demo</groupId>
    <artifactId>sharding-read-write-separation</artifactId>
    <packaging>jar</packaging>
    <version>1.0</version>
    <name>${artifactId}</name>

    <properties>
        <spring.boot.version>2.7.18</spring.boot.version>
        <lombok.version>1.18.34</lombok.version>
        <knife4j.version>4.4.0</knife4j.version>
        <mybatis-plus.version>3.5.7</mybatis-plus.version>
        <druid.version>1.2.23</druid.version>
        <starding-jdbc.version>4.1.1</starding-jdbc.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring.boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <!--springboot-web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--springboot配置提示-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
        </dependency>
        <!--knife4j-->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-openapi2-spring-boot-starter</artifactId>
            <version>${knife4j.version}</version>
        </dependency>
        <!-- mysql-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
        <!--sharding jdbc引入-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${starding-jdbc.version}</version>
        </dependency>
    </dependencies>
</project>

编写application.yml配置文件

server:
  port: 8888
spring:
  shardingsphere:
    props:
      sql:
        #开启SQL显示，默认false
        show: true
    sharding:
      default-data-source-name: master
    masterslave:
      # 从库负载均衡算法，可选值为：round_robin 和 random
      load-balance-algorithm-type: round_robin
      # 最终的数据源名称（可以随便指定）
      name: ds
      # 主库数据源名称
      master-data-source-name: master
      # 从库数据源名称列表，多个逗号分隔
      slave-data-source-names: slave1,slave2
    datasource:
      # 数据源名称，以英文逗号分隔，需要跟下面的每个数据源配置对应上
      names: master,slave1,slave2
      # 主库连接信息
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://rocky:3310/test?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true # MySQL Connector/J 8.X 连接的示例
        username: lbs
        password: Password@2024
        initial-size: 5 # 初始连接数
        min-idle: 10 # 最小连接池数量
        max-active: 20 # 最大连接池数量
        max-wait: 600000 # 配置获取连接等待超时的时间，单位：毫秒
      # 从库连接信息
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://rocky:3311/test?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true # MySQL Connector/J 8.X 连接的示例
        username: lbs
        password: Password@2024
        initial-size: 5 # 初始连接数
        min-idle: 10 # 最小连接池数量
        max-active: 20 # 最大连接池数量
        max-wait: 600000 # 配置获取连接等待超时的时间，单位：毫秒
      # 从库连接信息
      slave2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://rocky:3312/test?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true # MySQL Connector/J 8.X 连接的示例
        username: lbs
        password: Password@2024
        initial-size: 5 # 初始连接数
        min-idle: 10 # 最小连接池数量
        max-active: 20 # 最大连接池数量
        max-wait: 600000 # 配置获取连接等待超时的时间，单位：毫秒
mybatis-plus:
  configuration:
    #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: ASSIGN_ID

knife4j:
  enable: true
  openapi:
    title: sharding读写分离
    description: sharding读写分离演示相关文档
    email: liboshuai01@gmail.com
    concat: liboshuaiwechat
    url: https://liboshuai.com
    version: v4.0
    license: Apache 2.0
    license-url: https://stackoverflow.com/
    terms-of-service-url: https://stackoverflow.com/
    group:
      excel:
        group-name: excel
        api-rule: package
        api-rule-resources:
          - com.liboshuai.starlink.demo.sharding

编写启动类

@SpringBootApplication
@MapperScan(basePackages = "com.liboshuai.starlink.demo.sharding.dao")
public class ShardingApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingApplication.class, args);
    }
}

编写实体类

@Data
@TableName("users")
public class UserEntity {
    @TableId
    private Long id;
    @TableField
    private String username;
    @TableField
    private String password;
}

编写dao类

public interface UserDAO extends BaseMapper<UserEntity> {
}

编写service类

public interface UserService {
    void hello();
}

编写service实现类

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserDAO userDAO;

    @Override
    public void hello() {
        // 查询从库中的用户信息（两个从库轮训其中一个）
        LambdaQueryWrapper<UserEntity> queryWrapper = new LambdaQueryWrapper<UserEntity>().eq(UserEntity::getUsername, "lbs");
        UserEntity userEntity = userDAO.selectOne(queryWrapper);
        System.out.println("userEntity = " + userEntity);
        // 更新数据到主库
        userEntity.setPassword("liboshuai@2024");
        userDAO.updateById(userEntity);
        // 查询从库中的用户信息（两个从库轮训其中一个）
        UserEntity newUserEntity = userDAO.selectOne(queryWrapper);
        System.out.println("newUserEntity = " + newUserEntity);
    }
}

编写controller类

@RestController
@RequestMapping("/user")
public class UserController {

    @Resource
    private UserService userService;

    @GetMapping("/hello")
    public String hello() {
        userService.hello();
        return "hello";
    }
}

启动项目

测试

请求http://localhost:8888/user/hello接口
观察日志输出

结语

通过本文的示例，我们成功搭建了一个基于 Docker 的 MySQL 集群，并实现了 Spring Boot 应用的读写分离。Sharding-JDBC 的集成使得配置简单且灵活，能够轻松应对复杂的数据库访问需求。希望这篇文章能够为你的项目提供参考，让你的应用在高并发场景下更具弹性和性能。