4Sharding-JDBC 分库分表Config · SpringCloud微服务实战

导航

本节代码地址

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

本节主要介绍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 执行脚本

执行以下脚本将创建数据库ds_0和ds_1，并分别在两个库里面创建sys_user0、sys_user1表

CREATE DATABASE ds0;
USE ds0;
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 ds1;
USE ds1;
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-db-table-config

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

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读写分离需要两个库（真实项目中需要两个数据库实例）。

将sql日志信息打印出来

server.port=8900
#打印sql
spring.shardingsphere.props.sql.show=true
logging.level.com.yisu= debug
spring.main.allow-bean-definition-overriding=true

2.3 数据源配置

这里我们通过java bean 的方式手动创建HikariDataSource 来设置多个数据源，其中主键生成部分笔者使用的是MybatisPlus的雪花算法，也可以使用注释部分的，并且需要配置ShardingRuleConfiguration来配置需要分片的表、配置库的分片规则、需要分库分表的字段。


@Configuration
public class DataSourceConfig{

    
    @Bean
    public DataSource dataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        
        shardingRuleConfig.getBindingTableGroups().add("sys_user");
        

        
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}"));
        
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", new ShardingTableAlgorithmConfig()));
        Properties properties = new Properties();
        properties.setProperty("sql.show",Boolean.TRUE.toString());
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
    }

    





    TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("sys_user", "ds${0..1}.sys_user${0..1}");

        return result;
    }

    
    Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put("ds0", createDataSource("ds0"));
        result.put("ds1", createDataSource("ds1"));
        return result;
    }

    
    public static DataSource createDataSource(final String dataSourceName) {
        HikariDataSource result = new HikariDataSource();
        result.setDriverClassName(com.mysql.jdbc.Driver.class.getName());
        result.setJdbcUrl(String.format("jdbc:mysql://%s:%s/%s?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8", "localhost", "3306", dataSourceName));
        result.setUsername("root");
        result.setPassword("123456");
        return result;
    }

}

2.4 分表配置

因为我们在上面配置了id为分表的字段，因此按此字段取模分片即可。


public class ShardingTableAlgorithmConfig implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(final Collection<String> tableNames, final PreciseShardingValue<Long> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new RuntimeException("没有需要分的表");
    }
}

2.5 新建启动类


@SpringBootApplication
public class FwShardingsphereSubDbTableConfig{

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

}

2.6 新建单元测试

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

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

    @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.6.1 测试新增

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

运行testBatch方法，可以看到控制台的日志如下，可以看到走的实例是ds0和ds1都有

2020-03-22 17:18:33 INFO  main ShardingSphere-SQL Actual SQL: ds0 ::: 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  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241655496025108482, 123456789122, sys2, 2020-03-22 17:18:32.641, /home/avatar, fwcloud2, 0, realname2, 123456, 2020-03-22 17:18:32.641, 0, pos, sys2, depe2, ***@123.com2]
。。。。

2020-03-22 17:18:34 INFO  main ShardingSphere-SQL Actual SQL: ds0 ::: 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  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [1241655497413423106, 1234567891266, sys66, 2020-03-22 17:18:32.641, /home/avatar, fwcloud66, 0, realname66, 123456, 2020-03-22 17:18:32.641, 0, pos, sys66, depe66, ***@123.com66]

。。。

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

2.6.2 测试查询

运行testSelect方法，可以看到控制台的日志如下，因为没有读写分离，所以会查询所有实例和表，总数量100

2020-03-22 17:27:45 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==>  Preparing: SELECT COUNT( 1 ) FROM sys_user WHERE delete_flag=0 
2020-03-22 17:27:45 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount ==> Parameters: 
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Rule Type: sharding
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Logic SQL: SELECT COUNT( 1 ) FROM sys_user 
 WHERE  delete_flag=0
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@7bfc36d5, 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@46b55a0e, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@7abd75fc, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@31b67d61, containsSubquery=false)
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Actual SQL: ds0 ::: SELECT COUNT( 1 ) FROM sys_user0 
 WHERE  delete_flag=0
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Actual SQL: ds0 ::: SELECT COUNT( 1 ) FROM sys_user1 
 WHERE  delete_flag=0
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Actual SQL: ds1 ::: SELECT COUNT( 1 ) FROM sys_user0 
 WHERE  delete_flag=0
2020-03-22 17:27:46 INFO  main ShardingSphere-SQL Actual SQL: ds1 ::: SELECT COUNT( 1 ) FROM sys_user1 
 WHERE  delete_flag=0
2020-03-22 17:27:46 DEBUG main com.yisu.shardingsphere.common.mapper.SysUserMapper.selectCount <==      Total: 1
100

至此，分库分表基于java bena 的方式已完成

4Sharding-JDBC 分库分表Config · SpringCloud微服务实战 · 看云