6Sharding-JDBC 同库分表 · SpringCloud微服务实战

导航

本节代码地址

GitHub:https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-table

本节主要介绍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. 数据库创建

1.1 新建数据库

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

1.2 执行脚本

执行以下脚本将创建数据库master0，并分别在库里面创建sys_user0、sys_user1表

CREATE DATABASE master0;
USE master0;
DROP TABLE IF EXISTS `sys_user0`;
CREATE TABLE `sys_user0` (
  `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='系统用户表';
DROP TABLE IF EXISTS `sys_user1`;
CREATE TABLE `sys_user1` (
  `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='系统用户表';

2. 新建读写分离模块shardingsphere-learning-sub-table

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

2.1 添加依赖

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

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

2.2 应用配置

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

笔者在配置中先设置数据源的名称
配置数据库连接信息，如spring.shardingsphere.datasource.{数据源名称}.type
根据id配置分表规则
将sql日志信息打印出来

server.port=8900

#数据源定义
spring.shardingsphere.datasource.names=master0

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


#根据id分表
spring.shardingsphere.sharding.tables.sys_user.actual-data-nodes=master0.sys_user$->{0..1}
spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.sys_user.table-strategy.inline.algorithm-expression=sys_user$->{id % 2}

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

#调整日志为debug
logging.level.com.yisu= debug

2.3 新建启动类


@SpringBootApplication
public class FwShardingsphereSubTable{

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

}

2.4 新建单元测试

新建单元测试，用于测试同库分表的功能

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

    @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);
    }

}

2.4.1 测试新增

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

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

2020-03-23 15:34:23 INFO  main ShardingSphere-SQL Actual SQL: master0 ::: INSERT INTO sys_user0  ( 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  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241991664637906946, 123456789120, sys0, 2020-03-23 15:34:22.466, /home/avatar, fwcloud0, 0, realname0, 123456, 2020-03-23 15:34:22.463, 0, pos, sys0, depe0, ***@123.com0]


。。。。

2020-03-23 15:34:23 INFO  main ShardingSphere-SQL Actual SQL: master0 ::: INSERT INTO sys_user0  ( 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  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241991669247447042, 1234567891264, sys64, 2020-03-23 15:34:22.467, /home/avatar, fwcloud64, 0, realname64, 123456, 2020-03-23 15:34:22.467, 0, pos, sys64, depe64, ***@123.com64]

。。。

一共批量插入了100条数据，可以看到分别落入两个表里面

2.4.2 测试查询

运行testSelect方法，可以看到控制台的日志如下，可以看到查询的是sys_user0和sys_user1两张表，查询结果为100

2020-03-23 15:43:09 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==>  Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 
2020-03-23 15:43:09 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 
2020-03-23 15:43:10 INFO  main ShardingSphere-SQL Rule Type: sharding
2020-03-23 15:43:10 INFO  main ShardingSphere-SQL Logic SQL: SELECT COUNT( 1 ) FROM sys_user 
 WHERE  delete_flag=0
2020-03-23 15:43:10 INFO  main ShardingSphere-SQL SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@58932d08, tablesContext=TablesContext(tables=[Table(name=sys_user, alias=Optional.absent())], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=16, distinctRow=false, projections=[AggregationProjection(type=COUNT, innerExpression=( 1 ), alias=Optional.absent(), derivedAggregationProjections=[], index=-1)], columnLabels=[COUNT( 1 )]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@45648e75, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@e344f14, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@2d117280, containsSubquery=false)
2020-03-23 15:43:10 INFO  main ShardingSphere-SQL Actual SQL: master0 ::: SELECT COUNT( 1 ) FROM sys_user0 
 WHERE  delete_flag=0
2020-03-23 15:43:10 INFO  main ShardingSphere-SQL Actual SQL: master0 ::: SELECT COUNT( 1 ) FROM sys_user1 
 WHERE  delete_flag=0
2020-03-23 15:43:10 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <==      Total: 1
100

至此，同库分表已完成

6Sharding-JDBC 同库分表 · SpringCloud微服务实战 · 看云