ShardingJDBC 数据库与表分片实践
随着业务增长，数据库表（如订单、详情表）面临巨大压力，插入和查询效率显著下降，需要根据业务特点进行水平或垂直分片。以下是常见中间件方案：
- Cobar：阿里B2B团队开源，基于代理层。
- TDDL：淘宝团队，基于客户端层。
- Atlas：360开源，基于代理层。
- Sharding-jdbc：当当开源，基于客户端层，支持分片、读写分离、分布式ID生成。
- Mycat：基于Cobar改造，代理层。
更换数据库（如Oracle、PolarDB）也是一种选择。为在多服务中复用，本文通过自定义注解封装Sharding-jdbc配置。
Maven 依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.11</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.6.RELEASE</version>
</dependency>
自定义注解
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@Import(ShardingDataSourceAutoConfiguration.class)
public @interface EnableCirculationSharding {
}
数据源与分片配置
@Configuration
public class ShardingDataSourceAutoConfiguration {
@Value("${jdbc.url}")
private String url1;
@Value("${jdbc.username}")
private String user1;
@Value("${jdbc.password}")
private String pass1;
@Value("${jdbc.url2}")
private String url2;
@Value("${jdbc.username2}")
private String user2;
@Value("${jdbc.password2}")
private String pass2;
@Value("${jdbc.url3}")
private String url3;
@Value("${jdbc.username3}")
private String user3;
@Value("${jdbc.password3}")
private String pass3;
@Autowired
private BottleShardingConfig bottleSharding;
@Autowired
private CaseShardingConfig caseSharding;
@Autowired
private BoxShardingConfig boxSharding;
@Autowired
private BottleDetailShardingConfig bottleDetailSharding;
@Autowired
private CaseDetailShardingConfig caseDetailSharding;
@Autowired
private BoxDetailShardingConfig boxDetailSharding;
@Bean
@Primary
public DataSource shardingDataSource() throws SQLException {
Map<String, DataSource> dataSources = new HashMap<>(3);
DruidDataSource ds1 = new DruidDataSource();
ds1.setDriverClassName("com.mysql.jdbc.Driver");
ds1.setUrl(url1);
ds1.setUsername(user1);
ds1.setPassword(pass1);
dataSources.put("ds1", ds1);
DruidDataSource ds2 = new DruidDataSource();
ds2.setDriverClassName("com.mysql.jdbc.Driver");
ds2.setUrl(url2);
ds2.setUsername(user2);
ds2.setPassword(pass2);
dataSources.put("ds2", ds2);
DruidDataSource ds3 = new DruidDataSource();
ds3.setDriverClassName("com.mysql.jdbc.Driver");
ds3.setUrl(url3);
ds3.setUsername(user3);
ds3.setPassword(pass3);
dataSources.put("ds3", ds3);
ShardingRuleConfiguration ruleCfg = new ShardingRuleConfiguration();
ruleCfg.setDefaultDataSourceName("ds1");
ruleCfg.setDefaultTableShardingStrategyConfig(new NoneShardingStrategyConfiguration());
ruleCfg.getTableRuleConfigs().add(buildBottleRule());
ruleCfg.getTableRuleConfigs().add(buildBoxRule());
ruleCfg.getTableRuleConfigs().add(buildCaseRule());
ruleCfg.getTableRuleConfigs().add(buildBottleDetailRule());
ruleCfg.getTableRuleConfigs().add(buildCaseDetailRule());
ruleCfg.getTableRuleConfigs().add(buildBoxDetailRule());
Properties props = new Properties();
props.put("sql.show", "false");
return ShardingDataSourceFactory.createDataSource(dataSources, ruleCfg, props);
}
@Bean
public DataSourceTransactionManager transactionManager(DataSource ds) {
return new DataSourceTransactionManager(ds);
}
private TableRuleConfiguration buildBottleRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_bottle",
"ds${1}._bottle_$->{0..9}_$->{0..9}_$->{0..9}");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", bottleSharding));
return rule;
}
private TableRuleConfiguration buildBottleDetailRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_bottle_detail",
"ds${1}._bottle_detail_$->{0..9}_$->{0..9}_$->{0..9}_1");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", bottleDetailSharding));
return rule;
}
private TableRuleConfiguration buildBoxRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_box",
"ds${3}._box_$->{0..9}_$->{0..9}");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", boxSharding));
return rule;
}
private TableRuleConfiguration buildBoxDetailRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_box_detail",
"ds${3}._box_detail_$->{0..9}_$->{0..9}_1");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", boxDetailSharding));
return rule;
}
private TableRuleConfiguration buildCaseRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_casket",
"ds${2}._casket_$->{0..9}_$->{0..9}_$->{0..9}");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", caseSharding));
return rule;
}
private TableRuleConfiguration buildCaseDetailRule() {
TableRuleConfiguration rule = new TableRuleConfiguration("_casket_detail",
"ds${2}._casket_detail_$->{0..9}_$->{0..9}_$->{0..9}_1");
rule.setTableShardingStrategyConfig(
new ComplexShardingStrategyConfiguration("_barcode", caseDetailSharding));
return rule;
}
}
分片算法实现
以下展示一个分片策略，其余类似。本策略根据条码最后三位字符映射到对应分片表：
@Component
public class BottleShardingConfig implements ComplexKeysShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
ComplexKeysShardingValue<String> shardingValue) {
Map<String, Collection<String>> colValues = shardingValue.getColumnNameAndShardingValuesMap();
List<String> barcodes = null;
if (colValues.containsKey("_barcode")) {
barcodes = (List<String>) colValues.get("_barcode");
}
String logicTable = shardingValue.getLogicTableName();
Set<String> result = new HashSet<>();
if (barcodes != null && !barcodes.isEmpty()) {
String code = barcodes.get(0);
String lastChar = code.substring(code.length() - 1);
String midChar = code.substring(code.length() - 2, code.length() - 1);
String thirdChar = code.substring(code.length() - 3, code.length() - 2);
if (BarcodeMap.fall_map.containsKey(lastChar) &&
BarcodeMap.fall_map.containsKey(midChar) &&
BarcodeMap.fall_map.containsKey(thirdChar)) {
result.add(logicTable + "_" +
BarcodeMap.fall_map.get(thirdChar) + "_" +
BarcodeMap.fall_map.get(midChar) + "_" +
BarcodeMap.fall_map.get(lastChar));
}
}
return result;
}
}
