先说下大背景--用户数据交互数据业务数据都在同一个数据库上，当有时效的活动比如榜单结束的时候大量用户开始刷排名，导致数据库读写同步压力过大，数据延迟从而产生错误的排名； 当时对大数据表已经做了分表处理而且加了缓存，千人榜乃至万人榜的榜单结束是伴随着大量的计算，数据插入，消息推送；mysql IO瞬间就飙到60M；所以在不改变线上系统稳定运行的前提下把用户交互数据移出去。 springboot双数据库的例子网上有很多，但是不同的场景就有不同的处理方式。 直接上干货： 双数据源配置主要以下几个类：
/**
* Created by lixb on 2018/1/27.
* 多数据源数据库类型
* app基本库
* 社交库
* 消息库 等
*/
public enum DataSourceType {
db_app, //默认app
ab_msg //社交库
}
/**
* Created by lixb on 2018/1/26.
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({
ElementType.METHOD
})
public @interface DS {
DataSourceType value() default DataSourceType.db_app;
}
/**
* Created by lixb on 2018/1/26.
*/
public class DatabaseContextHolder {
private static final ThreadLocal<DataSourceType> contextHolder = new ThreadLocal<>();
// 设置数据源名
public static void setDB(DataSourceType dbType) {
contextHolder.set(dbType);
}
// 获取数据源名
public static DataSourceType getDB() {
return (contextHolder.get());
}
// 清除数据源名
public static void clearDB() {
contextHolder.remove();
}
}
/**
* Created by lixb on 2018/1/26.
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DatabaseContextHolder.getDB();
}
}
/** 切面控制切换数据源
* Created by lixb on 2018/1/26.
*/
@Aspect
@Component
public class DynamicDataSourceAspect {
@Before("@annotation(DS)")
public void beforeSwitchDS(JoinPoint point){
//获得当前访问的class
Class<?> className = point.getTarget().getClass();
//获得访问的方法名
String methodName = point.getSignature().getName();
//得到方法的参数的类型
Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
DataSourceType dataSourceType = DataSourceType.db_app;
try {
// 得到访问的方法对象
Method method = className.getMethod(methodName, argClass);
// 判断是否存在@DS注解
if (method.isAnnotationPresent(DS.class)) {
DS annotation = method.getAnnotation(DS.class);
// 取出注解中的数据源名
dataSourceType = annotation.value();
}
} catch (Exception e) {
e.printStackTrace();
}
// 切换数据源
DatabaseContextHolder.setDB(dataSourceType);
}
@After("@annotation(DS)")
public void afterSwitchDS(JoinPoint point){
DatabaseContextHolder.clearDB();
}
/**核心配置类，配置了druid线程池
* Created by lixb on 2018/1/26.
*/
@Configuration
public class MybatisBaseConfig {
@Autowired
WallFilter wallFilter;
@Autowired
StatFilter statFilter;
@Autowired
LogFilter logFilter;
@Bean(name = "wallConfig")
WallConfig wallFilterConfig(){
//这里设置访问限制 比如哪些表只可读 哪些表可写
WallConfig wc = new WallConfig ();
wc.setMultiStatementAllow(true);
return wc;
}
@Bean(name = "wallFilter")
@DependsOn("wallConfig")
WallFilter wallFilter(WallConfig wallConfig){
WallFilter wfilter = new WallFilter ();
wfilter.setConfig(wallConfig);
return wfilter;
}
@Bean(name = "statFilter")
MergeStatFilter statFilter(){
MergeStatFilter sfilter = new MergeStatFilter ();
sfilter.setSlowSqlMillis(3000);
sfilter.setLogSlowSql(true);
sfilter.setMergeSql(true);
return sfilter;
}
@Bean(name = "logFilter")
Slf4jLogFilter logFilter(){
Slf4jLogFilter lfilter = new Slf4jLogFilter();
lfilter.setStatementExecutableSqlLogEnable(false);
return lfilter;
}
@Bean(name = "dataSource")
@ConfigurationProperties(prefix = "spring.datasource") // application.properteis中对应属性的前缀
public DataSource dataSource1() {
DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
DruidDataSource druidDataSource = (DruidDataSource) dataSourceBuilder.build();
initDBInfo(druidDataSource);
return druidDataSource;
}
private void initDBInfo(DruidDataSource druidDataSource){
druidDataSource.setTimeBetweenConnectErrorMillis(600000);
druidDataSource.setTestWhileIdle(true);
druidDataSource.setValidationQuery("SELECT 1 FROM sys_common");
druidDataSource.setInitialSize(20);
druidDataSource.setMinIdle(10);
druidDataSource.setMaxActive(50);
druidDataSource.setMaxWait(60000);//获取链接的等待时间
druidDataSource.setMinEvictableIdleTimeMillis(300000);//配置单个线程的最小生命周期
druidDataSource.setTestOnBorrow(false);
druidDataSource.setTestOnReturn(false);
try {
List<Filter> filters = new ArrayList<>();
filters.add(wallFilter);
filters.add(statFilter);
filters.add(logFilter);
druidDataSource.setProxyFilters(filters);
}catch (Exception e){
e.printStackTrace();
}
}
@Bean(name = "db_msg")
@ConfigurationProperties(prefix = "spring.datasource1") // application.properteis中对应属性的前缀
public DataSource dataSource2() {
DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.type(com.alibaba.druid.pool.DruidDataSource.class);
DruidDataSource druidDataSource = (DruidDataSource) dataSourceBuilder.build();
initDBInfo(druidDataSource);
return dataSourceBuilder.build();
}
@Bean(name = "dynamicDS1")
public DataSource dataSource() {
DynamicDataSource dynamicDataSource = new DynamicDataSource();
// 默认数据源
dynamicDataSource.setDefaultTargetDataSource(dataSource1());
// 配置多数据源
Map<Object, Object> dsMap = new HashMap(5);
dsMap.put(DataSourceType.db_app, dataSource1());
dsMap.put(DataSourceType.ab_msg, dataSource2());
dynamicDataSource.setTargetDataSources(dsMap);
return dynamicDataSource;
}
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean() {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource());
bean.setTypeAliasesPackage("com.longbei.appservice.dao");
//分页插件
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("reasonable", "true");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "check");
properties.setProperty("params", "count=countSql");
pageHelper.setProperties(properties);
//添加插件
// bean.setPlugins(new Interceptor[]{pageHelper, new SqlMqElasticInterceptor()});
//开启懒加载
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLazyLoadingEnabled(true);
configuration.setAggressiveLazyLoading(false);
//开启缓存
configuration.setCacheEnabled(true);
bean.setConfiguration(configuration);
//添加XML目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath:mapping/*.xml"));
return bean.getObject();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Bean(name = "sqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
这里多说一点，以上代码我加了druid线程池以及响应监控的配置，监控配置了Servlet，也贴上来，大家可以参考：
@Bean(name = "druidServlet")
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
// IP白名单
// servletRegistrationBean.addInitParameter("allow", "192.168.1.117,127.0.0.1");
// IP黑名单(共同存在时，deny优先于allow)
// servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
//控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "admin");
//是否能够重置数据 禁用HTML页面上的“Reset All”功能
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
基本上以上代码可以跑起来了，因为两个数据源同一份配置，并且切换数据源通过切面触发，确保两个数据源之间没有连表操作；在mapper和cache之间封装Mappper实现，用户配置数据源；
/**
* Created by lixb on 2018/1/27.
*/
@Service
public class UserMsgMapperImpl {
@Autowired
private UserMsgMapper userMsgMapper;
@DS(DataSourceType.ab_msg)
public int deleteCommentMsg( String snsid, String gtype,
String gtypeid,
String commentid, String commentlowerid){
return userMsgMapper.deleteCommentMsg(snsid,gtype,gtypeid,commentid,commentlowerid);
}
@DS(DataSourceType.ab_msg)
public int deleteCommentMsgLike(String userid, String friendid){
return userMsgMapper.deleteCommentMsgLike(userid,friendid);
}
}
有问题欢迎qq 645627856；
![SpringBoot 应用 打包为 Docker 镜像，并设置时区同步[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)