如何使用sharding-sphere完成读写分离和分库分表?
一.sharding-sphere配置读写分离
1.先搭建好一个MySQL的主从集群,可以参考
2.在项目中导入相关依赖(记得刷新Maven)
org.apache.shardingsphere
shardingsphere-jdbc-core-spring-boot-starter
5.1.0
3.编写一个application-sharding.yml配置文件,可以参考官方文档.但是推荐参考最新版本的,不然很多坑.当然也可以使用下面配置好的,亲测可用!!!
spring:
shardingsphere:
datasource:
names: master1,slave1,slave2 # 指定所有数据源的名字
master1:
type: com.zaxxer.hikari.HikariDataSource # 数据源类型
url: jdbc:mysql://192.168.137.137:3306/qmall_product?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
slave1:
type: com.zaxxer.hikari.HikariDataSource # 数据源类型
url: jdbc:mysql://192.168.137.137:3307/qmall_product?useSSL=false
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
slave2:
type: com.zaxxer.hikari.HikariDataSource # 数据源类型
url: jdbc:mysql://192.168.137.137:3308/qmall_product?useSSL=false
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
rules:
readwrite-splitting: # 配置读写分离规则
data-sources:
ds_0: # 给一套集群起个名
type: static
props:
auto-aware-data-source-name: master1
write-data-source-name: master1
read-data-source-names: slave1,slave2
load-balancer-name: read-random
load-balancers:
read-random:
type: ROUND_ROBIN # 轮询负载均衡
props:
sql-show: true # 是否打印sql
sql-simple: true # 打印简单的sql
- 写完上面的配置文件别忘了在application.yml中激活引入一下
spring:
profiles:
include: sharding # 引入application-sharding.yml
4.编写测试代码
package com.qbb.qmall;
import com.qbb.qmall.model.product.BaseCategory1;
import com.qbb.qmall.product.ProductApplication;
import com.qbb.qmall.product.mapper.BaseCategoryMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
/**
* @author QiuQiu&LL (个人博客:https://www.cnblogs.com/qbbit)
* @version 1.0
* @date 2022-05-29 15:48
* @Description:
*/
@SpringBootTest(classes = ProductApplication.class)
public class ShardingTest {
@Autowired
private BaseCategoryMapper baseCategoryMapper;
@Test
public void write() {
BaseCategory1 baseCategory1 = new BaseCategory1();
baseCategory1.setName("qiuqiu");
baseCategoryMapper.insert(baseCategory1);
}
}
-
写数据是操作的master1库
-
下面测试一下读数据
@Test
public void read() {
BaseCategory1 one = baseCategoryMapper.selectById(18);
System.out.println("one = " + one);
BaseCategory1 two = baseCategoryMapper.selectById(18);
System.out.println("two = " + two);
BaseCategory1 three = baseCategoryMapper.selectById(18);
System.out.println("three = " + three);
BaseCategory1 four = baseCategoryMapper.selectById(18);
System.out.println("four = " + four);
}
- 因为我们上面配置的是轮询的负载均衡策略,所以是如上效果
二.sharding-sphere配置分库分表
- 话不多说在上面的基础上直接修改配置文件,如下
spring:
shardingsphere:
datasource: # 分库分两个库,分表分三张表
names: order-0-w,order-0-r1,order-0-r2,order-1-w,order-1-r1,order-1-r2 #指定所有数据源的名字
order-0-w:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3306/qmall_order_0?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
order-0-r1:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3307/qmall_order_0?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
order-0-r2:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3308/qmall_order_0?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
order-1-w:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3306/qmall_order_1?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
order-1-r1:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3307/qmall_order_1?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
order-1-r2:
type: com.zaxxer.hikari.HikariDataSource #数据源类型
url: jdbc:mysql://192.168.137.137:3307/qmall_order_1?useSSL=false # 数据库连接地址
username: root # 用户名
password: root # 密码
driver-class-name: com.mysql.jdbc.Driver # 数据库驱动
rules:
readwrite-splitting: #配置读写分离规则
data-sources:
order_0_rw: #给一套集群起个名
type: static
props:
write-data-source-name: order-0-w
read-data-source-names: order-0-r1,order-0-r2
load-balancer-name: read-random
order_1_rw: #给一套集群起个名
type: static
props:
write-data-source-name: order-1-w
read-data-source-names: order-1-r1,order-1-r2
load-balancer-name: read-random
load-balancers:
read-random:
type: ROUND_ROBIN #轮询负载均衡
#配置数据分片规则
sharding:
default-database-strategy:
standard:
sharding-column: user_id
shardingAlgorithmName: user-id-db-shard
tables: #指定逻辑表规则
order_info:
actualDataNodes: order-$->{0..1}-w.order_info_$->{1..3}
tableStrategy:
standard:
shardingColumn: user_id #告诉sharing如果插入或者查询数据。根据那一列去那张表
shardingAlgorithmName: user-id-table-shard
sharding-algorithms:
# 库分片规则
user-id-db-shard:
type: INLINE
props:
algorithm-expression: order-$->{user_id%2}-w
# 表的分片规则
user-id-table-shard:
type: INLINE
props:
algorithm-expression: order_info_$->{user_id % 3 + 1}
props:
sql-show: true # 是否打印sql
sql-simple: true # 打印简单的sql
- 分库
- 分表