十八般武艺玩转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 <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改写》，原文作者：两杯咖啡 。
