Spring 数据库读写分离

现在大型的电子商务系统，在数据库层面大都采用读写分离技术，就是一个Master数据库，多个Slave数据库。Master库负责数据更新和实时数据查询，Slave库当然负责非实时数据查询。因为在实际的应用中，数据库都是读多写少（读取数据的频率高，更新数据的频率相对较少），而读取数据通常耗时比较长，占用数据库服务器的CPU较多，从而影响用户体验。我们通常的做法就是把查询从主库中抽取出来，采用多个从库，使用负载均衡，减轻每个从库的查询压力。

采用读写分离技术的目标：有效减轻Master库的压力，又可以把用户查询数据的请求分发到不同的Slave库，从而保证系统的健壮性。我们看下采用读写分离的背景。

　　随着网站的业务不断扩展，数据不断增加，用户越来越多，数据库的压力也就越来越大，采用传统的方式，比如：数据库或者SQL的优化基本已达不到要求，这个时候可以采用读写分离的策略来改变现状。

　　具体到开发中，如何方便的实现读写分离呢?目前常用的有两种方式：

　　1 第一种方式是我们最常用的方式，就是定义2个数据库连接，一个是MasterDataSource,另一个是SlaveDataSource。更新数据时我们读取MasterDataSource，查询数据时我们读取SlaveDataSource。这种方式很简单，我就不赘述了。

　　2 第二种方式动态数据源切换，就是在程序运行时，把数据源动态织入到程序中，从而选择读取主库还是从库。主要使用的技术是：annotation，Spring AOP ，反射。下面会详细的介绍实现方式。

此章讲述第一种方式：

创建DatabaseConfiguration

package com.testdatasource.third.configuration.datasource;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.testdatasource.common.enums.DatasourceType;
import com.testdatasource.third.configuration.properties.DruidProperties;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.type.JdbcType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;


@Configuration
@EnableAutoConfiguration
public class DatabaseConfiguration {
    @Autowired
    private ApplicationContext appContext;

    @Autowired
    private DruidProperties druidProperties;


    @Bean(name = "readDatasource")
    @Primary
    @ConfigurationProperties(prefix = "read.datasource")
    public DataSource readDatasource() {
        return getReadDruidDataSource();
    }

    /**
     * 写入数据源连接池配置
     */
    @Bean(name = "writeDatasource")
    @ConfigurationProperties(prefix = "write.datasource")
    public DataSource writeDatasource() {
        return getWriteDruidDataSource();
    }

    private DruidDataSource getReadDruidDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        druidProperties.configR(dataSource);
        return dataSource;
    }

    private DruidDataSource getWriteDruidDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        druidProperties.configW(dataSource);
        return dataSource;
    }

    //初始化数据源bean，这个bean很重要，后面事务控制也会用到
    @Bean
    public AbstractRoutingDataSource roundRobinDataSouceProxy(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) {
        DynamicDataSource proxy = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put( DatasourceType.read.getName(), read);
        targetDataSources.put(DatasourceType.write.getName(), write);
        proxy.setDefaultTargetDataSource(read);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
    //初始化SqlSessionFactory，将自定义的多数据源ReadWriteSplitRoutingDataSource类实例注入到工厂中
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) throws Exception {

        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory. setDataSource(this.roundRobinDataSouceProxy(read, write));
        sqlSessionFactory.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*Mapper.xml"));
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        sqlSessionFactory.setGlobalConfig(globalConfiguration());
        return sqlSessionFactory.getObject();
    }

    @Bean
    public GlobalConfiguration globalConfiguration() {
        GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector());
        conf.setLogicDeleteValue("-1");
        conf.setLogicNotDeleteValue("1");
        conf.setIdType(0);
        conf.setDbColumnUnderline(true);
        conf.setRefresh(true);
        return conf;
    }
}

View Code

创建 DataSourceContextHolder

package com.testdatasource.third.configuration.datasource;


import com.testdatasource.common.enums.DatasourceType;

/**
 * ClassName:DataSourceContextHolder
 *
 * @Description : 当前线程数据源
 */
public class DataSourceContextHolder {
    /**
     * 默认数据源
     */
    public static final String DEFAULT_DS = DatasourceType.read.getName();

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    // 设置数据源名
    public static void setDB(String dbType) {
        contextHolder.set(dbType);
    }

    // 获取数据源名
    public static String getDB() {
        return (contextHolder.get());
    }

    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }
}

View Code

创建 DynamicDataSource

package com.testdatasource.third.configuration.datasource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * ClassName:DynamicDataSource
 * 
 * @Description : 动态数据源
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDB();
    }
}

View Code

创建 MybatisPlusConfig

package com.testdatasource.third.configuration.datasource;

import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * ClassName:MybatisPlusConfig 
 * @Description : mybatis分页插件
 * @version  
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * mybatis-plus分页插件<br>
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setLocalPage(true);// 开启 PageHelper 的支持
        return paginationInterceptor;
    }
}

View Code

创建 MyDataSourceTransactionManagerAutoConfiguration

package com.testdatasource.third.configuration.datasource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

/**
 *
 * 多数据源事物
 */
@Configuration
@EnableTransactionManagement
public class MyDataSourceTransactionManagerAutoConfiguration extends DataSourceTransactionManagerAutoConfiguration {
    @Autowired
    private ApplicationContext appContext;
    /**
     * 自定义事务
     * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
     * @return
     */
    @Bean(name = "transactionManager")
    public DataSourceTransactionManager transactionManagers() {
        return new DataSourceTransactionManager((DataSource)appContext.getBean("roundRobinDataSouceProxy"));
    }
}

View Code

数据源枚举类

/**
 * Project Name:finance-activity-integralMallAPI
 * File Name:DatasourceType.java
 * Package Name:com.hexin.finance.third.configuration.datasource.util
 * Date:2018年7月4日下午1:29:31
 * Copyright (c) 2018
 *
*/

package com.hexin.creditforce.common.enums;

/**
 * ClassName:DatasourceType
 * 
 * @Description :数据库读写类型
 * @version
 */
public enum DatasourceType {
    write("write"), read("read");

    private String name;

    private DatasourceType(String name) {
        this.name = name();
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

View Code

数据源配置类

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import java.sql.SQLException;


/**
 * ClassName:DruidProperties
 * 
 * @Description :
 *              druid连接池配置文件属性,说明:这个类中包含了许多默认配置,若这些配置符合您的情况,您可以不用管,若不符合,建议不要修改本类,建议直接在"application.yml"中配置即可
 *              </p>
 */
@Component
@ConfigurationProperties(prefix = "spring.datasource")
public class DruidProperties {

    @Value("${spring.datasource.read_url}")
    private String rurl;
    @Value("${spring.datasource.read_username}")
    private String rusername;
    @Value("${spring.datasource.read_password}")
    private String rpassword;
    @Value("${spring.datasource.write_url}")
    private String wurl;
    @Value("${spring.datasource.write_username}")
    private String wusername;
    @Value("${spring.datasource.write_password}")
    private String wpassword;

    private String driverClassName = "com.mysql.jdbc.Driver";

    private Integer initialSize = 2;

    private Integer minIdle = 1;

    private Integer maxActive = 20;

    private Integer maxWait = 60000;

    private Integer timeBetweenEvictionRunsMillis = 60000;

    private Integer minEvictableIdleTimeMillis = 300000;

    private String validationQuery = "SELECT 1 FROM DUAL";

    private Boolean testWhileIdle = true;

    private Boolean testOnBorrow = false;

    private Boolean testOnReturn = false;

    private Boolean poolPreparedStatements = true;

    private Integer maxPoolPreparedStatementPerConnectionSize = 20;

    private String filters = "stat";

    public void configR(DruidDataSource dataSource) {

        dataSource.setUrl(rurl);
        dataSource.setUsername(rusername);
//        dataSource.setPassword(AESUtil.decrypt(rpassword, null));
        dataSource.setPassword(AESUtils.AESDecode(null,rpassword));

        dataSource.setDriverClassName(driverClassName);
        dataSource.setInitialSize(initialSize); // 定义初始连接数
        dataSource.setMinIdle(minIdle); // 最小空闲
        dataSource.setMaxActive(maxActive); // 定义最大连接数
        dataSource.setMaxWait(maxWait); // 最长等待时间

        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

        // 配置一个连接在池中最小生存的时间，单位是毫秒
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);

        // 打开PSCache，并且指定每个连接上PSCache的大小
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {
            dataSource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void configW(DruidDataSource dataSource) {

        dataSource.setUrl(wurl);
        dataSource.setUsername(wusername);
        dataSource.setPassword(AESUtils.AESDecode(null,wpassword));
        
        
        dataSource.setDriverClassName(driverClassName);
        dataSource.setInitialSize(initialSize); // 定义初始连接数
        dataSource.setMinIdle(minIdle); // 最小空闲
        dataSource.setMaxActive(maxActive); // 定义最大连接数
        dataSource.setMaxWait(maxWait); // 最长等待时间

        // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

        // 配置一个连接在池中最小生存的时间，单位是毫秒
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setValidationQuery(validationQuery);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);

        // 打开PSCache，并且指定每个连接上PSCache的大小
        dataSource.setPoolPreparedStatements(poolPreparedStatements);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

        try {
            dataSource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public String getRurl() {
        return rurl;
    }

    public void setRurl(String rurl) {
        this.rurl = rurl;
    }

    public String getRusername() {
        return rusername;
    }

    public void setRusername(String rusername) {
        this.rusername = rusername;
    }

    public String getRpassword() {
        return rpassword;
    }

    public void setRpassword(String rpassword) {
        this.rpassword = rpassword;
    }

    public String getWurl() {
        return wurl;
    }

    public void setWurl(String wurl) {
        this.wurl = wurl;
    }

    public String getWusername() {
        return wusername;
    }

    public void setWusername(String wusername) {
        this.wusername = wusername;
    }

    public String getWpassword() {
        return wpassword;
    }

    public void setWpassword(String wpassword) {
        this.wpassword = wpassword;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public Integer getInitialSize() {
        return initialSize;
    }

    public void setInitialSize(Integer initialSize) {
        this.initialSize = initialSize;
    }

    public Integer getMinIdle() {
        return minIdle;
    }

    public void setMinIdle(Integer minIdle) {
        this.minIdle = minIdle;
    }

    public Integer getMaxActive() {
        return maxActive;
    }

    public void setMaxActive(Integer maxActive) {
        this.maxActive = maxActive;
    }

    public Integer getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(Integer maxWait) {
        this.maxWait = maxWait;
    }

    public Integer getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public Integer getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public Boolean getTestWhileIdle() {
        return testWhileIdle;
    }

    public void setTestWhileIdle(Boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public Boolean getTestOnBorrow() {
        return testOnBorrow;
    }

    public void setTestOnBorrow(Boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public Boolean getTestOnReturn() {
        return testOnReturn;
    }

    public void setTestOnReturn(Boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public Boolean getPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public void setPoolPreparedStatements(Boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public Integer getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public String getFilters() {
        return filters;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }
}

View Code

yml配置

spring:
  datasource:
    type: com.alibaba.druid.pool.xa.DruidXADataSource
    read_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true
    read_username: root
    read_password: root
    write_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true
    write_username: root
    write_password: root

好了这样配置就没问题了。

本文链接：https://www.cnblogs.com/java-xz/p/10553040.html

Spring 数据库读写分离

Spring 数据库读写分离的更多相关文章

随机推荐

热门专题

目录导航