ShardingSphere-JDBC进行分库分表

一、前言：分库分表

在大型的互联网系统中，可能单台MySQL的存储容量无法满足业务的需求，这时候就需要进行扩容了。

和之前的问题一样，单台主机的硬件资源是存在瓶颈的，不可能无限制地纵向扩展，这时我们就得通过多台实例来进行容量的横向扩容，我们可以将数据分散存储，让多台主机共同来保存数据。

那么问题来了，怎么个分散法？

垂直拆分

我们的表和数据库都可以进行垂直拆分，所谓垂直拆分，就是将数据库中所有的表，按照业务功能拆分到各个数据库中而对于一张表，也可以通过外键之类的机制，将其拆分为多个表。

水平拆分

水平拆分针对的不是表，而是数据，我们可以让很多个具有相同表的数据库存放一部分数据，相当于是将数据分散存储在各个节点上

那么要实现这样的拆分操作，我们自行去编写代码工作量肯定是比较大的，因此目前实际上已经有一些解决方案了，比如我们可以使用MyCat（也是一个数据库中间件，相当于挂了一层代理，再通过MyCat进行分库分表操作数据库，只需要连接就能使用，类似的还有ShardingSphere-Proxy）或是Sharding JDBC（应用程序中直接对SQL语句进行分析，然后转换成分库分表操作，需要我们自己编写一些逻辑代码），这里我们就讲解一下Sharding JDBC。

二、ShardingJDBC介绍

官方文档（中文）：https://shardingsphere.apache.org/document/5.1.0/cn/overview/#shardingsphere-jdbc

定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。

适用于任何基于 JDBC 的 ORM 框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template 或直接使用 JDBC；
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, HikariCP 等；
支持任意实现 JDBC 规范的数据库，目前支持 MySQL，PostgreSQL，Oracle，SQLServer 以及任何可使用 JDBC 访问的数据库。

三、使用Sharding-JDBC进行实战

环境准备

数据库：

数据库：需要两台MySQL数据库，我这里是两台MySQL8的数据库，并且保证能够正常连接访问

数据表：

建立数据库：sharding

两个数据库分别建立两个表：user

CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

使用SpringBoot项目进行分库

1、依赖


    org.apache.shardingsphere
    shardingsphere-jdbc-core-spring-boot-starter
    5.1.0


    mysql
    mysql-connector-java
    runtime


    org.projectlombok
    lombok


    org.springframework.boot
    spring-boot-starter-web


    org.mybatis.spring.boot
    mybatis-spring-boot-starter
    2.1.4

2、配置数据源

进行分库，必须要有两个数据库，现在来配置下两台数据库的连接池

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 连接地址
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

如果启动没有问题，那么就是配置成功了：

3、编写实体类跟Mapper　　

pojo/User.java

package com.zhixi.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @ClassName User
 * @Author zhangzhixi
 * @Description
 * @Date 2022-6-13 23:10
 * @Version 1.0
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Long id;
    private String name;
    private Integer age;
}

mapper/UserMapper.java

package com.zhixi.mapper;

import com.zhixi.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

/**
 * @ClassName UserMapper
 * @Author zhangzhixi
 * @Description
 * @Date 2022-6-13 23:12
 * @Version 1.0
 */
@Mapper
public interface UserMapper {

    @Select("select * from user where id = #{id}")
    User getUserById(int id);

    @Insert("insert into user(id, name, age) values(#{id}, #{name}, #{age})")
    int addUser(User user);
}

4、进行分片

实际上这些操作都是常规操作，在编写代码时关注点依然放在业务本身上，现在我们就来编写配置文件，我们需要告诉ShardingJDBC要如何进行分片，

首先明确：现在是两个数据库都有user表存放用户数据，我们目标是将用户信息分别存放到这两个数据库的表中。　　

全配置如下：

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 连接地址
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

    rules:
      sharding:
        tables:
          #这里填写表名称，程序中对这张表的所有操作，都会采用下面的路由方案
          #比如我们上面Mybatis就是对test表进行操作，所以会走下面的路由方案
          user:
            #这里填写实际的路由节点，比如现在我们要分两个库，那么就可以把两个库都写上，以及对应的表
            #也可以使用表达式，比如下面的可以简写为 db$->{0..1}.test
            actual-data-nodes: db0.user,db1.user
            #这里是分库策略配置
            database-strategy:
              #这里选择标准策略，也可以配置复杂策略，基于多个键进行分片
              standard:
                #参与分片运算的字段，下面的算法会根据这里提供的字段进行运算
                sharding-column: id
                #这里填写我们下面自定义的算法名称
                sharding-algorithm-name: my-alg
        sharding-algorithms:
          #自定义一个新的算法，名称随意
          my-alg:
            #算法类型，官方内置了很多种，这里演示最简单的一种
            type: MOD
            props:
              sharding-count: 2
    props:
      #开启日志，一会方便我们观察
      sql-show: true

其中，分片算法有很多内置的，可以在这里查询：https://shardingsphere.apache.org/document/5.1.0/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/sharding/，这里我们使用的是MOD，也就是取模分片算法，它会根据主键的值进行取模运算，比如我们这里填写的是2，那么就表示对主键进行模2运算，根据数据源的名称，比如db0就是取模后为0，db1就是取模后为1（官方文档描述的并不是很清楚），也就是说，最终实现的效果就是单数放在db1，双数放在db0，当然它还支持一些其他的算法，这里就不多介绍了。

那么现在我们编写一个测试用例来看看，是否能够按照我们上面的规则进行路由：

package com.zhixi;

import com.zhixi.mapper.UserMapper;
import com.zhixi.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest
class ShardingjdbcDemoApplicationTests {
    @Resource
    UserMapper userMapper;

    @Test
    void contextLoads() {
        for (int i = 1; i <= 10; i++) {
            userMapper.addUser(new User((long) i, "demo", 21));
        }
    }

}

运行一下查看结果：

可以看到这两张表，都成功按照我们指定的路由规则进行插入了，我们来看看详细的路由情况，通过控制台输出的SQL就可以看到：

使用SpringBoot项目进行分表

分库完成之后，接着我们来看分表，比如现在我们的数据库中有user_0和user_1两张表，表结构一样，但是我们也是希望能够根据id取模运算的结果分别放到这两个不同的表中，实现思路其实是差不多的，这里首先需要介绍一下两种表概念：

逻辑表：相同结构的水平拆分数据库（表）的逻辑名称，是 SQL 中表的逻辑标识。例：订单数据根据主键尾数拆分为 10 张表，分别是 t_order_0 到 t_order_9，他们的逻辑表名为 t_order
真实表：在水平拆分的数据库中真实存在的物理表。即上个示例中的 t_order_0 到 t_order_9

CREATE TABLE `user_0`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


CREATE TABLE `user_1`  (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

接着我们不要去修改任何的业务代码，Mybatis里面写的是什么依然保持原样，即使我们的表名已经变了，我们需要做的是通过路由来修改原有的SQL，分表全部配置如下：

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 不用我多说了吧
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

    rules:
      sharding:
        tables:
          #这里填写表名称，程序中对这张表的所有操作，都会采用下面的路由方案
          #比如我们上面Mybatis就是对test表进行操作，所以会走下面的路由方案
          user:
            #这里填写实际的路由节点，比如现在我们要分两个库，那么就可以把两个库都写上，以及对应的表
            #也可以使用表达式，比如下面的可以简写为 db$->{0..1}.test
            actual-data-nodes: db0.user_0,db0.user_1
            #这里是分库策略配置
            table-strategy:
              #基本都跟之前是一样的
              standard:
                sharding-column: id
                sharding-algorithm-name: my-alg
        sharding-algorithms:
          my-alg:
            #这里我们演示一下INLINE方式，我们可以自行编写表达式来决定
            type: INLINE
            props:
              #比如我们还是希望进行模2计算得到数据该去的表
              #只需要给一个最终的表名称就行了test_，后面的数字是表达式取模算出的
              #实际上这样写和MOD模式一模一样
              algorithm-expression: user_$->{id % 2}
              #没错，查询也会根据分片策略来进行，但是如果我们使用的是范围查询，那么依然会进行全量查询
              allow-range-query-with-inline-sharding: false
    props:
      #开启日志，一会方便我们观察
      sql-show: true

现在我们来测试一下，看看会不会按照我们的策略进行分表插入：

可以看到，根据我们的算法，原本的逻辑表被修改为了最终进行分表计算后的结果，我们来查看一下数据库：

插入我们了解完毕了，我们来看看查询呢：

可以看到，根据我们配置的策略，查询也会自动选择对应的表进行

那么如果是范围查询呢？

@Select("select * from user where id between #{start} and #{end}")
List getSectionUser(@Param("start") int start,@Param("end") int end);

我们来看看执行结果会怎么样：

可以看到INLINE算法默认是不支持进行全量查询的，我们得将上面的配置项改成true：

allow-range-query-with-inline-sharding: true

可以看到，最终出来的SQL语句是直接对两个表都进行查询，然后求出一个并集出来作为最后的结果。

分布式序列算法(雪花算法)

前面我们讲解了如何进行分库分表，接着我们来看看分布式序列算法。

在复杂分布式系统中，特别是微服构架中，往往需要对大量的数据和消息进行唯一标识。随着系统的复杂，数据的增多，分库分表成为了常见的方案，对数据分库分表后需要有一个唯一ID来标识一条数据或消息（如订单号、交易流水、事件编号等），此时一个能够生成全局唯一ID的系统是非常必要的。

比如我们之前创建过学生信息表、图书借阅表、图书管理表，所有的信息都会有一个ID作为主键，并且这个ID有以下要求：

为了区别于其他的数据，这个ID必须是全局唯一的。
主键应该尽可能的保持有序，这样会大大提升索引的查询效率。

那么我们在分布式系统下，如何保证ID的生成满足上面的需求呢？

1. 使用UUID

UUID是由一组32位数的16进制数字随机构成的，我们可以直接使用JDK为我们提供的UUID类来创建：

public static void main(String[] args) {
    String uuid = UUID.randomUUID().toString();
    System.out.println(uuid);
}

结果为73d5219b-dc0f-4282-ac6e-8df17bcd5860，生成速度非常快，可以看到确实是能够保证唯一性，因为每次都不一样，而且这么长一串那重复的概率真的是小的可怜。

但是它并不满足我们上面的第二个要求，也就是说我们需要尽可能的保证有序，而这里我们得到的都是一些无序的ID。

2.雪花算法(Snowflake)

我们来看雪花算法，它会生成一个一个64bit大小的整型的ID，int肯定是装不下了。

可以看到它主要是三个部分组成，时间+工作机器ID+序列号，时间以毫秒为单位，41个bit位能表示约70年的时间。

时间纪元从2016年11月1日零点开始，可以使用到2086年，工作机器ID其实就是节点ID，每个节点的ID都不相同，那么就可以区分出来，10个bit位可以表示最多1024个节点，最后12位就是每个节点下的序列号，因此每台机器每毫秒就可以有4096个系列号。

这样，它就兼具了上面所说的唯一性和有序性了，但是依然是有缺点的，第一个是时间问题，如果机器时间出现倒退，那么就会导致生成重复的ID，并且节点容量只有1024个，如果是超大规模集群，也是存在隐患的。

ShardingJDBC支持以上两种算法为我们自动生成ID，文档：https://shardingsphere.apache.org/document/5.1.0/cn/user-manual/shardingsphere-jdbc/builtin-algorithm/keygen/

这里，我们就是要ShardingJDBC来让我们的主键ID以雪花算法进行生成：

接着我们需要修改一下Mybatis的插入语句，因为现在id是由ShardingJDBC自动生成，我们就不需要自己加了：

@Insert("insert into user(name, age) values(#{name}, #{age})")
int addUser(User user);

基于分表的雪花算法配置如下：

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 不用我多说了吧
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

    rules:
      sharding:
        tables:
          #这里填写表名称，程序中对这张表的所有操作，都会采用下面的路由方案
          #比如我们上面Mybatis就是对test表进行操作，所以会走下面的路由方案
          user:
            #这里填写实际的路由节点，比如现在我们要分两个库，那么就可以把两个库都写上，以及对应的表
            #也可以使用表达式，比如下面的可以简写为 db$->{0..1}.test
            actual-data-nodes: db0.user_0,db0.user_1

            #这里是分库策略配置
            table-strategy:
              #基本都跟之前是一样的
              standard:
                sharding-column: id
                sharding-algorithm-name: my-alg

            #这里使用自定义的主键生成策略
            key-generate-strategy:
              column: id
              key-generator-name: my-gen

        #这里写我们自定义的主键生成算法
        key-generators:
          my-gen:
            #使用雪花算法
            type: SNOWFLAKE
            props:
              #工作机器ID，保证唯一就行
              worker-id: 8848

        sharding-algorithms:
          my-alg:
            #这里我们演示一下INLINE方式，我们可以自行编写表达式来决定
            type: INLINE
            props:
              #比如我们还是希望进行模2计算得到数据该去的表
              #只需要给一个最终的表名称就行了test_，后面的数字是表达式取模算出的
              #实际上这样写和MOD模式一模一样
              algorithm-expression: user_$->{id % 2}
              #没错，查询也会根据分片策略来进行，但是如果我们使用的是范围查询，那么依然会进行全量查询
              #这个我们后面紧接着会讲，这里先写上吧
              allow-range-query-with-inline-sharding: true
    props:
      #开启日志，一会方便我们观察
      sql-show: true

编写测试用例进行测试：

在插入的时候，将我们的SQL语句自行添加了一个id字段，并且使用的是雪花算法生成的值，并且也是根据我们的分表策略在进行插入操作。

查看结果：

基于分库的雪花算法配置如下：

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 不用我多说了吧
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

    rules:
      sharding:
        tables:
          #这里填写表名称，程序中对这张表的所有操作，都会采用下面的路由方案
          #比如我们上面Mybatis就是对test表进行操作，所以会走下面的路由方案
          user:
            #这里填写实际的路由节点，比如现在我们要分两个库，那么就可以把两个库都写上，以及对应的表
            #也可以使用表达式，比如下面的可以简写为 db$->{0..1}.test
            actual-data-nodes: db0.user,db1.user

            #这里是分库策略配置
            database-strategy:
              #这里选择标准策略，也可以配置复杂策略，基于多个键进行分片
              standard:
                #参与分片运算的字段，下面的算法会根据这里提供的字段进行运算
                sharding-column: id
                #这里填写我们下面自定义的算法名称
                sharding-algorithm-name: my-alg

            #这里使用自定义的主键生成策略
            key-generate-strategy:
              column: id
              key-generator-name: my-gen

        #这里写我们自定义的主键生成算法
        key-generators:
          my-gen:
            #使用雪花算法
            type: SNOWFLAKE
            props:
              #工作机器ID，保证唯一就行
              worker-id: 8848

        #分片算法
        sharding-algorithms:
          my-alg:
            type: MOD
            props:
              sharding-count: 2
    props:
      #开启日志，一会方便我们观察
      sql-show: true

测试结果如下：

插入的数据结果如下：

读写分离

最后我们来看看读写分离，我们之前实现了MySQL的主从：，搭建步骤就不再赘述了，那么我们就可以将主库作为读，从库作为写：

然后我们就可以配置ShardingJDBC了，全部配置如下：

spring:
  shardingsphere:
    datasource:
      # 有几个数据就配几个，这里是名称，按照下面的格式，名称+数字的形式
      names: db0,db1
      # 为每个数据源单独进行配置
      db0:
        # 数据源实现类，这里使用默认的HikariDataSource
        type: com.zaxxer.hikari.HikariDataSource
        # 数据库驱动
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 不用我多说了吧
        jdbc-url: jdbc:mysql://182.92.209.212:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://182.92.97.73:3306/sharding?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: xxx

    rules:
      #配置读写分离
      readwrite-splitting:
        data-sources:
          #名称随便写
          user-db:
            #使用静态类型，动态Dynamic类型可以自动发现auto-aware-data-source-name，这里不演示
            type: Static
            props:
              #配置写库（只能一个）
              write-data-source-name: db0
              #配置从库（多个，逗号隔开）
              read-data-source-names: db1
              #负载均衡策略，可以自定义
              load-balancer-name: my-load
        load-balancers:
          #自定义的负载均衡策略（轮询）
          my-load:
            type: ROUND_ROBIN

    props:
      #开启日志，一会方便我们观察
      sql-show: true

mapper接口：

 @Select("select * from user where id = #{id}")
 User getUserById(Long id);
 @Insert("insert into user(id,name, age) values(#{id},#{name}, #{age})")

写入数据测试如下：

可以看到都是从主库写入数据

读取数据测试如下：

可以看到是从从库进行读取数据的

数据库 springboot ShardingJDBC

ShardingSphere-JDBC进行分库分表

一、前言：分库分表

二、ShardingJDBC介绍

三、使用Sharding-JDBC进行实战

环境准备

使用SpringBoot项目进行分库

使用SpringBoot项目进行分表

分布式序列算法(雪花算法)

读写分离

相关

微信小程序+SpringBoot(Shiro)登录后，调用接口还是跑到了login。

eclipse搭建springboot开发环境

springboot学习之路31（整合minio服务）

Springboot配置tk-mybatis

springboot配置信息

mysql数据库死锁的产生原因及解决办法

springboot配置国际化功能

SpringBoot AOP+自定义注解实现日志功能

【数据库】快速安装 SQL Server Express

关于SQL递归查询在不同数据库中的实现方法

GraphX 在图数据库 Nebula Graph 的图计算实践

主流开源分布式图数据库 Benchmark

标签