摘要：本文将零碎介绍在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 <start> and <end>;复制代码即取按a排序后的从start到end的行数，对应的执行打算如下所示：
咱们发现对所有的数据进行了排序，而后返回了前10条数据，数据量较大时，所有数据量全排将大量消耗工夫。这种状况下，咱们能够在子查问中退出limit语句，这样排序变为top N排序，缩小了排序的工夫，批改后的语句为：
select a from (select a, row_number() over (order by a desc) rid from t1 limit <end>-<start>+1 offset <start>-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, c1*c2 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改写》，原文作者：两杯咖啡。
点击关注，第一工夫理解华为云陈腐技术~