springboot+shardingsphere实现读写分离和分表
参考:https://blog.csdn.net/weixin_44606481/article/details/140955787
前提:数据库配置了主从数据同步
1、依赖
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.4</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
2、读写分离
2.1 数据表:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
2.2 controller、service、Mapper、entity按平时的建立
package com.cgy.daily.controller;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.cgy.daily.entity.Iuser;
import com.cgy.daily.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class CesController {
@Autowired
UserMapper userMapper;
@GetMapping("/cs/test")
public String test() {
Iuser iuser = new Iuser();
iuser.setName("tom-1");
iuser.setAge(100);
iuser.setEmail("tt@qq.com");
userMapper.insert(iuser);
return "test";
}
@GetMapping("/cs/list")
public String list() {
List<Iuser> iusers = userMapper.selectList(new LambdaQueryWrapper<>());
System.out.println();
return "test";
}
}
2.3 配置application.yml文件
server:
port: 9901
spring:
shardingsphere:
props:
sql-show: true
datasource:
names: ds1,ss0,ss1
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
ss0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
ss1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
rules:
readwrite-splitting:
data-sources:
ms:
static-strategy:
write-data-source-name: ds1
read-data-source-names:
- ss0
- ss1
load-balancer-name: round_robin
load-balancers:
round_robin:
type: ROUND_ROBIN
当插入的时候,插入主库,查询的时候,轮询查询从库
3、分表
3.1 配置yaml文件
server:
port: 9901
spring:
shardingsphere:
# 开启sql显示
props:
sql-show: true
datasource:
#全部数据源名称,多个用逗号隔开
names: ds1,ss0,ss1
#主数据源
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
# 从数据源0
ss0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
#从数据源1
ss1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
rules:
sharding:
tables:
# user为需要分的表user表
user:
actual-data-nodes: ds1.user_$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
user-inline:
type: INLINE
props:
# 分片键为id,分片算法为INLINE,表达式为id % 2,即根据id的奇偶性决定数据存储在哪个表中
algorithm-expression: user_$->{id % 2}
key-generators:
snowflake:
# 使用SNOWFLAKE作为全局唯一ID生成器,确保分布式环境下的ID唯一性
type: SNOWFLAKE
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
4、读写分离+分表
4.1 整合后的yml文件
server:
port: 9901
spring:
shardingsphere:
# 开启sql显示
props:
sql-show: true
datasource:
#全部数据源名称,多个用逗号隔开
names: ds1,ss0,ss1
#主数据源
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
# 从数据源0
ss0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
#从数据源1
ss1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/dailyhub?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: 12345aass
rules:
readwrite-splitting:
data-sources:
# cgy是逻辑数据源名称,用于读写分离 自己定义
cgy:
# 写操作将路由到主数据源ds1,读操作将路由到从数据源ss0和ss1
static-strategy:
write-data-source-name: ds1
read-data-source-names:
- ss0
- ss1
load-balancer-name: round_robin
load-balancers:
# 使用ROUND_ROBIN负载均衡策略在多个从数据源之间分发读请求。
round_robin:
type: ROUND_ROBIN
sharding:
tables:
# user为需要分的表user表
user:
# user表被分片到ms.user_0和ms.user_1两个实际物理表中,cgy为逻辑数据源名称
actual-data-nodes: cgy.user_$->{0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
user-inline:
type: INLINE
props:
# 分片键为id,分片算法为INLINE,表达式为id % 2,即根据id的奇偶性决定数据存储在哪个表中
algorithm-expression: user_$->{id % 2}
key-generators:
snowflake:
# 使用SNOWFLAKE作为全局唯一ID生成器,确保分布式环境下的ID唯一性
type: SNOWFLAKE
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
查询从从库里分表查,插入到主库的分表。