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

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