写点什么

数仓调优实践丨 SQL 改写消除相关子查询

  • 2023-12-25
    广东
  • 本文字数:3114 字

    阅读完需:约 10 分钟

数仓调优实践丨SQL改写消除相关子查询

本文分享自华为云社区《【调优实践】SQL改写消除相关子查询》,作者: 门前一棵葡萄树 。

一、子查询


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


  • 子查询 SubQuery:对应于查询解析树中的范围表 RangeTblEntry,更通俗一些指的是出现在 FROM 语句后面的独立的 SELECT 语句。

  • 子链接 SubLink:对应于查询解析树中的表达式,更通俗一些指的是出现在 where/on 子句、targetlist 里面的语句。

1.1 非相关子查询


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


select t1.c1,t1.c2from t1where t1.c1 in (    select c2    from t2    where t2.c2 IN (2,3,4));
复制代码

1.2 相关子查询


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


select t1.c1,t1.c2from t1where t1.c1 in (    select c2    from t2    where t2.c1 = t1.c1 AND t2.c2 in (2,3,4));
复制代码

二、调优实战

2.1 案例:


UPDATE t1SET (c1,c2)=(SELECT COALESCE(t2.c1, t1.c2),c2 FROM t2 WHERE t1.i1 = t2.i1  -- 相关标量子查询);
复制代码


其中子查询 SELECT COALESCE(t2.c1, t1.c2),c2 FROM t2 WHERE t1.i1 = t2.i1 依赖于外层父查询的 t1 表,因此属于相关子查询。执行计划:


                                                                           QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------  id |                   operation                   |     A-time     | A-rows | E-rows | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs ----+-----------------------------------------------+----------------+--------+--------+------------+----------------+----------+---------+---------+---------   1 | ->  Streaming (type: GATHER)                  | 8.998          |      0 |      1 |            | 24KB           |          |         |      17 | 9.83   2 |    ->  Update on public.t1                    | [0.086, 0.096] |      2 |      2 |            | [308KB, 308KB] |          |         |      17 | 9.74   3 |       ->  Seq Scan on public.t1               | [0.058, 0.074] |      2 |      2 |            | [32KB, 32KB]   | 1MB      |         |      17 | 3.73   4 |          ->  Result  [3, SubPlan 1]           | [0.033, 0.034] |      2 |     10 |            | [16KB, 16KB]   | 1MB      |         |       6 | 1.36   5 |             ->  Materialize                   | [4.167, 4.458] |     20 |     10 |            | [16KB, 16KB]   | 16MB     | [24,24] |       6 | 1.36   6 |                ->  Streaming(type: BROADCAST) | [4.105, 4.406] |     10 |     10 |            | [48KB, 48KB]   | 2MB      |         |       6 | 1.33   7 |                   ->  Seq Scan on public.t2   | [0.013, 0.013] |      5 |      5 |            | [32KB, 32KB]   | 1MB      |         |       6 | 1.02   8 |          ->  Result  [3, SubPlan 2]           | [0.006, 0.021] |      2 |     10 |            | [16KB, 16KB]   | 1MB      |         |       6 | 1.36   9 |             ->  Materialize                   | [0.055, 0.061] |     20 |     10 |            | [16KB, 16KB]   | 16MB     | [24,24] |       6 | 1.36  10 |                ->  Streaming(type: BROADCAST) | [0.034, 0.040] |     10 |     10 |            | [48KB, 48KB]   | 2MB      |         |       6 | 1.33  11 |                   ->  Seq Scan on public.t2   | [0.005, 0.009] |      5 |      5 |            | [32KB, 32KB]   | 1MB      |         |       6 | 1.02
复制代码

2.2 子查询消除


改写策略就是解除子查询与父查询依赖关系,改写方案参考:


UPDATE t1SET (c1,c2)=(t3.c1,t3.c2)FROM (SELECT t2.i1,COALESCE(t2.c1, t1.c2) c1,t2.c2 FROM t1,t2 WHERE t1.i1 = t2.i1)t3WHERE t1.i1 = t3.i1;
复制代码


改写后,子查询独立,不再依赖父查询中元素。执行计划:


                                                                              QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------------  id |                      operation                      |     A-time     | A-rows | E-rows | E-distinct |  Peak Memory   | E-memory | A-width | E-width | E-costs ----+-----------------------------------------------------+----------------+--------+--------+------------+----------------+----------+---------+---------+---------   1 | ->  Streaming (type: GATHER)                        | 13.141         |      0 |      1 |            | 24KB           |          |         |      33 | 10.56   2 |    ->  Update on public.t1                          | [6.242, 6.362] |      2 |      2 |            | [308KB, 308KB] |          |         |      33 | 10.47   3 |       ->  Streaming(type: RESTORE)                  | [6.186, 6.310] |      2 |      2 |            | [48KB, 48KB]   | 2MB      |         |      33 | 4.46   4 |          ->  Nested Loop (5,11)                     | [4.082, 4.801] |      2 |      2 |            | [32KB, 32KB]   | 1MB      |         |      33 | 4.44   5 |             ->  Streaming(type: BROADCAST)          | [3.804, 4.541] |      4 |      4 |            | [48KB, 48KB]   | 2MB      |         |      27 | 2.36   6 |                ->  Nested Loop (7,8)                | [2.972, 4.267] |      2 |      2 |            | [32KB, 32KB]   | 1MB      |         |      27 | 2.20   7 |                   ->  Seq Scan on public.t1         | [0.010, 0.011] |      2 |      2 |            | [16KB, 16KB]   | 1MB      |         |      14 | 1.01   8 |                   ->  Materialize                   | [2.724, 4.055] |      6 |      4 |            | [16KB, 16KB]   | 16MB     | [28,28] |      13 | 1.17   9 |                      ->  Streaming(type: BROADCAST) | [2.667, 4.008] |      4 |      4 |            | [48KB, 48KB]   | 2MB      |         |      13 | 1.17  10 |                         ->  Seq Scan on public.t1   | [0.008, 0.012] |      2 |      2 |            | [16KB, 16KB]   | 1MB      |         |      13 | 1.01  11 |             ->  Materialize                         | [0.018, 0.022] |     12 |      5 |            | [16KB, 16KB]   | 16MB     | [32,32] |      14 | 2.03  12 |                ->  Seq Scan on public.t2            | [0.007, 0.009] |      5 |      5 |            
复制代码


点击关注,第一时间了解华为云新鲜技术~

发布于: 刚刚阅读数: 6
用户头像

提供全面深入的云计算技术干货 2020-07-14 加入

生于云,长于云,让开发者成为决定性力量

评论

发布
暂无评论
数仓调优实践丨SQL改写消除相关子查询_数据库_华为云开发者联盟_InfoQ写作社区