Sharding-JDBC分库分表-自定义分片算法-4

默认分片算法

Sharding JDBC通过org.apache.shardingsphere.sharding.spi.ShardingAlgorithm接口定义了数据分片算法，5.2.1版本默认提供了如下的分片算法

默认算法的继承关系如下

分片算法参考官方用户文档：默认分片算法介绍

自定义分片算法

自定义分片算法时通过配置分片策略类型和算法类名，实现自定义扩展。 CLASS_BASED 允许向算法类内传入额外的自定义属性，传入的属性可以通过属性名为 props 的 java.util.Properties 类实例取出。

自定义分片算法有三种类型

• 标准分片算法
• 复杂分片算法
• hint分片算法

对应需要实现的接口分别为：

分片算法开发

以标准算法为例，对下面的order_t进行分表

CREATE TABLE `order_t` (
  `order_id` bigint(20) NOT NULL COMMENT 'order_id主键',
  `order_no` varchar(32) DEFAULT NULL COMMENT '订单编号',
  `user_id` bigint(10) NOT NULL COMMENT '用户ID',
  `order_date` date NOT NULL COMMENT '下单时间',
  `order_amount` decimal(16,2) NOT NULL COMMENT '订单金额',
  `delivery_amount` decimal(16,2) DEFAULT '0.00' COMMENT '运费',
  `total_amount` decimal(16,2) NOT NULL COMMENT '汇总金额',
  `receiver_id` bigint(10) NOT NULL COMMENT '收货地址ID',
  `status` tinyint(4) DEFAULT '1' COMMENT '状态，1：已提交，2：已付款，3：待发货，4：已发货，5：已收货，6：已完成',
  `deleted` tinyint(4) DEFAULT '0' COMMENT '删除标志，0：未删除，1：已删除',
  `create_by` bigint(10) DEFAULT NULL COMMENT '创建人',
  `creation_date` datetime DEFAULT NULL COMMENT '创建时间',
  `last_update_by` bigint(10) DEFAULT NULL COMMENT '修改人',
  `last_update_date` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`order_id`),
  KEY `idx_useId` (`user_id`),
  KEY `idx_orderNo` (`order_no`),
  KEY `idx_orderDate` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

分表规则为

• 按照下单时间order_date分表
• 一个季度分一个表
• 只分2023年

目标表有：order_t1，order_t2，order_t3，order_t4

配置如下：

spring:
  shardingsphere:
    rules:
      sharding:
        tables: # 需要分库表的规则配置
          order_t:
            actual-data-nodes: ds0.order_t$->{1..4}  # 待选数据节点：ds0.order_t1、ds0.order_t2、ds0.order_t3
            key-generate-strategy:  # 分布式ID列，一般是主键
              column: order_id
              key-generator-name: beautySnowflake # 使用自定义分布式ID算法是
            table-strategy: # 分库策略配置
              standard: # 标准算法
                sharding-column: order_date # 分片列
                sharding-algorithm-name: quarter_std # 分片算法
        key-generators: # 分布式ID生成算法
          beautySnowflake:
            type: BEAUTY_SNOWFLAKE
        sharding-algorithms: # 分片算法，配置后可以在分片表的分片策略中被引用
          quarter_std:
            type: CLASS_BASED
            props:
              strategy: standard # 标准算法
              algorithmClassName: com.xlt.sharding.startegy.DateStdShardingAlgorithm # 自定义算法类路径
              lowerLimit: "2023-01-01 00:00:00" # 分片时间下限
              upperLimit: "2023-12-31 24:00:00" # 分片时间上限
              interval: 3  # 分片间隔月数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

自定义标准分片算法DateStdShardingAlgorithm需要实现StandardShardingAlgorithm接口，实现其精确分片和范围分片doSharding方法，除此之外还要实现init、getProps、getType等方法。

@Slf4j
public class DateStdShardingAlgorithm implements StandardShardingAlgorithm<Date> {

    private Date lowerDate;

    private Date upperDate;

    private Integer interval;

    private Properties properties;

    private static final String DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    @Override
    public void init(Properties properties) {
        String lowerLimit = (String) properties.get("lowerLimit");
        String upperLimit = (String) properties.get("upperLimit");
        String interval = (String) properties.get("interval");
        AssertUtil.isNull(lowerLimit, "lowerLimit can't be empty");
        AssertUtil.isNull(upperLimit, "upperLimit can't be empty");
        AssertUtil.isNull(interval, "interval can't be empty");
        this.lowerDate = parseDate(lowerLimit);
        this.upperDate = parseDate(upperLimit);
        this.interval = Integer.parseInt(interval);
        this.properties = properties;
    }

    private Date parseDate(String lowerLimit) {
        Date date = null;
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN);
        try {
            date = sdf.parse(lowerLimit);
        } catch (ParseException e) {
            log.error("String date parse error:", e);
            throw new CommonException(e.getMessage());
        }
        return date;
    }

    private String formatDate(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN);
        return sdf.format(date);
    }

    private int getYear(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.YEAR);
    }

    private int getMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.MONTH) + 1;
    }

    /**
     * Get properties.
     *
     * @return properties
     */
    @Override
    public Properties getProps() {
        return properties;
    }

    /**
     * Get type.
     *
     * @return type
     */
    @Override
    public String getType() {
        return "QUARTER_DATE";
    }

    /**
     * Sharding.
     *
     * @param tableNames    available data sources or table names
     * @param shardingValue sharding value
     * @return sharding result for data source or table name
     */
    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<Date> shardingValue) {
        log.info("tableNames={},shardingValue={},properties={}", JSON.toJSONString(tableNames), JSON.toJSONString(shardingValue), JSON.toJSONString(properties));
        Date date = shardingValue.getValue();
        AssertUtil.isTrue(date.getTime() < lowerDate.getTime(), formatDate(date) + " is before lowerLimit: " + formatDate(lowerDate));
        AssertUtil.isTrue(date.getTime() > upperDate.getTime(), formatDate(date) + " is after upperLimit: " + formatDate(upperDate));
        int idx = calTableIdx(date);
        log.info("idx={}", idx);
        String targetTable = "";
        for (String tableName : tableNames) {
            String tblIdxStr = tableName.substring(tableName.indexOf("t") + 1);
            int tblIdx = Integer.parseInt(tblIdxStr);
            if (tblIdx == idx) {
                targetTable = tableName;
                break;
            }
        }
        log.info("targetTable={}", targetTable);
        return targetTable;
    }

    private int calTableIdx(Date date) {
        int months = (getYear(date) - getYear(lowerDate)) * 12 + getMonth(date);
        int flag = months % interval == 0 ? 0 : 1;
        return months / interval + flag;
    }

    /**
     * Sharding.
     *
     * @param tableNames    available data sources or table names
     * @param shardingValue sharding value
     * @return sharding results for data sources or table names
     */
    @Override
    public Collection<String> doSharding(Collection<String> tableNames, RangeShardingValue<Date> shardingValue) {
        log.info("tableNames={},shardingValue={},properties={}", JSON.toJSONString(tableNames), JSON.toJSONString(shardingValue), JSON.toJSONString(properties));
        Date lowDate = shardingValue.getValueRange().lowerEndpoint();
        Date upDate = shardingValue.getValueRange().upperEndpoint();
        AssertUtil.isTrue(lowDate.getTime() > upDate.getTime(), formatDate(lowDate) + " is after upperEndpoint: " + formatDate(upDate));
        AssertUtil.isTrue(lowDate.getTime() < lowerDate.getTime(), formatDate(lowDate) + " is before lowerLimit: " + formatDate(lowerDate));
        AssertUtil.isTrue(upDate.getTime() > upperDate.getTime(), formatDate(upDate) + " is after upperLimit: " + formatDate(upperDate));
        int lowIdx = calTableIdx(lowDate);
        int upIdx =  calTableIdx(upDate);
        log.info("lowIdx={},upIdx={}", lowIdx, upIdx);
        List<String> targetTbls = new ArrayList<>();
        for (String tableName : tableNames) {
            String dsIdxStr = tableName.substring(tableName.indexOf("t") + 1);
            int dsIdx = Integer.parseInt(dsIdxStr);
            if (dsIdx >= lowIdx && dsIdx <= upIdx) {
                targetTbls.add(tableName);
            }
        }
        log.info("target table names={}", targetTbls);
        return targetTbls;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140

测试用例设计

针对以上的分片算法设计测试用例对其进行验证

1、新增订单数据

使用JMockData和Faker生成100条随机数据进行插入测试

@SpringBootTest
@Slf4j
public class ShardingJdbcTest {

    @Autowired
    private IOrderMapper mapper;

    /**
     * 新增订单
     */
    @Test
    public void addOrder() {
        for (int i = 0; i < 100; i++) {
            OrderPo orderPo = JMockData.mock(OrderPo.class);
            orderPo.setOrderId((long) i);
            orderPo.setStatus(1);
            orderPo.setDeleted(0);
            Faker faker = new Faker();
            Calendar fromDate = Calendar.getInstance();
            fromDate.set(2023, Calendar.JANUARY, 1);
            Calendar toDate = Calendar.getInstance();
            toDate.set(2023, Calendar.DECEMBER, 31);
            orderPo.setOrderDate(faker.date().between(fromDate.getTime(), toDate.getTime()));
            log.info("add new order:{}", orderPo);
            mapper.insert(orderPo);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

生成数据情况，order_t1中order_date为1-3月：

order_t2中order_date为4-6月：

2、查询订单数据-等值查询

使用分片键查询单一时间的数据

    /**
     * 单个查询
     */
    @Test
    public void queryOrder_1() {
        QueryWrapper<OrderPo> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("order_date", DateUtil.parseDate("2023-03-12", DateUtil.DATE_PATTERN_2));
        List<OrderPo> orderPos = mapper.selectList(queryWrapper);
        log.info("orderPos={}", JSON.toJSONString(orderPos));
    }
1
2
3
4
5
6
7
8
9
10

从日志中看，找到目标表之后，查到了对应的数据

3、范围查询订单数据

使用分片键查询一定时间范围内的数据

/**
 * 范围查询
 */
@Test
public void queryOrder_2() {
    QueryWrapper<OrderPo> queryWrapper = new QueryWrapper<>();
    queryWrapper.between("order_date", DateUtil.parseDate("2023-05-01", DateUtil.DATE_PATTERN_2), DateUtil.parseDate("2023-08-01", DateUtil.DATE_PATTERN_2));
    List<OrderPo> orderPos = mapper.selectList(queryWrapper);
    log.info("orderPos={}", JSON.toJSONString(orderPos));
}
1
2
3
4
5
6
7
8
9
10

可以看到查询过程中，根据order_date的范围，经过分片算法的计算，路由到了order_t_2和order_t_3表，然后查回相应的数据

分库分表运行过程

分库分表算法执行顺序