MySQL 读写分离
# 读写分离## 1 是什么
- 将数据库分为主从
- **主库进行写操作，从库进行读操作**
- 主从之间通过某种机制自动进行数据同步
- 一个主从同步的集群叫做一个分组
## 2 能干嘛
- 解决数据库**读**的性能瓶颈
- 若是数据不断累积且需要保留，瓶颈在写，则应使用水平切分
- 适合读多写少的场景
- **优先使用缓存**，然后才是读写分离
## 3 怎么玩
### 3.1 依赖
- spring-boot-starter-parent
- aop
- mybatis-plus
- mysql
*完整pom文件如下*
```xml
<?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>
<groupId>org.example</groupId>
<artifactId>msrw</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<artifactId>spring-boot-starter-parent</artifactId>
<groupId>org.springframework.boot</groupId>
<version>2.1.17.RELEASE</version>
</parent>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<lombok.version>1.18.2</lombok.version>
<mysql.version>8.0.18</mysql.version>
<druid.version>1.1.16</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- mybatisPlus插件 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.4.0</version>
</dependency>
<!-- 分页 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</dependency>
</dependencies>
</project>
```
### 3.2 配置
- 配置两个数据源
- mybaits 的xml文件位置
```yaml
spring:
datasource:
master:
name: msrw
username: root
password: password
jdbc-url: jdbc:mysql://192.168.159.128:3306/msrw?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
slave:
name: msrw
username: root
password: password
jdbc-url: jdbc:mysql://192.168.159.128:3307/msrw?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
mapperLocations: classpath*:mapper/*.xm
#mybatis-plus:
#configuration:
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
level:
per.zqx.msrw: debug
```
### 3.3 数据库类型枚举
```java
public enum DBEnum {
// 主库
MASTER,
// 从库
SLAVE;
}
```
### 3.4 上下文控制数据源切换
```java
@Slf4j
public class DBContextHolder {
private static final ThreadLocal<DBEnum> contextHolder = new ThreadLocal<>();
/**
* 注意返回的是枚举类型，不是 ThreadLocal !!!
* @Return DBEnum
*/
public static DBEnum getContextHolder() {
return contextHolder.get();
}
public static void set(DBEnum dbEnum){
contextHolder.set(dbEnum);
}
public static void master(){
set(DBEnum.MASTER);
System.out.println("************ Master Write Operation ************");
}
public static void slave(){
set(DBEnum.SLAVE);
System.out.println("************ Slave Read Operation ************");
}
public static void clear(){
contextHolder.remove();
}
}
```
### 3.5 路由选择类选择数据源*
- 通过重写`determineCurrentLookupKey`来决定使用哪个数据源
```java
/**
* MyRoutingDataSource, 路由选择类
*
* @AuThor Sa, Zhang 2021-12-28 12:49
*/
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DBContextHolder.getContextHolder();
}
}
```
### 3.6 数据源配置*
- 从配置文件读取数据源信息
- 将多个数据源放入数据源选择类对象
- 数据源获取的key与上下文设置的内容保持一致
```java
@Configuration
public class DataSourceConfig {
// 从配置文件获取主库
@Bean
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource(){
return DataSourceBuilder.create().build();
}
// 从配置文件获取从库连接
@Bean
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource slaveDataSource(){
return DataSourceBuilder.create().build();
}
// 返回路由选择类对象
@Bean
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaveDataSource") DataSource slaveDataSource){
HashMap<Object, Object> targetDataSource = new HashMap<>();
// key要和上下文内容保持一致，否则无法切换数据源
targetDataSource.put(DBEnum.MASTER,masterDataSource);
targetDataSource.put(DBEnum.SLAVE,slaveDataSource);
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
// 默认是读库，读多写少
myRoutingDataSource.setDefaultTargetDataSource(slaveDataSource);
myRoutingDataSource.setTargetDataSources(targetDataSource);
return myRoutingDataSource;
}
}
```
### 3.7 SqlSessionFactory和事务*
```java
/**
* MybatisPlusConfig,mybatis-plus 的SQLSessionFactory 和事务管理器
*
* @author Sa, Zhang 2021-12-28 12:55
*/
@Configuration
@EnableTransactionManagement
public class MybatisPlusConfig {
@Value(value = "${mybatis.mapperLocations}")
private String mapperLocations;
// 数据源选择器
@Resource(name = "myRoutingDataSource")
private DataSource myRoutingDataSource;
// mybatis-plus分页插件
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* Mybatis-plus 配置
*
* @return sqlSessionFactory
* @throws Exception 异常
*/
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
// com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
return sqlSessionFactoryBean.getObject();
}
/*
* 此处为使用mybatis时的sqlsessionFactory配置
* @return
* @throws Exception
*/
/*
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
return sqlSessionFactoryBean.getObject();
}
*/
/**
* 事务配置
*
* @return 事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager() {
DataSourceTransactionManager tx = new DataSourceTransactionManager();
tx.setDataSource(myRoutingDataSource);
return tx;
}
}
```
### 3.8 注解选择主从库
- 使用注解，配合AOP可以完成数据库的切换
- 在方法上使用
*master*
```java
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Master {
}
```
*slave，数据源选择器设置的默认数据源*
```java
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Slave {
}
```
### 3.9 AOP控制上下文*
- 方法可以加上@Master/@Slave 区分主从库
- 或者方法名按照一定的规范编写
```java
@Aspect
@Component
@Order(0) // 配置注解优先级，优于事物注解@Transactional先进行数据源切换，不然在事物中进行数据源切换无效
public class DataSourceAspect {
/**
* 写操作切点
*/
@Pointcut("@annotation(per.zqx.msrw.annos.Master)" +
"|| execution(* per.zqx.msrw.service.impl..add*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..update*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..delete*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..save*(..))")
public void writePointCut() {
}
/**
* 未加Master注解的，读操作切点
*/
@Pointcut("!@annotation(per.zqx.msrw.annos.Master)" +
"&& (@annotation(per.zqx.msrw.annos.Slave) " +
"|| execution(* per.zqx.msrw.service.impl..get*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..list*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..find*(..)) " +
"|| execution(* per.zqx.msrw.service.impl..select*(..)))")
public void readPointCut() {
}
/**
* 切换上下文
*/
@Before("readPointCut()")
public void read() {
DBContextHolder.slave();
}
/**
* 切换上下文
*/
@Before("writePointCut()")
public void write() {
DBContextHolder.master();
}
}
``` lcg2014 发表于 2021-12-31 12:44
是mysql功能还是java功能？其他语言能用吗？
读写分离是一种思想，这边是使用java做Demo yzq6431 发表于 2021-12-30 17:27
数据同步延时怎么解决
这边只是Demo，参考 https://www.jianshu.com/p/37f54c32c93c master/slave 带有歧义，我们不应该用他们 学习一下 不错的分享，学习下 牛叉，这个厉害了。学习学习 有项目源码么，学习一下 刚学完这个，就发现了这个教程，支持一下！ 不错，正好需要这方面的知识点 小项目这样可以,稍微大点的项目直接咔嚓了......:lol
不错的分享，学习下