mysql spring 数据库读写分离

最新推荐文章于 2024-08-09 21:25:57 发布

Ai小强

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量399

点赞数 1

分类专栏：技术分享读写分离 mysql 文章标签：数据库 spring mysql 数据读写分离

本文链接：https://blog.csdn.net/dddkl007/article/details/78899178

版权

技术分享读写分离 mysql 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

如果一个互联网公司的项目只有一个数据库在支撑，在出现大量访问的时候，页面会无限超时报错，为此，可以尝试去准备组建主从库，进行读写分离的数据库架构。

1.准备：两个数据库，一个主库 master，一个从库 slave，（主从库之间数据同步用的是中间件，这里暂时不讲，如果有兴趣，可以自己去百度下）。

2.在spring中的ApplicationContext.xml 文件中，配置两个数据源：

数据源（主库）1：

数据源（从库）2：

3.用java写动态数据源相关策略配置。

在书写动态源的时候，需要继承AbstractRoutingDataSource并，实现determineCurrentLookupKey和afterPropertiesSet方法，

package com.core.database.dynamicDataBase;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicLong;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import com.alibaba.druid.util.ThreadLocalRandom;

/**
* @Function 动态数据源
*/
public class DynamicDataSource extends AbstractRoutingDataSource {

private static final Log LOG = LogFactory.getLog(DynamicDataSource.class);
private static final Long MAX_POOL = Long.MAX_VALUE;

private Object writeDataSource; // 写数据源
private List<Object> readDataSources; // 多个读数据源

private int readDataSourcePollPattern = 0; // 获取读数据源方式，0：随机，1：轮询

private AtomicLong counter = new AtomicLong(0);
private final Lock lock = new ReentrantLock();

@Autowired
private DataSourceHeartBeat dataSourceHeartBeat;

public void setWriteDataSource(Object writeDataSource) {
this.writeDataSource = writeDataSource;
}

public void setReadDataSources(List<Object> readDataSources) {
this.readDataSources = readDataSources;
}

public void setReadDataSourcePollPattern(int readDataSourcePollPattern) {
this.readDataSourcePollPattern = readDataSourcePollPattern;
}

@Override
protected Object determineCurrentLookupKey() {
DataSourceTypeInfoDTO dataSourceTypeInfo = DynamicDataSourceHolder.getDataSource();

if (dataSourceTypeInfo == null || dataSourceTypeInfo.getDataSourceType() == null
|| dataSourceTypeInfo.getDataSourceType() == DynamicDataSourceGlobalEnum.WRITE || readDataSources.size() <= 0) {
return DynamicDataSourceGlobalEnum.WRITE.name();
}

int index = 1;
if (readDataSourcePollPattern == 1) {
// 轮询方式
long currValue = counter.incrementAndGet();
if ((currValue + 1) >= MAX_POOL) {
try {
lock.lock();
if ((currValue + 1) >= MAX_POOL) {
counter.set(0);
}
} finally {
lock.unlock();
}
}
index = (int) (currValue % readDataSources.size());
} else {
// 随机方式
index = ThreadLocalRandom.current().nextInt(0, readDataSources.size());
}
return dataSourceTypeInfo.getDataSourceType().name() + index;
}

@Override
public void afterPropertiesSet() {
if(writeDataSource == null) {
throw new IllegalArgumentException("property 'writeDataSource' is required");
}
setDefaultTargetDataSource(writeDataSource);
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
targetDataSources.put(DynamicDataSourceGlobalEnum.WRITE.name(), writeDataSource);
if (CollectionUtils.isNotEmpty(this.readDataSources)) {
for (int i = 0; i < readDataSources.size(); i++) {
targetDataSources.put(DynamicDataSourceGlobalEnum.READ.name() + i, readDataSources.get(i));
}
}
setTargetDataSources(targetDataSources);

super.afterPropertiesSet();
}

/**
*
* startDyHeartBeat(启动心跳)
*/
public void startDyHeartBeat() {
dataSourceHeartBeat.initDataSources(writeDataSource, readDataSources);
dataSourceHeartBeat.startHeartBeat();
}

/**
*
* refreshDataSources(刷新数据源)
* @param dataSources
*/
public void refreshDataSources(List<Object> dataSources) {
boolean isSame = false;
if (this.readDataSources.size() == dataSources.size()) {
if (this.readDataSources.containsAll(dataSources)) {
isSame = true;
}
}

if (!isSame) {
this.readDataSources.clear();
if (CollectionUtils.isNotEmpty(dataSources)) {
for (Object ds : dataSources) {
this.readDataSources.add(ds);
}
}

Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
targetDataSources.put(DynamicDataSourceGlobalEnum.WRITE.name(), writeDataSource);
if (CollectionUtils.isNotEmpty(this.readDataSources)) {
for (int i = 0; i < readDataSources.size(); i++) {
targetDataSources.put(DynamicDataSourceGlobalEnum.READ.name() + i, readDataSources.get(i));
}
}
setTargetDataSources(targetDataSources);
super.afterPropertiesSet();

logger.info("dataSources has changed");
}
logger.info("refersh success");
}
}

根据以上的类，注册bean并注入数据源，进行数据源切换。

4.数据库心跳配置

package com.core.database.dynamicDataBase;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;
import java.util.Timer;
import java.util.TimerTask;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;

/**
* @Function 类功能说明
*/
public class DataSourceHeartBeat {

/**
* 日志记录 *
*/
private static final Log LOG = LogFactory.getLog(DataSourceHeartBeat.class);
private String[] noticePhones = null;

@Autowired
private DynamicDataSource dynamicDataSource;


private BasicDataSource writeDataSource; // 写数据源
private List<BasicDataSource> readDataSources; // 多个只读数据源

private ExecutorService executorPool = Executors.newFixedThreadPool(30);

/**
*
* startHeartBeat(开启数据库监控心跳，90s执行一次数据库测试连接)
* @return
*/
public boolean startHeartBeat() {
if (noticePhones == null) {
ResourceBundle bundle = ResourceBundle.getBundle("system");
if (bundle.containsKey("db_exception_notice_phones")) {
noticePhones = bundle.getString("db_exception_notice_phones").split(",");
}
}

Timer timer = new Timer();
timer.schedule(new TimerTask() {

@Override
public void run() {
//测试写库
Future<Boolean> writeFuture = executorPool.submit(new CheckDBConnectCallable(writeDataSource));

//测试只读数据库
Map<Integer, Future<Boolean>> readFutureMap = new HashMap<Integer, Future<Boolean>>();
List<Object> normalReadDS = new ArrayList<Object>();
for (int i = 0; i < readDataSources.size(); i++) {
readFutureMap.put(i, executorPool.submit(new CheckDBConnectCallable(readDataSources.get(i))));
}

try {
if (writeFuture.get()) {
LOG.info("测试写库连接成功");
}
} catch (Exception e) {
LOG.error(e.getLocalizedMessage(), e);
}

for (Entry<Integer, Future<Boolean>> entry : readFutureMap.entrySet()) {
try {
if (entry.getValue().get()) {
normalReadDS.add(readDataSources.get(entry.getKey()));
}
} catch (Exception e) {
LOG.error(e.getLocalizedMessage(), e);
}
}

if (normalReadDS.size() == readDataSources.size()) {
LOG.info("测试所有只读库连接成功");
}

dynamicDataSource.refreshDataSources(normalReadDS);

}
}, 5000, 90000);

return true;
}

/**
*
* initReadOnlyDataSources(初始化只读数据库实例)
* @param readDataSources
*/
public void initDataSources(Object writeDataSource, List<Object> readDataSources) {
this.writeDataSource = (BasicDataSource) writeDataSource;
this.readDataSources = new ArrayList<BasicDataSource>();
if (CollectionUtils.isNotEmpty(readDataSources)) {
for (Object rds : readDataSources) {
this.readDataSources.add((BasicDataSource) rds);
}
}
}

/**
*
* destory(停止线程池)
*/
public void destory() {
try {
if (!executorPool.isShutdown()) {
executorPool.shutdown();
}
if (!executorPool.awaitTermination(60000, TimeUnit.MILLISECONDS)) {
executorPool.shutdownNow();
}
} catch (Exception e) {
LOG.error(e.getLocalizedMessage(), e);
executorPool.shutdownNow();
}
LOG.info("executorPool destory success");
}
}

在以上的基上，在xml中添加心跳bean

<bean id="dataSourceHeartBeat" class="com.core.database.dynamicDataBase.DataSourceHeartBeat" destroy-method="destory"/>

5.配置事务

6.配置切面相关数据
package com.core.database.dynamicDataBase;

import java.lang.reflect.Method;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;

/**
* @Function 类功能说明
*/
public class DataSourceAspect {
private static final Log LOG = LogFactory.getLog(DynamicDataSource.class);

/**
*
* pointCut(这里用一句话描述这个方法的作用)
*/
public void pointCut() {
};

/**
*
* before(aop before)
* @param point
*/
public void before(JoinPoint point) {
DataSourceTypeInfoDTO dataSourceTypeInfo = new DataSourceTypeInfoDTO();
dataSourceTypeInfo.setMethodInfo(point.getSignature().getDeclaringTypeName() + "." + point.getSignature().getName());
if (isReadOnly(point)) {
dataSourceTypeInfo.setDataSourceType(DynamicDataSourceGlobalEnum.READ);
} else {
dataSourceTypeInfo.setDataSourceType(DynamicDataSourceGlobalEnum.WRITE);
}
DynamicDataSourceHolder.putDataSource(dataSourceTypeInfo);
}

/**
*
* after(aop after)
* @param point
*/
public void after(JoinPoint point) {
DynamicDataSourceHolder.clearDataSource();
}

/**
* isReadOnly(判断是否为读库)
* @param point
* @return
*/
private Boolean isReadOnly(JoinPoint point) {
// 若方法上有注解，则直接选择指定的数据源
Object target = point.getTarget();
String methodName = point.getSignature().getName();
Class<?>[] classz = target.getClass().getInterfaces();
Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
try {
Method m = classz[0].getMethod(methodName, parameterTypes);
if (m != null && m.isAnnotationPresent(DataSource.class)) {
return DynamicDataSourceGlobalEnum.READ.equals(m.getAnnotation(DataSource.class).value());
}
} catch (Exception e) {
LOG.error(e.getLocalizedMessage(), e);
}

// 方法名以query、find、get开头的方法名走从库
return StringUtils.startsWithAny(methodName, new String[] { "query", "find", "get"});
}
}

在xml中配置相关的切面切点

<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
<bean id="dynamicDataSourceAspect" class="com.core.database.dynamicDataBase.DataSourceAspect" />
<aop:config>
<aop:aspect id="cdatasource" ref="dynamicDataSourceAspect">
<aop:pointcut id="tx" expression="execution(* com.zzq.mapper..*.*(..))"/>
<aop:before pointcut-ref="tx" method="before"/>
<aop:after pointcut-ref="tx" method="after"/>
</aop:aspect>
</aop:config>

读写分离的相关配置和代码流程大概就如上所诉，其实说白了，就是一下几个步骤，了解清楚了每个步骤，这个东西也不是很神秘的东西了。

1.准备：两个数据库
2.在spring中的ApplicationContext.xml 文件中，配置两个数据源
3.用java写动态数据源相关策略配置,注册bean并注入数据源，进行数据源切换。
4.数据库心跳配置,在xml中添加心跳bean
5.配置事务
6.配置切面相关数据,在xml中配置相关的切面切点,

在调用特定类的时候，进入切点，置换数据源，为了线程间不影响，需要启用本地化线程

如果有什么问题，请指正。

相关源码：http://download.csdn.net/download/dddkl007/10173830

============欢迎关注公众号（zzqJava）=========