2021-01-11

博客园Logo
首页
新闻
博问
专区
闪存
班级
代码改变世界
搜索
注册
登录
华为云开发者社区
博客园
首页
新随笔
联系
订阅
管理
随笔 - 694 文章 - 0 评论 - 89
十八般武艺玩转GaussDB(DWS)性能调优:SQL改写
摘要:本文将系统介绍在GaussDB(DWS)系统中影响性能的坏味道SQL及SQL模式,帮助大家能够从原理层面尽快识别这些坏味道SQL,在调优过程中及时发现问题,进行整改。
数据库的应用中,充斥着坏味道的SQL,非常影响查询的性能。坏味道SQL,即由于开发者写的随意,导致执行性能较差,需要通过优化SQL语句进行调优的SQL。在GaussDB(DWS)分布式场景下,相对于单机环境,将出现更多的坏味道SQL语句。本文将系统介绍在GaussDB(DWS)系统中影响性能的坏味道SQL及SQL模式,帮助大家能够从原理层面尽快识别这些坏味道SQL,在调优过程中及时发现问题,进行整改。

从大的方面来看,主要包含不支持下推导致的坏味道、不支持重分布导致的坏味道、数据类型转换导致的坏味道、全局性操作导致的坏味道、NestLoop类低效运算导致的坏味道和冗余操作导致的坏味道。本文将介绍每一类坏味道的原因,以及如何进行SQL改写及调优。

一.不支持下推导致的坏味道
在GaussDB(DWS)分布式场景下,数据运算应该全部下推到DN上执行,才能获得比较好的性能收益。但对于某些场景,数据必须在CN上执行,导致语句无法全部下推到DN运算,会导致两个主要的瓶颈点:

(1)只有基表扫描在DN执行,需要将大量数据传输到CN上,网络开销增大。

(2)原先可以在DN上分布式执行的数据,均由CN单个执行,瓶颈加大。

通常情况下,我们不支持不下推函数、复合类型、复杂语法及组合(例如:某些场景的with recursive语法,rollup函数+多count(distinct)语法)的下推,所以应该尽量避免在语句中使用以上元素。在客户场景中,经常遇到函数不能下推导致的问题,本篇博文重点以函数下推为例,讲述如何解决类似的问题。如下图计划所示,在语句中包含了不支持下推的函数unship_func(),导致整个计划不能下推,计划中出现“REMOTE_TABLE_QUERY”的字样,即会出现上述的瓶颈问题。遇到类似问题,需要根据具体应用场景,为函数设置合理的下推属性,使其可以下推。

通常来说,函数可以通过可变性和下推维度进行划分,主要包含以下函数属性:

以上两个属性可以通过系统表pg_proc的provalitile和proshippable字段查询。目前GaussDB以CN/DN行为是否一致作为下推标准,支持大部分immutable和stable函数的下推,以及特定场景少量volatile函数的下推。对于用户自定义函数,由于数据库无法知晓函数的行为,因为不知道函数的属性,因为默认是volatile和unshippable的。包含对应函数的语句将无法下推到DN执行。用户可以根据函数的行为,判断返回结果是否恒定,以及是否可以下推,设置对应的属性。具体的设置方法为:

(1)如果函数的返回结果是恒定的,比如数字计算函数,日期计算函数,则可以为其设置immutable属性。

(2)如果函数中使用了数据表,且数据表均是复制表的只读操作且不涉及事务操作(所以DN数据均相同,可以下推到一个DN上执行),则可以为其设置shippable属性。其余情况则还是不能下推,如果错误设置,会引发不可预知错误,因此需要慎重设置。

如果无法使函数下推,可以对语句进行改写,使不涉及函数的部分能够部分下推到DN执行。例如,对于以下SQL语句:

select unship_func() from t1 join t2 on t1.a=t2.a;
可以改写为:

select unship_func() from (select * from t1 join t2 on t1.a=t2.a);
这样,t1和t2 join的部分可以推到DN执行,只有unship_func()的计算是逐行在CN执行的,流程变化如下图所示,在join结果集较小的情况下,性能也可以得到明显的提升。

二.不支持重分布导致的坏味道
在share-nothing架构的分布式场景下,数据使用哈希分布在不同DN上,并且通过数据重分布使得中间结果均匀分布在各个DN上进行并行计算,进行执行查询的加速。所以在执行过程中,一直保持数据能够均匀分布在DN上,是保证性能的关键。

通常情况下,我们需要进行表的关联(Join)和聚集(Agg)操作,这就需要关联和聚集列能够支持重分布,从而进行灵活的重分布操作。在GaussDB(DWS)中,不支持重分布的类型主要有real和double类型,因此使用这两种类型进行操作时,将导致无法生成重分布的计划,在实际使用时要尽量避免。首先,在进行表定义时,要尽量避免使用这两种类型,使用numeric类型进行替代。同时,还要避免使用返回值为这两种类型的函数,进行关联和聚集运算,例如:ceil, floor, pow, sqrt, 以及一些分析类聚集函数如stddev_samp等。如果必须使用此类函数进行相关运算,需要在计算完对这些类型进行类型转换,转换成numeric类型。例如下面的语句:

select count(distinct ceil(a)) from t1;
由于ceil()返回值为double,原始生成的计划是不能下推的:

如果我们显式将ceil()转换成numeric类型,得到的下推计划如下:

三.数据类型转换导致的坏味道
数据库在进行不同列的比较、计算运算时,如果类型不同,需要进行类型转换。通常情况下,由优化先低的类型往优先级高的类型转换,字符串的优先级较数字较低,同时数字类型,精度低的会向精度高的转换。在应用中,经常遇到的是,字符串和数字进行比较,导致字符串需要转成数字进行比较操作。由于数据库的基本调优都是基于基表列的,数据转换后就会带来以下性能问题:

(1)无法使用索引。由于索引是基于列的排序构造的,字符串转换成数字后的排序性与字符串不一致(’2’与’12’,字符串’2’大,数字12大),故无法使用索引,对于返回数据量少的场景,使用全表扫描带来性能问题。

(2)无法进行分区剪枝。GaussDB(DWS)支持range分区,即根据分区键值的范围创建不同的分区。当需要进行分区键上的范围操作时,进行分区剪枝。而字符串转换成数字后,道理与(1)类似,打破排序性,导致分区剪枝失效。

(3)需要进行网络重分布操作。在进行表的关联,聚集操作时,如果涉及表分布键的操作,可以在本地并行进行。但如果涉及到类型转换,则hash值发生变化,需要进行网络重分布,增加了网络开销。

(4)估算不够准确,可能造成计划的性能问题。我们收集的统计信息都是基于基表列的,如果进行类型转换,则缺少转换后的统计信息,同样可能造成计划不准。

因此,在表设计之初就要把数据类型定义好,数字类型尽量使用整型或numeric(浮点型),尽量少使用字符串数据类型,除了与数字比较产生上述开销外,变长的字符串在处理时还产生了额外的空间申请释放,内存拷贝的开销,都是无形中性能的损耗。

四.全局性操作导致的坏味道
前面提到,GaussDB(DWS)分布式数据库的优势,就是利用多DN的资源进行并行计算,提高吞吐量。但有些SQL在这些方面不够注意,导致执行过程中由于全局性操作仅能在一个DN或CN上执行,造成了性能瓶颈。不能下推即属于这一类型问题。除了不能下推,本章节主要讨论在DN上进行全局操作导致的问题。客户场景遇到的主要问题,是需要对全量大数据量进行排序的问题,比如:windowagg函数没有partition by,但包含order by的问题。例如如下语句:

select * from (select ss_sold_date_sk, sum(ss_sales_price) over
(order by ss_sold_date_sk rows 2 preceding)
sum_2, sum(ss_sales_price) over (order by ss_sold_date_sk rows 5 preceding) sum_5 from store_sales), date_dim where ss_sold_date_sk=d_date_sk and d_year=2000 and d_moy=5 order by 1;
执行计划如下:

计划中第4层将所有DN的数据广播到一个DN上进行全局排序,并计算sum窗口函数的值,导致性能瓶颈。对于类似情况,在语义允许的情况下,尽量给窗口函数增加partition by的字段,这样分组计算时,GaussDB(DWS)可以将计算分配到不同的DN上进行,提高执行效率。

五.NestLoop类低效运算导致的坏味道
NestLoop是最简单的表关联手段,当然也是最低效的,每条元组之间都要进行匹配,数据量大的时候经常执行不出来。所以在GaussDB(DWS)中,我们经常使用HashJoin进行表的关联。但有些SQL语句从语义上决定,只能使用NestLoop的方式执行,导致性能问题。总结起来,有以下几方面:

(1)无等值关联条件场景
在GaussDB(DWS)中,我们推荐使用等值关联,这样可以使用HashJoin进行执行加速。但对于非等值关联条件,只能使用NestLoop的方式进行连接,同时需要将其中一个表进行Broadcast广播到所有DN进行。如果两个表都比较大,将导致性能瓶颈。例如如下:

select * from t1 join t2 on t1.a<t2.a;
计划如下:

类似的场景还有:<1> select * from t1 join t2 on 1=1;这个语句无关联条件,我们称为笛卡尔积关联,返回结果行数为t1和t2行数的乘积。<2> select * from t1 join t2 on t1.a=t2.a and t1.a=5;这个语句虽然有等值关联条件,但关联条件还有个过滤条件t1.a=5,所以实际上t1.a=t2.a=5,是在a=5过滤基础上的笛卡尔积。这类语句都会导致性能瓶颈。

(2)相关子查询场景
相关子查询,即子查询中需要依赖父查询的列值进行迭代计算的场景。例如如下语句:select (select ss_item_sk from store_sales where ss_item_sk<i_item_sk limit 1) from item;

计划如下:

在分布式框架下,为了保证每一行父查询元组均能够在子查询中迭代计算出结果,需要所有DN均维护一份子查询表的全局数据,在上面的计划中,子查询中的store_sales表进行了广播和物化,将数据存在所有DN上,见8层算子。第3层算子每一行数据均需要通过迭代子查询计算(第4层及之下的SubPlan 1)获得是否匹配的信息。这个计划执行非常慢且耗费资源,原因是:

a) 在DN节点非常多的分布式环境下,将数据广播到所有DN上进行物化,将导致网络资源和IO资源耗费巨大,同时大数据量的广播耗时也很长。

b) 对于父查询的每一行元组,均需要迭代计算子查询的值,类似于NestLoop的执行方式,效率极低。

以上两个问题带来的语句性能问题在各个客户现场均被识别,因此需要进行通用子链接提升转成join的查询重写来解决该问题。

在之前的版本中,我们已经支持了如下场景的子链接提升,即将子链接转化为join获得性能提升,这些子链接均出现在where条件里,包括:

a) IN/NOT IN的非相关子查询。

b) EXISTS/NOT EXISTS的等值相关子查询。

c) 包含Agg表达式的等值相关子查询。

d) 以上场景子查询的OR场景。

当然,目前我们还不支持目标列上出现相关子查询的场景,以及相关子查询中出现不等值比较的场景,需要首先识别出坏味道,进行语义等价的整改。

(3)Not in场景
当查询语句中包含NOT IN谓词时,例如如下语句:

select * from t t1 where t1.a not in (select b from t);
其计划如下图所示:

我们发现其走了NestLoop计划,原因是NOT IN的特殊语义,NULL值和任意值的比较结果是NULL,不是true,所以需要单独在Join条件上加上IS NULL的条件,导致等值关联变成非等值关联。一般客户场景下,使用NOT IN并不是为了处理NULL值,而是对NOT IN语义的误解,因此需要将两侧的NULL值去除(验证无NULL值,或建立临时表)后,使用下面两种方法解决性能问题:

a) 如果确认关联列没有NULL值,需要在关联列上建立NOT NULL约束,比如示例SQL语句,要在t表的a列和b列上均建立NOT NULL约束。

b) 可以将语句改写成NOT EXISTS,例如示例语句可以改写为:

select * from t t1 where not exists (select 1 from t where t.b=t1.a);
(4)IN list场景
IN list场景是指语句中包含大量常数的IN条件,例如如下语句:

select count(*) from customer where c_customer_sk in (1, 101, 201, 1001, 2001, 10001, 20001, 100001, 200001, 500001, 800001);
通常情况下,会生成基表扫描的查询计划,即对于customer表的每一条,需要检查c_customer_sk列是否会和IN list中的某个值匹配,匹配即返回该条元组。当IN list中的条件比较多时,匹配近似于NestLoop的操作。针对这种场景,GaussDB(DWS)实现了In list to Join的查询优化规则,根据代价估算,针对In list中的值较多的场景,生成Hash Join的计划,极大提升性能,如下图所示:

但代价估算存在估算不准的情况,对于列存表有min/max过滤,有时转成Join并不一定性能最好,因此我们增加了GUC参数:qrw_inlist2join_optmode,用户可以手动设置该参数的值进行调优,其值说明如下:

a) cost_base,即根据代价估算,默认值。

b) rule_base,强制使用转换成Join的优化规则。

c) 不小于2的整数,表示当In list中的常量个数不小于N时,使用转换成Join的优化规则。

六.冗余操作导致的坏味道
在GaussDB(DWS)场景中,经常会遇到一类SQL,其中存在大量的冗余操作,导致执行时进行了大量无效计算。这类语句的场景很多,需要根据performance数据详细分析,以下举几个例子简单看一下。

(1)语句中存在大量冗余case when语句的场景
例如如下语句,语句中有大量case when语句,大多数条件都是一样的,只是default值存在不同,但执行时,每个分支的case when均需要执行,导致时间成倍增加。

这种情况下需要对语句进行深入分析,根据语义进行等价改写。通常我们可以把case when加到过滤条件,分成多个子查询分别求值,或提取公共部分进行改写。经过优化后,消除了case when,性能得到了提升,修改后的语句如下所示。

再看一个例子,下图的例子中,会进行聚集函数的计算,但在下方的case when中频繁引用这些聚集函数,导致聚集函数计算多遍。

经过等价优化后,在子查询中仅计算一遍聚集函数,在父查询的case when中,直接使用计算好的聚集函数的值,避免多次计算,优化后的语句如下图所示。

(2)排序仅取部分数据的场景
在应用中,经常出现提取前若干条数据的场景。例如如下语句:

select a from (select a, row_number() over (order by a desc) rid from t1) where rid between and ;
即取按a排序后的从start到end的行数,对应的执行计划如下所示:

我们发现对所有的数据进行了排序,然后返回了前10条数据,数据量较大时,所有数据量全排将大量耗费时间。这种情况下,我们可以在子查询中加入limit语句,这样排序变为top N排序,减少了排序的时间,修改后的语句为:

select a from (select a, row_number() over (order by a desc) rid from t1
limit -+1 offset -1
) where rid between 1 and 10;
对应的计划变为:

(3)聚集操作顺序出现问题的场景
在GaussDB(DWS)场景中,通常为分析类应用,最终均需要进行聚集操作。通常聚集都是在语句最后进行,起到去重统计的效果。但是,如果去重前的重复值较多,但会显著影响关联的性能,如SQL语句:

select t1.c1, count(*) from t1 join t2 on t1.c1=t2.c1 group by t1.c1;
其计划如下图所示:

t1.c1和t2.c1有大量重复值,导致Join完之后行数激增,Join性能较差,因此需要将Agg下推到Join之前进行,通过提前的Agg操作减少Join结果的行数,修改后的语句为:

select t1.a, c1c2 from (select t1.a, count() c1 from t1 group by t1.a) t1 join (select t2.a, count(*) c2 from t2 group by t2.a) t2 on t1.a=t2.a;
我们称这个改写规则为Eager Agg,相反,如果改写后的语句,在子查询中的Agg去重效果不明显,但耗时较长,则可以做反向改写,去除冗余的Agg操作,我们称之为Lazy Agg。

以上只列出了客户场景经常出现的需要改写的语句,当然,要想深得SQL改写精髓,还是要深入了解GaussDB(DWS)实现原理,找到性能瓶颈,才能进行针对性的改写,起到事半功倍的效果。

本文分享自华为云社区《GaussDB(DWS)性能调优系列实战篇四:十八般武艺之SQL改写》,原文作者:两杯咖啡 。

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

分类: 华为云新鲜技术分享
标签: 算子, 数据库, SQL, GaussDB, 性能调优
好文要顶 关注我 收藏该文
华为云开发者社区
关注 - 0
粉丝 - 163
+加关注
00
« 上一篇: 让机器有温度:带你了解文本情感分析的两种模型
» 下一篇: 从标准到开发,解读基于MOF的应用模型管理
posted @ 2021-01-11 10:44 华为云开发者社区 阅读(85) 评论(0) 编辑 收藏
刷新评论刷新页面返回顶部
登录后才能发表评论,立即 登录 或 注册, 访问 网站首页
【推荐】阿里出品,对标P7!限时免费,七天深入MySQL实战营报名开启
【推荐】大型组态、工控、仿真、CADGIS 50万行VC++源码免费下载
【推荐】AWS携手博客园为开发者送福利,注册立享12个月免费套餐
【推荐】第一个NoSQL数据库,在大规模和一致性之间找到了平衡
【推荐】七牛云新老用户同享 1 分钱抢 CDN 1TB流量大礼包!
【推荐】了不起的开发者,挡不住的华为,园子里的品牌专区
【推荐】未知数的距离,毫秒间的传递,声网与你实时互动

相关博文:
· 华为高斯GaussDB100OLTP单机在RHEL7.6上的安装
· [SpakrSQL]常见SQL的SparkSQL实现
· SQL
· sparksql
· -sql-DDL
» 更多推荐…

最新 IT 新闻:
· 为什么贝壳、百度、蔚来值得放在一起研究?
· 能刷好评也能删差评 大众点评变味了?
· 前懂球帝CTO许立强日前正式加入字节跳动 曾担任百度主任架构师
· 82万的新车一月连撞两车 女司机称刹车失灵!特斯拉:你去告我们
· 比宝马5系贵2万多!秦力洪:蔚来ET7订单达预期 不会降价
» 更多新闻…
公告
昵称: 华为云开发者社区
园龄: 8个月
粉丝: 163
关注: 0
+加关注
< 2021年1月 >
日 一 二 三 四 五 六
27 28 29 30 31 1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31 1 2 3 4 5 6
搜索

常用链接
我的随笔
我的评论
我的参与
最新评论
我的标签
我的标签
华为云(215)
数据库(76)
数据(47)
AI(47)
物联网(43)
开发者(34)
ModelArts(34)
代码(32)
大数据(30)
容器(26)
更多
积分与排名
积分 - 141535
排名 - 5651
随笔分类 (668)
程序员之家(356)
华为云动态(71)
华为云新鲜技术分享(218)
云图说-复杂技术简单学(23)
随笔档案 (694)
2021年1月(24)
2020年12月(96)
2020年11月(89)
2020年10月(74)
2020年9月(98)
2020年8月(93)
2020年7月(100)
2020年6月(73)
2020年5月(47)
最新评论

  1. Re:一个敏捷教练成长必备的8项技能

–君君的喵爸
2. Re:基于区块链的政务平台设计探索
中国的区块链必定一地鸡毛,拭目以待!

–jsoft
3. Re:基于区块链的政务平台设计探索
任重而道远

–coder410621
4. Re:基于区块链的政务平台设计探索
{招聘} 同学,你是否还在为工作不开心而烦恼?😥 为没有升职机会而烦恼?😭为不能实现个人价值而烦恼?😣来阿里零售通烦恼全无!😏 阿里零售通,发展迅猛 💯 ,天猫小店就是属于我们的业务 。链接…
–砺能
5. Re:基于区块链的政务平台设计探索
1

–coder410621
阅读排行榜

  1. 【小白学YOLO】YOLOv3网络结构细致解析(3554)
  2. 8分钟为你详解React、Angular、Vue三大前端技术(2457)
  3. 这个应用魔方厉害了,让软件开发者效率提升10倍(2193)
  4. 鲲鹏迁移第一批吃螃蟹的人,践行技术国际化(1693)
  5. 7个获取访问者真实IP的方法,速学!!!(1643)
    评论排行榜
  6. 这个应用魔方厉害了,让软件开发者效率提升10倍(8)
  7. 3行!仅3行代码就能抓取B站(弹幕、评论、用户)数据(7)
  8. 科普干货|漫谈鸿蒙LiteOS-M与HUAWEI LiteOS内核的几大不同(6)
  9. 基于区块链的政务平台设计探索(4)
  10. Web前后端:如何分离,如何解耦?(4)
    推荐排行榜
  11. 7个获取访问者真实IP的方法,速学!!!(9)
  12. 高并发,你真的了解吗?(8)
  13. Git:改变世界的一次代码提交(5)
  14. 软件开发丨关于软件重构的灵魂四问(5)
  15. 技术解析丨C++元编程之Parser Combinator(4)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值