数据库的读写分离的好处有哪些?
1)将读操作和写操作分离到不同的数据库上,避免主服务器出现性能瓶颈;
2) 主服务器进行写操作时,不影响查询应用服务器的查询性能,降低阻塞,提高并发;
3) 数据拥有多个容灾副本,提高数据安全性,同时当主服务器故障时,可立即切换到其他服务器,提高系统可用性;
说到mysql数据库主从复制,读写分离,需要注意以下3点:
1.主从数据库数据一致(主从同步)
2.增删改走主库
3.查询走从库
一般来讲,读写分离有两种实现方式;第一种是依靠中间件(比如:MyCat,sharding),第二种是应用程序自己去做分离。这里我们选择程序自己来做,主要是利用Spring提供的路由数据源,以及AOP。
项目目录结构:
首先引入相关依赖:
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.1</version>
</dependency>
数据源配置类
@Configuration
public class DynamicDataSourceConfig {
private Logger logger = LogManager.getLogger(DynamicDataSourceConfig.class);
@Bean
@ConfigurationProperties("spring.datasource.druid.master")
public DataSource masterDataSource() {
logger.info("into master data source");
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("spring.datasource.druid.slave")
public DataSource slaveDataSource() {
logger.info("into slave data source");
return DruidDataSourceBuilder.create().build();
}
@Bean
@Primary
public DataSource myRoutingDataSource() {
logger.info("into my routing data source");
Map<Object, Object> targetDataSources = new HashMap<>(16);
targetDataSources.put(DBTypeEnum.MASTER, masterDataSource());
targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource());
MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
myRoutingDataSource.setDefaultTargetDataSource(masterDataSource());
myRoutingDataSource.setTargetDataSources(targetDataSources);
return myRoutingDataSource;
}
sqlSessionFactory配置类
@EnableTransactionManagement
@Configuration
public class MybatisConfig {
@Resource
private DataSource myRoutingDataSource;
@Value("${mybatis.type.aliases.package}")
private String typeAliasPackage;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
sqlSessionFactoryBean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage(typeAliasPackage);
return sqlSessionFactoryBean.getObject();
}
/**
* 创建SqlSessionTemplate对象
*
* @param sqlSessionFactory 创建SqlSessionTemplate所需的SessionFactory对象
* @return 创建成功的SqlSessionTemplate对象
*/
@Bean
public SqlSessionTemplate createSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
/**
* 创建事务用的Transaction Manager对象
*
* @return 创建成功的Transaction Manager对象
*/
@Bean
public PlatformTransactionManager createTransactionManager() {
return new DataSourceTransactionManager(myRoutingDataSource);
}
}
将数据源设置到本地线程ThreadLocal中
/**
* 通过ThreadLocal将数据源设置到每个线程上下文中
*/
public class DBContextHolder {
private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
public static void set(DBTypeEnum dbType) {
contextHolder.set(dbType);
}
public static DBTypeEnum get() {
return contextHolder.get();
}
public static void master() {
set(DBTypeEnum.MASTER);
System.out.println("切换到master");
}
public static void slave() {
set(DBTypeEnum.SLAVE);
System.out.println("切换到slave");
}
}
AOP切面类
@Aspect
@Order(1)
@Component
public class DataSourceAop {
@Pointcut("!@annotation(com.carrefour.cn.cdm.permission.annotation.Master) " +
"&& execution(* com.carrefour.cn.cdm.permission.service..*.select*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.find*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.get*(..)))")
public void readPointcut() {
}
@Pointcut("@annotation(com.carrefour.cn.cdm.permission.annotation.Master) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.insert*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.add*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.create*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.update*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.edit*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.delete*(..)) " +
"|| execution(* com.carrefour.cn.cdm.permission.service..*.remove*(..))")
public void writePointcut() {
}
@Before("readPointcut()")
public void read() {
DBContextHolder.slave();
}
@Before("writePointcut()")
public void write() {
DBContextHolder.master();
}
/**
* 另一种写法:if...else... 判断哪些需要读从数据库,其余的走主数据库
*/
// @Before("execution(* com.cjs.example.service.impl.*.*(..))")
// public void before(JoinPoint jp) {
// String methodName = jp.getSignature().getName();
//
// if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
// DBContextHolder.slave();
// }else {
// DBContextHolder.master();
// }
// }
}
新建一个Master注解类
如果需要查询强制走主库,可以直接在service层的方法上加上@Master即可。
单元测试结果
1.查询由从库切换为主库
2.查询走从库
3.新增/修改/删除走主库(修改、删除这里就不一一测试了)
至此,mysql数据库主从分离就完成了。