Sharding-JDBC分库分表

本章主要介绍Sharding分库分表实战，结合实际例子体验分库分表的益处和可能产生的问题。关键词：分库分表、shardingsphere、Sharding-JDBC

官网地址：https://shardingsphere.apache.org/

文章导读

背景

  上一篇《分库分表设计及常见问题》介绍了分库分表使用过程中的相关问题。不少读者咨询，有没有实战案例，能更好的体验一下。不多说，安排！

市面上，常用的分库分表中间技术：ShardingSphere、MyCat等。这章我们主要介绍ShardingSphere。

ShardingSphere生态圈

1、什么是ShardingSphere？

  Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈。旨在实现以下功能：

• 提供标准化的数据分片、分布式事务和数据库治理功能。
• 旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。
• Apache ShardingSphere 5.x 版本开始致力于可插拔架构。目前，数据分片、读写分离、多数据副本、数据加密、影子库压测等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持，均通过插件的方式织入项目。
• ShardingSphere生态包含：sharding-JDCB、shardingproxy、sharding-sidecar以及他们的配合使用。三者对比如下：

2、设计理念

ShardingSphere采用数据库+ 设计理念，致力于构建数据库上层的标准和生态，补充数据库在生态中缺失的能力。其实就是对数据库功能的增强。

图片来源于官网

3、核心概念

1、逻辑表

水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是t_orders_0到t_orders_9，他们的逻辑表名为t_orders。

2、真实表

在分片的数据库中真实存在的物理表。即上个示例中的t_orders_0到t_orders_9。

3、数据节点

数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_orders_0。

4、绑定表

指分片规则一致的主表和子表。例如：t_orders表和t_orders_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为：

        
          
SELECT i.* FROM t_orders o JOIN t_orders_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  

      

在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：

        
          
SELECT i.* FROM t_orders_0 o JOIN t_orders_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  
  
SELECT i.* FROM t_orders_0 o JOIN t_orders_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  
  
SELECT i.* FROM t_orders_1 o JOIN t_orders_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  
  
SELECT i.* FROM t_orders_1 o JOIN t_orders_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  

      

在配置绑定表关系后，路由的SQL应该为2条：

        
          
SELECT i.* FROM t_orders_0 o JOIN t_orders_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  
  
SELECT i.* FROM t_orders_1 o JOIN t_orders_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);  

      

其中t_orders在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。 所有路由计算将会只使用主表的策略，那么t_orders_item表的分片计算将会使用t_orders的条件。故绑定表之间的分区键要完全相同。

5、广播表

指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。

6、分片键

用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

7、分片算法

通过分片算法将数据分片，支持通过=、>=、<=、>、<、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。

目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。

• 精确分片算法

对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。

• 范围分片算法

对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。

• 复合分片算法

对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。

• Hint分片算法

对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。

8、分片策略

包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。

• 标准分片策略

对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。

• 复合分片策略

对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，提供最大的灵活度。

• 行表达式分片策略

对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。

• Hint分片策略

对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。

• 不分片策略

对应NoneShardingStrategy。不分片的策略。

Sharding-JDBC分库分表实战

下面我们进入正题，简单实现一个分库分表的案例。

需求分析：

设计一张订单表t_orders, 进行分库分表测试。

前期准备：

1. 创建订单表,t_orders_1和t_orders_2 同理。

        
          
-- ----------------------------  
-- Table structure for t\_orders\_1  
-- ----------------------------  
DROP TABLE IF EXISTS `t\_orders\_1`;  
CREATE TABLE `t\_orders\_1`  (  
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',  
  `order\_id` bigint NULL DEFAULT NULL COMMENT '订单编码',  
  `customer\_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '客户ID',  
  `amount` decimal(10, 2) NOT NULL COMMENT '订单金额',  
  `status` tinyint NOT NULL COMMENT '订单状态码',  
  `create\_date` datetime NOT NULL COMMENT '创建日期',  
  PRIMARY KEY (`id`) USING BTREE  
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;  
  

      

2. 环境配置

• 导入依赖

        
          
<dependency>  
    <groupId>org.apache.shardingsphere</groupId>  
    <artifactId>sharding-jdbc-core</artifactId>  
    <version>4.1.1</version>  
</dependency>  
  
<dependency>  
    <groupId>mysql</groupId>  
    <artifactId>mysql-connector-java</artifactId>  
    <version>8.0.29</version>  
</dependency>  

      

• 创建实体类

        
          
@Data  
@AllArgsConstructor  
@NoArgsConstructor  
@ToString  
public class Orders {  
    private Integer id;  
    private Long orderId;  
    private Integer customerId;  
    private Double amount;  
    private Integer status;  
    private Date createDate;  
  
}  

      

• 创建mapper接口

        
          
@Mapper  
@Repository  
public interface OrdersMapper {  
    @Insert("insert into t\_orders(id,order\_id,customer\_id,amount,status,create\_date) values(#{id},#{orderId},#{customerId},#{amount},#{status},#{createDate})")  
    public void insert(Orders orders);  
}  

      

前期工作就绪，准备开始分库分表

水平分表

1、在数据库中创建2张表，t_orders_1和t_orders_2

2、分片规则：如果订单编号是偶数添加到t_orders_1,如果是奇数添加到t_orders_2

        
          
# MyBatis配置  
mybatis:  
  type-aliases-package: com.example.demo.entity  
  #mapper-locations: classpath*:mappers/*.xml  
  
#配置数据源  
spring:  
  shardingsphere:  
    datasource:  
      names: ds1  
      ds1:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://1.92.XXX.XXX:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
    sharding:  
      tables:  
        #逻辑表名称  
        t\_orders:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds1.t\_orders\_$->{1..2}  
          #主键生成策略，目前库里id用的自增  
#          key-generator:  
#            column: id  
#            type: SNOWFLAKE  
          table-strategy:  
            inline:  
              #分片策略  
              sharding-column: order\_id  
              algorithm-expression: t\_orders\_${order\_id%2+1}  
  
    #日志显示具体的SQL  
    props:  
      sql:  
        show: true  

      

效果测试：

• 向t_orders_1,2 中导入测试数据

        
          
  @Test  
  public void addOrders() {  
      for (int i = 1; i <= 10; i++) {  
          Orders orders = new Orders();  
          //采用雪花算法生成16位的编号  
          orders.setOrderId(SnowFlakeUtil.getDefaultSnowFlakeId());  
          orders.setCustomerId(i);  
          orders.setAmount(1000.0 * i);  
          orders.setStatus(1);  
          orders.setCreateDate(new Date());  
          ordersMapper.insert(orders);  
      }  
  }  

      

效果演示：

显然结果符合预期。

注意区分 逻辑主键：id,业务主键：order_id。文中用order_id作分片, 这个通常使用UUID或者雪花算法实现。

本文作者故意设置id为数据库自增。如果用id做分片键，可能会抛出异常：Sharding value must implements Comparable.

水平分库

1、在不同的数据节点Node1,Node2上创建不同名称的数据库：mydb,mydb2

2、在两个数据库上创建相同的表t_orders_1,t_orders_2

3、分片规则，按照customer_id的奇偶性来进行分库，然后按照id的奇偶性进行分表

整体思路：

4、修改配置文件

        
          
# MyBatis配置  
mybatis:  
  type-aliases-package: com.example.demo.entity  
  #mapper-locations: classpath*:mappers/*.xml  
  
#配置数据源  
spring:  
  shardingsphere:  
    datasource:  
      names: ds1,ds2  
      ds1:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
      ds2:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://192.168.162.128:3306/mydb2?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
    sharding:  
      tables:  
        #逻辑表名称  
        t\_orders:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds$->{1..2}.t\_orders\_$->{1..2}  
          #主键生成策略，目前库里id用的自增  
#          key-generator:  
#            column: id  
#            type: SNOWFLAKE  
  
          #分库策略  
          database-strategy:  
            inline:  
              sharding-column: customer\_id  
              algorithm-expression: ds$->{customer\_id%2+1}  
          #分表策略  
          table-strategy:  
            inline:  
              sharding-column: order\_id  
              algorithm-expression: t\_orders\_$->{order\_id%2+1}  
  
    #日志显示具体的SQL  
    props:  
      sql:  
        show: true  
  

      

5、重复上述步骤插入数据

6、演示效果：

由此可见，按照customer_id分库和按照order_id分表均符合预期。

垂直分库

1、在不同的数据节点Node1,Node2创建相同的库mydb

2、在Node1上创建t_orders表，在Node2上创建t_customer表

3、分片规则：将不同的表插入到不同的库中

4、编写customer类

        
          
@Data  
@AllArgsConstructor  
@NoArgsConstructor  
@ToString  
public class Customer {  
    private Long id;  
    private Long customerId;  
    private String name;  
}  

      

5、编写Mapper

        
          
@Repository  
public interface CustomerMapper {  
    @Insert("insert into t\_customer(id,customer\_id,name) values(#{id},#{customerId},#{name})")  
    public void insertCustomer(Customer customer);  
}  

      

6、修改配置文件

        
          
# MyBatis配置  
mybatis:  
  type-aliases-package: com.example.demo.entity  
  #mapper-locations: classpath*:mappers/*.xml  
  
#配置数据源  
spring:  
  shardingsphere:  
    datasource:  
      names: ds1,ds2  
      ds1:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
      ds2:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://192.168.162.128:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password:   
    sharding:  
      tables:  
        #逻辑表名称  
        t\_orders:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds1.t\_orders  
          #分表策略  
          table-strategy:  
            inline:  
              sharding-column: order\_id  
              algorithm-expression: t\_orders  
        t\_customer:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds2.t\_customer  
          #分表策略  
          table-strategy:  
            inline:  
              sharding-column: customer\_id  
              algorithm-expression: t\_customer  
  
    #日志显示具体的SQL  
    props:  
      sql:  
        show: true  
  

      

7、演示代码

        
          
  @Test  
  public void insertCustomer(){  
      for (int i = 1; i <= 10 ; i++) {  
          Customer customer = new Customer();  
          customer.setId(i);  
          customer.setCustomerId(i);  
          customer.setCustomerName("cust\_"+i);  
          customerMapper.insertCustomer(customer);  
      }  
  }  

      

8、演示效果

垂直分库在实际生产中基本按照业务划分，比较简单。

垂直分表

1、整体思路：

垂直分表的意义用于：单行的某些字段数据很大，表占用空间很大，在读写时，严重降低性能，此时需要将拆分到另外一张表，且与原表是一对一的关系，这就是垂直分表。

比如：有一张记录提单明细的表 t_order_log，由于order_log_detail字段可能是提单整体报文。我们可以将其拆分为：t_order_log_base和t_order_log_detail

2、主要配置

        
          
#配置数据源  
spring:  
  shardingsphere:  
    datasource:  
      names: ds1  
      ds1:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
    sharding:  
      tables:  
        #逻辑表名称  
        t\_order\_log\_base:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds$->{1}.t\_order\_log\_base  
          #分表策略  
          table-strategy:  
            inline:  
              sharding-column: order\_log\_id  
              algorithm-expression: t\_order\_log\_base  
        t\_order\_log\_detail:  
          #配置在那个数据库，那个表  
          actual-data-nodes: ds$->{1}.t\_order\_log\_detail  
          #分表策略  
          table-strategy:  
            inline:  
              sharding-column: order\_log\_id  
              algorithm-expression: t\_order\_log\_detail  
    #日志显示具体的SQL  
    props:  
      sql:  
        show: true  

      

3、数据库SQL

        
          
-- ----------------------------  
-- Table structure for t\_order\_log\_base  
-- ----------------------------  
DROP TABLE IF EXISTS `t\_order\_log\_base`;  
CREATE TABLE `t\_order\_log\_base`  (  
  `order\_log\_id` bigint NOT NULL COMMENT '主键',  
  `order\_trans\_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '交易流水',  
  `customer\_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '提单客户',  
  `create\_time` datetime NULL DEFAULT NULL COMMENT '提单时间',  
  PRIMARY KEY (`order\_log\_id`) USING BTREE  
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;  
  
  
-- ----------------------------  
-- Table structure for t\_order\_log\_detail  
-- ----------------------------  
DROP TABLE IF EXISTS `t\_order\_log\_detail`;  
CREATE TABLE `t\_order\_log\_detail`  (  
  `order\_log\_id` bigint NOT NULL COMMENT '主键',  
  `order\_log\_detail\_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '订单详细报文信息',  
  PRIMARY KEY (`order\_log\_id`) USING BTREE  
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;  

      

4、接口实现

        
          
@Mapper  
@Repository  
public interface OrdersLogMapper {  
  
    @Insert("insert into t\_order\_log\_base(order\_log\_id,order\_trans\_id,customer\_id,create\_time) values(#{orderLogId},#{orderTransId},#{customerId},#{createTime})")  
    public void insertOrderBase(OrderLog orderLog);  
  
  
    @Insert("insert into t\_order\_log\_detail(order\_log\_id,order\_log\_detail\_info) values(#{orderLogId},#{orderLogDetail})")  
    public void insertOrderDetail(OrderLog orderLog);  
  
}  

      

实体类

        
          
@Data  
@AllArgsConstructor  
@NoArgsConstructor  
@ToString  
public class OrderLog {  
    private Long orderLogId;  
    private String orderTransId;  
    private String customerId;  
    private String orderLogDetail;  
    private Date createTime;  
}  

      

5、效果展示：

        
          
mysql> select *  from mydb.t_order_log_base;  
+------------------+----------------------------------+-------------+---------------------+  
| order_log_id     | order_trans_id                   | customer_id | create_time         |  
+------------------+----------------------------------+-------------+---------------------+  
| 2380628989266433 | 3236e7866a504365ab313cc1135f68ef | cust_6c4afc | 2024-03-17 03:22:05 |  
+------------------+----------------------------------+-------------+---------------------+  
1 row in set (0.00 sec)  
  

      

        
          
mysql> select * from mydb.t_order_log_detail;  
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------+  
| order_log_id     | order_log_detail_info                                                                                                                   |  
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------+  
| 2380628989266433 | {"orderTransId":"3236e7866a504365ab313cc1135f68ef","createTime":1710645724667,"orderLogId":2380628989266433,"customerId":"cust_6c4afc"} |  
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)  

      

我们看下后台，显然是生效的。

小结：事实上，不使用分库分表我们也会这样做。上述例子只说明垂直分表实现。对于这类大表在拆分时候有时也会在不同的库，感兴趣的可以参考水平分表那一小节配置对应的数据源。

广播表

其实就是公共字典表，很多地方通用的参数配置。

        
          
spring:  
  shardingsphere:  
    sharding:  
     #order\_dict\_common为字典表  
      broadcast-tables: order\_dict\_common  
      tables:  
        order\_dict\_common:  
          key-generator:  
            column: id  
            type: SNOWFLAKE  

      

读写分离

关于如何配置mysql的主从复制，读写分离，我们不在赘述，但是保证数据库主从能正常同步。 整体思路：

主库（master）：1.92.80.47

从库（slave）：192.168.162.168

配置修改

        
          
spring:  
  shardingsphere:  
    datasource:  
      names: ds1,ds2  
      ds1:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://1.92.80.47:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
      ds2:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driver-class-name: com.mysql.cj.jdbc.Driver  
        url: jdbc:mysql://192.168.162.128:3306/mydb?useUnicode=true&serverTimezone=UTC  
        username: root  
        password: ******  
    masterslave:  
      name: mymaster  
      master-data-source-name: ds1  
      slave-data-source-names: ds2  

      

前提保证数据库主从正常，读者可自行建立测试用例验证，这里只给出核心配置。

最近就业形式比较困难，为了感谢各位小伙伴对苏三一直以来的支持，我特地创建了一些工作内推群， 看看能不能帮助到大家。

你可以在群里发布招聘信息，也可以内推工作，也可以在群里投递简历找工作，也可以在群里交流面试或者工作的话题。

进群方式

添加苏三的私人微信 ，备注：

所在城市