SpringBoot实现MySQL读写分离

原创已于 2023-11-04 22:22:55 修改 · 1.4k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#spring boot #mysql #后端

于 2023-11-04 21:56:45 首次发布

本文详细介绍了如何在SpringBoot项目中，通过配置MySQL主从库和AOP技术，实现读写分离，以提高并发性能。涉及步骤包括主库和从库配置、数据源配置、AOP注解和读写分离AOP类的实现等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

前言
二、使用步骤
总结

前言

在高并发下，需要对应用进行读写分离，配置多数据源，即写操作走主库，读操作则走从库，主从数据库负责各自的读和写，
缓解了锁的争用，提高了读取性能。
实现读写分离有多种方式，如使用中间件MyCat、Sharding-JDBC等，这里我们使用Aop的方式在代码层面实现读写分离。

提示：以下是本篇文章正文内容，下面案例可供参考

二、使用步骤

1. 主库配置

本案例是在阿里云服务器上启动两个mysql docker容器，其中主服务器的my.cnf中设置如下
在这里插入图片描述
代码如下（示例）：

server_id = 1
log_bin=mysql-bin
binlog_format=row

2. 从库配置

2.1 my.cnf配置

在这里插入图片描述
代码如下（示例）：

datadir = /usr/local/mysql/3307/data
port = 3307
server_id = 2
log_bin=mysql-bin
read_only=1

2.2 查看主库状态

在主库中查询show master status;
在这里插入图片描述

2.3 执行同步SQL设置语句

CHANGE MASTER TO MASTER_HOST=‘localhost’, MASTER_PORT = 3306, MASTER_USER=‘root’, MASTER_PASSWORD=‘xxx’,MASTER_LOG_FILE=‘mysql-bin.000007’,MASTER_LOG_POS=157;

2.4 查看从库状态

SHOW SLAVE STATUS \G; 在mysql中使用该查询命令查看从库状态
如果读到以下信息,即为成功
Master_Log_File: binlog.000007
Read_Master_Log_Pos: 157
Slave_IO_Running:YES
Slave_SQL_Running:YES
在这里插入图片描述

3. 主从库建表

代码如下（示例）：

CREATE TABLE `tb_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(32) NOT NULL COMMENT '密码，加密存储',
  `phone` varchar(20) DEFAULT NULL COMMENT '注册手机号',
  `created` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb3 COMMENT='用户表'

4. springboot项目数据源配置yml

代码如下（示例）：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    master:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://服务器地址:3306/databaseXX
      username: root
      password: xxx
    slave:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://服务器地址:3307/databaseXX
      username: root
      password: xxx

5. 数据源配置

在这里插入图片描述

Slave代码如下：

package com.example.dynamicdb.config;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * 从数据库配置
 */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.slave")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Slave {
    private String url;
    private String driverClassName;
    private String username;
    private String password;
}

Master代码如下：

package com.example.dynamicdb.config;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;

/**
 * 主数据库配置
 */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.master")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Master {
    private String url;
    private String driverClassName;
    private String username;
    private String password;
}

DynamicDataSourceEnum代码如下：

package com.example.dynamicdb.config;

import lombok.Getter;

/**
 * 动态数据源枚举
 */
@Getter
public enum DynamicDataSourceEnum {
    MASTER("master"),
    SLAVE("slave");
    private String dataSourceName;
    DynamicDataSourceEnum(String dataSourceName) {
        this.dataSourceName = dataSourceName;
    }
}

DynamicDataSource 动态数据源流程步骤：
1、重写数据源选择策略determineCurrentLookupKey()。

2、数据源配置类将数据源存放在AbstractRoutingDataSource的 targetDataSources和defaultTargetDataSource中，然后通过afterPropertiesSet()方法将数据源分别进行复制到resolvedDataSources和resolvedDefaultDataSource中。

3、进行数据库连接时，调用AbstractRoutingDataSource的getConnection()的方法，此时会先调用determineTargetDataSource()方法返回DataSource再进行getConnection()。
代码如下：

package com.example.dynamicdb.config;

import com.example.dynamicdb.util.DynamicDataSourceContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

DataSourceConfig代码如下：

package com.example.dynamicdb.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

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

@Configuration
public class DataSourceConfig {

    @Autowired
    private Master master;

    @Autowired
    private Slave slave;

    @Bean(name = "masterDataSource")
    public DataSource masterDataSource(){
        return DataSourceBuilder.create().url(master.getUrl()).driverClassName(master.getDriverClassName())
                .username(master.getUsername()).password(master.getPassword()).build();
    }

    @Bean(name = "slaveDataSource")
    public DataSource slaveDataSource(){
        return DataSourceBuilder.create().url(slave.getUrl()).driverClassName(slave.getDriverClassName())
                .username(slave.getUsername()).password(slave.getPassword()).build();
    }

    @Bean(name = "dynamicDb")
    public DynamicDataSource dynamicDb(@Qualifier("masterDataSource") DataSource masterDataSource,
                                       @Qualifier("slaveDataSource") DataSource slaveDataSource){
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSource = new HashMap();
        targetDataSource.put(DynamicDataSourceEnum.MASTER.getDataSourceName(), masterDataSource);
        if(slaveDataSource != null){
            targetDataSource.put(DynamicDataSourceEnum.SLAVE.getDataSourceName(), slaveDataSource);
        }
        dynamicDataSource.setTargetDataSources(targetDataSource);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDb") DataSource dynamicDb) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dynamicDb);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "transactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("dynamicDb") DataSource dynamicDb){
        return new DataSourceTransactionManager(dynamicDb);
    }
}

6. 读写分离注解

DataSourceSelector代码如下：

package com.example.dynamicdb.util;

import com.example.dynamicdb.config.DynamicDataSourceEnum;
import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSelector {
    DynamicDataSourceEnum value() default DynamicDataSourceEnum.MASTER;
    boolean clear() default true;
}

7. 读写分离aop类

DataSourceContextAop代码如下：

package com.example.dynamicdb.aop;

import com.example.dynamicdb.util.DataSourceSelector;
import com.example.dynamicdb.util.DynamicDataSourceContextHolder;
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;

@Slf4j
@Order(value = 1)
@Aspect
@Component
public class DataSourceContextAop {

    @Around("@annotation(com.example.dynamicdb.util.DataSourceSelector)")
    public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
        boolean clear = true;
        try {
            MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
            Method method = methodSignature.getMethod();
            DataSourceSelector dataSourceImport = method.getAnnotation(DataSourceSelector.class);
            clear = dataSourceImport.clear();
            DynamicDataSourceContextHolder.setDataSourceType(dataSourceImport.value().getDataSourceName());
            log.info("======切换数据源至:{}", dataSourceImport.value().getDataSourceName());
            return pjp.proceed();
        } finally {
            if(clear){
                DynamicDataSourceContextHolder.clearDataSourceType();
            }
        }
    }

}

设置当前线程数据库连接 DynamicDataSourceContextHolder代码如下：

package com.example.dynamicdb.util;

import lombok.extern.slf4j.Slf4j;


@Slf4j
public class DynamicDataSourceContextHolder {
    /**
     * 存放当前线程使用的数据源类型信息
     */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    /**
     * 设置数据源
     *
     * @param dataSourceType
     */
    public static void setDataSourceType(String dataSourceType) {
        log.info("添加数据源实例到管理器中，dataSourceType{}", dataSourceType);
        contextHolder.set(dataSourceType);
    }

    /**
     * 获取数据源
     *
     * @return
     */
    public static String getDataSourceType() {
        log.info("从数据源实例管理器中获取当前实例");
        return contextHolder.get();
    }

    /**
     * 清除数据源
     */
    public static void clearDataSourceType() {
        log.info("清除当前数据源实例");
        contextHolder.remove();
    }

}

8. service调用加读写分离

UserInfoServiceImpl代码如下：

package com.example.dynamicdb.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.dynamicdb.config.DynamicDataSourceEnum;
import com.example.dynamicdb.entity.UserInfo;
import com.example.dynamicdb.mapper.UserInfoMapper;
import com.example.dynamicdb.service.UserInfoService;
import com.example.dynamicdb.util.DataSourceSelector;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.concurrent.TimeUnit;

@Service
@Slf4j
public class UserInfoServiceImpl implements UserInfoService {
    @Autowired
    private UserInfoMapper userInfoMapper;

    @Autowired
    private RedisTemplate redisTemplate;

    @DataSourceSelector(value = DynamicDataSourceEnum.SLAVE)
    @Override
    public List<UserInfo> findList() {
        return userInfoMapper.selectList(new QueryWrapper<>());
    }

    @DataSourceSelector(value = DynamicDataSourceEnum.SLAVE)
    @Override
    public UserInfo findOne(Integer id) {
        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id", id);
        UserInfo userInfo = userInfoMapper.selectOne(queryWrapper);
        log.info("username:{}",userInfo.getUsername());
        return userInfo;
    }

    @DataSourceSelector(value = DynamicDataSourceEnum.MASTER)
    @Override
    public int addUser(UserInfo userInfo) {
        redisTemplate.opsForValue().set(userInfo.getUsername(),userInfo,30,TimeUnit.MINUTES);
        return userInfoMapper.insert(userInfo);
    }

    @DataSourceSelector(value = DynamicDataSourceEnum.MASTER)
    @Override
    public int updateUser(UserInfo userInfo) {
        return userInfoMapper.updateById(userInfo);
    }
}

9. 调用controller接口测试读写分离

UserInfoController代码如下：

package com.example.dynamicdb.controller;

import cn.hutool.core.date.DateUtil;
import com.example.dynamicdb.entity.UserInfo;
import com.example.dynamicdb.response.Response;
import com.example.dynamicdb.response.ResponseHelper;
import com.example.dynamicdb.service.UserInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Date;
import java.util.List;

@RestController
@RequestMapping(value = "/user")
@Slf4j
public class UserInfoController {
    @Autowired
    private UserInfoService userInfoService;

    @ResponseBody
    @GetMapping(value = "/findList")
    public Response findList() {
        List<UserInfo> userList = userInfoService.findList();
        return ResponseHelper.buildOK(userList);
    }

    @ResponseBody
    @GetMapping(value = "/findOne/{id}")
    public Response findOne(@PathVariable("id") Integer id) {
        UserInfo userInfo = userInfoService.findOne(id);
        return ResponseHelper.buildOK(userInfo);
    }

    @ResponseBody
    @PostMapping(value = "/addOne")
    public Response addOne(@RequestBody UserInfo userInfo) {
        userInfo.setCreated(DateUtil.format(new Date(), "yyyy-MM-dd hh:mm:ss"));
        int result = userInfoService.addUser(userInfo);
        if(result<0){
            return ResponseHelper.buildFail("新增失败");
        }
        return ResponseHelper.buildOK("新增成功");
    }

    @ResponseBody
    @PostMapping(value = "/update")
    public Response findByAge(@RequestBody UserInfo userInfo) {
        int result = userInfoService.updateUser(userInfo);
        if(result<0){
            return ResponseHelper.buildFail("更新失败");
        }
        return ResponseHelper.buildOK("更新成功");
    }

}