如何使用子查询折叠功能 - 云原生数据库 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子查询，支持所有的运算算子。

互斥子查询

如果两个子查询的集合具备包含关系，依赖于逻辑运算上下文可以整体改写为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);

示例

互斥子查询消除

示例一：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));

云原生数据库 PolarDB：子查询折叠

背景知识

子查询类型

子查询包含关系

子查询折叠功能概述

同类型子查询

互斥子查询

使用前提

使用方法

示例

同类型子查询消除

示例一：AND条件下子查询消除

示例二：OR条件下子查询消除

同类型子查询合并

示例一：AND条件下的子查询合并

示例二：OR条件下的子查询合并