mysql读写分离之springboot集成

不念不忘少年蓝@ 2022-05-20 00:23 303阅读 0赞

## 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 
 * 
 * @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 
 * 
 * @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 
 * 
 * @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 
 * 
 * @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);
        }
    }