# Sharding-JDBC 同库分表读写分离 >>**导航** [TOC] ## 本节代码地址 GitHub:[https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-table-read-write](https://github.com/xuyisu/fw-shardingsphere-learning/tree/master/shardingsphere-learning-sub-table-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. 数据库创建 ### 1.1 新建数据库 mysql 数据库的搭建可以**参考容器化章节**（docker run 一个） ### 1.2 执行脚本 执行以下脚本将创建数据库master0，slave0，并分别在库里面创建`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='系统用户表'; CREATE DATABASE slave0; USE slave0; 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-read-write 上面我们已经配置了公共的针对用户表的基本操作，这些暂时已经够我们演示用了，如果不够，可以自定义自己的实现 ### 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读写分离需要两个库（真实项目中需要两个数据库实例）。 1. 笔者在配置中先设置数据源的名称 2. 配置数据库连接信息，如`spring.shardingsphere.datasource.{数据源名称}.type` 3. 配置读写分离实例的名称，并分别设置主从的实例名称（别分对应1中设置的哪个名称） 4. 根据id配置分表规则 5. 将sql日志信息打印出来 ~~~ server.port=8900 #数据源定义 spring.shardingsphere.datasource.names=master0,slave0 # 数据源 主库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 # 数据源 从库0 spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://${dbIp}:3306/slave0?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=123456 # 读写分离 spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0 spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0 #根据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 新建启动类 ~~~ /** * @description 启动类 * @author xuyisu * @date '2020-03-22' */ @SpringBootApplication public class FwShardingsphereSubTableReadWrite{ public static void main(String[] args) { SpringApplication.run(FwShardingsphereSubTableReadWrite.class, args); } } ~~~ ### 2.4 新建单元测试 新建单元测试，用于测试同库分表读写分离的功能 ~~~ @RunWith(SpringRunner.class) @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT) public class SysUserServiceImplSubTableReadWrite{ @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_user1 ( 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241991669247447041, 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方法，可以看到控制台的日志如下，可以看到查询的是slave0库sys_user0和sys_user1两张表，查询结果为0，因为从库没有数据 ~~~ 2020-03-23 16:19:28 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-23 16:19:28 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 2020-03-23 16:19:29 INFO main ShardingSphere-SQL Rule Type: sharding 2020-03-23 16:19:29 INFO main ShardingSphere-SQL Logic SQL: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 2020-03-23 16:19:29 INFO main ShardingSphere-SQL SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@4769537a, 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@4504a4ed, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@7ee3a2ed, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@38363e96, containsSubquery=false) 2020-03-23 16:19:29 INFO main ShardingSphere-SQL Actual SQL: slave0 ::: SELECT COUNT( 1 ) FROM sys_user0 WHERE delete_flag=0 2020-03-23 16:19:29 INFO main ShardingSphere-SQL Actual SQL: slave0 ::: SELECT COUNT( 1 ) FROM sys_user1 WHERE delete_flag=0 2020-03-23 16:19:29 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <== Total: 1 0 ~~~ 至此，同库分表读写分离已完成