数仓调优实践丨 SQL 改写消除相关子查询
作者:华为云开发者联盟
- 2023-12-25 广东
本文字数:3114 字
阅读完需:约 10 分钟
本文分享自华为云社区《【调优实践】SQL改写消除相关子查询》,作者: 门前一棵葡萄树 。
一、子查询
GaussDB(DWS)根据子查询在 SQL 语句中的位置把子查询分成了子查询、子链接两种形式。
子查询 SubQuery:对应于查询解析树中的范围表 RangeTblEntry,更通俗一些指的是出现在 FROM 语句后面的独立的 SELECT 语句。
子链接 SubLink:对应于查询解析树中的表达式,更通俗一些指的是出现在 where/on 子句、targetlist 里面的语句。
1.1 非相关子查询
子查询的执行不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解。示例:
select t1.c1,t1.c2
from t1
where 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.c2
from t1
where t1.c1 in (
select c2
from t2
where t2.c1 = t1.c1 AND t2.c2 in (2,3,4)
);
复制代码
二、调优实战
2.1 案例:
UPDATE t1
SET (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 t1
SET (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
)t3
WHERE 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
版权声明: 本文为 InfoQ 作者【华为云开发者联盟】的原创文章。
原文链接:【http://xie.infoq.cn/article/db4b3d06fb888030a30e1db1f】。文章转载请联系作者。
华为云开发者联盟
关注
提供全面深入的云计算技术干货 2020-07-14 加入
生于云,长于云,让开发者成为决定性力量
评论