2Sharding-JDBC 读写分离 · SpringCloud微服务实战

导航

本节代码地址

GitHub:https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-read-write

本节主要介绍Sharding-JDBC读写分离，并提供代码样例，在分布式项目架构中，读写分离也是常用的一种方案。

项目技术栈

技术栈	版本
jdk	1.8.0_161
SpringBoot	2.2.2.RELEASE
Shardingsphere	4.0.1
Mybatis-plus	3.3.0
Hutool	5.0.6
HikariCP	2.7.9

SpringBoot 基于SpringBoot 2.2.2.RELEASE 构建我们的应用
Shardingsphere 使用最新版的4.0.1来支撑读写分离
Mybatis-plus来作为我们的持久层框架，介绍代码的开发量
Hutool作为java工具包
HikariCP 项目中的连接池，性能要比Druid要高，目前SpringBoot 默认支持的数据连接池。
lombok 使代码简介，介绍getter\setter\构造器的创建，需要在idea中安装lombok插件

1. 新建模块shardingsphere-learning-common

为了减少代码开发量，笔者统一封装了一个shardingsphere-learning-common，用于将用户表（分库分表的功能全基于这张表演示）的增删改查功能封装。

1.1 maven 配置

这里我们将需要的连接池、Mybatis-plus、Shardingsphere 等依赖包添加进来作为公共依赖。后面的模块只需要依赖shardingsphere-learning-common包

<dependencies>

    <!-- 数据连接池-->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
    </dependency>
    <!-- mysql驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <!-- for spring boot -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>

    <!-- for spring namespace -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>${sharding-sphere.version}</version>
    </dependency>
</dependencies>

1.2 MybatisPlusConfig 配置

配置MybatisPlus，将mapper包的路径用@MapperScan配置起来，如果需要分页查询的动作，可以配置MybatisPlus自带的分页插件。


@Configuration
@MapperScan(value = "com.yisu.shardingsphere.common.mapper")
public class MybatisPlusConfig {
    
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

}

1.3 新建系统用户表-实体SysUser


@Data
@TableName("sys_user")
@EqualsAndHashCode(callSuper=false)
public class SysUser extends Model<SysUser> {

    
    @TableId(value = "id", type = IdType.ID_WORKER)
    private Long id;
    
    private Date createTime;
    
    private Date updateTime;
    
    private String createUser;
    
    private String updateUser;
    
    @TableLogic
    private Integer deleteFlag;
    
    private Integer disableFlag;

    
    private String posCode;
    
    private String avatar;
    
    private String email;
    
    private String password;
    
    private String userName;
    
    private String realName;
    
    private String deptCode;
    
    private String userPhone;
}

1.4 新建SysUserMapper 接口

并继承BaseMapper接口，BaseMapper是MybatisPlus 提供的默认Mapper实现


public interface SysUserMapper  extends BaseMapper<SysUser> {

}

实现BaseMapper的好处就是基本的增删改查不用自己写了

1.5 新建系统用户表-业务接口

接口需要继承IService，IService是MybatisPlus 针对service接口层的封装


public interface SysUserService extends IService<SysUser> {



}

可以看到IService里包含了很多的默认接口

1.6 新建系统用户表-业务实现

并继承ServiceImpl<SysUserMapper, SysUser>，ServiceImpl是MybatisPlus 提供的基本实现


@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {



}

ServiceImpl提了上面IService的节本实现，这样针对单表的增、删、改、查、分页查询、批量操作等

2. 数据库创建

2.1 新建数据库

mysql 数据库的搭建可以参考容器化章节（docker run 一个）

2.2 执行脚本

执行以下脚本将创建数据库ds_master和ds_slave，并分别在两个库里面创建sys_user表

CREATE DATABASE ds_master;
USE ds_master;
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  `create_user` varchar(100) NOT NULL COMMENT '创建人编码',
  `update_user` varchar(100) NOT NULL COMMENT '修改人编码',
  `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记(1 删除 0未删除)',
  `pos_code` varchar(50) DEFAULT NULL COMMENT '职位编码',
  `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '启用标记(1 禁用 0启用)',
  `avatar` varchar(100) DEFAULT NULL COMMENT '头像地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `dept_code` varchar(50) DEFAULT NULL COMMENT '部门编码',
  `user_phone` varchar(15) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '邮箱索引',
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用户名索引',
  KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部门编码索引',
  KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '职位编码索引'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='系统用户表';

CREATE DATABASE ds_slave;
USE ds_slave;
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  `create_user` varchar(100) NOT NULL COMMENT '创建人编码',
  `update_user` varchar(100) NOT NULL COMMENT '修改人编码',
  `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记(1 删除 0未删除)',
  `pos_code` varchar(50) DEFAULT NULL COMMENT '职位编码',
  `disable_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '启用标记(1 禁用 0启用)',
  `avatar` varchar(100) DEFAULT NULL COMMENT '头像地址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(50) DEFAULT NULL COMMENT '真实姓名',
  `dept_code` varchar(50) DEFAULT NULL COMMENT '部门编码',
  `user_phone` varchar(15) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_email` (`email`) USING BTREE COMMENT '邮箱索引',
  UNIQUE KEY `idx_user_name` (`user_name`) USING BTREE COMMENT '用户名索引',
  KEY `idx_dept_code` (`dept_code`) USING BTREE COMMENT '部门编码索引',
  KEY `idx_position_code` (`pos_code`) USING BTREE COMMENT '职位编码索引'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='系统用户表';

3. 新建读写分离模块shardingsphere-learning-read-write

上面我们已经配置了公共的针对用户表的基本操作，这些暂时已经够我们演示用了，如果不够，可以自定义自己的实现

3.1 添加依赖

这里我们将shardingsphere-learning-common的包依赖进来，方便我们操作用户表的增删改查

<dependencies>
    <dependency>
        <groupId>com.yisu.fwcloud</groupId>
        <artifactId>shardingsphere-learning-common</artifactId>
        <version>${version}</version>
    </dependency>
</dependencies>

3.2 应用配置

这里笔者将默认的端口修改为了8900，然后就是配置Sharding-JDBC的数据源，我们本节演示的Sharding-JDBC读写分离需要两个库（真实项目中需要两个数据库实例）。

笔者在配置中先设置数据源的名称
分别配置主备数据库连接信息，如spring.shardingsphere.datasource.{数据源名称}.type
对读写规则进行配置，查询时的负载均衡算法（load-balance-algorithm-type）默认是轮询、还可以选择随机，当然也可以实现MasterSlaveLoadBalanceAlgorithm接口自定义
配置读写分离实例的名称，并分别设置主从的实例名称（别分对应1中设置的哪个名称）
将sql日志信息打印出来

server.port=8900

#数据源定义
spring.shardingsphere.datasource.names=master,slave

# 数据源 主库
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://${dbIp}:3306/ds_master?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 数据源 从库
spring.shardingsphere.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave.jdbc-url=jdbc:mysql://${dbIp}:3306/ds_slave?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave.username=root
spring.shardingsphere.datasource.slave.password=123456

# 读写分离
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=slave

#打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true


logging.level.com.yisu= debug

3.3 新建启动类


@SpringBootApplication
public class FwShardingsphereReadWrite {

    public static void main(String[] args) {
        SpringApplication.run(FwShardingsphereReadWrite.class, args);
    }

}

3.4 新建单元测试

新建单元测试，用于测试读写分离的功能

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class UserinfoServiceImplReadWriteTest {

    @Autowired
    private SysUserService sysUserService;

    
    @Test
    public void testSelect(){
        int count = sysUserService.count();
        System.out.println(count);
    }

    
    @Test
    public void testInsert(){
        SysUser sysUser=new SysUser();
        sysUser.setAvatar("/home/avatar");
        sysUser.setCreateTime(DateUtil.date());
        sysUser.setCreateUser("sys");
        sysUser.setDeleteFlag(0);
        sysUser.setDeptCode("depe");
        sysUser.setDisableFlag(0);
        sysUser.setEmail("***@123.com");
        sysUser.setPassword("123456");
        sysUser.setPosCode("pos");
        sysUser.setRealName("realname");
        sysUser.setUpdateTime(DateUtil.date());
        sysUser.setUserName("fwcloud");
        sysUser.setUpdateUser("sys");
        sysUser.setUserPhone("12345678912");
        sysUserService.save(sysUser);
    }

    
    @Test
    public void testBatch(){
        List<SysUser> list=new ArrayList<>();
        for (int i = 0; i <100 ; i++) {
            SysUser sysUser=new SysUser();
            sysUser.setAvatar("/home/avatar");
            sysUser.setCreateTime(DateUtil.date());
            sysUser.setCreateUser("sys"+i);
            sysUser.setDeleteFlag(0);
            sysUser.setDeptCode("depe"+i);
            sysUser.setDisableFlag(0);
            sysUser.setEmail("***@123.com"+i);
            sysUser.setPassword("123456");
            sysUser.setPosCode("pos");
            sysUser.setRealName("realname"+i);
            sysUser.setUpdateTime(DateUtil.date());
            sysUser.setUserName("fwcloud"+i);
            sysUser.setUpdateUser("sys"+i);
            sysUser.setUserPhone("12345678912"+i);
            list.add(sysUser);
        }

        boolean saveBatch = sysUserService.saveBatch(list);
        Assert.assertEquals(true,saveBatch);
    }

}

3.4.1 测试新增

先配置数据库的ip地址，变量是dbIp，值是ip地址，也可以不用变量直接用真实的ip地址

运行testInsert方法，可以看到控制台的日志如下，可以看到走的实例是master

2020-03-22 16:45:50 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.insert ==>  Preparing: INSERT INTO sys_user ( id, user_phone, update_user, update_time, avatar, user_name, delete_flag, real_name, password, create_time, disable_flag, pos_code, create_user, dept_code, email ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) 
2020-03-22 16:45:50 INFO  main ShardingSphere-SQL Rule Type: master-slave
2020-03-22 16:45:50 INFO  main ShardingSphere-SQL SQL: INSERT INTO sys_user  ( id,
user_phone,
update_user,
update_time,
avatar,
user_name,
delete_flag,
real_name,
password,
create_time,
disable_flag,
pos_code,
create_user,
dept_code,
email )  VALUES  ( ?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
? ) ::: DataSources: master
2020-03-22 16:45:50 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.insert ==> Parameters: 1241647261536813058(Long), 12345678912(String), sys(String), 2020-03-22 16:45:50.467(Timestamp), /home/avatar(String), fwcloud(String), 0(Integer), realname(String), 123456(String), 2020-03-22 16:45:50.467(Timestamp), 0(Integer), pos(String), sys(String), depe(String), ***@123.com(String)
2020-03-22 16:45:50 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.insert <==    Updates: 1

3.4.2 测试查询

运行testSelect方法，可以看到控制台的日志如下，可以看到走的实例是slave，查询结果为0是因为读库没有数据

2020-03-22 16:49:42 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==>  Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 
2020-03-22 16:49:43 INFO  main ShardingSphere-SQL Rule Type: master-slave
2020-03-22 16:49:43 INFO  main ShardingSphere-SQL SQL: SELECT COUNT( 1 ) FROM sys_user 
 WHERE  delete_flag=0 ::: DataSources: slave
2020-03-22 16:49:43 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 
2020-03-22 16:49:43 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <==      Total: 1
0

至此，读写分离已完成

2Sharding-JDBC 读写分离 · SpringCloud微服务实战 · 看云