文档首页/ 云数据库 GaussDB/ 开发指南（集中式_V2.0-8.x）/ SQL调优指南/ 典型SQL调优点/ 子查询调优
更新时间：2024-12-06 GMT+08:00
查看PDF
分享

子查询调优

子查询背景介绍

应用程序通过SQL语句来操作数据库时会使用大量的子查询，这种写法比直接对两个表做连接操作在结构上和思路上更清晰，尤其是在一些比较复杂的查询语句中，子查询有更完整、更独立的语义，会使SQL对业务逻辑的表达更清晰更容易理解，因此得到了广泛的应用。

GaussDB根据子查询在SQL语句中的位置把子查询分成了子查询、子链接两种形式。

  • 子查询SubQuery：对应于查询解析树中的范围表RangeTblEntry，更通俗一些指的是出现在FROM语句后面的独立的SELECT语句。
  • 子链接SubLink：对应于查询解析树中的表达式，更通俗一些指的是出现在where/on子句、targetlist里面的语句。
    综上，对于查询解析树而言，SubQuery的本质是范围表，而SubLink的本质是表达式。针对SubLink场景而言，由于SubLink可以出现在约束条件、表达式中，按照GaussDB对sublink的实现，sublink可以分为以下几类：
    • exist_sublink：对应EXIST、NOT EXIST语句。
    • any_sublink：对应op ANY(select…)语句，其中OP可以是“<”、“>”、“=”操作符，IN/NOT IN (select...)也属于这一类。
    • all_sublink：对应op ALL(select…)语句，其中OP可以是“<”、“>”、“=”操作符。
    • rowcompare_sublink：对应record op(select …)语句。
    • expr_sublink：对应(SELECT with single targetlist item ...)语句。
    • array_sublink：对应ARRAY(select…)语句。
    • cte_sublink：对应with query(…)语句。

    其中的sublink为exist_sublink、any_sublink，在GaussDB的优化引擎中对其应用场景做了优化（子链接提升）。另外，expr_sublink也可以提升，但是由于SQL语句中子查询使用的灵活性，会带来SQL子查询过于复杂造成性能问题。如果希望关闭expr_sublink的提升优化，可以通过GUC参数rewrite_rule来设置。子查询从大类上来看，分为非相关子查询和相关子查询：

    • 非相关子查询None-Correlated SubQuery

      子查询的执行不依赖于外层父查询的任何属性值。这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解。

      例如：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
      		 
      gaussdb=# EXPLAIN SELECT t1.c1,t1.c2
FROM t1
WHERE t1.c1 IN (
    SELECT c2
    FROM t2
    WHERE t2.c2 IN (2,3,4)
);
                              QUERY PLAN
----------------------------------------------------------------
 Hash Join 
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1 
         Filter: (c1 = ANY ('{2,3,4}'::integer[]))
   ->  Hash 
         ->  HashAggregate 
               Group By Key: t2.c2
               ->  Seq Scan on t2  
                     Filter: (c2 = ANY ('{2,3,4}'::integer[]))
(9 rows)

    • 相关子查询Correlated-SubQuery

      子查询的执行依赖于外层父查询的一些属性值（如下列示例t2.c1 = t1.c1条件中的t1.c1）作为内层查询的一个AND-ed条件。这样的子查询不具备独立性，需要和外层查询按分组进行求解。

      例如：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
      		 
      gaussdb=# EXPLAIN SELECT t1.c1,t1.c2
FROM t1
WHERE t1.c1 IN (
    SELECT c2
    FROM t2
    WHERE t2.c1 = t1.c1 AND t2.c2 IN (2,3,4)
);
                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t1
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: ((c1 = t1.c1) AND (c2 = ANY ('{2,3,4}'::integer[])))
(5 rows)

GaussDB对SubLink的优化

针对SubLink的优化策略主要是让内层的子查询提升(pullup），能够和外表直接做关联查询。判断子查询是否存在性能风险，可以通过EXPLAIN查询语句查看Sublink的部分是否被转换成SubPlan的执行计划。

例如：

gaussdb=# EXPLAIN SELECT t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN(SELECT c2 FROM t2 WHERE t2.c1 = t1.c1);
           QUERY PLAN           
--------------------------------
 Seq Scan on t1
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: (c1 = t1.c1)
(5 rows)
  • 目前GaussDB支持的Sublink-Release场景
    • IN-Sublink无相关条件
      • 不能包含上一层查询表中的列（可以包含更高层查询表中的列）。
      • 不能包含易变函数。

      例如：

      gaussdb=# EXPLAIN SELECT t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN(SELECT c2 FROM t2 WHERE t2.c1 = 1);
              QUERY PLAN              
--------------------------------------
 Hash Join
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1
   ->  Hash
         ->  HashAggregate
               Group By Key: t2.c2
               ->  Seq Scan on t2
                     Filter: (c1 = 1)
(8 rows)
    • Exist-Sublink包含相关条件

      Where子句中必须包含上一层查询的表中的列，子查询的其它部分不能含有上层查询表中的列。其它限制如下：

      • 子查询必须含有from子句。
      • 子查询不能含有with子句。
      • 子查询不能含有聚集函数。
      • 子查询里不能包含集合操作、排序、limit、windowagg、having操作。
      • 子查询不能包含易变函数。

      例如下面查询语句：

      gaussdb=# EXPLAIN (COSTS OFF) SELECT t1.c1, t1.c2 FROM t1 WHERE exists (SELECT c2 FROM t2 WHERE t2.c1 = t1.c1);
          QUERY PLAN
------------------------------
 Hash Semi Join
   Hash Cond: (t1.c1 = t2.c1)
   ->  Seq Scan on t1
   ->  Hash
         ->  Seq Scan on t2
(5 rows)

      如上打印的执行计划应替换成下面的执行计划：

                  QUERY PLAN             
-----------------------------------
 Hash Join
   Hash Cond: (t1.c1 = t2.c1)
   ->  Seq Scan on t1
   ->  Hash
         ->  HashAggregate
               Group By Key: t2.c1
               ->  Seq Scan on t2
(7 rows)
    • 包含聚集函数的等值相关子查询的提升

      子查询的WHERE条件中必须含有来自上一层的列，而且此列必须和子查询本层涉及表中的列做相等判断，且这些条件必须用AND连接。其它地方不能包含上层的列。其它限制条件如下：

      • 子查询中WHERE条件包含的表达式(列名)必须是表中的列。
      • 子查询的SELECT关键字后，必须有且仅有一个输出列，此输出列必须是聚集函数（如max），并且聚集函数的参数t2.c2不能是来自外层表t1中的列。聚集函数不能是count。
        例如，下列示例可以提升。 
        1
2
3
        		 
        SELECT * FROM t1 WHERE c1 >(
       SELECT max(t2.c1) FROM t2 WHERE t2.c1=t1.c1
);

        下列示例不能提升，因为子查询没有聚集函数。

        1
2
3
        		 
        SELECT * FROM t1 WHERE c1 >(
       SELECT  t2.c1 FROM t2 WHERE t2.c1=t1.c1
);

        下列示例不能提升，因为子查询有两个输出列。

        1
2
3
        		 
        SELECT * FROM t1 WHERE (c1,c2) >(
       SELECT max(t2.c1),min(t2.c2) FROM t2 WHERE t2.c1=t1.c1
);
      • 子查询必须是FROM子句。
      • 子查询中不能有GROUP BY、HAVING、集合操作。
      • 子查询只能是INNER JOIN。
        例如：下列示例不能提升。 
        1
2
3
        		 
        SELECT * FROM t1 WHERE c1 >(
       SELECT max(t2.c1) FROM t2 FULL JOIN t3 ON (t2.c2=t3.c2) WHERE t2.c1=t1.c1
);
      • 子查询的targetlist中不能包含返回set的函数。
      • 子查询的where条件中必须含有来自上一层的列，而且此列必须和子查询层涉及表中的列做相等判断，且这些条件必须用and连接。其它地方不能包含上层中的列。例如：下列示例中的最内层子链接可以提升。 
        1
2
3
4
5
        		 
        SELECT * FROM t3 WHERE t3.c1=(
        SELECT t1.c1
        FROM t1 WHERE c1 >(
                SELECT max(t2.c1) FROM t2 WHERE t2.c1=t1.c1 
));

        基于上面的示例，再加一个条件，则不能提升，因为最内侧子查询引用了上上层中的列。示例如下：

        1
2
3
4
5
6
        		 
        SELECT * FROM t3 WHERE t3.c1=(
        SELECT t1.c1
        FROM t1 WHERE c1 >(
               SELECT max(t2.c1) FROM t2 WHERE t2.c1=t1.c1 AND t3.c1>t2.c2

));
    • 提升OR子句中的SubLink

      当WHERE过滤条件中有OR连接的EXIST相关SubLink，例如：

      1
2
3
      		 
      SELECT a, c FROM t1
WHERE t1.a = (SELECT avg(a) FROM t3 WHERE t1.b = t3.b) OR
EXISTS (SELECT * FROM t4 WHERE t1.c = t4.c);

      将OR连接的EXIST相关子查询OR子句的提升过程：

      1. 提取WHERE条件中，OR子句中的opExpr。为：t1.a = (SELECT avg(a) FROM t3 WHERE t1.b = t3.b)
      2. 这个op操作中包含subquery，判断是否可以提升，如果可以提升，重写subquery为：SELECT avg(a), t3.b FROM t3 GROUP BY t3.b，生成not null条件t3.b IS NOT NULL，并将opexpr用not null条件替换。此时SQL变为： 
        1
2
3
        		 
        SELECT a, c
FROM t1 LEFT JOIN (SELECT avg(a) avg, t3.b FROM t3 GROUP BY t3.b) AS t3 ON (t1.a = avg and t1.b = t3.b)
WHERE t3.b IS NOT NULL OR exists (SELECT * FROM t4 WHERE t1.c = t4.c);
      3. 再次提取or子句中的exists sublink，exists (select * from t4 where t1.c = t4.c)，判断是否可以提升，如果可以提升，转换subquery为：select t4.c from t4 group by t4.c生成not null条件t4.c is not null提升查询，SQL变为： 
        1
        		 
        SELECT t1.a, t1.c FROM t1 LEFT JOIN (SELECT avg(a) avg, t3.b FROM t3 GROUP BY t3.b) AS t3 ON (t1.a = avg AND t1.b = t3.b) LEFT JOIN (SELECT t5.c FROM t5 GROUP BY t5.c) AS t5 ON (t1.c = t5.c) WHERE t3.b IS NOT NULL OR t5.c IS NOT NULL;
  • 目前GaussDB不支持的Sublink-Release场景

    除了以上场景之外都不支持Sublink提升，因此关联子查询会被计划成SubPlan+Broadcast的执行计划，当inner表的数据量较大时则会产生性能风险。

    如果相关子查询中跟外层的两张表做join，那么无法提升该子查询，需要通过将父SQL创建成with子句，然后再跟子查询中的表做相关子查询。

    例如：

    1
2
    		 
    SELECT distinct t1.a, t2.a
FROM t1 LEFT JOIN t2 ON t1.a=t2.a AND NOT EXISTS (SELECT a,b FROM test1 WHERE test1.a=t1.a AND test1.b=t2.a);

    改写为：

    1
2
3
4
5
6
7
8
    		 
    WITH temp AS
(
        SELECT * FROM (SELECT t1.a AS a, t2.a AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a)

)
SELECT distinct a,b
FROM temp
WHERE NOT EXISTS (SELECT a,b FROM test1 WHERE temp.a=test1.a AND temp.b=test1.b);
    • 出现在targetlist里的相关子查询无法提升（不含count）

      例如：

      1
2
3
4
      		 
      gaussdb=# EXPLAIN (costs off)
SELECT (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) ssq, t1.c2
FROM t1
WHERE t1.c2 > 10;

      执行计划为：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
      		 
      gaussdb=# EXPLAIN (costs off)
SELECT (SELECT c2 FROM t2 WHERE t1.c1 = t2.c1) ssq, t1.c2
FROM t1
WHERE t1.c2 > 10;
           QUERY PLAN
--------------------------------
 Seq Scan on t1
   Filter: (c2 > 10)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: (t1.c1 = c1)
(5 rows)

      由于相关子查询出现在targetlist（查询返回列表）里，对于t1.c1=t2.c1不匹配的场景仍然需要输出值，因此使用right-outerjoin关联t2&t1，以确保t1.c1=t2.c1在不匹配时，子SSQ能够返回不匹配的补空值。

      SSQ和CSSQ的解释如下：

      • SSQ：ScalarSubQuery一般指返回1行1列scalar值的sublink，简称SSQ。
      • CSSQ：Correlated-ScalarSubQuery和SSQ相同不过是指包含相关条件的SSQ。

      上述SQL语句可以改写为：

      1
2
3
4
5
      		 
      WITH ssq AS
(
    SELECT * FROM t1 WHERE t1.c2 >10
)
SELECT t2.c2,ssq.c2 FROM t2 RIGHT JOIN ssq ON ssq.c1 = t2.c1;

      改写后的执行计划为：

      1
2
3
4
5
6
7
8
9
      		 
                 QUERY PLAN            
---------------------------------
 Hash Right Join
   Hash Cond: (t2.c1 = t1.c1)
   ->  Seq Scan on t2
   ->  Hash
         ->  Seq Scan on t1
               Filter: (c2 > 10)
(6 rows)

      可以看到出现在SSQ返回列表里的相关子查询SSQ，已经被提升成Right Join，从而避免当內表t2较大时出现SubPlan计划导致性能变差。

    • 出现在targetlist里的相关子查询无法提升（带count）

      例如：

      1
2
3
      		 
      SELECT (SELECT count(*) FROM t2 WHERE t2.c1=t1.c1) cnt, t1.c1, t3.c1
FROM t1,t3
WHERE t1.c1=t3.c1 ORDER BY cnt, t1.c1;

      执行计划为：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
      		 
                       QUERY PLAN
--------------------------------------------
 Sort
   Sort Key: ((SubPlan 1)), t1.c1
   ->  Hash Join
         Hash Cond: (t1.c1 = t3.c1)
         ->  Seq Scan on t1
         ->  Hash
               ->  Seq Scan on t3
         SubPlan 1
           ->  Aggregate
                 ->  Seq Scan on t2
                       Filter: (c1 = t1.c1)
(11 rows)

      由于相关子查询出现在targetlist(查询返回列表)里，对于t1.c1=t2.c1不匹配的场景仍然需要输出值，因此使用left-outerjoin关联T1&T2确保t1.c1=t2.c1在不匹配时子SSQ能够返回不匹配的补空值，但是这里带了count语句及时在t1.c1=t2.t1不匹配时需要输出0，因此可以使用case-when NULL then 0 else count(*)来代替。

      上述SQL语句可以改写为：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
      		 
      WITH ssq AS
(
    SELECT count(*) cnt, c1 FROM t2 GROUP BY c1
)
SELECT case WHEN
            ssq.cnt IS NULL THEN 0
            ELSE ssq.cnt
       END cnt, t1.c1, t3.c1
FROM t1 LEFT JOIN ssq ON ssq.c1 = t1.c1,t3
WHERE t1.c1 = t3.c1
ORDER BY ssq.cnt, t1.c1;

      改写后的执行计划为：

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
      		 
                      QUERY PLAN
-------------------------------------------
 Sort
   Sort Key: ssq.cnt, t1.c1
   CTE ssq
     ->  HashAggregate
           Group By Key: t2.c1
           ->  Seq Scan on t2
   ->  Hash Join
         Hash Cond: (t1.c1 = t3.c1)
         ->  Hash Left Join
               Hash Cond: (t1.c1 = ssq.c1)
               ->  Seq Scan on t1
               ->  Hash
                     ->  CTE Scan on ssq
         ->  Hash
               ->  Seq Scan on t3
(15 rows)
    • 相关条件为不等值场景

      例如：

      1
2
3
      		 
      SELECT t1.c1, t1.c2
FROM t1
WHERE t1.c1 = (SELECT agg() FROM t2.c2 > t1.c2);

      对于非等值相关条件的SubLink目前无法提升，从语义上可以通过做2次join（一次CorrelationKey，一次rownum自关联）达到提升改写的目的。

      改写方案有两种。

      • 子查询改写方式： 
        1
2
3
4
5
6
7
        		 
        SELECT t1.c1, t1.c2
FROM t1, (
    SELECT t1.rowid, agg() aggref
    FROM t1,t2
    WHERE t1.c2 > t2.c2 GROUP BY t1.rowid
) dt /* derived table */
WHERE t1.rowid = dt.rowid AND t1.c1 = dt.aggref;
      • CTE改写方式： 
         1
 2
 3
 4
 5
 6
 7
 8
 9
10
        		 
        WITH dt as
(
    SELECT t1.rowid, agg() aggref
    FROM t1,t2
    WHERE t1.c2 > t2.c2 GROUP BY t1.rowid
)
SELECT t1.c1, t1.c2
FROM t1, dt
WHERE t1.rowid = dt.rowid AND
t1.c1 = dt.aggref;
    • 对于AGG类型为count(*)时需要进行CASE-WHEN对没有match的场景补0处理，非COUNT(*)场景NULL处理。
    • CTE改写方式如果有sharescan支持性能上能够更优。

更多优化示例

示例：修改select语句，将子查询修改为和主表的join，或者修改为可以提升的subquery，但是在修改前后需要保证语义的正确性。

1
2
3
4
5
6
7
8
9
 
gaussdb=# explain (costs off) select * from t1 where t1.c1 in (select t2.c1 from t2 where t1.c1 = t2.c2);
           QUERY PLAN
--------------------------------
 Seq Scan on t1
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Seq Scan on t2
           Filter: (t1.c1 = c2)
(5 rows)

上面示例计划中存在一个subPlan，为了消除这个subPlan可以修改语句为：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
gaussdb=# explain (costs off) select * from t1 where exists (select t2.c1 from t2 where t1.c1 = t2.c2 and t1.c1 = t2.c1);
                QUERY PLAN
------------------------------------------
 Hash Join
   Hash Cond: (t1.c1 = t2.c2)
   ->  Seq Scan on t1
   ->  Hash
         ->  HashAggregate
               Group By Key: t2.c2, t2.c1
               ->  Seq Scan on t2
                     Filter: (c2 = c1)
(8 rows)

从计划可以看出，subPlan消除了，计划变成了两个表的hash join，这样可以提高执行效率。

父主题： 典型SQL调优点

相关文档