Proxy-水平分片
Salted Fish 2023/6/29 proxy分库分表
修改配置文件，配置文件中关键部分都有备注， 可以结合 JDBC-水平分片理解
# 修改配置config-sharding.yaml
schemaName: sharding_db
# 数据源配置
dataSources:
ds_user:
url: jdbc:mysql://192.168.100.201:3301/db_user?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order0:
url: jdbc:mysql://192.168.100.201:3310/db_order?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_order1:
url: jdbc:mysql://192.168.100.201:3311/db_order?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
# 分片规则
rules:
- !SHARDING
tables:
t_user:
actualDataNodes: ds_user.t_user
t_order:
actualDataNodes: ds_order${0..1}.t_order${0..1} # 指定数据源
databaseStrategy: # 水平分库配置，按照user_id字段，alg_mod分片算法（下面有定义）
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy: # 水平分表配置 ，按照order_no字段，alg_hash_mod分片算法（下面有定义）
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy: # 主键采用雪花算法
column: id
keyGeneratorName: snowflake
t_order_item:
actualDataNodes: ds_order${0..1}.t_order_item${0..1}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: alg_mod
tableStrategy:
standard:
shardingColumn: order_no
shardingAlgorithmName: alg_hash_mod
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake
bindingTables:
- t_order,t_order_item # 绑定表
broadcastTables: # 广播表，插入、更新操作会实时在所有节点上执行，查询只从一个节点获取
- t_dict
# 分片算法
shardingAlgorithms:
alg_inline_userid:
type: INLINE
props:
algorithm-expression: server-order$->{user_id % 2}
alg_mod: # 取模算法，有两个节点
type: MOD
props:
sharding-count: 2
alg_hash_mod: # 哈希取模，如果主键不是纯数字采用此算法，有两个节点
type: HASH_MOD
props:
sharding-count: 2
keyGenerators:
snowflake:
type: SNOWFLAKE
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
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