ShardingSphere-JDBC读写分离

基于之前搭建的mysql主从读写分离使用ShardingSphere-JDBC实现读写分离

参考文章：Docker 部署 MySQL 一主多从_书启秋枫的博客-CSDN博客

CREATE DATABASE mydb2;
USE mydb2;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('zhang3');
INSERT INTO t_user(uname) VALUES(@@hostname)

一、创建SpringBoot程序

1. 创建springboot项目

项目名：sharding-jdbc-demo

SpringBoot版本：2.3.7.RELEASE

2. 添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.1.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.1</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>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
</dependencies>

3. 创建实体类

@TableName("t_user")
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String uname;
}

4. 创建Mapper

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

5. 配置读写分离

application.properties：

# 多数据源： 主 从 库的数据源名称
spring.shardingsphere.datasource.names=master,slave1,slave2

# 配置第 1 个数据源  master第一个数据源名称
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://47.100.73.47:3316/mydb2
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://47.100.73.47:3307/mydb2
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root
# 配置第 3 个数据源
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.url=jdbc:mysql://47.100.73.47:3308/mydb2
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root

# 读写分离配置
# 读写分离类型，如: Static，Dynamic
# Static：数据源在配置文件中是直接配置的
# Dynamic：数据源是由程序动态读取的
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.type=Static
# 写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.write-data-source-name=master
# 读数据源名称，多个从数据源用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.read-data-source-names=master,slave1,slave2
# 负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=alg_round
# 负载均衡算法配置
# 负载均衡算法类型：ROUND_ROBIN、RANDOM、WEIGHT
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_round.type=ROUND_ROBIN
# 负载均衡算法属性配置：type=WEIGHT 的时候配置
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave1=1
#spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave2=1
# 打印SQL
spring.shardingsphere.props.sql-show=true

二、测试读写分离

@SpringBootTest
class ReadwriteTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 写入数据
     */
    @Test
    public void testInsert(){
        User user = new User();
        user.setUname("taotao");
        userMapper.insert(user);
    }

    /**
     * 读数据:测试负载均衡
     */
    @Test
    public void testSelectAll(){
        List<User> users1 = userMapper.selectList(null);
        List<User> users2 = userMapper.selectList(null);//执行第二次测试负载均衡
        users1.forEach(System.out::println);
        users2.forEach(System.out::println);
    }

}