分库分表-ShardingJDBC

项目地址: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