mysql读写分离之springboot集成

大家好，又见面了，我是你们的朋友全栈君。

springboot、mysql实现读写分离

1、首先在springcloud config中配置读写数据库

mysql:  
  datasource:  
    readSize: 1  #读库个数  
    type: com.alibaba.druid.pool.DruidDataSource 
    write:  
       url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false 
       username: root  
       password: 123123  
       driver-class-name: com.mysql.cj.jdbc.Driver
       minIdle: 5  
       maxActive: 100  
       initialSize: 10  
       maxWait: 60000  
       timeBetweenEvictionRunsMillis: 60000  
       minEvictableIdleTimeMillis: 300000  
       validationQuery: select 'x'  
       testWhileIdle: true  
       testOnBorrow: false  
       testOnReturn: false  
       poolPreparedStatements: true  
       maxPoolPreparedStatementPerConnectionSize: 50  
       removeAbandoned: true  
       filters: stat  
    read01:  
       url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false
       username: root  
       password: 123123  
       driver-class-name: com.mysql.cj.jdbc.Driver
       minIdle: 5  
       maxActive: 100  
       initialSize: 10  
       maxWait: 60000  
       timeBetweenEvictionRunsMillis: 60000  
       minEvictableIdleTimeMillis: 300000  
       validationQuery: select 'x'  
       testWhileIdle: true  
       testOnBorrow: false  
       testOnReturn: false  
       poolPreparedStatements: true  
       maxPoolPreparedStatementPerConnectionSize: 50  
       removeAbandoned: true  
       filters: stat  
    read02:  
       url: jdbc:mysql://200.200.4.34:3306/quote?characterEncoding=utf8&useSSL=false
       username: root  
       password: 123123  
       driver-class-name: com.mysql.cj.jdbc.Driver
       minIdle: 5  
       maxActive: 100  
       initialSize: 10  
       maxWait: 60000  
       timeBetweenEvictionRunsMillis: 60000  
       minEvictableIdleTimeMillis: 300000  
       validationQuery: select 'x'  
       testWhileIdle: true  
       testOnBorrow: false  
       testOnReturn: false  
       poolPreparedStatements: true  
       maxPoolPreparedStatementPerConnectionSize: 50  
       removeAbandoned: true  
       filters: stat  

2、编写读库注解

import java.lang.annotation.Documented;  
import java.lang.annotation.ElementType; 
import java.lang.annotation.Inherited;  
import java.lang.annotation.Retention;  
import java.lang.annotation.RetentionPolicy;  
import java.lang.annotation.Target;  

@Target({ElementType.METHOD, ElementType.TYPE})  
@Retention(RetentionPolicy.RUNTIME)  
@Inherited  
@Documented  
public @interface ReadDataSource {  

} 

3、增加数据源初始化配置

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

/**
 * name：DataSourceConfiguration  
 * <p></p>    
 * @author：lipeng    
 * @data：2018年6月27日 下午5:55:35      
 * @version  1.0
 */
@Configuration  
public class DataSourceConfiguration {  

    private static Logger log = LoggerFactory.getLogger(DataSourceConfiguration.class);  

    @Value("${mysql.datasource.type}")  
    private Class<? extends DataSource> dataSourceType;  

    /** 
     * 写库 数据源配置 
     * @return 
     */  
    @Bean(name = "writeDataSource")  
    @Primary  
    @ConfigurationProperties(prefix = "mysql.datasource.write")  
    public DataSource writeDataSource() {  
        log.info("-------------------- writeDataSource init ---------------------");  
        return DataSourceBuilder.create().type(dataSourceType).build();  
    }  

    /** 
     * 有多少个从库就要配置多少个 
     * @return 
     */  
    @Bean(name = "readDataSource01")  
    @ConfigurationProperties(prefix = "mysql.datasource.read01")  
    public DataSource readDataSourceOne() {  
        log.info("-------------------- read01 DataSourceOne init ---------------------");  
        return DataSourceBuilder.create().type(dataSourceType).build();  
    }


    @Bean(name = "readDataSource02")  
    @ConfigurationProperties(prefix = "mysql.datasource.read02")  
    public DataSource readDataSourceTwo() {  
        log.info("-------------------- read01 DataSourceOne init ---------------------");  
        return DataSourceBuilder.create().type(dataSourceType).build();  
    }

    @Bean("readDataSources")
    public List<DataSource> readDataSources(){
        List<DataSource> dataSources=new ArrayList<>();
        dataSources.add(readDataSourceOne());
        dataSources.add(readDataSourceTwo());
        return dataSources;
    }

}  

4、增加主从配置常量

/**
 * name：DataSourceType  
 * <p></p>    
 * @author：lipeng    
 * @data：2018年6月28日 上午9:25:44      
 * @version  1.0
 */
public enum DataSourceType {  

    read("read", "从库"),  
    write("write", "主库");  

    private String type;  

    private String name;  

    DataSourceType(String type, String name) {  
        this.type = type;  
        this.name = name;  
    }  

    public String getType() {  
        return type;  
    }  

    public void setType(String type) {  
        this.type = type;  
    }  

    public String getName() {  
        return name;  
    }  

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

}  

5、事务内读写配置

由于涉及到事务处理，可能会遇到事务中同时用到读库和写库，可能会有延时造成脏读，所以增加了线程变量设置，来保证一个事务内读写都是同一个库

/**
 * name：DataSourceContextHolder  
 * <p></p>    
 * @author：lipeng    
 * @data：2018年6月27日 下午5:57:39      
 * @version  1.0
 */
public class DataSourceContextHolder {  

    private static Logger log = LoggerFactory.getLogger(DataSourceContextHolder.class);  

    //线程本地环境  
    private static final ThreadLocal<String> local = new ThreadLocal<String>();  

    public static ThreadLocal<String> getLocal() {  
        return local;  
    }  

    /** 
     * 读库 
     */  
    public static void setRead() {  
        local.set(DataSourceType.read.getType());  
        log.info("数据库切换到读库...");  
    }  

    /** 
     * 写库 
     */  
    public static void setWrite() {  
        local.set(DataSourceType.write.getType());  
        log.info("数据库切换到写库...");  
    }  

    public static String getReadOrWrite() {  
        return local.get();  
    }  

    public static void clear(){  
        local.remove();  
    }  
}  

如果在注解在service层并且声明式事务也在service层，这个得保证拦截器优先级在声明式事务前面

/**
 * name：DataSourceAopInService  
 * 在service层觉得数据源 
 * 必须在事务AOP之前执行，所以实现Ordered,order的值越小，越先执行 
 * 如果一旦开始切换到写库，则之后的读都会走写库 
 *     
 * @author：lipeng    
 * @data：2018年6月27日 下午5:59:17      
 * @version  1.0
 */
@Aspect  
@EnableAspectJAutoProxy(exposeProxy=true,proxyTargetClass=true)  
@Component  
public class DataSourceAopInService implements PriorityOrdered{  

private static Logger log = LoggerFactory.getLogger(DataSourceAopInService.class);  


    @Before("@annotation(com.sangfor.quote.datasource.annotation.ReadDataSource) ")  
    public void setReadDataSourceType() {  
        //如果已经开启写事务了，那之后的所有读都从写库读  
        if(!DataSourceType.write.getType().equals(DataSourceContextHolder.getReadOrWrite())){  
            DataSourceContextHolder.setRead();  
        }  

    }  

    @Before("@annotation(com.sangfor.quote.datasource.annotation.WriteDataSource) ")  
    public void setWriteDataSourceType() {  
        DataSourceContextHolder.setWrite();  
    }  

    @Override  
    public int getOrder() {  
        /** 
         * 值越小，越优先执行 
         * 要优于事务的执行 
         * 在启动类中加上了@EnableTransactionManagement(order = 10)  
         */  
        return 1;  
    }  

} 

并且在启动类或者配置类中增加注解order配置
@EnableTransactionManagement(order = 10)

6、增加mybatis相关配置类

mybatis配置

@Configuration
@AutoConfigureAfter(DataSourceConfiguration.class)
@MapperScan(basePackages = "com.sangfor.springboot")
public class MybatisConfiguration {

    private static Logger log = LoggerFactory.getLogger(MybatisConfiguration.class);

    @Value("${mysql.datasource.readSize}")
    private String readDataSourceSize;
    @Autowired
    @Qualifier("writeDataSource")
    private DataSource writeDataSource;
    @Autowired
    @Qualifier("readDataSources")
    private List<DataSource> readDataSources;

    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
        sqlSessionFactoryBean.setTypeAliasesPackage("com.sangfor.quote.model");
         //设置mapper.xml文件所在位置   
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml");  
        sqlSessionFactoryBean.setMapperLocations(resources);  
        sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();
    }

    /**
     * 有多少个数据源就要配置多少个bean
     * 
     * @return
     */
    @Bean
    public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        int size = Integer.parseInt(readDataSourceSize);
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
        // 写
        targetDataSources.put(DataSourceType.write.getType(), writeDataSource);
        // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
        // 多个读数据库时
        for (int i = 0; i < size; i++) {
            targetDataSources.put(i, readDataSources.get(i));
        }
        proxy.setDefaultTargetDataSource(writeDataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }

}

多数据源切换

/**
 * 多数据源切换
 * name：MyAbstractRoutingDataSource  
 * <p></p>    
 * @author：lipeng    
 * @data：2018年6月27日 下午6:57:34      
 * @version  1.0
 */
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    private final int dataSourceNumber;
    private AtomicInteger count = new AtomicInteger(0);

    public MyAbstractRoutingDataSource(int dataSourceNumber) {
        this.dataSourceNumber = dataSourceNumber;
    }

    @Override
    protected Object determineCurrentLookupKey() {
        String typeKey = DataSourceContextHolder.getReadOrWrite();
        if(StringUtils.isBlank(typeKey)||typeKey.equals(DataSourceType.write.getType())) {
            return DataSourceType.write.getType();
        }
        // 读 简单负载均衡
        int number = count.getAndAdd(1);
        int lookupKey = number % dataSourceNumber;
        return new Integer(lookupKey);
    }
}

事务管理配置

@Configuration
@EnableTransactionManagement(order = 10)
@Slf4j
@AutoConfigureAfter({ MybatisConfiguration.class })
public class TransactionConfiguration extends DataSourceTransactionManagerAutoConfiguration {

    @Bean
    @Autowired
    public DataSourceTransactionManager transactionManager(MyAbstractRoutingDataSource roundRobinDataSouceProxy) {
        log.info("事物配置");
        return new DataSourceTransactionManager(roundRobinDataSouceProxy);
    }
}