SpringBoot 2.6 集成 MySQL 多数据源读写分离

1 摘要

为了缓解数据库的压力，在高并发系统中常采用数据库读写分离的模式。本文将介绍基于 SpringBoot 2.6 集成 MySQL 实现读写分离的功能。

java程序集成数据库读写分离基本原理图:

准备工作:

MySQL 数据库主从集群搭建

2 核心 Maven 依赖

./demo-read-write/pom.xml

        <!-- web,mvc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <!-- aop -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <!-- Validation -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <!-- mybatis plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatisplus.version}</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-core</artifactId>
            <version>${hutool.version}</version>
        </dependency>

依赖版本

        <springboot.version>2.6.3</springboot.version>
        <mysql.version>8.0.28</mysql.version>
        <mybatisplus.version>3.5.1</mybatisplus.version>
        <hutool.version>5.7.21</hutool.version>

3 数据源配置

./demo-read-write/src/main/resources/application.yml

## spring
spring:
  application:
    name: demo-read-write
  datasource:
    hikari:
      driver-class-name: com.mysql.cj.jdbc.Driver
    master:
      jdbc-url: "jdbc:mysql://192.168.1.11:3306/demo?useUnicode=true&characterEncoding=utf8\
        &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
        &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true\
        &nullCatalogMeansCurrent=true"
      username: root
      password: "Qwert12345!"
    slave:
      jdbc-url: "jdbc:mysql://192.168.1.12:3306/demo?useUnicode=true&characterEncoding=utf8\
        &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
        &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true\
        &nullCatalogMeansCurrent=true"
      username: read_user
      password: "12345!Qwert"

## mybatis-plus
mybatis-plus:
  mapper-locations: classpath*:mybatis/*.xml
  type-aliases-package: com.ljq.demo.springboot.readwrite.model.entity

4 核心代码

4.1 数据源配置类

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/common/config/DataSourceConfig.java

package com.ljq.demo.springboot.readwrite.common.config;

import com.ljq.demo.springboot.readwrite.common.constant.DataSourceConst;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description: 数据源配置
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
@Slf4j
@Configuration
public class DataSourceConfig {


    /**
     * 主数据源
     *
     * @return
     */
    @Bean(DataSourceConst.DATASOURCE_MASTER)
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        log.info("init master datasource");
        return DataSourceBuilder.create().build();
    }

    /**
     * 从数据源
     * @return
     */
    @Bean(DataSourceConst.DATASOURCE_SLAVE)
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        log.info("init slave datasource");
        return DataSourceBuilder.create().build();
    }

    /**
     * 主数据源
     *
     * @param masterDataSource
     * @param slaveDataSource
     * @return
     */
    @Bean
    @Primary
    DataSource primaryDataSource(@Qualifier(DataSourceConst.DATASOURCE_MASTER) DataSource masterDataSource,
                                 @Qualifier(DataSourceConst.DATASOURCE_SLAVE) DataSource slaveDataSource) {
        log.info("init datasource routing");
        Map<Object, Object> map = new HashMap<>(8);
        map.put(DataSourceConst.DATASOURCE_MASTER, masterDataSource);
        map.put(DataSourceConst.DATASOURCE_SLAVE, slaveDataSource);
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(map);
        routingDataSource.setDefaultTargetDataSource(masterDataSource);
        return routingDataSource;
    }

}

数据源常量类

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/common/constant/DataSourceConst.java

package com.ljq.demo.springboot.readwrite.common.constant;

/**
 * @Description: 数据源常量
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
public class DataSourceConst {

    private DataSourceConst() {
    }

    /**
     * 数据源 bean 名称/key
     * masterDataSource: 主数据源
     * slaveDataSource: 从数据源
     */
    public static final String DATASOURCE_MASTER = "masterDataSource";
    public static final String DATASOURCE_SLAVE = "slaveDataSource";

}

4.2 路由数据源

用于动态切换数据源

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/common/config/RoutingDataSource.java

package com.ljq.demo.springboot.readwrite.common.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @Description: 路由数据库
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
public class RoutingDataSource extends AbstractRoutingDataSource {


    @Override
    protected Object determineCurrentLookupKey() {
        return RoutingDataSourceContext.get();
    }
}

4.3 保存数据源 key 的本地线程类

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/common/config/RoutingDataSourceContext.java

package com.ljq.demo.springboot.readwrite.common.config;

import com.ljq.demo.springboot.readwrite.common.constant.DataSourceConst;
import lombok.extern.slf4j.Slf4j;

/**
 * @Description: 路由数据源路由上下文
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
@Slf4j
public class RoutingDataSourceContext {

    private RoutingDataSourceContext(){
    }

    private static final ThreadLocal<String> threadDataSourceKey = new ThreadLocal<>();

    /**
     * 设置数据源 key
     *
     * @param key
     */
    public static void set(String key) {
        threadDataSourceKey.set(key);
    }

    /**
     * 获取数据源 key
     *
     * @return
     */
    public static String get() {
        log.info("thread local datasource key:{}", threadDataSourceKey.get());
        return threadDataSourceKey.get() == null ? DataSourceConst.DATASOURCE_MASTER : threadDataSourceKey.get();
    }

    /**
     * 清理数据源 key
     */
    public static void close() {
        threadDataSourceKey.remove();
    }

}

代码说明: Tomcat 的用于处理数据请求的线程模型为一个线程池，每个线程处理一个请求。因此使用 ThreadLocal 来保存数据源的 Key 能够实现每一次请求都能够动态切换数据源。

4.4 数据源路由注解

该注解用于指定所使用的数据源

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/common/annotation/DataSourceRouting.java

package com.ljq.demo.springboot.readwrite.common.annotation;

import com.ljq.demo.springboot.readwrite.common.constant.DataSourceConst;

import java.lang.annotation.*;

/**
 * @Description: 数据源路由注解
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceRouting {

    String value() default DataSourceConst.DATASOURCE_MASTER;

}

4.5 数据源动态切换AoP

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/interceptor/DataSourceAop.java

package com.ljq.demo.springboot.readwrite.interceptor;

import com.ljq.demo.springboot.readwrite.common.annotation.DataSourceRouting;
import com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

/**
 * @Description: 数据源切换AOP
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
@Slf4j
@Aspect
@Order(1)
@Component
public class DataSourceAop {

    /**
     * 设置动态数据源
     *
     * @param joinPoint
     * @return
     * @throws Throwable
     */
    @Around("@annotation(com.ljq.demo.springboot.readwrite.common.annotation.DataSourceRouting)")
    public Object setDynamicDataSource(ProceedingJoinPoint joinPoint) throws Throwable {
        Method method = ((MethodSignature)joinPoint.getSignature()).getMethod();
        DataSourceRouting dataSourceRouting = method.getAnnotation(DataSourceRouting.class);
        try {
            RoutingDataSourceContext.set(dataSourceRouting.value());
            log.info("current request datasource: {}", RoutingDataSourceContext.get());
            return joinPoint.proceed();
        } finally {
            RoutingDataSourceContext.close();
        }
    }
}

代码说明: 拦截使用数据源路由的注解，读取注解中指定的数据源 Key 然后指定使用对应的数据源进行数据业务的处理。在每次执行完成之后，清除本地线程中的变量，一方面为了避免内存泄漏，另一方面也是因为 Tomcat 的线程模型(线程池)，Tomcat 的线程可能会重复使用，如果未及时清理，在下次线程使用的时候会导致数据源错误。

(bug 复现: 不清理本地线程缓存，设置两个接口，一个手动指定为从库数据源，执行查询操作，另一个不指定数据源(默认为主库)，执行 insert/update 操作，多次交叉请求这两个接口，则会抛出异常，执行 insert/update 操作的接口会使用从库数据源)

4.6 读写分离注解使用实践–Controller

./demo-read-write/src/main/java/com/ljq/demo/springboot/readwrite/controller/DataSourceController.java

package com.ljq.demo.springboot.readwrite.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import com.ljq.demo.springboot.readwrite.common.annotation.DataSourceRouting;
import com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext;
import com.ljq.demo.springboot.readwrite.common.constant.DataSourceConst;
import com.ljq.demo.springboot.readwrite.dao.UserDao;
import com.ljq.demo.springboot.readwrite.model.entity.UserEntity;
import com.ljq.demo.springboot.readwrite.model.param.UserSaveParam;
import com.ljq.demo.springboot.readwrite.model.param.UserUpdateParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

/**
 * @Description: 数据源控制层
 * @Author: junqiang.lu
 * @Date: 2022/2/14
 */
@Slf4j
@RestController
@RequestMapping("/api/springboot/datasource")
public class DataSourceController {

    @Qualifier("userDao")
    @Autowired
    private UserDao userDao;

    /**
     * 新增单条
     *
     * @param saveParam
     * @return
     */
    @PostMapping("/add")
    public ResponseEntity add(@RequestBody UserSaveParam saveParam) {
        log.info("\n requestPath: {}, \n param: {}", "/api/springboot/datasource/add", saveParam);
        log.info("current datasource: {}", RoutingDataSourceContext.get());
        UserEntity userEntity = new UserEntity();
        BeanUtil.copyProperties(saveParam, userEntity, CopyOptions.create().ignoreError().ignoreNullValue());
        userDao.insert(userEntity);
        return ResponseEntity.ok(userEntity);
    }

    /**
     * 查询单条
     *
     * @param id
     * @return
     */
    @GetMapping("/query/one")
    @DataSourceRouting(value = DataSourceConst.DATASOURCE_SLAVE)
    public ResponseEntity queryOne(Long id) {
        log.info("\n requestPath: {}, \n param: {}", "/api/springboot/datasource/query/one", id);
        log.info("current datasource: {}", RoutingDataSourceContext.get());
        UserEntity userEntity = userDao.selectById(id);
        return ResponseEntity.ok(userEntity);
    }

    /**
     * 更新单条
     *
     * @param updateParam
     * @return
     */
    @PutMapping("/update")
    @DataSourceRouting(DataSourceConst.DATASOURCE_MASTER)
    public ResponseEntity update(@RequestBody UserUpdateParam updateParam) {
        log.info("\n requestPath: {}, \n param: {}", "/api/springboot/datasource/update", updateParam);
        log.info("current datasource: {}", RoutingDataSourceContext.get());
        UserEntity userEntity = new UserEntity();
        BeanUtil.copyProperties(updateParam, userEntity, CopyOptions.create().ignoreError().ignoreNullValue());
        userDao.updateById(userEntity);
        return ResponseEntity.ok(userEntity);
    }

}

5 测试

5.1 不指定数据源

预期: 默认会走主数据源

请求接口: 新增接口

请求方式: POST

http://127.0.0.1:8850/api/springboot/datasource/add

请求参数:

{
    "userName": "Julian Howe",
    "userPasscode": "q8LM1MYmxHpMbJM",
    "userEmail": "Timmy23@yahoo.com"
}

后台日志:

2022-02-15 11:34:21 | INFO  | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 40| 
 requestPath: /api/springboot/datasource/add, 
 param: UserSaveParam(userName=Julian Howe, userPasscode=q8LM1MYmxHpMbJM, userEmail=Timmy23@yahoo.com)
2022-02-15 11:34:21 | INFO  | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:null
2022-02-15 11:34:21 | INFO  | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 41| current datasource: masterDataSource
2022-02-15 11:34:22 | INFO  | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:null
2022-02-15 11:34:22 | INFO  | http-nio-8850-exec-1 | com.zaxxer.hikari.HikariDataSource 110| HikariPool-1 - Starting...
2022-02-15 11:34:22 | INFO  | http-nio-8850-exec-1 | com.zaxxer.hikari.HikariDataSource 123| HikariPool-1 - Start completed.
2022-02-15 11:34:22 | DEBUG | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.dao.UserDao.insert 137| ==>  Preparing: INSERT INTO user ( user_name, user_passcode, user_email ) VALUES ( ?, ?, ? )
2022-02-15 11:34:22 | DEBUG | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.dao.UserDao.insert 137| ==> Parameters: Julian Howe(String), q8LM1MYmxHpMbJM(String), Timmy23@yahoo.com(String)
2022-02-15 11:34:22 | DEBUG | http-nio-8850-exec-1 | com.ljq.demo.springboot.readwrite.dao.UserDao.insert 137| <==    Updates: 1

结果分析: 从日志中可以看出数据处理走的是主数据源

关键日志:

current datasource: masterDataSource

5.2 指定从库数据源

预期: 走从库数据源

请求接口: 查询接口

请求方式: GET

http://127.0.0.1:8850/api/springboot/datasource/query/one?id=50

后台日志:

2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:slaveDataSource
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.interceptor.DataSourceAop 39| current request datasource: slaveDataSource
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 57| 
 requestPath: /api/springboot/datasource/query/one, 
 param: 50
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:slaveDataSource
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 58| current datasource: slaveDataSource
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:slaveDataSource
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.zaxxer.hikari.HikariDataSource 110| HikariPool-2 - Starting...
2022-02-15 11:38:51 | INFO  | http-nio-8850-exec-3 | com.zaxxer.hikari.HikariDataSource 123| HikariPool-2 - Start completed.
2022-02-15 11:38:51 | DEBUG | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.dao.UserDao.selectById 137| ==>  Preparing: SELECT id,user_name,user_passcode,user_email,user_insert_time,user_update_time,user_status,user_version,user_del FROM user WHERE id=? AND user_del=0
2022-02-15 11:38:51 | DEBUG | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.dao.UserDao.selectById 137| ==> Parameters: 50(Long)
2022-02-15 11:38:52 | DEBUG | http-nio-8850-exec-3 | com.ljq.demo.springboot.readwrite.dao.UserDao.selectById 137| <==      Total: 1

结果分析: 从日志中可以看出，查询接口走的是从库数据源

关键日志:

current datasource: slaveDataSource

5.3 指定主库数据源

预期: 走主库数据源

请求接口: 更新接口

请求方式: PUT

http://127.0.0.1:8850/api/springboot/datasource/update

请求参数:

{
    "id": 40,
    "userName": "Boyd Herzog",
    "userPasscode": "SVz04Ng7KmzGhSu",
    "userEmail": "Selmer.Renner@gmail.com"
}

后台日志:

2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:masterDataSource
2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.interceptor.DataSourceAop 39| current request datasource: masterDataSource
2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 72| 
 requestPath: /api/springboot/datasource/update, 
 param: UserUpdateParam(id=40, userName=Boyd Herzog, userPasscode=SVz04Ng7KmzGhSu, userEmail=Selmer.Renner@gmail.com)
2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:masterDataSource
2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.controller.DataSourceController 73| current datasource: masterDataSource
2022-02-15 11:42:26 | INFO  | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.common.config.RoutingDataSourceContext 34| thread local datasource key:masterDataSource
2022-02-15 11:42:26 | DEBUG | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.dao.UserDao.updateById 137| ==>  Preparing: UPDATE user SET user_name=?, user_passcode=?, user_email=? WHERE id=? AND user_del=0
2022-02-15 11:42:26 | DEBUG | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.dao.UserDao.updateById 137| ==> Parameters: Boyd Herzog(String), SVz04Ng7KmzGhSu(String), Selmer.Renner@gmail.com(String), 40(Long)
2022-02-15 11:42:26 | DEBUG | http-nio-8850-exec-5 | com.ljq.demo.springboot.readwrite.dao.UserDao.updateById 137| <==    Updates: 1

结果分析: 从日志中可以看出更新接口走的是主库数据源

关键日志:

current datasource: masterDataSource

6 推荐参考资料

Spring Boot 实现读写分离，还有谁不会？？

读写分离原来这么简单，一个小注解就够了

7 Github 源码

Gtihub 源码地址 : https://github.com/Flying9001/springBootDemo

个人公众号:404Code,分享半个互联网人的技术与思考，感兴趣的可以关注.