背景
随着业务发展，订单线上数据已到达每日200万+，需要提前对订单做分库分表。结合市面上解决方案，决定采用sharding。sharding支持只分库，只分表，分库并分表。针对订单业务，我采用的是分库并分表。
使用
sharding官网有很清晰的教程，本文是对使用过程中问题的一个记录。
我是用的是javaBean的配置形式，也支持yaml与properties，因人而异。
1. 配置分库算法
public class OrderDatabaseAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> databaseNames, PreciseShardingValue<String> shardingValue) {
String value = shardingValue.getValue();
// 将数据库分为order_0 ~ order_15 16个数据库
String key = String.valueOf(value.hashCode() & 15);
String dsName = "";
for (String each : databaseNames) {
if (each.endsWith(key)) {
dsName = each;
break;
}
}
return dsName;
}
}
2. 配置分表算法
public class OrderTableAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> preciseShardingValue) {
String value = preciseShardingValue.getValue();
// 将order表分为order_0 ~ order_127 128张表
String key = String.valueOf(value.hashCode() & 127);
return preciseShardingValue.getLogicTableName() + "_" + key;
}
}
我将订单根据分片键分为128张表，16个库。 128*16=2048张表
3. 数据库配置类
@Data
public class ShardingSliceProperties {
private String driverClassName;
private String jdbcUrl;
private String username;
private String password;
// 分片数据库
private String sliceDatabase;
// 分片表
private String sliceTable;
// 数据库分片键
private String sliceDatabaseKey;
// 表分片键
private String sliceTableKey;
}
4. 配置分库分表规则，并注入数据源
@Configuration
public class ShardingConfig {
@Bean
public DataSource getShardingDataSource(ShardingSliceProperties shardingSliceProperties) throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration(shardingSliceProperties.getSliceTable(),
shardingSliceProperties.getSliceDatabase() + "_${0..15}." + shardingSliceProperties.getSliceTable() + "_${0..127}");
// 设置表规则
shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfiguration);
// 设置分库规则
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(
shardingSliceProperties.getSliceDatabaseKey(), new OrderDatabaseAlgorithm()));
// 设置分表规则
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(
shardingSliceProperties.getSliceTableKey(), new OrderTableAlgorithm()));
// 配置数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
for (int i = 0; i < 16; i++) {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName(shardingSliceProperties.getDriverClassName());
hikariConfig.setJdbcUrl(shardingSliceProperties.getJdbcUrl() + "/" + shardingSliceProperties.getSliceDatabase() + "_" + i);
hikariConfig.setUsername(shardingSliceProperties.getUsername());
hikariConfig.setPassword(shardingSliceProperties.getPassword());
hikariConfig.setMaximumPoolSize(25);
hikariConfig.setMinimumIdle(5);
HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
dataSourceMap.put(shardingSliceProperties.getSliceDatabase() + "_" + i, hikariDataSource);
}
Properties props = new Properties();
// 设置显示sql
props.put(ShardingPropertiesConstant.SQL_SHOW.getKey(), String.valueOf(Boolean.TRUE));
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
}
}
启动一下：
2019-07-31 20:23:43.153 INFO o.a.c.core.StandardService log:173 Starting service [Tomcat]
2019-07-31 20:23:43.153 INFO o.a.catalina.core.StandardEngine log:173 Starting Servlet engine: [Apache Tomcat/9.0.21]
2019-07-31 20:23:43.269 INFO o.a.c.c.C.[.[localhost].[/] log:173 Initializing Spring embedded WebApplicationContext
2019-07-31 20:23:43.269 INFO o.s.web.context.ContextLoader prepareWebApplicationContext:283 Root WebApplicationContext: initialization completed in 2531 ms
2019-07-31 20:23:43.710 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-1 - Starting...
2019-07-31 20:23:43.867 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-1 - Start completed.
2019-07-31 20:23:43.868 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-2 - Starting...
2019-07-31 20:23:43.916 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-2 - Start completed.
2019-07-31 20:23:43.917 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-3 - Starting...
2019-07-31 20:23:43.939 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-3 - Start completed.
2019-07-31 20:23:43.939 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-4 - Starting...
2019-07-31 20:23:44.166 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-4 - Start completed.
2019-07-31 20:23:44.166 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-5 - Starting...
2019-07-31 20:23:44.192 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-5 - Start completed.
2019-07-31 20:23:44.193 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-6 - Starting...
2019-07-31 20:23:44.216 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-6 - Start completed.
2019-07-31 20:23:44.217 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-7 - Starting...
2019-07-31 20:23:44.239 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-7 - Start completed.
2019-07-31 20:23:44.239 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-8 - Starting...
2019-07-31 20:23:44.262 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-8 - Start completed.
2019-07-31 20:23:44.262 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-9 - Starting...
2019-07-31 20:23:44.284 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-9 - Start completed.
2019-07-31 20:23:44.285 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-10 - Starting...
2019-07-31 20:23:44.304 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-10 - Start completed.
2019-07-31 20:23:44.305 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-11 - Starting...
2019-07-31 20:23:44.330 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-11 - Start completed.
2019-07-31 20:23:44.330 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-12 - Starting...
2019-07-31 20:23:44.349 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-12 - Start completed.
2019-07-31 20:23:44.350 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-13 - Starting...
2019-07-31 20:23:44.377 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-13 - Start completed.
2019-07-31 20:23:44.377 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-14 - Starting...
2019-07-31 20:23:44.400 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-14 - Start completed.
2019-07-31 20:23:44.401 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-15 - Starting...
2019-07-31 20:23:44.424 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-15 - Start completed.
2019-07-31 20:23:44.425 INFO c.zaxxer.hikari.HikariDataSource <init>:80 HikariPool-16 - Starting...
2019-07-31 20:23:44.448 INFO c.zaxxer.hikari.HikariDataSource <init>:82 HikariPool-16 - Start completed.
2019-07-31 20:23:44.792 INFO o.a.s.c.c.l.ConfigurationLogger log:134 ShardingRuleConfiguration
defaultDatabaseStrategy:
standard:
preciseAlgorithmClassName: com.aduer.pay.order.config.OrderDatabaseAlgorithm
shardingColumn: merchant_no
defaultTableStrategy:
standard:
preciseAlgorithmClassName: com.aduer.pay.order.config.OrderTableAlgorithm
shardingColumn: merchant_no
tables:
order:
actualDataNodes: pay-order_${0..15}.order_${0..127}
logicTable: order
2019-07-31 20:23:44.793 INFO o.a.s.c.c.l.ConfigurationLogger log:134 Properties
sql.show: 'true'
看日志，正确加载了数据源与数据表，测试一下，正常并通过。搞定。
以上。
