为什么要使用读写分离?
读写分离是互联网公司常用的技术方案,用来分摊数据库压力的。我们知道在一般的互联网项目中都是读多写少,这就导致数据库在大部分时候都在进行读操作,那么在高并发、高流量读的情况下,数据库的读操作就可能成为系统的性能瓶颈。
有的人可能会说,可以通过加缓存来解决数据库读的瓶颈,当然缓存也是一种有效的方案,可以迅速缓解数据库的压力。但有些场景我们不适合加缓存,比如数据变化比较频繁而我们对实时性要求比较高的场景下,使用缓存可以会导致业务上的逻辑存在问题。那我们只能通过查库来解决业务问题
本篇幅不会讲解数据库的主从搭建,重点讲解Springboot + mybatis代码层面的读写分离实现,下面我们进入正题
读写分离代码实现
1、配置多数据源
读写分离至少要包含一个写库、一个读库,那就会涉及到多数据源,数据库配置如下:
# 以一主一从为例,一主多从是类似的配置mysql: datasource: master: jdbc-url: jdbc:mysql://localhost:3306/testdb1 username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver slave1: jdbc-url: jdbc:mysql://localhost:3306/testdb2 username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver
2、继承spring的AbstractRoutingDataSource进行多数据源管理
public class MyRoutingDataSource extends AbstractRoutingDataSource { @Nullable @Override protected Object determineCurrentLookupKey() { return DBContextHolder.get(); }}
3、数据源相关配置
@Configurationpublic class DataSourceConfig { @Bean(name = "masterDataSource") @ConfigurationProperties(prefix = "mysql.datasource.master") public DataSource masterDataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "slaveDataSource") @ConfigurationProperties(prefix = "mysql.datasource.slave1") public DataSource slaveDataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "myRoutingDataSource") public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("slaveDataSource") DataSource slaveDataSource){ Map targetDataSource = new HashMap<>(); targetDataSource.put(DBTypeEnum.MASTER,masterDataSource); targetDataSource.put(DBTypeEnum.SLAVE,slaveDataSource); MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource(); myRoutingDataSource.setDefaultTargetDataSource(masterDataSource); myRoutingDataSource.setTargetDataSources(targetDataSource); return myRoutingDataSource; }}
// sqlSessionFactory和事务相关配置@EnableTransactionManagement@Configurationpublic class MyBatisConfig { @Resource(name = "myRoutingDataSource") private DataSource myRoutingDataSource; @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(myRoutingDataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/**/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager platformTransactionManager(){ return new DataSourceTransactionManager(myRoutingDataSource); }}
4、使用AOP进行数据源动态切换
public enum DBTypeEnum { MASTER,SLAVE}
//线程级别的数据源设置public class DBContextHolder { private static final ThreadLocal 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规则进行数据源的动态切换@Component@Aspectpublic class DataSourceAop { @Pointcut("@annotation(com.kxg.demo.annotation.ReadOnly)" + "|| execution(* com.kxg.demo.mapper..*.find*(..))" + "|| execution(* com.kxg.demo.mapper..*.get*(..))") public void readPointcut(){ } @Pointcut("!@annotation(com.kxg.demo.annotation.ReadOnly)" + "&& execution(* com.kxg.demo.mapper..*.insert*(..))" + "|| execution(* com.kxg.demo.mapper..*.update*(..))" + "|| execution(* com.kxg.demo.mapper..*.delete*(..))") public void writePointcut(){ } @Before("readPointcut()") public void read(){ DBContextHolder.slave(); } @Before("writePointcut()") public void write(){ DBContextHolder.master(); }}
5、测试结果
@Datapublic class User { private Integer userId; private String userName;}
<?xml version="1.0" encoding="UTF-8"?> select * from tb_user insert into tb_user(user_id,user_name) values(#{userId},#{userName})
@Mapperpublic interface UserMapper { List findAll(); int insert(User user);}
@Servicepublic class UserService { @Autowired private UserMapper userMapper; @Transactional public void insert(User user){ userMapper.insert(user); } @Transactional public void save(){ } //打上readOnly的注解,表示走读库 @ReadOnly public List findAll(){ return userMapper.findAll(); }}
//写个测试方法测试一下@RunWith(SpringRunner.class)@SpringBootTest(classes = MultiDataSourceDemo.class)@MapperScan(basePackages = "com.kxg.demo")@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)public class MultiDataSourceDemo { @Autowired private UserService userService; @Test public void testWrite(){ User user = new User(); Integer userId = new Random().nextInt(); user.setUserId(userId); user.setUserName("user" + userId); userService.insert(user); } @Test public void testRead(){ userService.findAll(); }}
分别运行testWrite()、testRead()方法得到的结果如下: