如果一个互联网公司的项目只有一个数据库在支撑,在出现大量访问的时候,页面会无限超时报错,为此,可以尝试去 准备组建主从库,进行读写分离的数据库架构。
1.准备:两个数据库,一个主库 master,一个从库 slave,(主从库之间数据同步用的是中间件,这里暂时不讲,如果有兴趣,可以自己去百度下)。
2.在spring中的ApplicationContext.xml 文件中,配置两个数据源:
数据源(主库)1:
<bean id="master" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="url" value="jdbc:mysql://ip:port/dbname" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="username" />
<property name="password" value="password" />
<!--maxTotal: 可以在这个池中同时被分配的有效连接数的最大值,如设置为负数,则不限制-->
<property name="maxTotal" value="500"/>
<property name="maxWaitMillis" value="600000" />
<property name="maxIdle" value="20" />
<property name="minIdle" value="20" />
<property name="initialSize" value="20" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="100" />
<property name="removeAbandonedOnBorrow" value="true" />
<!-- 申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能 -->
<property name="testOnBorrow" value="true" />
<!-- 归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能 -->
<property name="testOnReturn" value="false" />
<!-- 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 -->
<property name="testWhileIdle" value="true" />
<!-- 用来检测连接是否有效的sql,要求是一个查询语句,如果validationQuery为 null,testOnBorrow、testOnReturn、testWhileIdle都不起其作用。 -->
<property name="validationQuery" value="SELECT 1 FROM DUAL" />
</bean>
数据源(从库)2:
<bean id="readOnlyDataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="url" value="jdbc:mysql://ip:port/dbname? useUnicode=true&autoReconnect=true&failOverReadOnly=false&characterEncoding=UTF-8" />
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="username" value="username" />
<property name="password" value="password" />
<!--maxTotal: 可以在这个池中同时被分配的有效连接数的最大值,如设置为负数,则不限制-->
<property name="maxTotal" value="500"/>
<property name="maxWaitMillis" value="600000" />
<property name="maxIdle" value="20" />
<property name="minIdle" value="20" />
<property name="initialSize" value="20" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="100" />
<property name="removeAbandonedOnBorrow" value="true" />
<!-- 申请连接时执行validationQuery检测连接是否有效,配置为true会降低性能 -->
<property name="testOnBorrow" value="true" />
<!-- 归还连接时执行validationQuery检测连接是否有效,配置为true会降低性能 -->
<property name="testOnReturn" value="false" />
<!-- 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 -->
<property name="testWhileIdle" value="true" />
<!-- 用来检测连接是否有效的sql,要求是一个查询语句,如果validationQuery为 null,testOnBorrow、testOnReturn、testWhileIdle都不起其作用。 -->
<property name="validationQuery" value="SELECT 1 FROM DUAL" />
</bean>
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并注入数据源,进行数据源切换。
<bean id="dataSource" class="com.core.database.dynamicDataBase.DynamicDataSource">
<property name="writeDataSource" ref="master" />
<property name="readDataSources">
<list>
<ref bean="readOnlyDataSource" />
</list>
</property>
<!--轮询方式 -->
<property name="readDataSourcePollPattern" value="1" />
<property name="defaultTargetDataSource" ref="master" />
</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.配置事务
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
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)=========