ShardingSphere实战（5）- 读写分离

上篇博客，我们讲了 ShardingSphere实战（4）- 广播表和默认数据源 ，这篇博客，我们实现一下读写分离。

一、读写分离配置

# sharding-jdbc 读写分离策略
# 给数据源起别名，这里名称需要和下面的一致
spring.shardingsphere.datasource.names=master,slave0,slave1

# 配置数据源
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.jdbc-url=jdbc:mysql://xxx:3316/master?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.master.username=xxx
spring.shardingsphere.datasource.master.password=xxx

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://xxx:3316/slave0?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.slave0.username=xxx
spring.shardingsphere.datasource.slave0.password=xxx

spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://xxx/slave1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.slave1.username=xxx
spring.shardingsphere.datasource.slave1.password=xxx


# 负载均衡策略（轮询）
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin

# 读写分离规则名称
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

# 是否开启 SQL解析日志
spring.shardingsphere.props.sql.show=true

mybatis.mapper-locations=classpath:sqlmapper/*.xml

二、创建数据库

create database master;
create database slave0;
create database slave1;

create table `master`.t_user (
    user_id bigint not null,
    user_name varchar(50) null,
    primary key (user_id)
);
create table `slave0`.t_user (
    user_id bigint not null,
    user_name varchar(50) null,
    primary key (user_id)
);
create table `slave1`.t_user (
    user_id bigint not null,
    user_name varchar(50) null,
    primary key (user_id)
);

我们模拟一主两从的架构，ShardingSphere不支持数据同步，这里就不讲数据同步的方案了。

三、运行测试

1. 保存数据

/**
 * 读写分离测试-保存
 */
@GetMapping("/test9")
public String test9(@RequestParam("count") Integer count) {

    for (int i = 0; i < count; i++) {
        UserDO userDO = new UserDO();
        userDO.setUserId(this.getId());
        userDO.setUserName("userName" + i);
        userMapper.insertSelective(userDO);
    }
    return "success";
}

测试结果：

写操作进入了master库

2. 查询数据

/**
 * 读写分离测试-查询
 */
@GetMapping("/test10")
public String test10(@RequestParam("userId") Long userId) {

    UserDO userDO = userMapper.selectByPrimaryKey(userId);
    log.info("userDO: {}", userDO.toString());

    return "success";
}

测试结果：

读操作进入了slave库，因为我们的 负载均衡策略 是 round_robin，所以读请求轮询进入了 slave0和slave0

---

总结：本篇博客实现了 读写分离，后续会陆续更新 数据分片 + 读写分离、分布式事务等