SpringBoot整合ShardingSphere4读写分离、分库分表

2 篇文章 0 订阅
1 篇文章 0 订阅

一、读写分离

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

二、单库分表

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

三、分库不分表

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

四、分库+分表

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

五、单库分表+读写分离

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

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

  • 1
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,我可以为您解答关于SpringBootShardingSphere实现读写分离的问题。 首先,您需要在pom.xml文件中添加ShardingSphere相关依赖: ```xml <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>${sharding-sphere.version}</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency> ``` 其中${sharding-sphere.version}为ShardingSphere的版本号。 接下来,您需要在application.yml或application.properties中配置ShardingSphere的数据源和规则,例如: ```yaml spring: shardingsphere: datasource: names: ds_master, ds_slave_0, ds_slave_1 ds_master: url: jdbc:mysql://localhost:3306/mydb_master?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8 username: root password: root ds_slave_0: url: jdbc:mysql://localhost:3306/mydb_slave_0?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8 username: root password: root ds_slave_1: url: jdbc:mysql://localhost:3306/mydb_slave_1?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8 username: root password: root sharding: default-data-source-name: ds_master master-slave-rules: ds_0: master-data-source-name: ds_master slave-data-source-names: ds_slave_0, ds_slave_1 load-balance-algorithm-type: round_robin ``` 以上配置中,我们配置了3个数据源:ds_master, ds_slave_0和ds_slave_1,其中ds_master为主库,ds_slave_0和ds_slave_1为从库。然后我们使用了ShardingSphere提供的master-slave规则将ds_master和ds_slave_0、ds_slave_1进行了关联,并使用了轮询算法进行负载均衡,从而实现了读写分离。 最后,您需要在SpringBoot主类上添加@EnableSharding注解,以启用ShardingSphere的功能。 这就是使用SpringBootShardingSphere实现读写分离的基本步骤。希望对您有所帮助!

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值