背景

数据库是任何应用系统的核心组件，它负责存储和管理数据，提供数据的增删改查等操作。随着业务的发展，数据库面临着越来越多的挑战，比如：

  • 数据量的增长导致单一数据库的性能和容量不足，无法满足高并发的访问需求。
  • 数据库的单点故障会影响整个系统的可用性，需要实现数据库的高可用和故障转移。
  • 数据库的安全性和一致性也是重要的考量因素，需要保证数据的加密和事务处理。

为了解决这些问题，我们可以使用一些开源的工具来对数据库进行优化和扩展，比如：

  • 使用ProxySQL 代理数据库，实现读写分离和负载均衡，提高数据库的性能和可用性。
  • 使用ShardingJDBC 实现分库分表，实现数据的水平拆分和扩展，突破单一数据库的限制。

本文将介绍这两个工具的原理和优势，并分享我在实践中使用它们的经验和心得。

什么是ShardingJDBC?

ShardingJDBC 是一个开源的、轻量级的、基于 JDBC 的 Java 框架。它可以在应用层对数据库进行分片操作，实现水平扩展和弹性伸缩。ShardingJDBC 的主要功能有：

  • 数据分片：根据配置的分片策略（如取模、范围、哈希等），将一个大表拆分为多个小表，并分布在不同的数据库节点上，实现数据分片。
  • 分布式事务：支持 XA 和 BASE 两种模式的分布式事务管理器，保证跨数据源的数据一致性和事务处理。
  • 数据加密：支持对敏感数据进行加密和解密操作，提高数据安全性。
  • 影子库：支持在同一个数据源中创建影子库，用于隔离测试数据和生产数据，避免数据污染和影响。

为什么使用ProxySQL和ShardingJDBC？

我们使用的MGR搭建的Mysql集群，因为是主从模式，所以可能会有主节点宕机的情况，如果主节点宕机，那么MGR就可以选举出新的主节点，但是如果我们只使用ShardingJDBC，ShardingJDBC需要我们在yml定义主节点和从节点，如果选举出了新的主节点，就需要我们手动修改配置文件，在此期间，数据库的写入功能就会出现问题。而使用了ProxySQL作为代理，当检测到主服务器故障时，ProxySQL可以自动切换到选举出新的主节点，并更新路由规则，并且ProxySQL 也可以配置读写分离的规则，根据 SQL 语句的类型（读或写），将请求路由到不同的后端服务器（主或从）。

环境准备

在上一篇文章，我们搭建了ProxySQL+MySQL MGR 实现主从同步+读写分离，但是并没有涉及到分库分表。

环境准备需要8台服务器：

Mysql集群-1：192.168.31.240（ProxySQL）、192.168.31.230（主）、192.168.31.231（从）、192.168.31.232（从）

Mysql集群-2：192.168.31.241（ProxySQL）、192.168.31.233（主）、192.168.31.234（从）、192.168.31.235（从）

两个集群都建立相同的5张表：

表的内容一定要包含id（目前演示是根据id进行分片）

具体搭建方式可以看我的上一篇文章。

大致的架构如图所示：

配置ShardingJDBC

在Spring Boot项目中引入shardingJDBC starter

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

根据id hash分库，根据id hash分表

在yml中配置数据源以及数据库分片和数据表分片规则，数据源只需要配置两个集群的ProxySQL即可，因为ShardingJDBC分库是根据数据源进行分库，分表是根据id进行hash运算，然后再拼接表名找到对应的数据表。读写分离交给ProxySQL完成。

spring:
  application:
    name: webChat
  shardingsphere:
    datasource:
      names: ds0,ds1 # 数据源名称，多个用逗号分隔
      ds0: # 数据源 0
        type: com.zaxxer.hikari.HikariDataSource # 连接池类型
        driver-class-name: com.mysql.cj.jdbc.Driver # 驱动类名
        jdbc-url: jdbc:mysql://192.168.31.240:6033/testdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai # 连接地址
        username: proxysql # 用户名
        password: proxysql@1025 # 密码
      ds1: # 数据源 1
        type: com.zaxxer.hikari.HikariDataSource # 连接池类型
        driver-class-name: com.mysql.cj.jdbc.Driver # 驱动类名
        jdbc-url: jdbc:mysql://192.168.31.241:6033/testdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai # 连接地址
        username: proxysql # 用户名
        password: proxysql@1025 # 密码
    sharding:
      tables:
        employee: # 逻辑表名
          actual-data-nodes: ds${0..1}.employee${0..4} # 实际数据节点，使用 ${} 表示范围，用 .. 分隔起止值，用 . 分隔多级范围，用 , 分隔枚举值
          database-strategy:
            inline:
              sharding-column: id # 分库键
              algorithm-expression: ds${id % 2} # 分库的内联表达式，使用取模运算符 % 实现 hash 算法
          table-strategy:
            inline:
              sharding-column: id # 分表键
              algorithm-expression: employee${id % 5} # 分表的内联表达式，使用取模运算符 % 实现 hash 算法
          key-generator:
            column: id # 主键列名
            type: SNOWFLAKE # 雪花算法类型
            props:
              worker.id: 123 # 工作节点 ID
  redis:
    host: XXXX
    password: XXXX
    port: 6379


server:
  port: 1221


logging:
  level:
    com:
      zaxxer:
        hikari:
          com.zaxxer.hikari.HikariConfig: DEBUG
    io:
      shardingsphere: DEBUG

插入1000条数据

    @Autowired
    private UserMapper userMapper;
    @Test
    void contextLoads() {
        for (int i = 0; i < 1000; i++) {
            Employee e = new Employee();
            e.setName("张三"+i);
            userMapper.insert(e);
        }
    }

发现这1000条数据分表存入了两个数据库的五张表中

根据id hash分库，根据时间分表

根据时间分表，比如以一个月分一次表， 在这里从2023-2026按月分表,创建存储过程如下，在两个主节点中分别执行以下脚本。

-- 创建一个存储过程，用于循环创建表
DELIMITER //
CREATE PROCEDURE create_tables()
BEGIN
  -- 定义变量，用于存储年份和月份
  DECLARE year INT DEFAULT 2023;
  DECLARE month INT DEFAULT 1;
  -- 定义一个标志，用于判断循环是否结束
  DECLARE done INT DEFAULT FALSE;
  -- 循环创建表，直到年份为2026，月份为12
  WHILE NOT done DO
    -- 拼接表名，格式为t_orderyyyyMM
    SET @table_name = CONCAT('t_order', year, LPAD(month, 2, '0'));
    -- 拼接创建表的SQL语句
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @table_name, '(
      id BIGINT PRIMARY KEY,
      product_name VARCHAR(255),
      price DECIMAL(10,2),
      create_time VARCHAR(255)
    )');
    -- 执行SQL语句
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- 判断是否到达循环结束的条件
    IF year = 2026 AND month = 12 THEN
      SET done = TRUE;
    ELSE
      -- 如果没有结束，就增加月份，如果月份超过12，就增加年份，重置月份为1
      SET month = month + 1;
      IF month > 12 THEN
        SET year = year + 1;
        SET month = 1;
      END IF;
    END IF;
  END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL create_tables();

-- 删除存储过程
DROP PROCEDURE create_tables();

shardingJDBC配置

spring:
  application:
    name: webChat
  shardingsphere:
    datasource:
      names: ds0,ds1 # 数据源名称，多个用逗号分隔
      ds0: # 数据源 0
        type: com.zaxxer.hikari.HikariDataSource # 连接池类型
        driver-class-name: com.mysql.cj.jdbc.Driver # 驱动类名
        jdbc-url: jdbc:mysql://192.168.31.240:6033/testdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai # 连接地址
        username: proxysql # 用户名
        password: proxysql@1025 # 密码
      ds1: # 数据源 1
        type: com.zaxxer.hikari.HikariDataSource # 连接池类型
        driver-class-name: com.mysql.cj.jdbc.Driver # 驱动类名
        jdbc-url: jdbc:mysql://192.168.31.241:6033/testdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai # 连接地址
        username: proxysql # 用户名
        password: proxysql@1025 # 密码
    sharding:
      tables:
        t_order: # 逻辑表名
          actual-data-nodes: ds${0..1}.t_order${2023..2026}${(1..12).collect{t ->t.toString().padLeft(2,'0')}} # 实际数据节点，使用 ${} 表示范围，用 .. 分隔起止值，用 . 分隔多级范围，用 , 分隔枚举值
          database-strategy:
            inline:
              sharding-column: id # 分库键
              algorithm-expression: ds${id % 2} # 分库的内联表达式，使用取模运算符 % 实现 hash 算法
          table-strategy:
            inline:
              sharding-column: create_time # 分表键
              algorithm-expression: t_order${create_time.substring(0,4)}${create_time.substring(5,7)} # 分表的内联表达式，使用取模运算符 % 实现 hash 算法
          key-generator:
            column: id # 主键列名
            type: SNOWFLAKE # 雪花算法类型
            props:
              worker.id: 123 # 工作节点 ID
  redis:
    host: XXX
    password: XXX
    port: 6379


server:
  port: 1221


logging:
  level:
    com:
      zaxxer:
        hikari:
          com.zaxxer.hikari.HikariConfig: DEBUG
    io:
      shardingsphere: DEBUG

运行测试代码，插入数据，比如今天是2023年06月，那么根据分片规则，首先会根据id找到对应的数据库，然后再根据日期存入不同的表，比如id是：1666362350293602305，那么对2取模就是1，就存放在一号数据库，然后再对日期的年月进行截取，最后拼接到表名上，结果就是：ds1.t_order202306

@Test
    void contextLoads() {
        for (int i = 0; i < 100; i++) {
            Order order = new Order();
            System.out.println(DateUtil.getDate());
            order.setPrice(BigDecimal.TEN);
            order.setProductName("商品：" + i);
            orderMapper.insert(order);
        }
    }

既然插入可以成功，那么查询肯定也能成功，查询是跟插入是使用的同样的规则。

但是在使用范围查询的时候就会抛出异常

Caused by: java.lang.IllegalStateException: Inline strategy cannot support this type 
sharding:RangeRouteValue(columnName=create_time, tableName=t_order, valueRange=(2023-06-07‥+∞))

我们所配置是用来实现根据create_time字段来分表的，但是需要注意，如果使用inline策略，那么默认是不支持范围查询的，也就是说，不能使用BETWEEN AND, >, <, >=, <=等操作符来查询数据。如果想要支持范围查询，需要使用标准策略或者复合策略，并自定义分片算法。

最后修改：2023 年 06 月 09 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏