分库分表-ShardingJDBC

发表于 分类于 阅读次数： 本文字数： 1.1k 阅读时长 ≈ 4 分钟

项目地址：https://github.com/chenkaixin12121/study/tree/master/sharding

分库分表

创建 ds0、ds1 数据库并生成表
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
DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0`  (
  `order_id` bigint(0) NOT NULL,
  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `amt` decimal(12, 2) NULL DEFAULT NULL,
  `user_id` bigint(0) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_order_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_1`;
CREATE TABLE `t_order_1`  (
  `order_id` bigint(0) NOT NULL,
  `order_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `amt` decimal(12, 2) NULL DEFAULT NULL,
  `user_id` bigint(0) NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_order_item_0
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_0`;
CREATE TABLE `t_order_item_0`  (
  `item_id` bigint(0) NOT NULL,
  `order_id` bigint(0) NULL DEFAULT NULL,
  `item_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `price` decimal(12, 2) NULL DEFAULT NULL,
  `qty` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`item_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_order_item_1
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_1`;
CREATE TABLE `t_order_item_1`  (
  `item_id` bigint(0) NOT NULL,
  `order_id` bigint(0) NULL DEFAULT NULL,
  `item_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `price` decimal(12, 2) NULL DEFAULT NULL,
  `qty` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`item_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
修改配置文件 application.yml
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
spring:
  jpa:
    hibernate:
      ddl-auto: none
    show-sql: true
  shardingsphere:
    # 数据源配置
    datasource:
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: root
    sharding:
      # 默认数据源，未分片的表存在这
      default-data-source-name: ds0
      tables:
        # 分片表
        t_order:
          # 真实数据节点
          actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
          key-generator:
            # 自增主键
            column: order_id
            # 主键 id 生成，使用雪花算法
            type: SNOWFLAKE
            props:
              worker:
                # 工作机器唯一 id
                id: 0000
          # 分库策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds$->{user_id % 2}
          # 分表策略
          table-strategy:
            inline:
              # 分表分片键
              sharding-column: order_id
              # 分表算法
              algorithm-expression: t_order_$->{order_id % 2}
        t_order_item:
          actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..1}
          key-generator:
            column: item_id
            type: SNOWFLAKE
            props:
              worker:
                id: 0000
          database-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: ds$->{order_id % 2}
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_item_$->{order_id % 2}
      # 绑定两张表
      binding-tables: t_order, t_order_item
    # 开启 sql 解析日志
    props:
      sql:
        show: true
测试方法 ShardingApplicationTest.save()

分片策略

  • 标准分片策略
  • 复合分片策略
  • Hint 分片策略
  • 行表达式分片策略

分片算法

精确分片算法
  1. 实现 PreciseShardingAlgorithm 接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 * 标准分片策略，分库或分表
 */
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    /**
     * @param collection 所有分片库或者分片表的集合
     * @param preciseShardingValue 分片属性，logicTableName 逻辑表，logicTableName 分片键，value 分片键的值
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {

        for (String databaseName : collection) {
            String suffix = String.valueOf(preciseShardingValue.getValue() % collection.size());
            if (databaseName.endsWith(suffix)) {
                return databaseName;
            }
        }
        throw new IllegalArgumentException();
    }
}
  1. 修改配置文件
1
2
3
4
5
6
7
8
9
spring:
  shardingsphere:
    sharding:
      tables:
        t_order:
          database-strategy:
            standard:
              sharding-column: order_id
              precise-algorithm-class-name: ink.ckx.sharding.algorithm.MyPreciseShardingAlgorithm
范围分片算法
1
select * from t_order where user_id between 1 and 100000;
  1. 实现 RangeShardingAlgorithm 接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/**
 * 范围分片算法
 */
public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Integer> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Integer> shardingValue) {
        Range<Integer> valueRange = shardingValue.getValueRange();
        Set<String> result = new LinkedHashSet<>();
        for (int i = valueRange.lowerEndpoint(); i <= valueRange.upperEndpoint(); i++) {
            for (String databaseName : availableTargetNames) {
                String suffix = String.valueOf(i % availableTargetNames.size());
                if (databaseName.endsWith(suffix)) {
                    result.add(databaseName);
                }
            }
        }
        return result;
    }
}
  1. 修改配置文件
1
2
3
4
5
6
7
8
9
spring:
  shardingsphere:
    sharding:
      tables:
        t_order:
          database-strategy:
            standard:
              sharding-column: order_id
              range-algorithm-class-name: ink.ckx.sharding.algorithm.MyRangeShardingAlgorithm
复合分片算法
1
select * from t_order where order_id = 0 and user_id = 1;
  1. 实现 ComplexKeysShardingAlgorithm 接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public class MyComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, ComplexKeysShardingValue<Long> shardingValue) {
        // 对应分片键的值
        Map<String, Collection<Long>> columnNameAndShardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
        Collection<Long> orderIdValues = columnNameAndShardingValuesMap.get("order_id");
        Collection<Long> userIdValues = columnNameAndShardingValuesMap.get("user_id");
        // 对分片键取模
        List<String> shardingSuffix = new ArrayList<>();
        for (Long userId : userIdValues) {
            for (Long orderId : orderIdValues) {
                String suffix = userId % 2 + "_" + orderId % 2;
                for (String databaseName : availableTargetNames) {
                    if (databaseName.endsWith(suffix)) {
                        shardingSuffix.add(databaseName);
                    }
                }
            }
        }
        return shardingSuffix;
    }
}
  1. 修改配置文件
1
2
3
4
5
6
7
8
9
spring:
  shardingsphere:
    sharding:
      tables:
        t_order:
          database-strategy:
            complex:
              sharding-column: order_id,user_id
              algorithm-class-name: ink.ckx.sharding.algorithm.MyComplexKeysShardingAlgorithm
Hint 分片算法
  1. 实现 HintShardingAlgorithm 接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public class MyHintShardingAlgorithm implements HintShardingAlgorithm<String> {

    @Override
    public Collection<String> doSharding(Collection<String> availableTargetNames, HintShardingValue<String> shardingValues) {
        Collection<String> result = new ArrayList<>();
        for (String tableName : availableTargetNames) {
            for (String shardingValue : shardingValues.getValues()) {
                String suffix = String.valueOf(Long.parseLong(shardingValue) % availableTargetNames.size());
                if (tableName.endsWith(suffix)) {
                    result.add(tableName);
                }
            }
        }
        return result;
    }
}
  1. 修改配置文件
1
2
3
4
5
6
7
8
spring:
  shardingsphere:
    sharding:
      tables:
        t_order:
          database-strategy:
            hint:
              algorithm-class-name: ink.ckx.sharding.algorithm.MyHintShardingAlgorithm
  1. 强制指定
1
2
3
4
5
6
7
8
HintManager.clear();
HintManager hintManager = HintManager.getInstance();
// 指定数据库
hintManager.addDatabaseShardingValue("t_order", 0);
// 指定数据库表
hintManager.addTableShardingValue("t_order" , 0);
// 在读写分离数据库中，Hint 可以强制读主库
// hintManager.setMasterRouteOnly();

读写分离

1
2
3
4
5
6
7
8
9
10
11
12
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2
    rules:
      readwrite-splitting:
        data-sources:
          master:
            write-data-source-name: ds0
          slave:
            read-data-source-names: ds1,ds2
            load-balancer-name: round_robin

数据脱敏

1
2
3
4
5
6
7
8
9
10
11
12
13
14
spring:
  shardingsphere:
      encrypt-rule:
        encryptors:
          aes_encryptor:
            type: AES
            props:
              aes.key.value: 123456abc
        tables:
          t_order:
            columns:
              order_no:
                cipherColumn: order_no
                encryptor: aes_encryptor