上一篇文章将介绍了ShardingSphere-Proxy的安装和基本配置,包括安装包的下载及安装,权限配置、属性配置、server的属性配置等。此处接上文,继续介绍的数据分片配置,数据分片配置在conf目录的config-sharding.yaml配置文件。
初始化两台分库:
10.10.1.1:3306/demo_ds_0
10.10.1.2:3306/demo_ds_1
两个分库分别建立分表:t_order、t_order_item的分表1、2
两台分库分别执行以下SQL:
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)
);
config-sharding.yaml配置示例
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
数据分片配置说明
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: # 分片审计算法属性配置
# ...
插入测试数据
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');
查询结果