#Sharding-绑定表-链表查询（六）

野性酷女 2023-10-08 20:52 11阅读 0赞

之前分表查询都是基于单表查询，但是在实际业务中通常需要链表才能查询到相应的结果。

例如：order表和 order\_detail表，均按照 order\_id分片，则此两张表一对一关系。

#### 新增表 ####

CREATE TABLE `t_order_detail_1` (
      `id` bigint NOT NULL,
      `business_name` varchar(255) DEFAULT NULL,
      `count` int DEFAULT NULL,
      `order_id` bigint DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
    
    CREATE TABLE `t_order_detail_2` (
      `id` bigint NOT NULL,
      `business_name` varchar(255) DEFAULT NULL,
      `count` int DEFAULT NULL,
      `order_id` bigint DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
    
    CREATE TABLE `t_order_1` (
      `order_id` bigint NOT NULL COMMENT '主键',
      `order_name` varchar(50) DEFAULT NULL COMMENT '订单名称',
      `order_type` bigint DEFAULT NULL COMMENT '订单类型',
      `order_desc` varchar(200) DEFAULT NULL COMMENT '订单详情',
      `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
      `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
    
    CREATE TABLE `t_order_1` (
      `order_id` bigint NOT NULL COMMENT '主键',
      `order_name` varchar(50) DEFAULT NULL COMMENT '订单名称',
      `order_type` bigint DEFAULT NULL COMMENT '订单类型',
      `order_desc` varchar(200) DEFAULT NULL COMMENT '订单详情',
      `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
      `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`order_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

#### 关联查询举例 ####

绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。

举例说明，测试类使用addUserAndOrder添加数据

/**
         * 模拟数据
         */
        @Test
        public void addUserAndOrder() {
            List<String> orderNameList = Arrays.asList("华为手机", "零食", "苹果手机", "台灯");
            List<String> businessNameList = Arrays.asList("天猫", "京东"); // 商家
            for (int i = 0; i < 10; i++) {
                Order order = new Order();
                int productInt = i % 2;// 模拟商品
                int nowUserInt = i % 2;// 模拟用户Id
                order.setOrderName(orderNameList.get(productInt));
                order.setCreateUserId(Long.parseLong(String.valueOf(nowUserInt)));
                orderMapper.insert(order);
                // 明细表
                OrderDetail orderDetail = new OrderDetail();
                orderDetail.setBusinessName(businessNameList.get(productInt));
                orderDetail.setCount(i);
                orderDetail.setOrderId(order.getOrderId());
                orderDetailMapper.insert(orderDetail);
            }
        }
    
        /**
         * 查询用户订单信息
         */
        @Test
        public void queryUserOderListByUserIdList() {
            List<HashMap<String, Object>> orderList = orderMapper.selectOrderByOrderId(1637428102410088450L);
            orderList.forEach(o -> System.out.println(o));
        }

使用queryUserOderListByUserIdList查询数据，逻辑SQL 为：

select o.*,d.* from t_order o LEFT JOIN t_order_detail d on o.order_id = d.order_id WHERE o.order_id = ?

在不配置绑定表关系时，那么路由后的 SQL 应该为 4 条，它们呈现为笛卡尔积：

select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_1 d on o.order_id = d.order_id WHERE o.order_id = ?
    
    select o.*,d.* from t_order_2 o LEFT JOIN t_order_detail_2 d on o.order_id = d.order_id WHERE o.order_id = ?
    
    select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_2 d on o.order_id = d.order_id WHERE o.order_id = ?
    
    select o.*,d.* from t_order_2 o LEFT JOIN t_order_detail_1 d on o.order_id = d.order_id WHERE o.order_id = ?

可以发现出现了笛卡尔积，全表查询

2023-03-19 19:55:28.090  INFO 5232 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_2 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1]
    2023-03-19 19:55:28.090  INFO 5232 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_1 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1]
    2023-03-19 19:55:28.091  INFO 5232 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_2 o LEFT JOIN t_order_detail_2 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1]
    2023-03-19 19:55:28.091  INFO 5232 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_2 o LEFT JOIN t_order_detail_1 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1]
    {order_id=1, business_name=京东, create_time=2023-03-20 03:53:34.0, count=5, id=1637422055792431107, order_id=1637422055792431106, order_name=零食}
    {order_id=1, business_name=京东, create_time=2023-03-20 03:53:34.0, count=9, id=1637422055922454531, order_id=1637422055922454530, order_name=零食}
    {order_id=1, create_time=2023-03-20 03:53:34.0, order_id=1637422055792431106, order_name=零食}
    {order_id=1, create_time=2023-03-20 03:53:34.0, order_id=1637422055922454530, order_name=零食}
    {order_id=1, create_time=2023-03-20 03:53:34.0, order_id=1637422055658213377, order_name=零食}
    {order_id=1, create_time=2023-03-20 03:53:34.0, order_id=1637422055725322243, order_name=零食}
    {order_id=1, create_time=2023-03-20 03:53:34.0, order_id=1637422055859539971, order_name=零食}
    {order_id=1, business_name=京东, create_time=2023-03-20 03:53:34.0, count=1, id=1637422055658213378, order_id=1637422055658213377, order_name=零食}
    {order_id=1, business_name=京东, create_time=2023-03-20 03:53:34.0, count=3, id=1637422055725322244, order_id=1637422055725322243, order_name=零食}
    {order_id=1, business_name=京东, create_time=2023-03-20 03:53:34.0, count=7, id=1637422055859539972, order_id=1637422055859539971, order_name=零食}

#### yam配置文件 ####

此时应该配置binding-tables 参数是表集合

spring.shardingsphere.sharding.binding-tables=

**注意！**

**其中要注意分片键 column: order\_id 和 分片列sharding-column: order\_id，如果这里指定错误！得到的效果会错误order表主键是order\_id ，order\_detail表主键是id，order\_detail中有order\_id和order表关联，所以配置order\_detail表时候应该指定分片键是order\_id**

#  单库分表 配置
    spring:
      shardingsphere:
        datasource:
          # 配置数据库名称 相当于给数据源取别名（可以配置多个库，以逗号隔开）
          names: m1
          # 配置具体数据库连接信息
          m1:
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.cj.jdbc.Driver
            # 配置 数据库 test
            url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
            username: root
            password: root
        # 分片策略
        sharding:
          # 配置不同表的 分片策略
          tables:
            # 配置 具体的 逻辑表的 分片策略
            t_order:
              # t_order 订单表的 主键规则
              keyGenerator:
                # 主键列
                column: order_id
                # 主键生成规则 （SNOWFLAKE 雪花算法 生成分布式唯一ID）
                type: SNOWFLAKE
                # 未知作用
              #            props:
              #              worker:
              #                id: 1
              # 配置 t_order 订单表的 具体数据库物理表的映射关系 表达式
              actualDataNodes: m1.t_order_$->{1..2}
              # 表策略
              tableStrategy:
                inline:
                  # 分片列
                  sharding-column: order_id
                  # 分片规则 表达式（映射到具体的物理表 ）
                  algorithm-expression: t_order_$->{order_id % 2 + 1}
            # 配置 具体的 逻辑表的 分片策略
            t_order_detail:
              # t_order 订单表的 主键规则
              keyGenerator:
                # 主键列
                column: id
                # 主键生成规则 （SNOWFLAKE 雪花算法 生成分布式唯一ID）
                type: SNOWFLAKE
                # 未知作用
              #            props:
              #              worker:
              #                id: 1
              # 配置 t_order 订单表的 具体数据库物理表的映射关系 表达式
              actualDataNodes: m1.t_order_detail_$->{1..2}
              # 表策略
              tableStrategy:
                inline:
                  # 分片列
                  sharding-column: order_id
                  # 分片规则 表达式（映射到具体的物理表 ）
                  algorithm-expression: t_order_detail_$->{order_id % 2 + 1}
        #      bindingTables:
        #        t_order,t_order_detail
        # 配置是否打印SQL
        props:
          sql.show: true
    
      # 解决一个bean映射到多张表问题
      main:
        allow-bean-definition-overriding: true

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

2023-03-19 20:37:56.136  INFO 9752 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_2 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1637428102410088450]
    2023-03-19 20:37:56.137  INFO 9752 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: select o.*,d.* from t_order_1 o LEFT JOIN t_order_detail_1 d on o.order_id = d.order_id WHERE o.order_id = ? ::: [1637428102410088450]
    {create_user_id=0, create_time=2023-03-20 04:17:36.0, order_id=1637428102410088450, order_name=华为手机}
    {create_user_id=0, business_name=天猫, create_time=2023-03-20 04:17:36.0, count=2, id=1637428102410088451, order_id=1637428102410088450, order_name=华为手机}