MySQL读写分离2 - SpringBoot 实现简易版
1. 读写分离流程
通常来讲,读写分离就是写主库读从库,从而用来减轻主库的负载压力。显然这里涉及到的是一个动态数据源切换的方案。
简易版的流程如下:
- 配置多个数据源,如一主多从
- 将数据源加入到DataSource中
- 动态选择DataSource,如insert/update操作使用主库,select操作使用从库
2. 简易版实现
2.1 配置文件
首先是配置文件,下面给出一个yaml文件格式的:
mysql:
datasource:
#读库数目
read-num: 2
type-aliases-package: com.lupu.cloudlearning.dao
mapper-locations: classpath:/mapper/*.xml
config-location: classpath:/mybatis-config.xml
write:
url: jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
read1:
url: jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
read2:
url: jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
这里选择了3个key的形式,当然你也可以将read库写成数组。
properties文件类似:
# mysql 配置,一读两写
mysql.datasource.read-num:2
mysql.datasource.type-aliases-package:xxx
mysql.datasource.mapper-locations:classpath:/mapper/*.xml
mysql.datasource.config-location:classpath:/mybatis-config.xml
# master for write
mysql.datasource.write.url:jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
mysql.datasource.write.username:root
mysql.datasource.write.password:123456
mysql.datasource.write.driver-class-name:com.mysql.cj.jdbc.Driver
# slave for read 1
mysql.datasource.read1.url:jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
mysql.datasource.read1.username:root
mysql.datasource.read1.password:123456
mysql.datasource.read1.driver-class-name:com.mysql.cj.jdbc.Driver
# salve for read 2
mysql.datasource.read2.url:jdbc:mysql://host:port/database_name?useUnicode=true&characterEncoding=utf-8&useSSL=false&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
mysql.datasource.read2.username:root
mysql.datasource.read2.password:123456
mysql.datasource.read2.driver-class-name:com.mysql.cj.jdbc.Driver
2.2 多数据源配置
既然用到了多数据源,我们首先会想到万能的spring已经提供了实现。在AbstractRoutingDataSource
类中,获取一个连接时:
// class: AbstractRoutingDataSource
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
// 注意这里,调用的抽象方法,返回DataSource的key
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
// 作为 fallback,使用默认的
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
所以,我们需要实现AbstractRoutingDataSource类的determineCurrentLookupKey()方法:
@Slf4j
public class RoutingDataSource extends AbstractRoutingDataSource {
// 读库的个数,方便做简单的负载均衡
@Value("${mysql.datasource.read-num:1}")
private int num;
@Override
protected Object determineCurrentLookupKey() {
String typeKey = DbContextHolder.getDbType();
if (DbContextHolder.WRITE.equals(typeKey)) {
log.info("使用了写库");
return typeKey;
}
// 使用随机数决定使用哪个读库
// 此处可以扩展读库的选择策略
int current = new Random().nextInt(num) + 1;
log.info("使用了读库{},一共{}个读库", current, num);
return DbContextHolder.READ + current;
}
}
然后就是MySQL的配置:
@Configuration
public class MysqlConfig {
private static final String MAPPER_LOCATION = "classpath:mapping/**/*.xml";
// 这里配置了三个DataSource,对应一主二从
@Primary
@Bean
@ConfigurationProperties(prefix = "mysql.datasource.write")
public DataSource writeDataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean("read1Ds")
@ConfigurationProperties(prefix = "mysql.datasource.read1")
public DataSource read1DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean("read2Ds")
@ConfigurationProperties(prefix = "mysql.datasource.read2")
public DataSource read2DataSource() {
return DruidDataSourceBuilder.create().build();
}
@Bean
public AbstractRoutingDataSource routingDataSource() {
// 此处是上面定义的动态数据源
RoutingDataSource proxy = new RoutingDataSource();
Map<Object, Object> targetDataSources = new HashMap<>(3);
// 此处的DbContextHolder实现在下面
targetDataSources.put(DbContextHolder.WRITE, writeDataSource());
targetDataSources.put(DbContextHolder.READ + "1", read1DataSource());
targetDataSources.put(DbContextHolder.READ + "2", read2DataSource());
// 默认主库
proxy.setDefaultTargetDataSource(writeDataSource());
proxy.setTargetDataSources(targetDataSources);
return proxy;
}
// ... 省略其它代码
这里要注意一个地方,@ConfigurationProperties(prefix =
这里的key值不要拼错了,否则,Druid虽然不会报错,但是回去尝试读取spring.datasource
前缀下的配置…
2.3 使用切面处理方法,
使用ThreadLocal进行线程隔离,防止互相影响。
public class DbContextHolder {
public static final String WRITE = "write";
public static final String READ = "read";
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
public static void setDbType(String dbType) {
Assert.notNull(dbType, "DB type must not be null");
CONTEXT_HOLDER.set(dbType);
}
public static String getDbType() {
return CONTEXT_HOLDER.get() == null ? WRITE : CONTEXT_HOLDER.get();
}
public static void clearDbType() {
CONTEXT_HOLDER.remove();
}
}
定义注解
来标注需要走从库的方法(即不更新数据的查询方法),此处不需要其他信息,所以是一个标记接口:
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}
定义切面
@Aspect
@Component
public class ReadOnlyAspect implements Ordered {
@Around("@annotation(readOnly)")
public Object setRead(ProceedingJoinPoint joinPoint, ReadOnly readOnly) throws Throwable {
try {
DbContextHolder.setDbType(DbContextHolder.READ);
return joinPoint.proceed();
} finally {
// 此处切记不要忘记清除holder里的DbType,原因有两个:
// 1、是防止内存泄漏;
// 2、更重要的是防止对后续在本线程上的操作造成影响
DbContextHolder.clearDbType();
}
}
@Override
public int getOrder() {
return 0;
}
}
使用方法
在需要从从库读的方法上,加上注解,比如:
@Mapper
public interface OrderMapper {
@ReadOnly
Order getOrderById(int orderId);
// 省略MyBatis的xml实现
}
到此一个简易版的基于动态切换数据源的读写分离就已经实现了。
这里面还有一些可以优化的点,比如:支持更好的从库选择策略;其中一个从库挂了的时候能否自动切换从而实现failover等等… : (