备案控制台
文档
产品文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB MySQL版 用户指南 查询优化（Query Optimizer) 查询改写 子查询折叠

子查询折叠

更新时间：
一键部署
产品详情
相关技术圈
我的收藏

子查询折叠是将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条件下子查询消除。

右子集

无

消除

消除左子集，保留右子查询。

同为NOT EXISTS、NOT IN、!= ALL

不可比较

  • 仅限SPJ子查询，或仅含SPJ+HAVING。

  • 仅有WHERE条件或HAVING条件不一致。

合并（不总是最优）

说明

不总是最优是指折叠后执行效率可能比折叠前差，并不能保证一定是一个正收益优化。实际上需要配合基于CBQT组件才能决定是否应用当前规则。

合并二者的WHERE或HAVING条件，合并为一个新的子查询。 参考示例一：AND条件下的子查询合并。

OR

同为EXISTS、IN、ANY、ALL

左子集、相等

无

消除

消除左子集，保留右子集。参考示例二：OR条件下子查询消除。

右子集

无

消除

消除右子集，保留左子集。

同为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热数据，开启子查询折叠功能前后的查询耗时如下，耗时短表示改写更优：

image

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

上一篇：查询改写下一篇：IN谓词转JOIN
文档推荐