java联表查很慢如何优化 sharding jdbc 联表查询

转载

mob64ca1414c613 2023-12-09 14:43:20

文章标签 java联表查很慢如何优化数据库 sql java SQL 文章分类 Java 后端开发

前言

主子表关联在我们的开发业务中是及其常见的，本文我们就来看下如何在分库分表的情况下，使用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)]

t_order 和 t_order_item 都定位到了 2 ，这个是正确的

这里感谢茶佬的博客，在实战时真的一堆幺蛾子，但是都是特别低级的错误，有茶佬在旁边指导，真的非常感谢~

未完，下面将研究下读写分离~

码云传送门

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。