Apache ShardingSphere 读写分离实战

# Apache ShardingSphere 读写分离实战 ## 为什么需要读写分离 当数据库成为系统瓶颈时，读写分离是最直接的优化手段。大多数应用读多写少（通常 8:2 甚至 9:1），将读请求分散到多个从库可以显著提升吞吐量。 Apache ShardingSphere 提供了透明的读写分离解决方案，应用层无需改动任何 SQL。 ## 架构概览 ``` Application | ShardingSphere-JDBC (代理层) | +-- Write --> Master DB | +-- Read --> Slave DB 1 | -> Slave DB 2 ``` ## Maven 依赖 ```xml org.apache.shardingsphere shardingsphere-jdbc 5.5.0 ``` ## 配置文件 在 `application.yml` 中配置： ```yaml spring: datasource: driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver url: jdbc:shardingsphere:classpath:sharding-config.yaml # sharding-config.yaml dataSources: master: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://master-host:3306/mydb username: root password: ${DB_MASTER_PASSWORD} connectionPoolSize: 20 slave0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://slave0-host:3306/mydb username: readonly password: ${DB_SLAVE_PASSWORD} connectionPoolSize: 30 slave1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://slave1-host:3306/mydb username: readonly password: ${DB_SLAVE_PASSWORD} connectionPoolSize: 30 rules: - !READWRITE_SPLITTING dataSources: readwrite_ds: writeDataSourceName: master readDataSourceNames: - slave0 - slave1 loadBalancerName: round_robin loadBalancers: round_robin: type: ROUND_ROBIN random: type: RANDOM weight: type: WEIGHT props: slave0: 60 slave1: 40 ``` ## 事务内强制走主库 ShardingSphere 默认在事务内所有查询都路由到主库，这保证了数据一致性： ```java @Service public class OrderService { @Transactional public void createOrder(OrderDTO dto) { // INSERT 走主库 Order order = orderMapper.insert(dto.toEntity()); // 事务内 SELECT 也走主库，保证能读到刚写入的数据 Order saved = orderMapper.selectById(order.getId()); // ... } } ``` 如果需要在非事务场景下强制走主库，可以使用 Hint： ```java try (HintManager hintManager = HintManager.getInstance()) { hintManager.setWriteRouteOnly(); // 下面的查询会路由到主库 User user = userMapper.selectById(userId); } ``` ## 自定义负载均衡 实现 `ReadQueryLoadBalanceAlgorithm` 接口： ```java public class LatencyAwareLoadBalancer implements ReadQueryLoadBalanceAlgorithm { private final Map latencyMap = new ConcurrentHashMap(); @Override public String getDataSource(String name, String writeDataSourceName, List readDataSourceNames) { // 选择延迟最低的从库 return readDataSourceNames.stream() .min(Comparator.comparingLong( ds -> latencyMap.getOrDefault(ds, 0L))) .orElse(readDataSourceNames.get(0)); } @Override public String getType() { return "LATENCY_AWARE"; } } ``` 通过 SPI 注册：在 `META-INF/services/` 下创建对应文件。 ## 监控与告警 ```java @Component @Slf4j public class ReplicationLagMonitor { @Scheduled(fixedRate = 30000) public void checkReplicationLag() { for (DataSourceInfo slave : slaveDataSources) { long lag = queryReplicationLag(slave); if (lag > 10) { log.warn("Replication lag on {}: {}s", slave.getName(), lag); // 触发告警或自动摘除该从库 } } } private long queryReplicationLag(DataSourceInfo ds) { // SHOW SLAVE STATUS -> Seconds_Behind_Master return jdbcTemplate.queryForObject( "SHOW SLAVE STATUS", (rs, i) -> rs.getLong("Seconds_Behind_Master")); } } ``` ## 常见坑点 1. **主从延迟导致读不到刚写入的数据**：关键业务用 HintManager 强制走主库 2. **从库挂掉导致读失败**：配置健康检查自动摘除故障节点 3. **连接池配置不当**：从库连接池应大于主库（读多写少） 4. **忽略 DDL 同步**：Flyway 迁移只在主库执行，从库靠主从复制同步 ## 总结 ShardingSphere 的读写分离方案对应用透明，配置简单，支持多种负载均衡策略。结合事务内自动走主库的机制，在保证数据一致性的同时大幅提升了读性能。在生产环境中，务必配合复制延迟监控和故障自动摘除策略使用。