读写分离
1 是什么
- 将数据库分为主从
- 主库进行写操作，从库进行读操作
- 主从之间通过某种机制自动进行数据同步
- 一个主从同步的集群叫做一个分组
2 能干嘛
-
解决数据库读的性能瓶颈
- 若是数据不断累积且需要保留，瓶颈在写，则应使用水平切分
-
适合读多写少的场景
-
优先使用缓存，然后才是读写分离
3 怎么玩
3.1 依赖
- spring-boot-starter-parent
- aop
- mybatis-plus
- mysql
完整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>
<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 配置
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 数据库类型枚举
public enum DBEnum {
// 主库
MASTER,
// 从库
SLAVE;
}
3.4 上下文控制数据源切换
@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来决定使用哪个数据源
/**
* 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与上下文设置的内容保持一致
@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和事务*
/**
* 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
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Master {
}
slave，数据源选择器设置的默认数据源
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Slave {
}
3.9 AOP控制上下文*
- 方法可以加上@Master/@Slave 区分主从库
- 或者方法名按照一定的规范编写
@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();
}
}
[复制链接]
发表于 2021-12-30 12:53
|
发表于 2021-12-31 13:24
