如何使用子查詢摺疊功能 - PolarDB

子查詢摺疊是將SQL語句中多個子查詢根據某種規則進行摺疊，以減少SQL語句中子查詢的數量，從而加快SQL語句執行速率的一種子查詢最佳化手段。本文介紹了子查詢摺疊的背景知識、子查詢摺疊的摺疊原理、使用方法以及樣本等內容。

背景知識

子查詢類型

PolarDB MySQL版支援的子查詢類型見下表：

子查詢類型	運算元關鍵字	比較運算元	備忘
EXISTS	`EXISTS`、`NOT EXISTS`	無	無
IN	`IN`、`NOT IN`	無	無
ANY	無	=、!=、<、<=、<、>=	如`WHERE t.a > ANY(select t2.a ...)`
ALL	無	=、!=、<、<=、<、>=	如`WHERE t.a < ALL(select t2.a ...)`
單行標量子查詢	如`WHERE t.a < (SELECT MIN(t2.a) ...)`，子查詢摺疊功能不考慮此子查詢類型。

同類型子查詢：如果子查詢類型與對應的運算元關鍵字一致，則稱之為同類型子查詢。如兩個子查詢都是EXISTS，或者兩個子查詢都是> ANY，則為同類型子查詢。
互斥子查詢：如果子查詢類型與對應的運算元關鍵字語義相反，則稱之為互斥子查詢。如EXISTS與NOT EXISTS即為一對互斥子查詢，IN與NOT IN為一對互斥子查詢。更多互斥子查詢參考如下表：
子查詢
互斥子查詢
EXISTS
NOT EXISTS
IN
NOT IN
= ANY
!= ALL
!= ANY
= ALL
< ANY
>= ALL或> ALL
<= ANY
> ALL
> ANY
<= ALL或< ALL
>= ANY
< ALL

子查詢內含項目關聯性

子查詢的右側結果集是一個集合。集合有三種內含項目關聯性：左子集、右子集、相等。如果集合沒有內含項目關聯性，則稱之為不可比較。下文以左子集為例進行介紹。

左子集：若子查詢左側的集合是右側集合的子集，則稱為左子集。樣本如下：

SELECT a
FROM t
WHERE EXISTS (
		SELECT /*+ subq1 */ t2.a
		FROM t2
		WHERE t2.a > 10
	)
	AND EXISTS (
		SELECT /*+ subq2 */ t2.a
		FROM t2
	)

從上述樣本中可以看出，左側subq1的條件更嚴格，結果集更小，是右側subq2集合的子集，所以稱為左子集。

子查詢摺疊功能概述

說明

摺疊的對象可以出現在WHERE、HAVING、JOIN ON條件的任何位置上，子查詢同時出現在AND/OR邏輯運算元下。

子查詢可以是EXISTS、IN子查詢，ALL或ALL子查詢，支援所有的運算運算元。

同類型子查詢

如果兩個子查詢的集合具備內含項目關聯性，則消除其中一個。具體規則如下：

子查詢間邏輯運算	左右子查詢類型	子查詢內含項目關聯性	限制	摺疊類型	摺疊說明
AND	同為EXISTS、IN、ANY、ALL	左子集、相等	無	消除	消除右子集，保留左子查詢。參考樣本一：AND條件下子查詢消除。
	同為EXISTS、IN、ANY、ALL	右子集	無	消除	消除左子集，保留右子查詢。
	同為NOT EXISTS、NOT IN、!= ALL	不可比較	僅限SPJ子查詢，或僅含SPJ+HAVING。僅有WHERE條件或HAVING條件不一致。	合并（不總是最優）說明不總是最優是指摺疊後執行效率可能比摺疊前差，並不能保證一定是一個正收益最佳化。實際上需要配合基於CBQT組件才能決定是否應用當前規則。	合并二者的WHERE或HAVING條件，合并為一個新的子查詢。參考樣本一：AND條件下的子查詢合并。
OR	同為EXISTS、IN、ANY、ALL	左子集、相等	無	消除	消除左子集，保留右子集。參考樣本二：OR條件下子查詢消除。
	同為EXISTS、IN、ANY、ALL	右子集	無	消除	消除右子集，保留左子集。
	同為EXISTS、IN、ANY	不可比較	僅限SPJ子查詢，或僅含SPJ+HAVING。僅有WHERE條件或HAVING條件不一致。	合并（不總是最優）	合并二者的WHERE或HAVING條件，合并為一個新的子查詢。參考樣本二：OR條件下的子查詢合并。

互斥子查詢

如果兩個子查詢的集合具備內含項目關聯性，依賴於邏輯運算上下文可以整體改寫為TRUE或FALSE，或者將兩個子查詢合二為一，產生一個新的子查詢。具體規則如下：

子查詢間邏輯運算	左右子查詢類型	子查詢內含項目關聯性	限制	摺疊類型	摺疊說明
AND	EXISTS與NOT EXISTS IN與NOT IN	左子集、相等	無	消除	將AND條件改寫為FALSE。參考樣本一：EXISTS互斥類型衝突。
	EXISTS與NOT EXISTS	右子集	子查詢的查詢塊不能是UNION。僅WHERE條件不同，其餘部分相同。子查詢內部支援存在嵌套子查詢。	合并（不總是最優）	合并集合，增加`HAVING SUM(CASE WHEN extra_cond THEN 1 ELSE 0 END) ==0`條件。參考樣本四：EXISTS互斥子查詢合并。
	!=ANY與=ALL <ANY與>=ALL或 >ALL <=ANY與>ALL >ANY與<=ALL或 <ALL >=ANY與<ALL	左子集、相等	無	消除	將AND條件改寫為FALSE。參考樣本二：ANY或ALL互斥類型衝突。
	IN與NOT IN =ANY與!=ALL	右子集	子查詢的查詢塊不能是UNION。僅WHERE或HAVING條件不同，其餘部分相同。子查詢內部支援存在嵌套子查詢。	合并（總是最優）	合并集合，增加LNNVL運算元摺疊總是最優，預設摺疊。參考樣本五：ANY或ALL互斥子查詢合并。
OR	EXISTS與NOT EXISTS	右子集	無	消除	將OR條件改寫為TRUE。參考樣本三：OR條件下EXISTS查詢消除。

使用前提

叢集版本需為PolarDB MySQL版8.0版本且修訂版本需為8.0.2.2.23或以上。如何查看叢集版本，請參見查詢版本號碼。

使用方法

您可以通過將參數loose_polar_optimizer_switch的值設定為coalesce_subquery=on來開啟子查詢摺疊功能，以及將參數force_coalesce_subquery的值設定為ON來開啟子查詢合并功能。設定參數值的具體操作請參見設定叢集參數和節點參數。

參數名稱

層級

描述

loose_polar_optimizer_switch

Global

僅開啟或關閉子查詢摺疊功能。預設不做子查詢合并。

取值範圍如下：

coalesce_subquery=on：開啟子查詢摺疊功能。
coalesce_subquery=off：關閉子查詢摺疊功能。

force_coalesce_subquery

Global

開啟或關閉子查詢合并功能，子查詢摺疊規則表格中的不總是最優摺疊會強制執行。

取值範圍如下：

OFF（預設值）：關閉子查詢合并功能。
ON：開啟子查詢合并功能。

說明

您可以在會話中設定該參數的值。樣本如下：
```
SET force_coalesce_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 ) AND
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);

樣本

同類型子查詢消除

樣本一：AND條件下子查詢消除

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)  --子查詢1
	AND EXISTS (SELECT 1 FROM t2); 		              --子查詢2

其中，子查詢1是子查詢2的子集，因此直接消除子查詢2。消除後的SQL語句如下：

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

樣本二：OR條件下子查詢消除

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE c2 = 0)  --子查詢1
	or EXISTS (SELECT 1 FROM t2);		              --子查詢2

其中，子查詢1被消除掉，OR條件改寫為EXISTS (SELECT 1 FROM t2)，保留大集合。消除後的SQL語句如下：

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

同類型子查詢合并

樣本一：AND條件下的子查詢合并

SELECT * FROM t1 WHERE NOT EXISTS (SELECT t1.a AS f FROM t1 WHERE a >10 AND b < 10)
AND NOT EXISTS (SELECT a FROM t1 WHERE a > 10  AND 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);

樣本二：OR條件下的子查詢合并

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

合并後的SQL語句如下：

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

互斥子查詢消除

樣本一：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;

樣本二：ANY或ALL互斥類型衝突

適用情境：

>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 (SELECT c1 FROM t2 WHERE  c1 > 10);

將AND條件改寫為FALSE，改寫後的SQL語句如下：

SELECT * FROM t1 WHERE false; //ANY是ALL集合的子集

樣本三：OR條件下EXISTS查詢消除

SELECT * FROM t1 WHERE exists (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的子集

樣本四：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 extra_cond THEN 1 ELSE 0 END) ==0);

說明

合并不總是最優的，您需要基於代價選擇是否進行摺疊，如確認改寫較優，需將參數force_coalesce_subquery的值設定為ON來開啟子查詢合并功能。

基於TPCH Q21熱資料，開啟子查詢摺疊功能前後的查詢耗時如下，耗時短表示改寫更優：

樣本五：ANY或ALL互斥子查詢合并

適用情境：

IN與NOT IN，並且NOT IN集合更小，是左側子集。
=ANY與 != ALL ，並且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 (SELECT c1 FROM t2 WHERE c1 > 10 AND LNNVL(c1 >100));

子查詢	互斥子查詢
`EXISTS`	`NOT EXISTS`
`IN`	`NOT IN`
`= ANY`	`!= ALL`
`!= ANY`	`= ALL`
`< ANY`	`>= ALL`或`> ALL`
`<= ANY`	`> ALL`
`> ANY`	`<= ALL`或`< ALL`
`>= ANY`	`< ALL`