文档

子查询折叠

更新时间:

子查询折叠是将SQL语句中多个子查询根据某种规则进行折叠,以减少SQL语句中子查询的数量,从而加快SQL语句执行速率的一种子查询优化手段。本文介绍了子查询折叠的背景知识、子查询折叠的折叠原理、使用方法以及示例等内容。

背景知识

子查询类型

PolarDB MySQL版支持的子查询类型见下表:

子查询类型

算子关键字

比较算子

备注

EXISTS

EXISTSNOT EXISTS

IN

INNOT 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,则为同类型子查询。

  • 互斥子查询:如果子查询类型与对应的算子关键字语义相反,则称之为互斥子查询。如EXISTSNOT EXISTS即为一对互斥子查询,INNOT 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集合的子集,所以称为左子集。

子查询折叠功能概述

    说明

    折叠的对象可以出现在WHEREHAVINGJOIN ON条件的任何位置上,子查询同时出现在AND/OR逻辑算子下。

    子查询可以是EXISTSIN子查询,ALL或ALL子查询,支持所有的运算算子。

同类型子查询

如果两个子查询的集合具备包含关系,则消除其中一个。具体规则如下:

子查询间逻辑运算

左右子查询类型

子查询包含关系

限制

折叠类型

折叠说明

AND

同为EXISTS、IN、ANY、ALL

左子集、相等

消除

消除右子集,保留左子查询。 参考示例一:AND条件下子查询消除

右子集

消除

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

同为NOT EXISTS、NOT IN、!= ALL

不可比较

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

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

合并(不总是最优)

说明

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

合并二者的WHEREHAVING条件,合并为一个新的子查询。 参考示例一:AND条件下的子查询合并

OR

同为EXISTS、IN、ANY、ALL

左子集、相等

消除

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

右子集

消除

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

同为EXISTS、IN、ANY

不可比较

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

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

合并(不总是最优)

合并二者的WHEREHAVING条件,合并为一个新的子查询。 参考示例二:OR条件下的子查询合并

互斥子查询

如果两个子查询的集合具备包含关系,依赖于逻辑运算上下文可以整体改写为TRUEFALSE,或者将两个子查询合二为一,生成一个新的子查询。具体规则如下:

子查询间逻辑运算

左右子查询类型

子查询包含关系

限制

折叠类型

折叠说明

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

  • WHEREHAVING条件不同,其余部分相同。

  • 子查询内部支持存在嵌套子查询。

合并(总是最优)

合并集合,增加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互斥类型冲突

适用场景EXISTSNOT EXISTSINNOT 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互斥子查询合并

适用场景

  • INNOT 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));