ShardingSphere-分库分表
LiJunYi 2/2/2023 ShardingSphere
# 分库分表代码示例
# 一般yml配置
spring:
shardingsphere:
#显示sql
props:
sql:
show: true
#配置数据源
datasource:
# 取别名
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
#配置从库ds2
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
sharding:
default-data-source-name: ds1
#配置分表的规则
tables:
#ksd_user 逻辑表名,库中实际不存在
ksd_user:
#数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
# 拆分库策略，也就是什么样的数据放到哪个数据库中
database-strategy:
inline:
sharding-column: age
algorithm-expression: ds$->{age % 2}
table-strategy:
inline:
sharding-column: age #分片字段（分片键）
algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
#需求:对用户1000w的数据进行分表和分库的操作。
#根据年龄单数存储在ksd_userl 偶数ksd_user0;同时age单数存储在ds1偶数ds0中
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
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
# 自定义分片策略处理类
public class BirthdayAlgorithm implements PreciseShardingAlgorithm<Date>
{
List<Date> dateList = new ArrayList<>();
{
Calendar calendar2 = Calendar.getInstance();
calendar2.set(2021,1,1,0,0,0);
Calendar calendar3 = Calendar.getInstance();
calendar3.set(2022,1,1,0,0,0);
// 数据源有几个，这里就写几个，否则下面循环会报错
dateList.add(calendar2.getTime());
dateList.add(calendar3.getTime());
}
/**
* 进行切分
*
* @param collection 数据源集合(ds0 ds1)
* @param preciseShardingValue 精确的分片值
* @return {@link String}
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
// 获取属性数据库的值
Date date = preciseShardingValue.getValue();
// 获取数据源的名称信息列表
Iterator<String> iterator = collection.iterator();
String target = null;
for (Date s : dateList)
{
target = iterator.next();
// 如果数据晚于指定的日期直接返回
if (date.before(s))
{
break;
}
}
return target;
}
}
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
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
# yml指定分片策略
spring:
shardingsphere:
#显示sql
props:
sql:
show: true
#配置数据源
datasource:
# 取别名
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
#配置从库ds2
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
sharding:
default-data-source-name: ds1
#配置分表的规则
tables:
#ksd_user 逻辑表名,库中实际不存在
ksd_user:
#数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
actual-data-nodes: ds$->{0..1}.ksd_user$->{0..1}
# 拆分库策略，也就是什么样的数据放到哪个数据库中
database-strategy:
standard:
shardingColumn: year
preciseAlgorithmClassName: com.example.shardingjdbc.config.BirthdayAlgorithm
table-strategy:
inline:
sharding-column: age #分片字段（分片键）
algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式
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
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
# 按年月分库分表
# 自定义按年月分库分表分片算法
public class YearMonthShardingAlgorithm implements PreciseShardingAlgorithm<String>
{
private static final String SPLITTER = "_";
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
String tbName = preciseShardingValue.getLogicTableName() + "_" + preciseShardingValue.getValue();
System.out.println("Sharding input :" + preciseShardingValue.getLogicTableName() + ", output:{}" + tbName);
return tbName;
}
}
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# yml配置
# 按年月分库分表实现
spring:
shardingsphere:
#显示sql
props:
sql:
show: true
#配置数据源
datasource:
# 取别名
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.101:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
#配置从库ds2
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://115.236.38.120:3306/test_slave?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: root
password: admin123
maxPoolSize: 100
minPoolSize: 5
sharding:
default-data-source-name: ds0
#配置分表的规则
tables:
ksd_user_order:
#数据节点:数据源$->{0..N}.逻辑表名$->{0..N}
actual-data-nodes: ds$->{0..1}.ksd_user_order$->{2021..2022}${(1..3).collect{t -> t.toString().padLeft(2,'0')}
key-generator:
column: orderid
type: SNOWFLAKE
database-strategy:
inline:
sharding-column: orderid
algorithm-expression: ds$->{orderid % 2}
table-strategy:
standard:
shardingColumn: yearmonth
preciseAlgorithmClassName: com.example.shardingjdbc.config.YearMonthShardingAlgorithm
# inline:
# sharding-column: yearmonth
# algorithm-expression: ksd_user_order_$->{yearmonth} # 分片算法表达式
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
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
# 分布式主键配置
ShardingSphere 提供灵活的配置分布式主键生成策略方式。在分片规则配置模块克配置每个表的主键生成策略。默认使用雪花算法。(snowflake)生成64bit的长整型数据。支持两种方式配置(注意：主键列不能自增长，数据类型是: bigint(20) ):
snowflakeUUID
# yml配置
spring:
shardingsphere:
sharding:
tables:
ksd_user:
# 分布式主键的设置
key-generator:
# 主键的列名
column: id
type: SNOWFLAKE
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 分页和统计
public interface UserDao{
/**
* 用户分页
* ksd_user 对应yml的逻辑表
* @param pageNo 页面
* @param pageSize 页面大小
* @return {@link List<UserModel>}
*/
@Select("select * from ksd_user limit #{pageNo},#{pageSize}")
List<UserModel> userLimit(@Param("pageNo") Integer pageNo,@Param("pageSize") Integer pageSize);
/**
* 数用户
* shardingJdbc 会把你规则的所有可能性形成一个笛卡尔积
* @return int
*/
@Select("select count(1) from ksd_user")
int countUser();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21