初次接触Sharding-JDBC并实际应用

Sharding-Jdbc是什么

在学习新的技术之前，先要了解这个技术是什么，能够解决什么样的问题。Sharding-Jdbc是ShardingSphere的一个组成部分，而ShardingSphere是从Apache毕业的一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩等能力对原有数据库进行增强。Sharding-Jdbc被官方定义为一个轻量级的Java框架，在原Jdbc做了额外的封装并提供服务，在使用时，只需要引入对应的jar包即可。目前支持MySQL，PostgreSQL，Oracle等多种数据库，支持Mybatis,JPA等多种ORM框架，支持c3p0,DBCP,Druid等多种数据库连接池。目前Sharding-Jdbc的功能有很多，包括数据分片，分布式事务，读写分离等，其中使用最多的就是数据分片。

官网地址： ​​shardingsphere.apache.org/document/cu…​​

数据分片

从性能上讲，在MySQL单一节点部署的情况下，当数据量达到一定的级别时，性能会大大下降。我们都知道MySQL的索引采用B+Tree的数据结构进行实现的，当存储大数据量时，B+Tree的深度就会增加，树的深度增加，那就意味着磁盘IO的次数也在增加。从运维成本上讲，单机数据库的数据备份或迁移会随着数据量的增加而增加。而数据分片，就是通过某种规则，将数据分发到不同的数据库或数据表中，从而提高数据库性能的一种解决方式。通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下，以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。数据分片按照拆分方式可以分为垂直分片和水平分片

垂直分片

垂直分片可以分为垂直分库和垂直分表，可以理解为专库专用，专表专用。这种垂直分表方式应该在项目开始前或者建表前就应该考虑，否则后期改动很大。以我司的部分业务为例，用户在接种疫苗前，可以在手机上进行预约，这时预约信息会存储在预约表中(app_user_appointment)，当接种完成后会生成一条接种记录并存储在记录表中(app_user_vaccinate_record)。

• 垂直分库：按照功能划分，将数据存储在不同的数据库。传统模式下，所有的数据表都在一个数据库下，后期数据将会愈发臃肿。而垂直分库，对数据进行了划分处理，比如预约表的数据存储在数据库DB0中，而接种记录数据存储在数据库DB1中，这样就可以做到专库专用，库与库之间不存在什么影响，数据也会被各个库分担。

• 垂直分表：将一个大表拆分为多个子表，减少字段的水平膨胀。比如可以将预约表app_user_appointment拆分为预约信息主表app_user_appointment_info和预约信息详情表app_user_appointment_detail。

水平分片

水平分片可以分为水平分库和水平分表。水平分片在于多个库或多张表结构相同并且命名存在一定的规律，这样在存储数据时，可以根据一个或多个字段并结合一定的规则，将数据存储到不同的数据库或数据表中。通过自己的实践发现，水平分片在扩容方面，要比垂直分片来的容易。

分片应用

在后面的例子中将会采用水平分片的方式，具体使用的相关技术有SpringBoot+MybatisPlus+Mysql5.7+ShardingJdbc+Druid。那么现在来模拟这样一个需求，用户在注册时会选择所在的省市，注册完成后会进行疫苗预约操作。后台在存储数据时，根据用户选择的省份编码来进行水平分库，用户预约疫苗时根据所在的市区编码进行水平分表。假设现在有两个省，分别为江苏省(310000),浙江省(320000)。其中江苏省下有两个市开通了预约功能，分别为南京市(310010)和苏州市(310020)。浙江省下有两个开通了预约功能，分别为杭州(320010)和宁波(320020)

根据上面的案例可以确定出需要针对于江苏省的数据库shardingjdbc_1和浙江省的数据库shardingjdbc_2，以及对应的数据表。

-- 模拟用户表(江苏) --
CREATE TABLE `app_user`  (
  `user_id` bigint(20) NOT NULL,
  `prov_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 模拟用户预约表(江苏-南京) --
CREATE TABLE `app_user_appointment_31001`  (
  `id` int(11) NOT NULL,
  `prov_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `time` datetime NULL DEFAULT NULL,
  `vaccine_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 模拟用户预约表(江苏-苏州) --
CREATE TABLE `app_user_appointment_31002`  (
  `id` int(11) NOT NULL,
  `prov_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `city_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_id` int(11) NULL DEFAULT NULL,
  `time` datetime NULL DEFAULT NULL,
  `vaccine_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- 模拟疫苗信息表 --
CREATE TABLE `vaccine_info`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vaccine_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

对于浙江的数据库和上面这几个表结构一样，只是表的名称会有所不同，因为后面需要根据这些表名的规则进行分表。最终的数据库的设计如下面两张图。

在搞定数据库后，下面就可以编写测试代码了。在编写代码前先引入sharding-jdbc的pom依赖到项目中。

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

单库单表

引入pom后，开始编写配置文件，数据库等相关配置。先从简单的单库开始，慢慢的升级到分库分表的配置。

server.port=8080
spring.main.allow-bean-definition-overriding=true
# 定义分库别名
spring.shardingsphere.datasource.names=db1
# 配置别名db1数据库信息
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/shardingjdbc_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root

这里一个简单的单体数据库已经配置完成了(不涉及分表)，现在可以编写测试代码像配置的shardingjdbc_1(江苏库)中添加一个用户信息。因为用的是MybatisPlus框架，所以对于单表的CRUD操作完全不需要我们手动编写SQL代码(这个很香)，直接调用对应的方法即可。

@Service
public class AppUserServiceImpl extends ServiceImpl<AppUserMapper, AppUser> implements AppUserService {

    /**
     * 保存用户信息
     * @param appUser
     * @return
     */
    @Override
    public boolean saveUser(AppUser appUser) {
        //业务代码....
        return this.save(appUser);
    }

    /**
     * 批量保存用户信息
     * @param appUsers
     * @return
     */
    @Override
    public boolean saveBatchUser(List<AppUser> appUsers) {
        //业务代码....
        return this.saveBatch(appUsers);
    }
}

@RestController
@RequestMapping("/shardingJdbc/user/")
public class AppUserController {

    @Resource
    private AppUserService appUserService;

    @PostMapping("/save")
    public boolean save(@RequestBody AppUser appUser) {
        return appUserService.saveUser(appUser);
    }

    @PostMapping("/saveBatch")
    public boolean saveBatch(@RequestBody List<AppUser> appUsera) {
        return appUserService.saveBatchUser(appUsera);
    }
}

现在测试代码已经编写完成，代码不多，但是实际业务中当然不会这么简单。下面可以简单的测试一下接口和配置是否正确。

分库

上面的代码能够正常的执行，说明sharding-jdbc在不分表分库的情况下仍然是可以正常应用的。那么现在继续对配置信息进行改造，做到用户注册时，根据选择的省份进行分库存储。

#定义数据库别名(db1->江苏库,db2->浙江库)
spring.shardingsphere.datasource.names=db1,db2

#配置江苏库连接信息
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/shardingjdbc_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=root
#配置浙江库连接信息
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/shardingjdbc_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=root
复制代码

在多数据源配置完成后，需要根据分片键(用于将数据库/表水平拆分的数据库字段)来决定存储到哪个数据库。

# app_user表在各个数据库中的分布情况，sharding-jdbc采用了groovy语法，表示db1.app_user , db2.app_user
spring.shardingsphere.sharding.tables.app_user.actual-data-nodes=db$->{1..2}.app_user
# 指定分片键为prov_code
spring.shardingsphere.sharding.tables.app_user.database-strategy.standard.sharding-column=prov_code
# 自定义分片算法
spring.shardingsphere.sharding.tables.app_user.database-strategy.standard.precise-algorithm-class-name=com.example.shardingsphere.algorithm.db.DbStandardAlgorithm

在sharding-jdbc中分片策略有五种，分片算法有四种。分片策略分别为标准分片算法(standard)，复合分片算法(complex),Hint分片算法以及行列分片算法(inline)。inline算法适合简单的模运算等操作，比如要根据用户id的奇偶进行分片，那么就可以使用inline。standard算法适用于使用单一分片键执行=，in，>=，<=，>，<,between and操作(准确分片，范围分片)。complex算法适用于多个通过多个分片键进行分片的场景，逻辑比较复杂。Hint算法无需配置分片键，分片键值也不再从SQL中解析，而是由外部指定分片信息，让SQL在指定的分库、分表中执行。最后一种分片策略即为不分片。分片算法分别为精确分片算法(PreciseShardingAlgorithm),范围分片算法(RangeShardingAlgorithm),复合分片算法(ComplexKeysShardingAlgorithm)以及Hint分片算法(HintShardingAlgorithm)。这里我们使用standard算法进行精准分片。

/**
 * @Author gdz
 * @Date 2022/11/2
 * @Desc 自定义Standard精准分片
 */
@Slf4j
public class DbStandardAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        log.info(JSONUtil.toJsonStr(collection)); // 数据源集合
        log.info(JSONUtil.toJsonStr(preciseShardingValue)); // 精准分片信息
        String value = preciseShardingValue.getValue(); // 获取分片键值
        if (StrUtil.startWith(value, "3100")) { //如果分片键prov_code以3100开头，则分配到江苏库
            return "db1";
        } else if (StrUtil.startWith(value, "3200")) { //如果分片键prov_code以3200开头，则分配到浙江库
            return "db2";
        }
        return null;
    }

}

现在配置已经完成，可以进行简单的测试，批量添加多个不同省份的用户信息，看最终数据分布情况是否正确。不过在测试之前，有个严重的问题，那就是用户唯一标识user_id怎么赋值。如果采用自增，那么就会出现两个库user_id冲突的问题。那么可以采用UUID，雪花算法或者是redis自增来保证分布式存储的user_id唯一性。sharding-jdbc中目前可以采用UUID或者雪花算法来生成user_id。推荐使用雪花算法，如果使用UUID，生成的user_id是一个字符串，这样无论在存储，查询都不如雪花算法生成的UUID效率高(索引底层涉及比较排序操作)。

#指定唯一主键
spring.shardingsphere.sharding.tables.app_user.key-generator.column=user_id
#使用雪花算法生成唯一id
spring.shardingsphere.sharding.tables.app_user.key-generator.type=snowflake
spring.shardingsphere.sharding.tables.app_user.key-generator.props.worker.id=1
复制代码

既然分库存储已经实现，那么查询的时候会不会出现问题呢，现在可以简单的测试一下，通过user_id或是prov_code来进行用户查询，看看最终效果如何。

/**
 * 通过用户id或省编码查询用户
 *
 * @param userId
 * @param provCode
 * @return
 */
@Override
public List<AppUser> getUser(long userId, String provCode) {
    List<AppUser> list = this.lambdaQuery()
            .eq(userId != 0, AppUser::getUserId, userId)
            .eq(StrUtil.isNotEmpty(provCode), AppUser::getProvCode, provCode)
            .list();
    return CollUtil.emptyIfNull(list);
}

@GetMapping("/getUser")
public List<AppUser> getUser(@RequestParam(name = "userId",required = false) long userId,
                             @RequestParam(name = "provCode",required = false) String provCode){
    return appUserService.getUser(userId,provCode);
}
复制代码

可以看到查询结果没有问题，因为user_id并不是分片键，所以sharding-jdbc在执行查询时不能确定查询具体的数据库，所以所有的数据库都会查询一边，已到达查询到指定数据的目的。这种情况我们可以通过输出的执行日志可以看的出来。

为了避免查询无用的数据库，在查询的时候可以加上分片键prov_code，这样sharding-jdbc就可以确定查询哪个些数据库。

分表

上面对分库做了简单的测试，那么现在就来测试分表，模拟用户预约疫苗时，按照预约的省市来进行分表。江苏南京的用户预约数据存储到shardingjdbc_1.app_user_appointment_31001中，江苏苏州的用户预约数据存储到shardingjdbc_1.app_user_appointment_31002中。浙江杭州的用户预约数据存储到shardingjdbc_2.app_user_appointment_32001中，浙江宁波的用户预约数据存储到shardingjdbc_2.app_user_appointment_32002中。

@Service
public class UserAppointmentServiceImpl extends ServiceImpl<UserAppointmentMapper, UserAppointment> implements UserAppointmentService {

    /**
     * 模拟用户预约
     *
     * @param userAppointment
     * @return
     */
    @Override
    public boolean saveUserAppointment(UserAppointment userAppointment) {
        userAppointment.setTime(new Date());
        return this.save(userAppointment);
    }

    /**
     * 模拟用户批量预约
     *
     * @param userAppointments
     * @return
     */
    @Override
    public boolean saveUserAppointmentBatch(List<UserAppointment> userAppointments) {
        userAppointments = userAppointments.stream().peek(p -> p.setTime(new Date())).collect(Collectors.toList());
        return this.saveBatch(userAppointments);
    }
}

@RestController
@RequestMapping("/shardingJdbc/appointment")
public class UserAppointmentController {

    @Resource
    private UserAppointmentService userAppointmentService;

    @PostMapping("/save")
    public boolean save(UserAppointment userAppointment) {
        return userAppointmentService.saveUserAppointment(userAppointment);
    }

    @PostMapping("/saveBatch")
    public boolean saveBatch(List<UserAppointment> userAppointments) {
        return userAppointmentService.saveUserAppointmentBatch(userAppointments);
    }
}

在编写完代码后，开始修改配置文件。需要注意的是，配置文件中的数据表app_user_appointment为逻辑表。逻辑表是指相同结构的水平拆分数据表的逻辑名称，是SQL中表的逻辑标识。比如app_user_appointment_31001的逻辑表名就是app_user_appointment。

# 确定分库字段为prov_code
spring.shardingsphere.sharding.tables.app_user_appointment.database-strategy.standard.sharding-column=prov_code
# 自定义分库算法
spring.shardingsphere.sharding.tables.app_user_appointment.database-strategy.standard.precise-algorithm-class-name=com.example.shardingsphere.algorithm.db.DbStandardAlgorithm
# 预约表在两个库的分布情况
spring.shardingsphere.sharding.tables.app_user_appointment.actual-data-nodes=db$->{1..2}.app_user_appointment_$->{['31001','31002','32001','32002']}
# 确定分表字段为city_code
spring.shardingsphere.sharding.tables.app_user_appointment.table-strategy.standard.sharding-column=city_code
# 自定义分表算法
spring.shardingsphere.sharding.tables.app_user_appointment.table-strategy.standard.precise-algorithm-class-name=com.example.shardingsphere.algorithm.table.TablesStandardAlgorithm
# 雪花算法生成id
spring.shardingsphere.sharding.tables.app_user_appointment.key-generator.column=id
spring.shardingsphere.sharding.tables.app_user_appointment.key-generator.type=snowflake
spring.shardingsphere.sharding.tables.app_user_appointment.key-generator.props.worker.id=1
复制代码

/*
* 自定义分表算法。判断cityCode的前位标识，来确定具体分到哪张表。分库的算法在DbStandardAlgorithm已经实现
*/
public class TablesStandardAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
       // 获取分片键cityCode的值
        String value = preciseShardingValue.getValue(); 
        String tableName = "";
        if (StrUtil.startWith(value, "31001")) {
            tableName = "app_user_appointment_31001";
        } else if (StrUtil.startWith(value, "31002")) {
            tableName = "app_user_appointment_31002";
        } else if (StrUtil.startWith(value, "32001")) {
            tableName = "app_user_appointment_32001";
        } else if (StrUtil.startWith(value, "32002")) {
            tableName = "app_user_appointment_32002";
        }
        return tableName;
    }
}
复制代码

现在代码和配置已经编写完成，那么就来测试一下是否能达到分库分表的效果。在ApiPost中添加多条模拟数据，以达到均匀分布的效果。

这样看上面的需求已经全部完成了，但是这些都是针对与数据存储的操作，那么数据查询会不会根据分片键定位到具体的数据库和表呢，下面有一段简单的SQL，用于分页查询某省市下用户预约信息的。

<select  resultType="com.example.shardingsphere.vo.UserAppointmentInfoVo">
    select
        a.user_id as userId,
        a.id as appointmentId,
        u.user_name as userName,
        a.prov_code as provCode,
        a.city_code as cityCode,
        a.time as appointmentDate,
        v.vaccine_name as vaccineName
    from  app_user_appointment a
    inner join app_user u on u.user_id = a.user_id
    inner join vaccine_info v on a.vaccine_id = v.id
    where a.prov_code = #{provCode} and a.city_code=#{cityCode}
    order by a.user_id
    limit #{pageNo},#{pageSize}
</select>
复制代码

经过测试，查询关联查询使用逻辑表名，也同样会根据分片键查询到具体的库和表。 [图片上传失败...(image-26a465-1667440970163)]

总结

对于海量数据的存储，sharding-jdbc确实是一个不错的解决方案，可以通过分表分库的方式减轻Mysql服务压力。业务中，可以根据具体的需求制定具体的分库或分表方案。但是需要注意的是，在分库分表的情况下有可能需要考虑分布式事务的问题，如果使用case when中包含子查询或者使用逻辑表名会出现报错的情况，具体有哪些不支持的情况可以参考官方文档。