为什么数据库需要配置读写分离f
实现的原理
实现的原理主要分为两种:
1.静态的选择Master/Slave数据库(通过单独配置读写库,在方法中已经写死使用读库或者写库)
2.数据库选择是动态切换(这里写的是这种方式实现读写分离)
动态获取连接的流程:
ps:我们的ReadWriteDataSourceConfig需要继承于AbstractRoutingDataSource
1.AbstractRoutingDataSource初始化会依赖注入配置文件中你所配置的DataSource, 并保存于Map中(结构为key-DataSource,其中key为配置文件中设置的,key是为了标识对应的数据库,后续动态获取时会从ThreadLocal中获取key)
2.通过aop拦截注解DBReadOnly(自定义),readDataSource的key保存于ThreadLocal中。
3.获取数据库链接时,AbstractRoutingDataSource会调用覆写的getConnetion,其中getConnetion方法会调用determineTargetDataSource方法,它会调用抽象方法determineCurrentLookupKey(目的就是为了获取对应保存数据库的Map的key),我们的ReadWriteDataSourceConfig需要覆写determineCurrentLookupKey方法,返回ThreadLocal中的key(如果Key不存在,会使用默认库,下面会详细介绍),从而AbstractRoutingDataSource会去获取对应的数据库连接,实现动态获取数据库连接。
实现的原理:
/**
* <p>Attempts to establish a connection with the data source that
* this {@code DataSource} object represents.
*
* @return a connection to the data source
* @exception SQLException if a database access error occurs
* @throws java.sql.SQLTimeoutException when the driver has determined that the
* timeout value specified by the {@code setLoginTimeout} method
* has been exceeded and has at least tried to cancel the
* current database connection attempt
*/
Connection getConnection()
throws
SQLException;
|
public
abstract
class
AbstractRoutingDataSource
extends
AbstractDataSource
implements
InitializingBean{
private
Map<Object, Object> targetDataSources;
//保存对应key的DataSource
private
Object defaultTargetDataSource;
//默认的使用的DataSource的Bean
private
boolean
lenientFallback =
true
;
private
DataSourceLookup dataSourceLookup =
new
JndiDataSourceLookup();
private
Map<Object, DataSource> resolvedDataSources;
//所有DateSource保存于这个Map中
private
DataSource resolvedDefaultDataSource;
//默认使用的DataSource,下面会详谈
@Override
public
void
afterPropertiesSet() {
if
(
this
.targetDataSources ==
null
) {
throw
new
IllegalArgumentException(
"Property 'targetDataSources' is required"
);
}
this
.resolvedDataSources =
new
HashMap<Object, DataSource>(
this
.targetDataSources.size());
for
(Map.Entry<Object, Object> entry :
this
.targetDataSources.entrySet()) {
//这是
Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
this
.resolvedDataSources.put(lookupKey, dataSource);
}
if
(
this
.defaultTargetDataSource !=
null
) {
this
.resolvedDefaultDataSource = resolveSpecifiedDataSource(
this
.defaultTargetDataSource);
}
}
}
|
1.AbstractRoutingDataSource中的成员变量,和初始化所做的事情(转换并保存Map<key, DataSource>,可设置key不存在时候的默认DataSourcere)
AbstractRoutingDataSource中有targetDataSources,AbstractRoutingDataSource初始化会依赖注入配置文件中你所配置的DataSource, 并保存于Map中(结构为key-DataSource,其中key为配置文件中设置的,key是为了标识对应的数据库,后续动态获取时会从ThreadLocal中获取key),最终你所需要的数据库连接是通过你给于的key去获取,覆写determineCurrentLookupKey方法给它。
Object lookupKey = determineCurrentLookupKey();
//需要ReadWriteDataSourceConfig覆写的方法
DataSource dataSource =
this
.resolvedDataSources.get(lookupKey);
if
(dataSource ==
null
&& (
this
.lenientFallback || lookupKey ==
null
)) {
dataSource =
this
.resolvedDefaultDataSource;
}
|
@Override
public
Connection getConnection()
throws
SQLException {
return
determineTargetDataSource().getConnection();
}
|
/**
* Retrieve the current target DataSource. Determines the
* {@link #determineCurrentLookupKey() current lookup key}, performs
* a lookup in the {@link #setTargetDataSources targetDataSources} map,
* falls back to the specified
* {@link #setDefaultTargetDataSource default target DataSource} if necessary.
* @see #determineCurrentLookupKey()
*/
protected
DataSource determineTargetDataSource() {
Assert.notNull(
this
.resolvedDataSources,
"DataSource router not initialized"
);
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource =
this
.resolvedDataSources.get(lookupKey);
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;
}
|
/**
* Determine the current lookup key. This will typically be
* implemented to check a thread-bound transaction context.
* <p>Allows for arbitrary keys. The returned key needs
* to match the stored lookup key type, as resolved by the
* {@link #resolveSpecifiedLookupKey} method.
*/
protected
abstract
Object determineCurrentLookupKey();
|
配置与实现:
我们默认为使用写库,当标记了自定义注解DBReadOnly时使用读库(为了方便这里三个库都连的是同一个数据库)
<bean id =
"mysql_master"
parent=
"dataSource"
>
<property name=
"url"
value=
"${test.jdbc.url}"
/>
<property name=
"username"
value=
"${test.jdbc.username}"
/>
<property name=
"password"
value=
"${test.jdbc.password}"
/>
</bean>
<bean id =
"mysql_slave1"
parent=
"dataSource"
>
<property name=
"url"
value=
"${test.jdbc.url}"
/>
<property name=
"username"
value=
"${test.jdbc.username}"
/>
<property name=
"password"
value=
"${test.jdbc.password}"
/>
</bean>
<bean id =
"mysql_slave2"
parent=
"dataSource"
>
<property name=
"url"
value=
"${test.jdbc.url}"
/>
<property name=
"username"
value=
"${test.jdbc.username}"
/>
<property name=
"password"
value=
"${test.jdbc.password}"
/>
</bean>
<bean id=
"dataSource"
class
=
"com.alibaba.druid.pool.DruidDataSource"
init-method=
"init"
destroy-method=
"close"
>
<property name=
"filters"
value=
"mergeStat"
/>
<property name=
"maxActive"
>
<value>
30
</value>
</property>
<property name=
"initialSize"
>
<value>
5
</value>
</property>
<property name=
"maxWait"
>
<value>
60000
</value>
</property>
<property name=
"minIdle"
>
<value>
1
</value>
</property>
<property name=
"timeBetweenEvictionRunsMillis"
>
<value>
600000
</value>
</property>
<property name=
"minEvictableIdleTimeMillis"
>
<value>
300000
</value>
</property>
<property name=
"validationQuery"
>
<value>SELECT
1
</value>
</property>
<property name=
"testWhileIdle"
>
<value>
true
</value>
</property>
<property name=
"testOnBorrow"
>
<value>
false
</value>
</property>
<property name=
"testOnReturn"
>
<value>
false
</value>
</property>
<property name=
"connectionProperties"
value=
"druid.stat.slowSqlMillis=200"
/>
<property name=
"poolPreparedStatements"
>
<value>
false
</value>
</property>
<property name=
"removeAbandoned"
value=
"false"
/>
<property name=
"useGlobalDataSourceStat"
value=
"false"
/>
</bean>
<bean id =
"mysqlDataSource"
class
=
"cn.test.test.datasource.ReadWriteDataSourceConfig"
>
<property name =
"defaultTargetDataSource"
ref=
"mysql_master"
/>
<property name=
"targetDataSources"
>
<map>
<entry key=
"read_mysql_slave1"
value-ref=
"mysql_slave1"
/>
<entry key=
"read_mysql_slave2"
value-ref=
"mysql_slave2"
/>
</map>
</property>
</bean>
<bean id=
"sqlSessionFactory"
class
=
"org.mybatis.spring.SqlSessionFactoryBean"
>
<property name=
"dataSource"
ref=
"mysqlDataSource"
/>
<property name=
"typeAliasesPackage"
value=
"cn.test.test.bean"
/>
<property name=
"mapperLocations"
value=
"classpath:cn/test/test/dao/mysql/**/*.xml"
/>
<property name=
"configLocation"
value=
"classpath:mybatis-config.xml"
/>
</bean>
<bean
class
=
"org.mybatis.spring.mapper.MapperScannerConfigurer"
>
<property name=
"sqlSessionFactoryBeanName"
value=
"sqlSessionFactory"
/>
<property name=
"basePackage"
value=
"cn.test.test.dao.mysql"
/>
</bean>
<bean id=
"transactionManager"
class
=
"org.springframework.jdbc.datasource.DataSourceTransactionManager"
>
<property name=
"dataSource"
ref=
"mysqlDataSource"
/>
<qualifier value=
"mysql"
/>
</bean>
<tx:annotation-driven transaction-manager=
"transactionManager"
></tx:annotation-driven>
|
public
class
ReadWriteDataSourceConfig
extends
AbstractRoutingDataSource {
private
static
final Test
Logger logger = TestLogger.getInstance(ReadWriteDataSourceConfig.
class
);
/**
* @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
*/
@Override
protected
Object determineCurrentLookupKey() {
return
HoldDataSourceKey.getRead();
}
enum
ReadKey {
read_mysql_slave1, read_mysql_slave2;
}
static
class
HoldDataSourceKey {
private
static
final
ThreadLocal<ReadKey> holder =
new
ThreadLocal<>();
public
static
void
setRead() {
ReadKey[] keys = ReadKey.values();
holder.set(keys[
new
Random().nextInt(keys.length)]);
}
public
static
String getRead() {
logger.infoLog(
"所使用的dataSource`s key = {}"
, String.valueOf(holder.get()));
return
holder.get() ==
null
?
null
: holder.get().toString();
}
public
static
void
reset() {
holder.set(
null
);
}
}
}
|
通过aop拦截标有注解DBReadOnly的操作
ps:@DBReadOnly最好是注在相关业务的外层service上,并处于同一事物中,不然可能会出现多次查询查询到不同的Slave,Slave之间也有延迟,导致数据不同步,或者数据不存在等等问题。
@Target
({ ElementType.METHOD })
@Retention
(RetentionPolicy.RUNTIME)
public
@interface
DBReadOnly {
}
|
@Component
@Aspect
public
class
ReadWriteAspect
implements
Ordered {
private
static
final
Test
class
);
@Override
public
int
getOrder() {
return
1
;
}
@Around
(
"@annotation(cn.test.test.datasource.annotation.DBReadOnly)"
)
public
Object around(ProceedingJoinPoint pjp) {
Object result =
null
;
try
{
HoldDataSourceKey.setRead();
result = pjp.proceed();
HoldDataSourceKey.reset();
}
catch
(Throwable e) {
logger.errorLog(
"get datasource key fail!"
, e);
}
finally
{
HoldDataSourceKey.reset();
}
return
result;
}
}
|
在下面方法上中加@DBReadOnly,调用该方法
@DBReadOnly
@Override
public
UserPayWayConfig queryByUserId(Integer userId, Integer type) {
return
userPayWayConfigMapper.queryByUserId(userId, type);
}
2017
-
12
-
28
16
:
06
:
25.820
INFO itpsgf1tHHPCkFLYmsHIbG ReadWriteDataSourceConfig 所使用的dataSource`s key =read_mysql_slave2
|
缺陷与不足:
最大的缺陷就是继承AbstractRoutingDataSource不能实现多数据库之间的读写分离,比如使用了sqlserver、mysql,它是无法做到同时使用读/写库的,因为获取Key的方法determineCurrentLookupKey是没有上下文的,无法确定该操作是操作sqlserver还是mysql,那么它就无法返回对应数据源的key了。
所以,要实现数据库的读写分离。。。还是去继承AbstractDataSource吧,实现原理和AbstractRoutingDataSource实现差不多,就是将单例的bean变为多例的class。
但是,如果只是使用单一数据库,继承这个类还是相对来说比较简单的。