表关联 #

DBPlusEngine 表关联目前支持多个分片表设置为表组场景的关联查询，以及广播表和分片表、广播表和单表的关联查询，不支持未配置表组的多个分片表关联查询，以及分片表和单表，单表和单表的跨库关联查询。

DBPlusEngine 在处理表关联查询时，尽可能地将关联查询下推至底层数据库执行，充分使用原生数据库的计算能力，DBPlusEngine 会对底层数据库返回的结果集进行归并，使得最终的结果满足逻辑 SQL 的关联查询语义。

分片表关联 #

说明 #

使用表组进行多表关联查询时，通过使用分片键进行关联，可以避免笛卡尔积关联，从而提升查询效率；
对于分片表和其他表关联的场景，可以将大表根据业务场景进行分片，需要关联的小表可以采用广播方式下发到各个数据源，当分片表和广播表需要关联查询时，选择当前数据源上的广播表关联即可；
对于业务场景中单独使用，不需要和分片表关联的小表，可以将它们存储在指定的数据源上，以单表的形式进行更新和查询。

示例 #

以如下的 SQL 为例，我们来对比下使用表组和未使用表组的关联查询的差异。

SELECT i.* FROM t_order o JOIN t_order_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_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);

在配置表组，并且使用 order_id 进行关联后，路由的 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);

可以看出配置表组之后，查询的效率大大提升。

单表关联 #

说明 #

目前支持同逻辑库下同数据库实例中不同数据库的关联查询

示例 #

逻辑库配置

databaseName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 2}

  keyGenerators:
    snowflake:
      type: SNOWFLAKE

- !SINGLE
  tables:
    - "*.*"

- !BROADCAST
  tables:
    - t_config

单表&单表跨库 JOIN

mysql> preview select * from t_single_0 s0 inner join t_single_1 s1 on s0.order_id = s1.order_id;
+------------------+-------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                            |
+------------------+-------------------------------------------------------------------------------------------------------+
| ds_0             | select * from demo_ds_0.t_single_0 s0 inner join demo_ds_1.t_single_1 s1 on s0.order_id = s1.order_id |
+------------------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

分片表&单表跨库 JOIN

mysql> preview select * from t_order o inner join t_single_0 s0 on o.order_id = s0.order_id;
+------------------+------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                               |
+------------------+------------------------------------------------------------------------------------------+
| ds_0             | select * from t_order_0 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_0             | select * from t_order_1 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_1             | select * from t_order_0 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_1             | select * from t_order_1 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
+------------------+------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

分片表&单表&单表跨库 JOIN

mysql> preview select * from t_order o inner join t_single_0 s0 on o.order_id = s0.order_id;
+------------------+------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                               |
+------------------+------------------------------------------------------------------------------------------+
| ds_0             | select * from t_order_0 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_0             | select * from t_order_1 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_1             | select * from t_order_0 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
| ds_1             | select * from t_order_1 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id |
+------------------+------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

分片表&广播表&单表跨库

mysql> preview select * from t_order o inner join t_single_0 s0 on o.order_id = s0.order_id inner join t_config c on s0.order_id = c.config_id where o.order_id = 1 and o.user_id = 1;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                                                                         |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_1             | select * from t_order_1 o inner join demo_ds_0.t_single_0 s0 on o.order_id = s0.order_id inner join t_config c on s0.order_id = c.config_id where o.order_id = 1 and o.user_id = 1 |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)