條件下推（串連條件下推至物化派生表） - 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中的，只能在派生表完成物化之後再進行過濾操作。