读写分离实践(二) - 基于应用层解决方案

   MySQL读写分离主要有两种解决方案，一种是基于应用层解决，即在程序内部实现；另一种是通过中间件的方式。

应用层解决方案原理：通过 AOP 拦截 + 继承Spring AbstractRoutingDataSource 抽象类，实现抽象方法determineCurrentLookupKey 来传递数据源，如下图所示(图片来源于:https://www.cnblogs.com/ngy0217/p/8987508.html)

• 优点：

  1.数据源的切换由程序自动完成，切换自由

  2.不需要引入额外的第三方依赖

• 缺点：
  1.运维无法参与操作
  2.不能做到动态增加数据源

1.添加依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
</dependency>

2.application.yml配置

server:
  port: 8010
spring:
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    show-sql: true
  datasource: 
    type: com.alibaba.druid.pool.DruidDataSource
    #druid console: http://localhost:8010/druid
    druid:
      write:
        url: jdbc:mysql://106.54.33.142:3306/domgo?useUnicode=true&characterEncoding=utf-8&useSSL=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
      read:
        url: jdbc:mysql://192.168.107.206:3306/domgo?useUnicode=true&characterEncoding=utf-8&useSSL=true
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: root
      maxActive: 50
      initialSize: 10
      maxWait: 60000
      minIdle: 10
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20   

3.设置获取数据源的ContextHolder工具类

public class DynamicDataSourceContextHolder {

	private static final ThreadLocal<DataBaseMode> contextHolder = new ThreadLocal<DataBaseMode>();
	
	public static void putDataSourceKey(DataBaseMode key) {
		if(key == null) throw new NullPointerException();
		contextHolder.set(key);
	}
	
	public static DataBaseMode getDataSourceKey() {
		return contextHolder.get();
	}
	
	public static void markWriter() {
		putDataSourceKey(DataBaseMode.WRITE);
	}
	
	public static void markRead() {
		putDataSourceKey(DataBaseMode.READ);
	}
	
	public static void removeMode() {
		contextHolder.remove();
	}
	
}

public enum DataBaseMode {

	READ("read"),
	
	WRITE("write");
	
	private String mode;

	private DataBaseMode(String mode) {
		this.mode = mode;
	}

	public String getMode() {
		return mode;
	}

	public void setMode(String mode) {
		this.mode = mode;
	}
	
}

4.定义只读注解

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ReadOnly {

}

5.实现aop切面拦截

@Order(0)
@Aspect
@Component
public class ReadWriterInterceptor {

	private static final Logger log = LoggerFactory.getLogger(ReadWriterInterceptor.class);
	
	@Around("@annotation(readOnly)")
	public Object interceptor(ProceedingJoinPoint point, ReadOnly readOnly) throws Throwable {
		try {
			String methodName = ((MethodSignature)point.getSignature()).getMethod().getName();
			DynamicDataSourceContextHolder.putDataSourceKey(DataBaseMode.READ);
			Object result = point.proceed();
			log.info("方法[{}]执行读库", methodName);
			return result;
		} finally {
            //清楚掉线程内的数据源key，防止本线程上的后续操作产生干扰
			DynamicDataSourceContextHolder.removeMode();
		}
	}
	
}

6.数据源切换

public class DynamicDataSource extends AbstractRoutingDataSource{

	@Override
	protected Object determineCurrentLookupKey() {
		DataBaseMode dataSource = DynamicDataSourceContextHolder.getDataSourceKey();
		DynamicDataSourceContextHolder.removeMode();
		return dataSource;
	}

}

7.数据源配置

@Configuration
@EnableTransactionManagement
public class DataSourceConfig {

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

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

	@Bean(name = "masterDataSource")
	@Primary
	@ConfigurationProperties(prefix = "spring.datasource.druid.write")
	public DataSource masterDataSource() {
		DataSource masterDataSource = DataSourceBuilder.create().type(type).build();
		log.info("======Write DataBase {}======", masterDataSource);
		return masterDataSource;
	}

	@Bean(name = "slaveDataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid.read")
	public DataSource slaveDataSource() {
		DataSource slaveDataSource = DataSourceBuilder.create().type(type).build();
		log.info("======Read DataBase {}======", slaveDataSource);
		return slaveDataSource;
	}
}

8.mybatis数据源注册

@Configuration
@AutoConfigureAfter(DataSourceConfig.class)
public class MybatisConfig extends MybatisAutoConfiguration {

	@Resource(name="masterDataSource")
    private DataSource masterDataSource;
    
    @Resource(name="slaveDataSource")
    private DataSource slaveDataSource;
	
    @SuppressWarnings("rawtypes")
	public MybatisConfig(MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider,
			ObjectProvider<TypeHandler[]> typeHandlersProvider,
			ObjectProvider<LanguageDriver[]> languageDriversProvider, ResourceLoader resourceLoader,
			ObjectProvider<DatabaseIdProvider> databaseIdProvider,
			ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider) {
		super(properties, interceptorsProvider, typeHandlersProvider, languageDriversProvider, resourceLoader,
				databaseIdProvider, configurationCustomizersProvider);
	}
    
	@Bean(name = "sqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		// 放入datasource 需要mybatis的AbstractRoutingDataSource 实现主从切换
		return super.sqlSessionFactory(roundRobinDataSourceProxy());
	}

	@SuppressWarnings("unchecked")
	public AbstractRoutingDataSource roundRobinDataSourceProxy() {
		DynamicDataSource proxy = new DynamicDataSource();
		// proxy.
		SoftHashMap targetDataSource = new ClassLoaderRepository.SoftHashMap();
		targetDataSource.put(DataBaseMode.WRITE, masterDataSource);
		targetDataSource.put(DataBaseMode.READ, slaveDataSource);
		// 默认数据源
		proxy.setDefaultTargetDataSource(masterDataSource);
		// 装入两个主从数据源
		proxy.setTargetDataSources(targetDataSource);
		return proxy;
	}

}

最后编写测试程序进行测试即可。读写分离只是为了缓解主库的压力，实际上主从库的数据一致性主要是通过MySQL的主从复制进行的，所以进行写操作时一定要在主库进行，只有在单纯进行读操作时才使用从库，主从复制间存在延迟，但是这点延迟在可接受范围内。

附：代码地址：https://github.com/domgao/domgo 下 domgo-web