多数据源切换并实现读写分离

coding_robot
251 阅读5分钟

背景

多数据中心（每个数据中心一主多从，根据租户划分数据中心），期望根据请求（上下文选取 对应的数据中心）判断如果是写请求则操作该数据中心的主库，如果是读请求则随机选取数据中心的从数据库进行访问。

思路&方案

思路

  1. 根据请求上下文获取到租户信息
  2. 判断是写请求还是读请求
  3. 根据租户和读写选取某个数据中的数据库配置
  4. 重新复制被选中的数据库切换DynamicDataSource
  5. 执行业务代码

方案

  • 程序启动后读取多数据源配置初始化DynamicDataSource设置数据源map
  • 定义注解 标识请求执行的 service方法是读请求还是写请求
  • 定义aop拦截 获取注解值以及上下文租户信息，组装切换数据库对应得 key
  • 根据组装的key 切换之前配置好的map中的 数据库配置

核心代码

  1. 动态数据源设置以及获取

    @Component
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class);
    private static DynamicDataSource instance;
    private static final byte[] LOCK = new byte[0];
    private static final Map<Object,Object> DATA_SOURCE_MAP = Maps.newConcurrentMap();

    private DynamicDataSource(){}

    /**
     * 获取当前数据源
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return (UserInfoContext.getUser() == null ||  Strings.isNullOrEmpty(UserInfoContext.getUser().getDbKey()))
                ? DBCluster.DEFAULT_DB_NAME_PRE :UserInfoContext.getUser().getDbKey();
    }

    /**
     * 设置数据源
     * @param targetDataSources
     */
    @Override
    public void setTargetDataSources(Map<Object, Object> targetDataSources) {
        super.setTargetDataSources(targetDataSources);
        synchronized (DATA_SOURCE_MAP) {
            DATA_SOURCE_MAP.putAll(targetDataSources);
        }
        super.afterPropertiesSet();
    }

    /**
     * 获取存储已实例的数据源map
     * @return
     */
    Map<Object, Object> getDataSourceMap() {
        return DATA_SOURCE_MAP;
    }

    public static synchronized DynamicDataSource getInstance(){
        if(instance == null){
            synchronized (LOCK){
                if(instance == null){
                    instance = new DynamicDataSource();
                }
            }
        }
        return instance;
    }

    /**
     * 是否存在当前key的 DataSource
     * @param key
     * @return 存在返回 true, 不存在返回 false
     */
    public static boolean isExistDataSource(String key) {
        return DATA_SOURCE_MAP.containsKey(key);
    }
}

  2. 数据源配置类

    @EnableConfigurationProperties({MybatisProperties.class})
public class DataSourceConfigure {
    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceConfigure.class);

    @Autowired
    private MultiDataSourceService multiDataSource;
    private MybatisProperties mybatisProperties;
    public DataSourceConfigure(MybatisProperties mybatisProperties) {
        this.mybatisProperties = mybatisProperties;
    }

    @Bean
    public DynamicDataSource dynamicDataSource() {
        DynamicDataSource dynamicDataSource =  DynamicDataSource.getInstance();
        Map<Object,Object> map = new HashMap<>();
        map.putAll(multiDataSource.getAllDruidDataSourceMap());
        DruidDataSource defaultDataSource = (DruidDataSource)dynamicDataSource.getDataSourceMap().getOrDefault(DBCluster.DATASOURCE_KEY_FORMAT,null);
        dynamicDataSource.setTargetDataSources(map);
        //设置默认数据源
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);

        return dynamicDataSource;
    }


		//mybatis plus 分页
    @ConditionalOnMissingBean
    public PaginationInterceptor paginationInterceptor(TenantHandler tenantHandler) {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setLimit(50);
        return paginationInterceptor;
    }

    /**
     *　配置mybatis的sqlSession连接动态数据源
     * @param dynamicDataSource
     * @return
     * @throws Exception
     */
    @Bean
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("dynamicDataSource") DataSource dynamicDataSource)
            throws Exception {

        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        MybatisConfiguration mybatisConfiguration =  (MybatisConfiguration)mybatisProperties.getConfiguration();
        if (mybatisConfiguration == null){
            mybatisConfiguration = new MybatisConfiguration();
        }

        mybatisConfiguration.setMapUnderscoreToCamelCase(false);
        mybatisConfiguration.addInterceptor(paginationInterceptor(tiensTenantHandler()));
        sqlSessionFactoryBean.setConfiguration(mybatisConfiguration);
        sqlSessionFactoryBean.setMapperLocations(mybatisProperties.resolveMapperLocations());
        sqlSessionFactoryBean.setTypeAliasesPackage(mybatisProperties.getTypeAliasesPackage());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/*Mapper.xml"));
        return sqlSessionFactoryBean.getObject();


        //return bean.getObject();
    }

    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
            @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory)
            throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 将动态数据源添加到事务管理器中，并生成新的bean
     * @return the platform transaction manager
     */
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}


    @Component
@EnableConfigurationProperties({MultiDataSourceProperties.class})
public class MultiDataSourceService {
    private static final Logger LOGGER = LoggerFactory.getLogger(MultiDataSourceService.class);

    private final Map<Integer,Map<Integer, List<DruidDataSource>>> dbDruidDataSourceWithIndexMap = Maps.newConcurrentMap();
    private final MultiDataSourceProperties mybatisProperties;

    public MultiDataSourceService(MultiDataSourceProperties mybatisProperties) {
        this.mybatisProperties = mybatisProperties;
    }

    public int getDbIndex (String source){
        return this.mybatisProperties.getDb().indexOf(getSingleDataSourceProperties(source));
    }

    public SingleDataProperties getSingleDataSourceProperties(String source){
        return this.mybatisProperties.getSingleDataSourceProperties(source);
    }

    private Map<Integer,List<DruidDataSource>> getDateSource(String source) {
        int index = getDbIndex(source);
        if (dbDruidDataSourceWithIndexMap.containsKey(index)){
            return dbDruidDataSourceWithIndexMap.get(index);
        }else {
            synchronized (dbDruidDataSourceWithIndexMap){
                Map<Integer, List<DruidDataSource>> map =  getCurrentDateSource(source);
                dbDruidDataSourceWithIndexMap.put(index,map);
                return map;
            }
        }
    }

    public Tuple2<Integer,Map<Integer,List<DruidDataSource>>>  getDateSourceTuple(String source) {
        int index = getDbIndex(source);
        if (dbDruidDataSourceWithIndexMap.containsKey(index)){
           return Tuple2.of(index,dbDruidDataSourceWithIndexMap.get(index));
        }else {
            synchronized (dbDruidDataSourceWithIndexMap){
                Map<Integer, List<DruidDataSource>> map =  getCurrentDateSource(source);
                dbDruidDataSourceWithIndexMap.put(index,map);
                return Tuple2.of(index,map);
            }
        }
    }

    private Map<Integer, List<DruidDataSource>> getCurrentDateSource (String source){
        Map<Integer, List<DruidDataSource>> map = Maps.newHashMap();
        try {
            List<SelfMybatisProperties> properties = getSingleDataSourceProperties(source).getAttribute();
            if (CollectionUtil.isEmpty(properties)) {
                return map;
            }

            map = properties.stream()
                    .collect(Collectors.groupingBy(SelfMybatisProperties::getMasterSlave,
                            Collectors.mapping(p -> {
                                DruidDataSource dataSource = new DruidDataSource();
                                dataSource.setUrl(p.getUrl());
                                dataSource.setUsername(p.getUser());
                                dataSource.setPassword(p.getPassword());
                                dataSource.setDriverClassName(p.getDriverClassName());
                                return dataSource;
                            }, Collectors.toList())));

        } catch (Exception ex) {
            log.error("getCurrentDateSource:",ex );
        }
        return map;
    }

    public Map<String,DruidDataSource> getAllDruidDataSourceMap (){
       return initData();
    }

    private Map<String,DruidDataSource> initData(){
        Map<String,DruidDataSource> result = Maps.newHashMap();
        for (int i = 0;i < mybatisProperties.getDb().size();i++) {
            SingleDataProperties singleDataProperties =  mybatisProperties.getDb().get(i);
            List<SelfMybatisProperties> properties = singleDataProperties.getAttribute();
            if (CollectionUtil.isEmpty(properties)) {
                continue;
            }
            List<SelfMybatisProperties>  masterProperties = properties.stream().filter(p -> p.getMasterSlave() == 0).collect(Collectors.toList());
            List<SelfMybatisProperties>  salveProperties = properties.stream().filter(p -> p.getMasterSlave() == 1).collect(Collectors.toList());

            handlerResult(masterProperties,result,i);
            handlerResult(salveProperties,result,i);

        }
        return result;
    }

    private void handlerResult( List<SelfMybatisProperties> properties,Map<String,DruidDataSource> result,int dbIndex){
        for (int j = 0;j<properties.size();j++) {
            String key = String.format(DBCluster.DATASOURCE_KEY_FORMAT, DBCluster.DB_NAME_PRE + dbIndex, properties.get(j).getMasterSlave(), j);
            if (result.containsKey(key)){
                continue;
            }

            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setUrl(properties.get(j).getUrl());
            dataSource.setUsername(properties.get(j).getUser());
            dataSource.setPassword(properties.get(j).getPassword());
            dataSource.setDriverClassName(properties.get(j).getDriverClassName());
            dataSource.setValidationQuery("SELECT 1 FROM DUAL");
            dataSource.setTestWhileIdle(true);
            dataSource.setTestOnBorrow(false);
            dataSource.setTestOnReturn(false);
            dataSource.setPoolPreparedStatements(true);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            dataSource.setMinEvictableIdleTimeMillis(300000);
            dataSource.setTimeBetweenEvictionRunsMillis(60000);
            dataSource.setMaxActive(20);
            dataSource.setMaxWait(60000);
            dataSource.setMinIdle(5);
            dataSource.setInitialSize(5);
            dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            dataSource.setDbType("com.alibaba.druid.pool.DruidDataSource");
            Properties dataProperties = new Properties();
            dataProperties.setProperty("druid.stat.mergeSql", "true");
            dataProperties.setProperty("druid.stat.slowSqlMillis", "5000");
            dataSource.setConnectProperties(dataProperties);
            result.put(key,dataSource);
        }
    }


}

  3. MultiDataSourceProperties 用于接收配置映射对象

    @ConfigurationProperties(prefix = "ds")
public class MultiDataSourceProperties {
    private List<SingleDataProperties> db;


    public SingleDataProperties getDefaultDb(){
       return getSingleDataSourceProperties(DBCluster.DEFAULT);
    }
    public SingleDataProperties getSingleDataSourceProperties(String source){
        Optional<SingleDataProperties> optional =  db.stream().filter(d -> d.getTenantNation().contains(source)).findFirst();
        return optional.orElseGet(this::getDefaultDb);
    }
}

@Data
public class SelfMybatisProperties implements Serializable {
    private String url;
    private String password;
    private String type;
    private String user;
    private int masterSlave;
    private String driverClassName;
}

@Data
public class SingleDataProperties implements Serializable {
    private String tenant_nation;
    private List<String> tenantNation = Lists.newArrayList();
    private List<SelfMybatisProperties> attribute = Lists.newArrayList();

    public List<String> getTenantNation() {
       if (Strings.isNullOrEmpty(tenant_nation)){
           return tenantNation;
       }else {
           return StrUtil.split(tenant_nation,',');
       }
    }
}

  4. Aop切面 拦截自定义注解根据当前租户拼装好对应的dataSource key 保存至上下文用于切

    @Component
public class DynamicDataSourceAspect {

    private static final Random random = new Random();
    @Autowired
    private MultiDataSourceService multiDataSourceService;

    @Before("@annotation(dbType)")
    public void switchDataSource(JoinPoint pjp, DBConfigKey dbType) throws Throwable {
        UserInfo userInfo = UserInfoContext.getUser();
        if (userInfo != null){
            Tuple2<Integer, Map<Integer,List<DruidDataSource>>> tuple2 = multiDataSourceService.getDateSourceTuple(userInfo.getSelectNationId().toLowerCase());
            List<DruidDataSource>  druidDataSources =tuple2.r2
                .getOrDefault(dbType.value().getValue(),null);
            if (CollectionUtil.isNotEmpty(druidDataSources)){
                int size = druidDataSources.size();
                int num = random.nextInt(size);
                String key = String.format(DBCluster.DATASOURCE_KEY_FORMAT
                        ,DBCluster.DB_NAME_PRE + tuple2.r1,dbType.value().getValue(),num);
                if (DynamicDataSource.isExistDataSource(key)) {
                   UserInfoContext.getUser().setDbKey(key);
                }
            }
        }
    }
}

对应的配置参数(多租户支持逗号或数组方式配置)

ds.db[0].tenant_nation[0] = default   //默认数据中心 其他配置 如：ru，zk ua

ds.db[0].attribute[0].url= jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false //地址
ds.db[0].attribute[0].password = root //密码
ds.db[0].attribute[0].user = root //用户名
ds.db[0].attribute[0].driver-class-name = com.mysql.cj.jdbc.Driver 
ds.db[0].attribute[0].master_slave = 0 // 该数据库是 主/从
ds.db[0].attribute[0].type = com.alibaba.druid.pool.DruidDataSource

ds.db[0].attribute[1].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[0].attribute[1].password = root
ds.db[0].attribute[1].user = root
ds.db[0].attribute[1].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[0].attribute[1].master_slave = 1
ds.db[0].attribute[1].type = com.alibaba.druid.pool.DruidDataSource

ds.db[0].attribute[2].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[0].attribute[2].password = root
ds.db[0].attribute[2].user = root
ds.db[0].attribute[2].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[0].attribute[2].master_slave = 1
ds.db[0].attribute[2].type = com.alibaba.druid.pool.DruidDataSource

ds.db[0].attribute[3].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[0].attribute[3].password = root
ds.db[0].attribute[3].user = root
ds.db[0].attribute[3].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[0].attribute[3].master_slave = 1
ds.db[0].attribute[3].type = com.alibaba.druid.pool.DruidDataSource


ds.db[1].tenant_nation = ru,us

ds.db[1].attribute[0].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[1].attribute[0].password = root
ds.db[1].attribute[0].user = root
ds.db[1].attribute[0].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[1].attribute[0].master_slave = 0
ds.db[1].attribute[0].type = com.alibaba.druid.pool.DruidDataSource

ds.db[1].attribute[1].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[1].attribute[1].password = root
ds.db[1].attribute[1].user = root
ds.db[1].attribute[1].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[1].attribute[1].master_slave = 1
ds.db[1].attribute[1].type = com.alibaba.druid.pool.DruidDataSource

ds.db[1].attribute[2].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[1].attribute[2].password = root
ds.db[1].attribute[2].user = root
ds.db[1].attribute[2].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[1].attribute[2].master_slave = 1
ds.db[1].attribute[2].type = com.alibaba.druid.pool.DruidDataSource

ds.db[1].attribute[3].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[1].attribute[3].password = root
ds.db[1].attribute[3].user = root
ds.db[1].attribute[3].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[1].attribute[3].master_slave = 1
ds.db[1].attribute[3].type = com.alibaba.druid.pool.DruidDataSource


ds.db[2].tenant_nation[0] = zk
ds.db[2].tenant_nation[1] = ua

ds.db[2].attribute[0].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[2].attribute[0].password = root
ds.db[2].attribute[0].user = root
ds.db[2].attribute[0].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[2].attribute[0].master_slave = 0
ds.db[2].attribute[0].type = com.alibaba.druid.pool.DruidDataSource

ds.db[2].attribute[1].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[2].attribute[1].password = root
ds.db[2].attribute[1].user = root
ds.db[2].attribute[1].ru.driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[2].attribute[1].master_slave = 1
ds.db[2].attribute[1].type = com.alibaba.druid.pool.DruidDataSource

ds.db[2].attribute[2].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[2].attribute[2].password = root
ds.db[2].attribute[2].user = root
ds.db[2].attribute[2].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[2].attribute[2].master_slave = 1
ds.db[2].attribute[2].type = com.alibaba.druid.pool.DruidDataSource

ds.db[2].attribute[3].url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
ds.db[2].attribute[3].password = root
ds.db[2].attribute[3].user = root
ds.db[2].attribute[3].driver-class-name = com.mysql.cj.jdbc.Driver
ds.db[2].attribute[3].master_slave = 1
ds.db[2].attribute[3].type = com.alibaba.druid.pool.DruidDataSource

使用方法

  • 【读操作】在Service【类】中的【方法】加@DBConfigKey(DBType.*SLAVE*)
public class ProductController {
    @GetMapping()
    @DBConfigKey(DBType.SLAVE)
    public void getProductDetail(int productId) {
        //执行查询操作
    }
}
  • 【写操作】在Service【类】中的【方法】加@DBConfigKey(DBType.*MASTER*)
public class ProductController {
    @PutMapping()
    @DBConfigKey(DBType.MASTER)
    public void insertProduct() {
        //执行插入操作
    }
}

参考：https://www.jianshu.com/p/7f1b785cd986

avatar
文章
阅读
粉丝