一直做了互联网的小项目,感觉小公司的性能瓶颈主要在数据库端。大公司没去过,不清楚~
一般用mysql数据库做主从,读写分离,减少主库的压力。假设1主4从。4个从库每次的访问是随机,压力平摊。
先把搜来的贴出来。先记录下,再去code实验~
采用spring的AbstractRoutingDataSource就可以简单的解决这个问题。下面是用ibatis的。单独的spring mvc 实现也是用AbstractRoutingDataSource类
AbstractRoutingDataSource实现了javax.sql.DataSource接口,因此可以理解为一个虚拟的动态DataSource,在需要的时候根据上下文Context动态决定使用哪个数据源。
下面这个是ibatis 的 没实验只是简单看看,不过每次 service都要
- DbContextHolder.setDbType("2");
/******************************一 、复制来的例子 开始**********************************************/
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:aop="http://www.springframework.org/schema/aop"
- xmlns:tx="http://www.springframework.org/schema/tx"
- xmlns:jee="http://www.springframework.org/schema/jee"
- xsi:schemaLocation="
- http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
- http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
- http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
- http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd">
- <!-- ========================= GENERAL DEFINITIONS ========================= -->
- <jee:jndi-lookup id="ds0" jndi-name="jdbc/ds0"/>
- <jee:jndi-lookup id="ds1" jndi-name="jdbc/ds1"/>
- <jee:jndi-lookup id="ds2" jndi-name="jdbc/ds2"/>
- <bean id="dataSource" class="com.xxx.xxx.util.DynamicDataSource">
- <property name="targetDataSources">
- <map key-type="java.lang.String">
- <entry key="0" value-ref="ds0"/>
- <entry key="1" value-ref="ds1"/>
- <entry key="2" value-ref="ds2"/>
- </map>
- </property>
- <property name="defaultTargetDataSource" ref="1"/>
- </bean>
- <!-- SqlMap setup for iBATIS Database Layer -->
- <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
- <property name="dataSource" ref="dataSource"/>
- <property name="configLocation" value="classpath:com/xxx/xxx/dao/sqlmap/sql-map-config.xml"/>
- </bean>
- <bean id="testDAO" class="com.xxx.xxx.dao.impl.TestDAO">
- <property name="sqlMapClient" ref="sqlMapClient"/>
- </bean>
- <bean id="testService" class="com.xxx.xxx.service.impl.TestService">
- <property name="testDAO" ref="testDAO"/>
- </bean>
- </beans>
其核心是DynamicDataSource,代码如下
- package com.xxx.xxx.util;
- import org.apache.log4j.Logger;
- import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
- public class DynamicDataSource extends AbstractRoutingDataSource {
- static Logger log = Logger.getLogger("DynamicDataSource");
- @Override
- protected Object determineCurrentLookupKey() {
- // TODO Auto-generated method stub
- return DbContextHolder.getDbType();
- }
- }
上下文DbContextHolder为一线程安全的ThreadLocal,如下
- package com.xxx.xxx.util;
- public class DbContextHolder {
- private static final ThreadLocal contextHolder = new ThreadLocal();
- public static void setDbType(String dbType) {
- contextHolder.set(dbType);
- }
- public static String getDbType() {
- return (String) contextHolder.get();
- }
- public static void clearDbType() {
- contextHolder.remove();
- }
- }
sql-map-config.xml如下
- <?xml version="1.0" encoding="UTF-8" standalone="no"?>
- <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
- "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
- <sqlMapConfig>
- <sqlMap resource="com/xxx/xxx/dao/sqlmap/Object.xml"/>
- </sqlMapConfig>
这样在调用service之前只需要设置一下上下文即可调用相应的数据源,如下:
- DbContextHolder.setDbType("2");
- //execute services
- //.........
dao如下
- package com.xxx.xxx.dao.impl;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.log4j.Logger;
- import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
- import com.xxx.xxx.vo.TestObj;
- public class TestDAO extends SqlMapClientDaoSupport implements ITestDAO {
- static Logger log = Logger.getLogger(TestDAO.class);
- public TestObj getTestObj(String objID) throws Exception {
- return (TestObj) getSqlMapClientTemplate().queryForObject("getTestObj", objID);
- }
- }
/******************************一 、复制来的例子 结束**********************************************/
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">
<!-- 数据库配置文件加载 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:/jdbc.properties</value>
</list>
</property>
</bean>
<!-- 数据源parent-->
<bean id="parentDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close" abstract="true">
<property name="driverClass">
<value>${jdbc.driverClassName}</value>
</property>
<property name="jdbcUrl">
<value>${jdbc.url}</value>
</property>
<property name="user">
<value>${jdbc.username}</value>
</property>
<property name="password">
<value>${jdbc.password}</value>
</property>
<property name="maxPoolSize">
<value>${jdbc.maxPoolSize}</value>
</property>
<property name="minPoolSize">
<value>${jdbc.minPoolSize}</value>
</property>
<property name="initialPoolSize">
<value>${jdbc.initialPoolSize}</value>
</property>
<property name="idleConnectionTestPeriod">
<value>${jdbc.idleConnectionTestPeriod}
</value>
</property>
<property name="maxIdleTime">
<value>${jdbc.maxIdleTime}</value>
</property>
</bean>
<!-- 主库数据源-->
<bean id="writedb" parent="parentDataSource"></bean>
<!-- 从库数据源-->
<bean id="read02" destroy-method="close" parent="parentDataSource">
<property name="jdbcUrl">
<value>${jdbc.read.db02.url}</value>
</property>
<property name="user">
<value>${jdbc.read.db02.username}</value>
</property>
<property name="password">
<value>${jdbc.read.db02.password}</value>
</property>
</bean>
<!-- 从库数据源-->
<bean id="read03" destroy-method="close" parent="parentDataSource">
<property name="jdbcUrl">
<value>${jdbc.read.db03.url}</value>
</property>
<property name="user">
<value>${jdbc.read.db03.username}</value>
</property>
<property name="password">
<value>${jdbc.read.db03.password}</value>
</property>
</bean>
<!-- 动态数据源 -->
<bean id="dataSource" class="com.share.common.database.DataSourceRouter">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="writedb" value-ref="writedb" />
<entry key="read02" value-ref="read02" />
<entry key="read03" value-ref="read03" />
</map>
</property>
<property name="defaultTargetDataSource" ref="writedb" />
<property name="dataSourceKey">
<ref local="dataSourceKey" />
</property>
</bean>
<!-- 读写管理 -->
<bean id="dataSourceKey" class="com.share.common.database.DataSourceKeyImpl">
<property name="readDateSourceMap">
<map key-type="java.lang.String">
<entry key="read02" value="read02" />
<entry key="read03" value="read03" />
</map>
</property>
<property name="writedbKey">
<value>writedb</value>
</property>
</bean>
<!-- 事务配置 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 事务管理拦截器 -->
<bean id="transactionInterceptor"
class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="transactionManager" />
<property name="transactionAttributes">
<props>
<prop key="query*">PROPAGATION_SUPPORTS,-Exception</prop>
<prop key="select*">PROPAGATION_SUPPORTS,-Exception</prop>
<prop key="find*">PROPAGATION_SUPPORTS,-Exception</prop>
<prop key="get*">PROPAGATION_SUPPORTS,-Exception</prop>
<prop key="save*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="delete*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="add*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="edit*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="*">PROPAGATION_SUPPORTS,-Exception</prop>
</props>
</property>
</bean>
<!-- 动态数据源拦截器 -->
<bean id="dataSourceInterceptor" class="com.share.common.database.DataSourceInterceptor">
<property name="attributes">
<props>
<prop key="query*">readdb</prop>
<prop key="select*">readdb</prop>
<prop key="find*">readdb</prop>
<prop key="get*">readdb</prop>
<prop key="save*">writedb</prop>
<prop key="update*">writedb</prop>
<prop key="delete*">writedb</prop>
<prop key="add*">writedb</prop>
<prop key="edit*">writedb</prop>
<prop key="*">readdb</prop>
</props>
</property>
<property name="dataSourceKey">
<ref bean="dataSourceKey" />
</property>
</bean>
<!-- 根据service名称拦截 -->
<bean
class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
<property name="beanNames">
<list>
<value>*Service</value>
</list>
</property>
<property name="interceptorNames">
<list>
<value>dataSourceInterceptor</value>
</list>
</property>
</bean>
<!-- ibatis sqlMapClient-->
<bean id="sqlMapClient"
class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation"
value="classpath:/config/ibatis/SqlMapConfig.xml"/>
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- dao 基类 -->
<bean id="simpleDao" class="com.share.common.dao.IbatisSimpleDaoImpl">
<property name="sqlMapClient" ref="sqlMapClient"/>
</bean>
</beans>
jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
jdbc.username=root
jdbc.password=root
jdbc.maxPoolSize=100
jdbc.minPoolSize=10
jdbc.initialPoolSize=10
jdbc.idleConnectionTestPeriod=900
jdbc.maxIdleTime=1800
jdbc.read.db02.url=jdbc:mysql://127.0.0.1:3306/test02?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
jdbc.read.db02.username=root
jdbc.read.db02.password=root
jdbc.read.db03.url=jdbc:mysql://127.0.0.1:3306/test03?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
jdbc.read.db03.username=root
jdbc.read.db03.password=root
![](https://img-my.csdn.net/uploads/201209/21/1348202829_2936.jpg)
![](https://img-my.csdn.net/uploads/201209/21/1348202838_9378.jpg)