条件下推（连接条件下推至物化派生表）_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB支持条件下推（连接条件下推至物化派生表）功能，可以将符合下推规则的条件表达式，从JOIN语句中的ON子句中转移至物化派生表（Derived Tables）中，从而有效地将数据过滤的过程前移，确保谓词条件尽早地应用于数据源，进而减少后续处理阶段的数据量，提高查询性能。

说明

在本文中介绍的连接条件下推至物化派生表功能，不同于连接条件下推（下称JPP）功能，且两者适用的场景也不同。本文中的下推技术基于WHERE谓词的等值关系，能够推导出仅依附于物化派生表的单表条件，并将这些条件下推至物化派生表。这一策略属于启发式规则，一般情况下可以提升SQL的执行性能。相较之下，JPP机制针对的是包含多表条件的ON谓词，这类条件在下推时需转换为相关子查询，且该转换是基于代价估计的，因此并不一定能够保证性能的正向提升。更多信息，请参见连接条件下推。

版本限制

适用的数据库引擎版本如下，如何查看内核版本，请参见查询版本号。

MySQL 8.0.1，且小版本需为8.0.1.1.44及以上版本。
MySQL 8.0.2，且小版本需为8.0.2.2.25及以上版本。

使用限制

如果物化派生表（Derived Tables）有LIMIT限制，则禁止下推，例如：

SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c LIMIT 2) dt ON t1.a < dt.a AND t1.a = 1;

外层WHERE条件表达式的列或者映射到物化派生表对应的列，存在以下情况则不支持下推：
- 该列引用了子查询或者是非确定性的（相同的输入条件下，可能会产生不同的结果），例如：
```
SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c) dt ON t1.a < dt.a AND t1.a = RAND();
```
- 该列是存储过程或者存储函数，例如：
```
CREATE FUNCTION f1() RETURNS INT
BEGIN
...
END;

SELECT * FROM t1 LEFT JOIN (SELECT c, MAX(d) FROM t2 GROUP BY c) dt ON t1.a < dt.a AND t1.a = f1();
```

使用方法

准备工作

在使用该条件下推功能之前，请根据您的业务需求调整loose_join_cond_push_into_derived_mode参数。具体操作，请参见设置集群参数和节点参数。

参数说明如下：

参数名称

级别

说明

loose_join_cond_push_into_derived_mode

Global

从连接条件到Derived Tables的条件下推功能的控制开关。取值范围如下：

REPLICA_ON（默认值）：仅在只读节点开启该条件下推功能。
ON：开启该条件下推功能。
OFF：关闭该条件下推功能。

示例

说明

如果条件表达式中的所有列（或对应的等价列）都来自物化的派生表（Derived Tables），则该条件可以下推至物化派生表。

执行如下代码创建示例表：

CREATE TABLE t1 (a INT, b INT, c INT, d INT);
CREATE TABLE t2 (e INT, f INT, g INT);

设置loose_join_cond_push_into_derived_mode为ON后，执行如下代码：

EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) dt ON dt.x > t1.a WHERE t1.a = 1;

执行结果如下：

-> Left hash join (no condition)
    -> Filter: (t1.a = 1)  (cost=0.55 rows=1)
        -> Table scan on t1  (cost=0.55 rows=3)
    -> Hash
        -> Table scan on dt
            -> Materialize
                -> Filter: (t2.x > 1)  (cost=0.45 rows=1)
                    -> Table scan on t2  (cost=0.45 rows=2)

说明

在查询计划中，WHERE条件中的等值信息t1.a = 1成功传递至JOIN谓词上，推导出dt.x > 1，从而有效地下推至派生表dt中，使派生表dt中的数据量便进一步减少，从而提高了查询性能。

设置loose_join_cond_push_into_derived_mode为OFF后，执行如下代码：

EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN (SELECT * FROM t2) dt ON dt.x > t1.a WHERE t1.a=1;

执行结果如下：

-> Left hash join (no condition)
    -> Filter: (t1.a = 1)  (cost=0.55 rows=1)
        -> Table scan on t1  (cost=0.55 rows=3)
    -> Hash
        -> Filter: (dt.x > 1)
            -> Table scan on dt
                -> Materialize
                    -> Table scan on t2  (cost=0.45 rows=2)

说明

loose_join_cond_push_into_derived_mode参数设置为OFF后，dt.x > 1没有下推到Derived Tables中的，只能在派生表完成物化之后再进行过滤操作。