关于数据库:十八般武艺玩转GaussDBDWS性能调优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 改写》，原文作者：两杯咖啡。

点击关注，第一工夫理解华为云陈腐技术~