WHERE句をINサブクエリにプッシュダウンする - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB では、WHERE 句を IN サブクエリにプッシュダウンして、複雑なクエリを最適化し、特にネストされたサブクエリの場合のクエリのパフォーマンスを向上させることができます。

前提条件

この機能は、次のデータベースエンジンを使用するクラスタでサポートされています。データベースエンジンのバージョンのクエリ方法の詳細については、「エンジンのバージョンをクエリする」をご参照ください。

MySQL 8.0.1 リビジョンバージョン 8.0.1.1.42 以降。
MySQL 8.0.2 リビジョンバージョン 8.0.2.19 以降。

シナリオ

SQL クエリでは、GROUP BY で使用されるサブクエリは、多くの場合マテリアライズされます。サブクエリが IN サブクエリである場合を考えてみましょう。例：(FIELD[1],...) IN (SELECT col[1], ... FROM ... GROUP BY ...) で、これはメインクエリの WHERE 条件内の AND 句の一部を形成します。例：COND(FIELD[1])... AND (FIELD[1],...) IN (SELECT col[1], ... FROM ... GROUP BY ...)。結果セットの各行は、FIELD[1] = col[1] を満たす必要があります。その結果、条件 COND(FIELD[1]) をサブクエリにプッシュダウンできます。

制限事項

サブクエリに句がある場合、条件のプッシュダウンはサポートされていません。
```
SELECT * FROM t WHERE a > 1 AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c LIMIT 2);
```
外部条件の列とマテリアライズされたテーブルの列が特定の基準を満たしていない場合、たとえば、列がサブクエリを参照している場合、非決定的である場合、または列がストアドプロシージャまたは関数の一部である場合、条件のプッシュダウンはサポートされません。
- 列はサブクエリを参照しているか、非決定的である可能性があり、同じ入力条件で異なる結果が生成される可能性があります。例：
```
SELECT * FROM t WHERE a > 5*RAND() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```
- 列は、ストアドプロシージャまたは関数に関連付けられている可能性があります。ただし、複雑なストアド関数は繰り返し実行してもメリットがない可能性があるため、プッシュダウンの対象とは見なされません。例：
```
CREATE FUNCTION f1() RETURNS INT
BEGIN
... /* ... */
END;

SELECT * FROM t WHERE a > f1() AND (a, b) IN (SELECT c, MAX(d) FROM t2 GROUP BY c);
```

使用方法

準備

条件プッシュダウンを利用するには、要件に基づいて loose_subquery_cond_pushdown_mode パラメーターを設定します。詳細については、「クラスタとノードのパラメーターを設定する」をご参照ください。

次の表に、パラメーターを示します。

パラメーター名

レベル

説明

loose_subquery_cond_pushdown_mode

グローバル

WHERE 句から IN サブクエリへの条件プッシュダウン機能の制御スイッチ。有効な値：

REPLICA_ON: 読み取り専用ノードでのみ条件プッシュダウン機能を有効にします。
ON: 条件プッシュダウン機能を有効にします。
OFF (デフォルト): 条件プッシュダウンの機能を無効にします。

または、loose_subquery_cond_pushdown_mode を変更せずに、OPTIMIZE HINT（SUBQUERY_CONDITION_PUSHDOWN または NO_SUBQUERY_CONDITION_PUSHDOWN）を使用して、特定のサブクエリのプッシュダウンを制御します。

例：

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

--このクエリ文は、現在のクエリブロックがサブクエリ @subq1-- に条件をプッシュダウンすることを禁止していることを示しています。
SELECT  /*+ NO_SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

--このクエリ文は、現在のクエリブロックがルールを満たす条件をサブクエリ @subq1-- にプッシュダウンすることを示しています。
SELECT  /*+ SUBQUERY_CONDITION_PUSHDOWN(@subq1) */ * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT /*+ QB_NAME(subq1) */ t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

例 1

WHERE 句を IN サブクエリにプッシュダウンする機能を有効にする前後の実行プランを比較するには、次のコードを実行します。

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE t1.c<25 AND
(t1.a,t1.c) IN (SELECT t2.e,MAX(t2.g) FROM t2 WHERE t2.e<5 GROUP BY t2.e);

結果例：

-- loose_subquery_cond_pushdown_mode を有効にする前
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (条件内のサブクエリ。1 回だけ実行)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c) を使用した <materialized_subquery> のインデックスルックアップ
                    -> 重複排除によるマテリアライズ
                        -> <temporary> のテーブルスキャン
                            -> 一時テーブルを使用した集計
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> t2 のテーブルスキャン (cost=*** rows=***)
                                
-- loose_subquery_cond_pushdown_mode を有効にした後
-> Filter: ((t1.c < 25) and <in_optimizer>((t1.a,t1.c),(t1.a,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (条件内のサブクエリ。1 回だけ実行)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> <auto_distinct_key> (e=t1.a, MAX(t2.g)=t1.c) を使用した <materialized_subquery> のインデックスルックアップ
                    -> 重複排除によるマテリアライズ
                        -> Filter: (max(t2.g) < 25)
                            -> <temporary> のテーブルスキャン
                                -> 一時テーブルを使用した集計
                                    -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                        -> t2 のテーブルスキャン (cost=*** rows=***)

説明

SQL セマンティクスを解析すると、テーブル t1 のクエリ結果セットは、t1.c とサブクエリ内の射影列 MAX(t2.g) の間の等価関係に準拠する必要があることがわかります。その結果、メインクエリの WHERE 条件 t1.c < 25 が与えられると、サブクエリも MAX(t2.g)<25 を満たす必要があります。オプティマイザーは、WHERE 条件をサブクエリにプッシュダウンする機能を活用して、条件 t1.c<25 をサブクエリにカスケードダウンできるようにします。ただし、GROUP BY 句には g 列が含まれていないため、この条件はサブクエリの HAVING 句にのみ適用できます。

例 2

WHERE 句を IN サブクエリにプッシュダウンする機能を有効にする前後の実行プランを比較するには、次のコードを実行します。

EXPLAIN FORMAT=TREE SELECT * FROM t1
WHERE ((t1.a<2 OR t1.a=5) AND t1.b>3) AND
(t1.a,t1.b,t1.c) IN
(
SELECT t2.e,t2.f,MAX(t2.g)
FROM t2
WHERE t2.e<5
GROUP BY t2.e,t2.f
);

結果例：

-- loose_subquery_cond_pushdown_mode を有効にする前
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (条件内のサブクエリ。1 回だけ実行)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c) を使用した <materialized_subquery> のインデックスルックアップ
                    -> 重複排除によるマテリアライズ
                        -> <temporary> のテーブルスキャン
                            -> 一時テーブルを使用した集計
                                -> Filter: (t2.e < 5)  (cost=*** rows=***)
                                    -> t2 のテーブルスキャン (cost=*** rows=***)

-- loose_subquery_cond_pushdown_mode を有効にした後
-> Filter: ((t1.b > 3) and ((t1.a < 2) or (t1.a = 5)) and <in_optimizer>((t1.a,t1.b,t1.c),(t1.a,t1.b,t1.c) in (select #2)))  (cost=*** rows=***)
    -> Table scan on t1  (cost=*** rows=***)
    -> Select #2 (条件内のサブクエリ。1 回だけ実行)
        -> Filter: ((t1.a = `<materialized_subquery>`.e) and (t1.b = `<materialized_subquery>`.f) and (t1.c = `<materialized_subquery>`.`MAX(t2.g)`))
            -> Limit: 1 row(s)
                -> <auto_distinct_key> (e=t1.a, f=t1.b, MAX(t2.g)=t1.c) を使用した <materialized_subquery> のインデックスルックアップ
                    -> 重複排除によるマテリアライズ
                        -> <temporary> のテーブルスキャン
                            -> 一時テーブルを使用した集計
                                -> Filter: ((t2.e < 5) and (t2.f > 3) and ((t2.e < 2) or (t2.e = 5)))  (cost=*** rows=***)
                                    -> t2 のテーブルスキャン (cost=*** rows=***)

説明

SQL クエリのメインクエリでは、WHERE 条件が少し複雑です。ただし、WHERE 条件下でサブクエリをプッシュダウンする原則を分析することにより、メインクエリテーブル t1 の列 t1.a, t1.b, t1.c がサブクエリの射影列 t2.e, t2.f, MAX(t2.g) と同等であることがわかります。したがって、メインクエリで WHERE 条件 ((t1.a<2 OR t1.a=5) AND t1.b>3) が AND で接続されている場合、サブクエリの対応する列が条件 ((t2.e<2 OR t2.e=5) AND t2.f.b>3) を満たす必要があると推測できます。 WHERE 条件下でサブクエリをプッシュダウンする機能を有効にすると、オプティマイザーは WHERE 条件 ((t1.a<2 OR t1.a=5) AND t1.b>3) をサブクエリにプッシュダウンできます。分析によると、サブクエリ GROUP BY の列には t2.e, t2.f が含まれているため、サブクエリの WHERE 句にプッシュダウンされます。