SpringBoot整合ShardingSphere4读写分离、分库分表_spring.shardingsphere.sharding.master-slave-rules

一、读写分离

spring.shardingsphere.datasource.names=master,slave

## 第一个数据源
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.38.134:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

## 第二个数据源
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://192.168.38.135:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

## 读写分离
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
## 从库负载均衡，这里只设置了一个从库
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin
## 显示sharding-jdbc sql
spring.shardingsphere.props.sql.show=true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

二、单库分表

spring.shardingsphere.datasource.names=ds0

## 第一个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://192.168.38.134:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

## 分表后生成的真实表，t_order 生成 t_order0,  t_order1,  t_order2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->{0..2}
## 分片字段
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
## 分片算法，分片字段对3取模
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{orderId % 3}
## 主键自增字段
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
## 自增列值生成器类型，UUID 和 SNOWFLAKE（雪花算法）是内置的，
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item$->{0..2}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
## 绑定表，t_order和t_order_item 使用相同的分片策略，都是用order_id进行分片，提升查询效率
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
## 广播表
## spring.shardingsphere.sharding.broadcast-tables=t_config
## 开启sql显示
spring.shardingsphere.props.sql.show=true
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

三、分库不分表

spring.shardingsphere.datasource.names=ds0, ds1, ds2

## 第一个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://192.168.38.134:3306/order_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

## 第二个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.38.134:3306/order_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

## 第三个数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.38.134:3306/order_db2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456

# 默认数据源，不涉及分表的表会使用这个数据源
spring.shardingsphere.sharding.default-data-source-name=ds0

spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ds$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
## 绑定表，t_order和t_order_item 使用相同的分片策略，都是用order_id进行分片，提升查询效率
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
## 广播表
## spring.shardingsphere.sharding.broadcast-tables=t_config
## 开启sql显示
spring.shardingsphere.props.sql.show=true
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

四、分库+分表

spring.shardingsphere.datasource.names=ds0, ds1, ds2

## 第一个数据源
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://192.168.38.134:3306/order_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=123456

## 第一个数据源
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.38.134:3306/order_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456

## 第一个数据源
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.38.134:3306/order_db2?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456

# 默认数据源，不涉及分表的表会使用这个数据源
spring.shardingsphere.sharding.default-data-source-name=ds0

## 先根据分库算法，分出3个库，在每个库中在根据分表算法分出4个表
## 分库的inline表达式注意写法，数据源+序号，ds$-{orderId % 3}
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..2}.t_order$->{0..3}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{orderId % 4}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..2}.t_order_item$->{0..3}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{orderId % 4}
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ds$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
## 绑定表，t_order和t_order_item 使用相同的分片策略，都是用order_id进行分片，提升查询效率
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
## 广播表
## spring.shardingsphere.sharding.broadcast-tables=t_config
## 开启sql显示
spring.shardingsphere.props.sql.show=true
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

五、单库分表+读写分离

spring.shardingsphere.datasource.names=master,slave

## 第一个数据源（主）
spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://192.168.38.134:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

## 第二个数据源（从）
spring.shardingsphere.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.url=jdbc:mysql://192.168.38.135:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds0.t_order$->{0..2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds0.t_order_item$->{0..2}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{orderId % 3}
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE
## 绑定表，t_order和t_order_item 使用相同的分片策略，都是用order_id进行分片，提升查询效率
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item

## 读写分离
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=slave
## 从库负载均衡，这里只设置了一个从库
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin
## 显示sharding-jdbc sql
spring.shardingsphere.props.sql.show=true
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

项目配套源码：SpringBoot整合ShardingSphere4,实现读写分离+分库分表