一、 分库分表配置以及动态的读写分离
- pom.xml 增加 shardjdbc支持
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-core -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.4</version>
</dependency>
- 数据源配置
/**
* 数据库分库分表配置
*/
@Configuration
public class ShardJdbcConfig {
@Bean
@ConfigurationProperties("datasource.c3p0.write")
public DataSource writeDataSource(){
return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();
}
@Bean
@ConfigurationProperties("datasource.c3p0.read")
public DataSource readDataSource(){
return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();
}
/**
* C3P0-0.9.1.2, shardingjdbc-3.1.0
* Shard-JDBC 分库配置
*/
@Bean
@DependsOn({"writeDataSource", "readDataSource"})
@Primary
public DataSource dataSource(DataSource writeDataSource, DataSource readDataSource) throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(TableShardConfigs.financeOrdTbRule());
shardingRuleConfig.setDefaultDataSourceName("ds_write");
/*数据源读写分离*/
shardingRuleConfig.getMasterSlaveRuleConfigs()
.add(new MasterSlaveRuleConfiguration("ds_0", "ds_write", Arrays.asList("ds_read")));
Map<String,DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds_write", writeDataSource);
dataSourceMap.put("ds_read", readDataSource);
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
}
}
- 表分片配置
public class TableShardConfigs {
public static TableRuleConfiguration financeOrdTbRule() {
TableRuleConfiguration configuration =
new TableRuleConfiguration("t_user", "ds_0.t_user_${0..7}");
configuration.setTableShardingStrategyConfig(
new StandardShardingStrategyConfiguration("user_id", (PreciseShardingAlgorithm<Long>) (collection, value) -> {
return "t_user_" + (value.getValue() % 8);
})
);
return configuration;
}
}
以上配置可满足基本的分库分表配置以及动态的读写分离了,但是不知为何,我这边使用InlineShardingStrategyConfiguration 方式就会报错,如果有知道的,可以告诉我下,感谢
二、配置手动指定读写库
在一些场景,我们会手动的去写库里进行数据查询,那就要手动的指定写库
看源码中主要是这句话判断了数据源的读库或者写库
//org.apache.shardingsphere.core.route.router.masterslave.ShardingMasterSlaveRouter#isMasterRoute
private boolean isMasterRoute(SQLStatement sqlStatement) {
return !(sqlStatement instanceof SelectStatement) || MasterVisitedManager.isMasterVisited() || HintManager.isMasterRouteOnly();
}
那只要改变 MasterVisitedManager 设置为 masterVisited即可 MasterVisitedManager.setMasterVisited
这里可以参考文章 SpringBoot + MyBatis + MySQL 读写分离实战
三、小结
根据以上代码可以看出,shardingjdbc的读写分离是主备方案的,满足 以下条件之一则走主库,否则走从库集合,通过一定的LB策略来选择其中一个从库进行操作
- 非 Select 语句
- 存在主库访问
- HintManager.getInstance().setMasterRouteOnly();方式指定只走主库
shardingjdbc的方案是除非手动指定主库,或者进行的是非读操作,否则走的是从库。