ShardingSphere-Proxy的安装及配置2

1
2

10.10.1.1:3306/demo_ds_0
10.10.1.2:3306/demo_ds_1

 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

CREATE TABLE IF NOT EXISTS t_order_0
(
	order_id BIGINT NOT NULL AUTO_INCREMENT, 
	user_id INT NOT NULL, 
	address_id BIGINT NOT NULL, 
	status VARCHAR(50), 
	PRIMARY KEY (order_id)
);
CREATE TABLE IF NOT EXISTS t_order_1
(
	order_id BIGINT NOT NULL AUTO_INCREMENT, 
	user_id INT NOT NULL, 
	address_id BIGINT NOT NULL, 
	status VARCHAR(50), 
	PRIMARY KEY (order_id)
);

CREATE TABLE IF NOT EXISTS t_order_item_0
(
	order_item_id BIGINT NOT NULL AUTO_INCREMENT,
	order_id BIGINT NOT NULL, 
	user_id INT NOT NULL, 
	status VARCHAR(50), 
	PRIMARY KEY (order_item_id)
);
CREATE TABLE IF NOT EXISTS t_order_item_1
(
	order_item_id BIGINT NOT NULL AUTO_INCREMENT,
	order_id BIGINT NOT NULL, 
	user_id INT NOT NULL, 
	status VARCHAR(50), 
	PRIMARY KEY (order_item_id)
);

 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

databaseName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://10.10.1.1:3306/demo_ds_0?serverTimezone=GMT%2b8&useSSL=false
    username: shardingsphereproxyuser
    password: yourpasswd
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://10.10.1.2:3306/demo_ds_1?serverTimezone=GMT%2b8&useSSL=false
    username: shardingsphereproxyuser
    password: yourpasswd
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
      auditStrategy:
        auditorNames:
          - sharding_key_required_auditor
        allowHintDisable: true
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
  defaultAuditStrategy:
    auditorNames:
      - sharding_key_required_auditor
    allowHintDisable: true

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 2}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE

  auditors:
    sharding_key_required_auditor:
      type: DML_SHARDING_CONDITIONS

 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

rules:
- !SHARDING
  tables: # 数据分片规则配置
    <logic_table_name> (+): # 逻辑表名称
      actualDataNodes (?): # 由数据源名 + 表名组成（参考 Inline 语法规则）
      databaseStrategy (?): # 分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
        standard: # 用于单分片键的标准分片场景
          shardingColumn: # 分片列名称
          shardingAlgorithmName: # 分片算法名称
        complex: # 用于多分片键的复合分片场景
          shardingColumns: # 分片列名称，多个列以逗号分隔
          shardingAlgorithmName: # 分片算法名称
        hint: # Hint 分片策略
          shardingAlgorithmName: # 分片算法名称
        none: # 不分片
      tableStrategy: # 分表策略，同分库策略
      keyGenerateStrategy: # 分布式序列策略
        column: # 自增列名称，缺省表示不使用自增主键生成器
        keyGeneratorName: # 分布式序列算法名称
      auditStrategy: # 分片审计策略
        auditorNames: # 分片审计算法名称
          - <auditor_name>
          - <auditor_name>
        allowHintDisable: true # 是否禁用分片审计hint
  autoTables: # 自动分片表规则配置
    t_order_auto: # 逻辑表名称
      actualDataSources (?): # 数据源名称
      shardingStrategy: # 切分策略
        standard: # 用于单分片键的标准分片场景
          shardingColumn: # 分片列名称
          shardingAlgorithmName: # 自动分片算法名称
  bindingTables (+): # 绑定表规则列表
    - <logic_table_name_1, logic_table_name_2, ...> 
    - <logic_table_name_1, logic_table_name_2, ...> 
  broadcastTables (+): # 广播表规则列表
    - <table_name>
    - <table_name>
  defaultDatabaseStrategy: # 默认数据库分片策略
  defaultTableStrategy: # 默认表分片策略
  defaultKeyGenerateStrategy: # 默认的分布式序列策略
  defaultShardingColumn: # 默认分片列名称
  
  # 分片算法配置
  shardingAlgorithms:
    <sharding_algorithm_name> (+): # 分片算法名称
      type: # 分片算法类型
      props: # 分片算法属性配置
      # ...
  
  # 分布式序列算法配置
  keyGenerators:
    <key_generate_algorithm_name> (+): # 分布式序列算法名称
      type: # 分布式序列算法类型
      props: # 分布式序列算法属性配置
      # ...
  # 分片审计算法配置
  auditors:
    <sharding_audit_algorithm_name> (+): # 分片审计算法名称
      type: # 分片审计算法类型
      props: # 分片审计算法属性配置
      # ...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672101465849856, 1, 1, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672101696536577, 2, 2, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672101847531521, 3, 3, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672101985943553, 4, 4, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102166298625, 5, 5, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102304710656, 6, 6, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102472482816, 7, 7, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102636060672, 8, 8, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102828998656, 9, 9, 'INSERT_TEST');
INSERT INTO `t_order` (`order_id`,`user_id`,`address_id`,`status`) VALUES (832672102992576512, 10, 10, 'INSERT_TEST');
 
 
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672101637816320, 832672101465849856, 1, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672101776228352, 832672101696536577, 2, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672101906251776, 832672101847531521, 3, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102094995456, 832672101985943553, 4, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102233407489, 832672102166298625, 5, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102380208129, 832672102304710656, 6, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102556368897, 832672102472482816, 7, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102711558145, 832672102636060672, 8, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672102925467649, 832672102828998656, 9, 'INSERT_TEST');
INSERT INTO `t_order_item`  (`order_item_id`,`order_id`,`user_id`,`status`)  VALUES (832672103059685377, 832672102992576512, 10, 'INSERT_TEST');