(其实更好的做法是使用sharding-jdbc 实现分表分库啥的)文档
垂直分表:可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表,这样既能使业务清晰,还能提升部分性能。拆分后,尽量从业务角度避免联查,否则性能方面将得不偿失。
垂直分库:可以把多个表按业务耦合松紧归类,分别存放在不同的库,这些库可以分布在不同服务器,从而使访问压力被多服务器负载,大大提升性能,同时能提高整体架构的业务清晰度,不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。
水平分库:可以把一个表的数据(按数据行)分到多个不同的库,每个库只有这个表的部分数据,这些库可以分布在不同服务器,从而使访问压力被多服务器负载,大大提升性能。它不仅需要解决跨库带来的所有复杂问题,还要解决数据路由的问题(数据路由问题后边介绍)。
水平分表:可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中,每个表只有这个表的部分数据,这样做能小幅提升性能,它仅仅作为水平分库的一个补充优化。
一般来说,在系统设计阶段就应该根据业务耦合松紧来确定垂直分库,垂直分表方案,在数据量及访问压力不是特别大的情况,首先考虑缓存、读写分离、索引技术等方案。若数据量极大,且持续增长,再考虑水平分库水平分表方案。
以上引用(https://blog.csdn.net/weixin_44062339/article/details/100491744)
1.配置两个数据源
<bean id="dataSyncchineseSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${spring.datasource.driverClassName}" />
<property name="url" value="${spring.datasource.url}" />
<property name="username" value="${spring.datasource.username}" />
<property name="password" value="${spring.datasource.password}" />
<property name="maxActive" value="${spring.datasource.maxActive}"/>
<property name="initialSize" value="${spring.datasource.initialSize}"/>
<property name="minIdle" value="${spring.datasource.minIdle}"/>
<property name="maxWait" value="${spring.datasource.maxWait}"/>
<property name="timeBetweenEvictionRunsMillis" value="${spring.datasource.timeBetweenEvictionRunsMillis}"/>
<property name="minEvictableIdleTimeMillis" value="${spring.datasource.timeBetweenEvictionRunsMillis}"/>
<property name="validationQuery" value="${spring.datasource.validationQuery}"/>
<property name="testWhileIdle" value="${spring.datasource.testWhileIdle}"/>
<property name="testOnBorrow" value="${spring.datasource.testOnBorrow}"/>
<property name="testOnReturn" value="${spring.datasource.testOnReturn}"/>
<property name="poolPreparedStatements" value="${spring.datasource.poolPreparedStatements}"/>
<property name="maxOpenPreparedStatements" value="${spring.datasource.maxOpenPreparedStatements}"/>
<property name="filters" value="${spring.datasource.filters}"/>
<property name="connectionProperties" value="${spring.datasource.connectionProperties}"/>
</bean>
<bean id="dataOperationSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
<property name="driverClassName" value="${spring.datasource.driverClassName}" />
<property name="url" value="${spring.datasource1.url}" />
<property name="username" value="${spring.datasource1.username}" />
<property name="password" value="${spring.datasource1.password}" />
<property name="maxActive" value="${spring.datasource.maxActive}"/>
<property name="initialSize" value="${spring.datasource.initialSize}"/>
<property name="minIdle" value="${spring.datasource.minIdle}"/>
<property name="maxWait" value="${spring.datasource.maxWait}"/>
<property name="timeBetweenEvictionRunsMillis" value="${spring.datasource.timeBetweenEvictionRunsMillis}"/>
<property name="minEvictableIdleTimeMillis" value="${spring.datasource.timeBetweenEvictionRunsMillis}"/>
<property name="validationQuery" value="${spring.datasource.validationQuery}"/>
<property name="testWhileIdle" value="${spring.datasource.testWhileIdle}"/>
<property name="testOnBorrow" value="${spring.datasource.testOnBorrow}"/>
<property name="testOnReturn" value="${spring.datasource.testOnReturn}"/>
<property name="poolPreparedStatements" value="${spring.datasource.poolPreparedStatements}"/>
<property name="maxOpenPreparedStatements" value="${spring.datasource.maxOpenPreparedStatements}"/>
<property name="filters" value="${spring.datasource.filters}"/>
<property name="connectionProperties" value="${spring.datasource.connectionProperties}"/>
</bean>
2.
<bean id="dataSource" class="com.XXX.XXX.XXX.XXX.admin.data.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<!-- 指定lookupKey和与之对应的数据源 -->
<entry key="dataSyncchineseSource" value-ref="dataSyncchineseSource"/>
<entry key="dataOperationSource" value-ref="dataOperationSource"/>
</map>
</property>
<!-- 这里可以指定默认的数据源 -->
<property name="defaultTargetDataSource" ref="dataSyncchineseSource" />
</bean>
<!-- mybatis的SqlSessionFactoryBean用于进行数据库操作 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:spring/mybatis-config.xml"/>
<property name="mapperLocations">
<list>
<value>classpath:com/XXX/**/dao/mapper/*.xml</value>
</list>
</property>
</bean>
3.新建一个DynamicDataSource.java
package com.XXX.XXX.XXX.XXX.admin.data;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource{
public static final String dataSyncchineseSource= "dataSyncchineseSource";
public static final String dataOperationSource = "dataOperationSource";
//本地线程,获取当前正在执行的currentThread
public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
/**
* 切换数据源
*@Description:
*@param dataSource
*@Author:hf
*@Creattime:2019年9月3日 下午8:52:17
*@Company:
*/
public static void changeDataSource(String dataSource) {
clearCustomerType();
DynamicDataSource.setCustomerType(dataSource);
}
public static void setCustomerType(String customerType) {
contextHolder.set(customerType);
}
public static String getCustomerType() {
return contextHolder.get();
}
public static void clearCustomerType() {
contextHolder.remove();
}
//重写
@Override
protected Object determineCurrentLookupKey() {
return getCustomerType();
}
}
//源码:AbstractRoutingDataSource
determineCurrentLookupKey()方法,这是AbstractRoutingDataSource类中的一个抽象方法,而它的返回值是你所要用的数据源dataSource的key值,有了这个key值,resolvedDataSources(这是个map,由配置文件中设置好后存入的
)就从中取出对应的DataSource,如果找不到,就用配置默认的数据源。原理大致如下 这时候可以扩展AbstractRoutingDataSource类,并重写其中的determineCurrentLookupKey()方法,来实现数据源的切换
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = this.determineCurrentLookupKey();
DataSource 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 + "]");
} else {
return dataSource;
}
}
@Nullable
protected abstract Object determineCurrentLookupKey();
4.具体使用
4.1
@ResponseBody
@RequestMapping(value = "/selectOperationActivity", method = RequestMethod.POST, produces = {"application/json;charset=UTF-8" })
public String selectOperationActivity( HttpServletRequest request,
HttpServletResponse response) {
OperationActivityVo operationActivityVo=new OperationActivityVo();
try {
DynamicDataSource.changeDataSource(DynamicDataSource.dataSyncchineseSource);
List<OperationActivityPojo> list = new ArrayList<OperationActivityPojo>();
operationActivityVo= operationActivityService.selectById(26);
} catch (Exception e) {
return ResultVoTemplate.outputFailed(e.getMessage(),FailedStatusEnum.OPERATE_FAIL, null);
}
return ResultVoTemplate.outputSuccess(operationActivityVo);
}
4.2
6.