[大数据] Sharding-JDBC主子表（绑定表）关联

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Sharding-JDBC主子表（绑定表）关联 -> 正文阅读

[大数据]Sharding-JDBC主子表（绑定表）关联

前言

主子表关联在我们的开发业务中是及其常见的，本文我们就来看下如何在分库分表的情况下，使用Sharding-JDBC来完成主子关联。

什么是绑定表？

官方文档
指分片规则一致的主表和子表。例如：t_order 表和 t_order_item 表，均按照 order_id 分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。
在不配置绑定表关系时，假设分片键 order_id 将数值 10 路由至第 0 片，将数值 11 路由至第 1 片，那么路由后的 SQL 应该为 4 条，它们呈现为笛卡尔积：

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

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

SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11); 
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);

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

上面的例子简单理解是：

一共有 2 个分片，一共是 4 张表：t_order_0、t_order_1、t_order_item_0、t_order_item_1 ，当查询的条件 order_id 在 2 个分片中的时候，会生成 4 条语句查询，因为 t_order_item 表有 4 个
如果是绑定表关系的话：t_order_item 的分片规则跟着主表的分片规则走，所以只会生成 2 条 SQL 查询

创建子表

分别在 sharding-order 和 shard-order 数据库中创建 t_order_item_1、t_order_item_2 的子表

CREATE TABLE `t_order_item_1` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL COMMENT '订单 ID',
  `product_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
  `user_id` int(11) DEFAULT NULL COMMENT '用户 ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

boot 配置编写

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3307/sharding-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8
        username: root
        password: root
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://localhost:3308/shard-order?serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=utf8
        username: root
        password: root
    sharding:
      broadcast-tables:
        - area
      # 分片表配置
      tables:
        # 逻辑表名
        # 下面的没有自动提示，可以点击配置属性，会跳转到具体的自动配置文件中
        # 然后就会看到源码对应的配置类是什么，就知道有哪些属性可以配置了
        # 比如 org.apache.shardingsphere.core.yaml.config.sharding.YamlTableRuleConfiguration
        t_order:
          # 实际节点
          actual-data-nodes: ds$->{0..1}.t_order_$->{1..2}
          # 数据库分片策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds$->{user_id % 2}
          # 表分片策略
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: t_order_$->{id % 2 + 1}
        # 订单子表的分片规则，设置为和 订单表一样的，表分片字段设置为 order_id
        t_order_item:
          # 实际节点
          actual-data-nodes: ds$->{0..1}.t_order_item_$->{1..2}
          # 数据库分片策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds$->{user_id % 2}
          # 表分片策略
          table-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: t_order_item_$->{order_id % 2 + 1}
      # 设置绑定表，左边的为主表，右边的为子表
      binding-tables:
        - t_order,t_order_item
    # 打印 sharding 的 sql 信息
    props:
      sql.show: true

功能测试

    /**
     * 绑定表的子表插入数据
     */
    @Test
    public void testBindingTable() {
        OrderItem order = new OrderItem();
        // 数据库：userId 偶数分到 sharding-order，奇数分到 shard-order
        order.setUserId(19);
        // 表：orderId 偶数分到 t_order_item_1, 奇数分到 t_order_item_2
        order.setId(1);
        order.setOrderId(1);
        order.setProductName("商品 1");
        orderItemMapper.insertSelective(order);

        // 那么这条语句期望是插入到：shard-order.t_order_item_2 中
    }

控制台的配置输出信息对比

bindingTables:
- t_order,t_order_item
broadcastTables:
- area
tables:
  t_order:
    actualDataNodes: ds$->{0..1}.t_order_$->{1..2}
    databaseStrategy:
      inline:
        algorithmExpression: ds$->{user_id % 2}
        shardingColumn: user_id
    logicTable: t_order
    tableStrategy:
      inline:
        algorithmExpression: t_order_$->{id % 2 + 1}
        shardingColumn: id
  t_order_item:
    actualDataNodes: ds$->{0..1}.t_order_item_$->{1..2}
    databaseStrategy:
      inline:
        algorithmExpression: ds$->{user_id % 2}
        shardingColumn: user_id
    logicTable: t_order_item
    tableStrategy:
      inline:
        algorithmExpression: t_order_item_$->{order_id % 2 + 1}
        shardingColumn: order_id

父子表关联查询测试


    @Autowired
    private OrderItemxMapper orderItemxMapper;

    @Test
    public void testBingdingTables() {
        List<OrderDetail> details = orderItemxMapper.selectOrder();
        System.out.println(details);
    }

控制台打印

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b1f5fcc] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@21688427] will not be managed by Spring
==>  Preparing: SELECT o.id AS orderId, o.user_id AS userId, item.id AS orderItemId, item.product_name AS productName FROM t_order AS o LEFT JOIN t_order_item AS item ON o.id = item.order_id WHERE o.id IN (2)
==> Parameters: 
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Rule Type: sharding
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Logic SQL: SELECT o.id              AS orderId,
               o.user_id         AS userId,
               item.id           AS orderItemId,
               item.product_name AS productName
        FROM t_order AS o
                 LEFT JOIN t_order_item AS item ON o.id = item.order_id
        WHERE o.id IN (2)
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : SQLStatement: SelectSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@cc91fe3, tablesContext=TablesContext(tables=[Table(name=t_order, alias=Optional.of(o)), Table(name=t_order_item, alias=Optional.of(item))], schema=Optional.absent())), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=176, distinctRow=false, projections=[ColumnProjection(owner=o, name=id, alias=Optional.of(orderId)), ColumnProjection(owner=o, name=user_id, alias=Optional.of(userId)), ColumnProjection(owner=item, name=id, alias=Optional.of(orderItemId)), ColumnProjection(owner=item, name=product_name, alias=Optional.of(productName))], columnLabels=[orderId, userId, orderItemId, productName]), groupByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.groupby.GroupByContext@481c1e92, orderByContext=org.apache.shardingsphere.sql.parser.relation.segment.select.orderby.OrderByContext@66f223fa, paginationContext=org.apache.shardingsphere.sql.parser.relation.segment.select.pagination.PaginationContext@7ac47f14, containsSubquery=false)
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT o.id              AS orderId,
               o.user_id         AS userId,
               item.id           AS orderItemId,
               item.product_name AS productName
        FROM t_order_1 AS o
                 LEFT JOIN t_order_item_1 AS item ON o.id = item.order_id
        WHERE o.id IN (2)
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT o.id              AS orderId,
               o.user_id         AS userId,
               item.id           AS orderItemId,
               item.product_name AS productName
        FROM t_order_1 AS o
                 LEFT JOIN t_order_item_2 AS item ON o.id = item.order_id
        WHERE o.id IN (2)
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT o.id              AS orderId,
               o.user_id         AS userId,
               item.id           AS orderItemId,
               item.product_name AS productName
        FROM t_order_1 AS o
                 LEFT JOIN t_order_item_1 AS item ON o.id = item.order_id
        WHERE o.id IN (2)
2022-09-23 21:12:28.422  INFO 17184 --- [           main] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT o.id              AS orderId,
               o.user_id         AS userId,
               item.id           AS orderItemId,
               item.product_name AS productName
        FROM t_order_1 AS o
                 LEFT JOIN t_order_item_2 AS item ON o.id = item.order_id
        WHERE o.id IN (2)
<==    Columns: orderId, userId, orderItemId, productName
<==        Row: 2, 19, 1, 商品 2
<==        Row: 2, 19, null, null
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b1f5fcc]
[OrderDetail(orderId=2, userId=19, orderItemId=1, productName=商品 2), OrderDetail(orderId=2, userId=19, orderItemId=null, productName=null)]