Spring Boot + MyBatis 如何优雅的实现数据库读写分离？

1. 配置主从数据源

在application.yml中配置主库和从库信息：

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://master-host:3306/db
      username: user
      password: pass
      driver-class-name: com.mysql.cj.jdbc.Driver
    slaves:
      slave1:
        jdbc-url: jdbc:mysql://slave1-host:3306/db
        username: user
        password: pass
        driver-class-name: com.mysql.cj.jdbc.Driver
      slave2:
        jdbc-url: jdbc:mysql://slave2-host:3306/db
        username: user
        password: pass
        driver-class-name: com.mysql.cj.jdbc.Driver

2. 创建动态数据源路由

继承AbstractRoutingDataSource实现动态路由：

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    private final List<Object> slaveKeys = new ArrayList<>();
    private final AtomicInteger counter = new AtomicInteger(0);

    @Override
    public void afterPropertiesSet() {
        super.afterPropertiesSet();
        this.slaveKeys.addAll(getResolvedDataSources().keySet().stream()
                .filter(key -> key.toString().startsWith("slave"))
                .collect(Collectors.toList()));
    }

    @Override
    protected Object determineCurrentLookupKey() {
        // 存在上下文指定数据源则优先使用
        String key = DynamicDataSourceContextHolder.getDataSourceKey();
        if (key != null) return key;

        // 事务内根据读写决定
        if (TransactionSynchronizationManager.isActualTransactionActive()) {
            return TransactionSynchronizationManager.isCurrentTransactionReadOnly() 
                    ? getSlaveKey() : "master";
        }

        // 默认主库
        return "master";
    }

    private Object getSlaveKey() {
        if (slaveKeys.isEmpty()) return "master";
        int index = counter.incrementAndGet() % slaveKeys.size();
        if (counter.get() > 9999) counter.set(0);
        return slaveKeys.get(index);
    }
}

3. 配置数据源Bean

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slaves.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slaves.slave2")
    public DataSource slave2DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource dynamicDataSource(DataSource masterDataSource, DataSource slave1DataSource, DataSource slave2DataSource) {
        DynamicRoutingDataSource dynamicDataSource = new DynamicRoutingDataSource();
        Map<Object, Object> dataSources = new HashMap<>();
        dataSources.put("master", masterDataSource);
        dataSources.put("slave1", slave1DataSource);
        dataSources.put("slave2", slave2DataSource);
        dynamicDataSource.setTargetDataSources(dataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}

4. 数据源上下文管理

使用ThreadLocal保存当前线程的数据源选择：

public class DynamicDataSourceContextHolder {
    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

    public static void setDataSourceKey(String key) {
        CONTEXT.set(key);
    }

    public static String getDataSourceKey() {
        return CONTEXT.get();
    }

    public static void clearDataSourceKey() {
        CONTEXT.remove();
    }
}

5. 自定义注解与AOP切面

定义@ReadOnly注解：

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

创建切面处理非事务读操作：

@Aspect
@Component
public class ReadOnlyDataSourceAspect {
    @Around("@annotation(readOnly)")
    public Object proceed(ProceedingJoinPoint joinPoint, ReadOnly readOnly) throws Throwable {
        try {
            DynamicDataSourceContextHolder.setDataSourceKey("slave");
            return joinPoint.proceed();
        } finally {
            DynamicDataSourceContextHolder.clearDataSourceKey();
        }
    }
}

6. 事务中的读写分离

• 写操作：使用默认的@Transactional，自动路由到主库。

• 读操作：使用@Transactional(readOnly = true)，自动路由到从库。

7. 清理数据源上下文

添加过滤器确保请求结束后清理上下文：

@Component
public class DataSourceCleanupFilter extends OncePerRequestFilter {
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain) 
            throws ServletException, IOException {
        try {
            filterChain.doFilter(request, response);
        } finally {
            DynamicDataSourceContextHolder.clearDataSourceKey();
        }
    }
}

总结

通过上述步骤，实现了以下功能：

1. 动态路由：根据事务属性和注解自动选择主从库。

2. 负载均衡：多个从库间轮询分配读请求。

3. 事务安全：确保事务内数据源一致性。

4. 灵活控制：通过注解显式指定数据源。