サブクエリの折りたたみ機能の使用方法 - PolarDB - Alibaba Cloud ドキュメントセンター

サブクエリフォールディングは、特定のルールに基づいてSQLステートメント内の複数のサブクエリをフォールドして、SQLステートメント内のサブクエリの数を減らすサブクエリ最適化方法です。これにより、SQL文の実行が高速化されます。このトピックでは、サブクエリの折りたたみの背景、原則、使用法、および例について説明します。

背景

サブクエリの種類

次の表に、PolarDB for MySQLでサポートされているサブクエリの種類を示します。

サブクエリタイプ	演算子キーワード	比較演算子	補足
EXISTS	`EXISTS`および`NOT EXISTS`	なし	なし
IN	`IN`と`NOT IN`	なし	なし
任意の	なし	=, ! =, <, <=, <, >=	例: `WHERE t.a > ANY(select t2.a ...)`
すべて	なし	=, ! =, <, <=, <, >=	例: `WHERE t.a > ANY(select t2.a ...)`
単一行スカラーサブクエリ	例: `WHERE t.a < (SELECT MIN(t2.a) ...)` サブクエリ折りたたみ機能は、このサブクエリ型をサポートしていません。

同じ種類のサブクエリ: 同じ演算子キーワードを持つサブクエリは、同じ種類のサブクエリです。 2つのサブクエリが両方ともEXISTSまたは > ANYの場合、サブクエリは同じタイプのサブクエリです。
相互排他的サブクエリ: オペレータキーワードが反対のサブクエリは、相互排他的サブクエリです。たとえば、EXISTSとNOT EXISTSは相互に排他的なサブクエリであり、INとNOT INは相互に排他的なサブクエリです。次の表に、相互排他的なサブクエリを示します。
サブクエリ
相互排他的サブクエリ
出口
存在しない
イン
NOT IN
= ANY
!=すべて
!=誰でも
=すべて
< ANY
>= ALLまたは > ALL
<= ANY
> すべて
> ANY
<= ALLまたは < ALL
>= ANY
<すべて

サブクエリ包含関係

サブクエリの右側はセットです。セットには、left subset、right subset、equalの3種類の包含関係があります。 2つのセットに包含関係がない場合、それらのセットは比較不可能です。次のセクションでは、例として左サブセットを使用します。

左サブセット: サブクエリの左側のセットが右側のセットのサブセットである場合、左側のセットは左サブセットです。例：

SELECT a
tから
存在する場所 (
SELECT /* + subq1 */ t2.a
t2から
どこt2.a > 10
)
そして出口 (
SELECT /* + subq2 */ t2.a
t2から
)

前の例では、左側のsubq1セットは条件が厳しく、サイズが小さく、右側のsubq2セットのサブセットです。したがって、subq1は左サブセットである。

概要

説明

折りたたまれたオブジェクトは、WHERE、HAVING、またはJOIN ON条件の任意の位置に表示でき、サブクエリはandおよびor演算子の下に同時に表示されます。

サブクエリには、EXISTS、IN、またはALLサブクエリを使用できます。すべての演算子がサポートされます。

同じタイプのサブクエリ

2つのサブクエリのセットに包含関係がある場合、サブクエリの1つが削除されます。次の表に、詳細を示します。

サブクエリ間の演算子	左または右サブクエリのタイプ	サブクエリ包含関係	制限	折りたたみタイプ	説明
AND	タイプは両方ともEXISTS、IN、ANY、またはALLです。	左サブセットと等しい	なし	削除	右サブセットは削除され、左サブクエリは保持されます。詳細については、「例1: AND条件でサブクエリを削除する」をご参照ください。
	タイプは両方ともEXISTS、IN、ANY、またはALLです。	右サブセット	なし	削除	左サブセットは除去され、右サブクエリは保持される。
	タイプは両方ともEXISTS、NOT IN、または! =すべて。	比類のない	SPJサブクエリと、SPJおよびHAVING条件のみを含むサブクエリがサポートされています。 WHERE条件のみを含むサブクエリと、HAVING条件に矛盾するサブクエリがサポートされます。	マージ (常に最適ではない) 説明常に最適とは、折り畳み後の実行効率が折り畳み前の実行効率よりも悪い可能性があることを意味し、折り畳みが正の最適化であるとは保証されません。ルールを適用するかどうかを決定するには、コストベースのクエリ変換 (CBQT) コンポーネントを使用する必要があります。	2つのサブクエリのWHEREまたはHAVING条件は、新しいサブクエリにマージされます。詳細については、「例1: AND条件でサブクエリをマージする」をご参照ください。
OR	タイプは両方ともEXISTS、IN、ANY、またはALLです。	左サブセットと等しい	なし	削除	左サブセットは除去され、右サブセットは保持される。詳細については、「例2: OR条件のサブクエリを削除する」をご参照ください。
	タイプは両方ともEXISTS、IN、ANY、またはALLです。	右サブセット	なし	削除	右サブセットは除去され、左サブセットは保持される。
	タイプは両方ともEXISTS、IN、またはANYです。	比類のない	SPJサブクエリと、SPJおよびHAVING条件のみを含むサブクエリがサポートされています。 WHERE条件のみを含むサブクエリと、HAVING条件に矛盾するサブクエリがサポートされます。	マージ (常に最適ではない)	2つのサブクエリのWHEREまたはHAVING条件は、新しいサブクエリにマージされます。詳細については、「例2: OR条件でサブクエリをマージする」をご参照ください。

相互排他的サブクエリ

2つのサブクエリのセットに包含関係がある場合、論理演算コンテキストに基づいてサブクエリをTRUEまたはFALSEに書き換えるか、2つのサブクエリを新しいサブクエリにマージできます。次の表に、詳細を示します。

サブクエリ間の演算子	左または右サブクエリのタイプ	サブクエリ包含関係	制限	折りたたみタイプ	説明
AND	EXISTSとNOT EXISTS INとNOT IN	左サブセットと等しい	なし	削除	AND条件はFALSEに書き換えられます。詳細については、「例1: EXISTS mutual exclusive type conflict」をご参照ください。
	EXISTSとNOT EXISTS	右サブセット	サブクエリのクエリブロックをUNIONにすることはできません。 WHERE条件のみが異なります。サブクエリはネストされたサブクエリをサポートします。	マージ (常に最適ではない)	セットがマージされ、`HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0` 条件が追加されます。詳細については、「例4: EXISTS相互排他的サブクエリのマージ」をご参照ください。
	! =ANYおよび=ALL <ANY and >=ALLまたは> ALL <=ANYおよび> すべて > ANYおよび <=ALLまたは <ALL >=ANYおよび <ALL	左サブセットと等しい	なし	削除	AND条件はFALSEに書き換えられます。詳細については、「例2: 任意またはすべての相互排他型の競合」をご参照ください。
	INとNOT IN =とにかく! =すべて	右サブセット	サブクエリのクエリブロックをUNIONにすることはできません。 WHEREまたはHAVING条件のみが異なります。サブクエリはネストされたサブクエリをサポートします。	マージ (常に最適)	セットはマージされ、LNNVL演算子が追加されます。折りたたみは常に最適で、サブクエリはデフォルトで折りたたまれます。詳細については、「例5: 任意またはすべての相互排他型の競合」をご参照ください。
OR	EXISTSとNOT EXISTS	右サブセット	なし	削除	OR条件はTRUEに書き換えられます。詳細については、「例3: OR条件でのEXISTSクエリの削除」をご参照ください。

前提条件

クラスターのバージョンはPolarDB for MySQL 8.0で、リビジョンバージョンは8.0.2.2.23以降である必要があります。クラスターバージョンの表示方法については、「エンジンバージョン5.6、5.7、および8.0」をご参照ください。

使用法

サブクエリの折りたたみを有効にするには、loose_polar_optimizer_switchパラメーターをcoalece_subquery=onに設定し、サブクエリのマージを有効にするにはforce_coalesce_subqueryパラメーターをONに設定します。パラメーターの設定方法の詳細については、「クラスターおよびノードパラメーターの設定」をご参照ください。

パラメーター

レベル

説明

loose_polar_optimizer_switch

グローバル

サブクエリの折りたたみ機能を有効または無効にします。デフォルトでは、サブクエリはマージされません。

有効な値：

coalece_subquery=on: サブクエリの折りたたみ機能を有効にします。
coalece_subquery=off: サブクエリの折りたたみ機能を無効にします。

force_coalece_subquery

グローバル

サブクエリマージ機能を有効または無効にします。サブクエリ折り畳み規則テーブル内の必ずしも最適な折り畳み規則が実施されるとは限らない。

有効な値：

OFF (デフォルト): サブクエリマージ機能を無効にします。
ON: サブクエリのマージ機能を有効にします。

説明

このパラメーターはセッションで設定できます。例：
```
SET force_coalece_subquery=ON;
```

HINT構文を使用して、折りたたむサブクエリを指定することもできます。例：

DESC SELECT /* + SUBQUERY_COALESCE(qb1, qb2) SUBQUERY_COALESCE(qb3, qb4) */ * FROM t1 LEFT JOIN t2 ON t1.a = any (SELECT /* + QB_NAME(qb1) */ a FROM t2)
t1.a! =ALL (SELECT /* + QB_NAME(qb2) */ a FROM t2 WHERE a <100) HAVING t1.b = ANY (SELECT /* + QB_NAME(qb3) */ b FROM t2 ) AND
t1.b! =ALL (SELECT /* + QB_NAME(qb4) */ b FROM t2 WHERE b <1);

例

同じタイプのサブクエリの削除

例1: AND条件でのサブクエリの削除

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0) -- サブクエリ1
AND EXISTS (SELECT 1 FROM t2); -- サブクエリ2

サブクエリ1は、サブクエリ2のサブセットである。したがって、サブクエリ2は削除されます。サブクエリ2が削除されたSQL文:

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0);

例2: OR条件のサブクエリの削除

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0) -- サブクエリ1
またはEXISTS (SELECT 1 FROM t2); -- サブクエリ2

サブクエリ1が削除され、OR条件がEXISTS (SELECT 1 FROM t2) に書き換えられ、大きい方のセットが保持されます。サブクエリ1が削除されたSQL文:

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

同一型サブクエリのマージ

例1: AND条件でのサブクエリのマージ

SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a >10 AND b < 10)
ありません (選択aからt1 WHERE a > 10およびc <3);

サブクエリがマージされたSQLステートメント:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a >10 AND (b < 10 OR c <3);

例2: OR条件でのサブクエリのマージ

SELECT * FROM t1 WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a >10 AND b < 10)
またはEXISTS (選択aからt1 WHERE a > 10およびc <3);

サブクエリがマージされたSQLステートメント:

SELECT * FROM t1 WHERE EXISTS (SELECT t1.a AS f FROM t1 WHERE a >10 AND (b < 10 OR c <3);

相互排他的サブクエリの削除

例1: EXISTS相互排他型の競合

シナリオ: EXISTSおよびNOT EXISTS、INまたはNOT IN

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c1 = 0) -- サブクエリ1
      AND NOT EXISTS (SELECT 1 FROM t2); -- サブクエリ2

AND条件はFALSEに書き換えられます。条件が書き換えられたSQL文:

SELECT * FROM t1 WHERE false;

例2: 任意またはすべての相互排他的型の競合

シナリオ

>ANYおよび <ALL, <=ALL
<ANYおよび >ALL, >=ALL

SELECT * FROM t1 WHERE t1.c1 > ANY (SELECT c1 FROM t2 WHERE c1 > 10 AND c2 > 1)
                   AND t1.c1 < ALL (選択c1 FROM t2 WHERE c1 > 10);

AND条件はFALSEに書き換えられます。条件が書き換えられたSQL文:

SELECT * FROM t1 WHERE false; // ANYセットはALLセットのサブセットです。

例3: OR条件でのEXISTSクエリの削除

SELECT * FROM t1 WHEREが存在する (SELECT 1 FROM t2 ) -サブクエリ1
      OR NOT exists (SELECT 1 FROM t2 WHERE c1 = 0); -- サブクエリ2

OR条件はTRUEに書き換えられます。条件が書き換えられたSQL文:

SELECT * FROM t1 WHERE true; // サブクエリ2はサブクエリ1のサブセットです。

例4: EXISTS相互排他的サブクエリのマージ

SELECT * FROM t1 WHERE EXIST (SELECT 1 FROM t2) -サブクエリ1
AND NOT EXIST (SELECT 1 FROM t2 WHERE c2 = 0); -- サブクエリ2

セットはマージされ、HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0条件が追加されます。セットがマージされたSQLステートメント:

SELECT * FROM t1 WHERE EXIST (SELECT 1 FROM t2 HAVING SUM (CASE WHEN extal_cond THEN 1 ELSE 0 END) ==0);

説明

マージは必ずしも最適ではありません。サブクエリを折りたたむかどうかを判断するには、CBQTコンポーネントを使用する必要があります。書き換えが最適であることを確認した場合、サブクエリマージ機能を有効にするには、force_coalesce_subqueryパラメーターをONに設定する必要があります。

TPCH Q21のホットデータに基づいて、サブクエリフォールディング機能が有効化される前後のクエリ期間は次のとおりです。短い期間はより良い書き換えを示します:

例5: 任意またはすべての相互排他型競合

シナリオ

INとNOT IN。 NOT INセットはより小さく、左サブセットです。
=とにかく! =すべて ALLセットはより小さく、左サブセットです。

SELECT * FROM t1 WHERE t1.c1 = ANY (SELECT c1 FROM t2 WHERE c1 > 10) AND
t1.c1! =ALL (SELECT c1 FROM t2 WHERE c1 > 100);

セットがマージされ、LNNVL演算子が追加されます。セットがマージされたSQLステートメント:

SELECT * FROM t1 WHERE t1.c1=
ANY (選択c1 FROM t2 WHERE c1> 10およびLNNVL(c1 >100));

サブクエリ	相互排他的サブクエリ
`出口`	`存在しない`
`イン`	`NOT IN`
`= ANY`	`!=すべて`
`!=誰でも`	`=すべて`
`< ANY`	`>= ALL`または `> ALL`
`<= ANY`	`> すべて`
`> ANY`	`<= ALL`または `< ALL`
`>= ANY`	`<すべて`