1.说明
假设有一个订单表 order
,用户经常查询自己的订单,该如何分表呢?
这时候就需要使用复合分片。ComplexKeysShardingAlgorithm
:
1.支持精确查询和范围查询,提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
2.ComplexShardingStrategy支持多分片键。
回到订单表,可以把用户的最后一位拼接至订单order_id的最后一位。
2.yml配置
tables:
order:
actual-data-nodes: ds1.order_$->{1..2}
table‐strategy:
complex:
##分片键 是sharding‐columns
sharding‐columns: order_id,user_id
##分片算法,内置的精确算法
algorithm-class-name: com.sharding.algorithm.TablesComplexKeysShardingAlgorithm
#主键生成策略
key-generator:
#主键
column: order_id
#生成算法
type: SNOWFLAKE
3.代码
TablesComplexKeysShardingAlgorithm
public class TablesComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
public final static String LOGIC_ORDER_ID="order_id";
public final static String LOGIC_USER_ID="user_id";
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
//范围查询的分片键值集合:userId/orderId
Map<String, Range<Long>> shardingRangeMaps = shardingValue.getColumnNameAndRangeValuesMap();
//精确查询的分片键值集合:userId/orderId
Map<String, Collection<Long>> shardingMaps = shardingValue.getColumnNameAndShardingValuesMap();
if (!shardingRangeMaps.isEmpty()){
throw new UnsupportedOperationException("只支持精确查询,不支持范围查询");
}
//逻辑表的名称,t_order
String logicTableName = shardingValue.getLogicTableName();
//orderIds:订单编号分片键值的集合
Collection<Long> orderIds = shardingMaps.getOrDefault(LOGIC_ORDER_ID,new ArrayList<>());
//userIds:用户id分片键值的集合
Collection<Long> userIds = shardingMaps.getOrDefault(LOGIC_USER_ID,new ArrayList<>());
List<Long> ids=new ArrayList<>();
ids.addAll(orderIds);
ids.addAll(userIds);
//id%2+1->t_order_1/t_order_2
return ids.stream().map(id-> MessageFormat.format(logicTableName+"_{0}",Integer.parseInt(id.toString().substring(id.toString().length()-1))%availableTargetNames.size()+1)).collect(Collectors.toSet());
}
}
4.测试代码
@Test
public void complex(){
List<Long> userIds = new ArrayList<>();
for (int i = 1; i < 3; i++) {
userIds.add((Long)snowflake.generateKey());
}
for (int i = 1; i < 11; i++) {
Long userId = userIds.get(0);
//唯一ID+userId后1位
Long orderId = Long.valueOf((Long)snowflake.generateKey() + userId.toString().substring(userId.toString().length() - 1));
Order order = Order.builder()
.userId(userId)
.orderId(orderId)
.price(new BigDecimal("1000"))
.status(1)
.build();
orderService.save(order);
}
}