赞
踩
假设有一个订单表 order
,用户经常查询自己的订单,该如何分表呢?
这时候就需要使用复合分片。ComplexKeysShardingAlgorithm
:
1.支持精确查询和范围查询,提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
2.ComplexShardingStrategy支持多分片键。
回到订单表,可以把用户的最后一位拼接至订单order_id的最后一位。
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
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()); } }
@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); } }
赞
踩
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。