GaussDB(DWS)实践系列-SQL语句上线验收操作指导

SQL语句上线验收操作指导

一、摘要

为了最大限度提升应用开发人员的代码质量、减少业务SQL的性能风险、降低运维调优工作量，需要针对上线的SQL语句进行验收审核，并输出上线前验收Checklist，协助完成数据库开发规范自检。

二、DML语句验收CheckList

应用开发人员自检工作主要分为两个阶段，包括开发阶段和验收阶段：

开发阶段：开发人员严格按照设计规范进行代码开发，并通过验收checklist中的排查方法和标准对所负责模块的SQL进行自检。

验收阶段：业务人员进行系统全流程点击，根据第四章【附件3】的方法抓取TOP SQL，按照checklist排查方法验证是否符合验收标准，并汇总输出验收表，详细验收checklist如下所示：

三、DML语句验收标准

DML（Data Manipulation Language数据操作语言），用于对数据库表中的数据进行操作。如：插入、更新、查询、删除，此处的DML语句还包括视图定义、存储过程中的SQL语句。

1.

2.

标准1：执行下推&没有stream

分布式数据库架构下需最大限度的降低查询时节点之间的数据流动，以提升查询效率，因此SQL语句执行要实现stream算子为0。可通过第四章【附件1】方式查看SQL语句执行计划，从而判断执行计划是否下推，以及是否含有stream算子。

（一）判断执行计划是否下推

数据库后台根据第四章【附件3】的方法统计TOP SQL，如果TOP SQL中bxt_count列均为0，表示优化后没有不下推的SQL，验收通过。

详细说明：如果执行计划中有Data Node Scan节点，那么此执行计划为不可下推的执行计划；如果执行计划中有Streaming节点，那么计划是可以下推的。

下图执行计划信息(红色方框部分)可看出此SQL语句不能下推，这种场景需要分析并消除不下推的因素，具体可查看客户端连接的coordinator实例的日志信息辅助定位分析，并进行优化整改。

（二）判断执行计划是否含有stream算子

数据库后台根据第四章【附件3】的方法抓取TOP SQL，如果TOP SQL中stream_count列均为0，表示优化后SQL不含有stream算子，验收通过。

详细说明：执行计划中含有Streaming(type: Gather)，如果Streaming(type: Gather)下面的计划信息中存在Streaming字符串信息，那么执行计划含有stream算子，否则不含stream算子。

（1）如下是含有stream算子的计划(下面的红色方框部分含有Streaming字符串信息)，需要进行SQL改写消除Stream算子。

（2）如下是不含stream算子的执行计划(下面的红色方框部分不含Streaming字符串信息)。

标准2：没有关联子查询

数据库后台根据第四章【附件3】的方法抓取TOP SQL，如果TOP SQL中subplan_count列均为0，表示优化后没有关联子查询，验收通过。

详细说明：当SQL语句存在不能提升的关联子查询时，执行计划中会显示SubPlan关键字，如下图所示。

对于这种场景需要将关联子查询提升为跟父表的关联，消除SubPlan。

标准3：有效使用索引

关于索引，经常遇到的问题是缺乏索引、索引过滤效果不佳，这两类问题场景可通过第四章【附件2】方式查看SQL语句执行信息进行识别。

（一）缺乏索引

扫描命中率小于10%的SQL需要添加索引。如下执行信息中，从红色椭圆框可以看到表boss_t_fb_datasourceinfo过滤条件province = '610000' AND type = 'SELECT' AND year = 2019过滤掉2342条记录，最终输出0条记录，这种就是典型的缺乏索引的场景。

（二）索引过滤效果不佳

如下执行信息中，从红色椭圆框可以看到表epay_t_voucherreceive_log 经过索引index_pki_epay_voucherreceive_log_vouno扫描之后，还需要经过条件vtcode = '5106' AND voucherstatus = 1过滤掉118682个元组，最终输出393条元组，这种就是典型的索引过滤效果不明显的场景，需要进行索引优化。

（三）高效索引特征

高效索引一般会直接通过Index Cond命中绝大部分有效输出，体现在执行信息上为没有“Rows Removed by Filter:”输出，如下图所示。

或者“Rows Removed by Filter:”后面跟的数字远小于对应算子在A-rows列的数据，或者“Rows Removed by Filter:”后面跟的数字非常小（例如调优经验参考值，该数字小于100）。

标准4：避免冗余ORDER BY语句

冗余ORDER BY场景主要出现在含有string_agg函数的SQL语句中，如下图所示，括号内的order by动作需要提升在父查询中，否则子查询的排序结果不能传递给父查询，会导致string_agg函数的输出出现非预期结果。

标准5：SELECT FOR UPDATE语句必须在事物块中使用

for update语句功能是在当前事务中对指定行进行加锁，事务提交后释放。该语句必须在事务块或者存储过程中使用，且锁会持续到事务结束。如果在事务块或者存储过程外使用，SQL语句执行完成之后相关锁就会自动释放，无法实现预期的锁效果。

标准6：不能对复制表进行并发更新操作

分布式场景下业界通用准则是将字典表(又称维度表)建成复制表，使用复制表可减少参与计算的线程数和减少网络数据交互，以提升查询性能。从业务上角度分析，这类表的数据相对稳定，通常对这类数据进行只读操作，仅当基础信息发生变更时才会由业务维护人员对字典表进行修改。

因此从数据特征上讲，复制表不会发生并发更新动作，如果存在并发更新场景，就需要考虑复制表的设计是否合适。

标准7：递归调用语句必须存在递归终结条件

建议谨慎使用递归语句(WITH RECURSIVE)，使用WITH RECURSIVE的时候一定要注意递归调用的终止条件，确保递归可终止，否则会进入死循环，导致内存耗尽或者下盘文件撑爆磁盘空间，最终导致集群不可用。

如下语句中，如果存在满足多条记录的superguid和guid成环的场景(比如表gl_t_account_subject中满足条件code = '2011' AND acctsystypeguid = 'DCD3A09596DF4B339F3406107871A7B4' AND province = '610324' AND year = 2020的记录的superguid和guid相等)，就会导致递归调用陷入死循环，中间结果下盘导致磁盘空间被占满。

WITH   RECURSIVE result AS

(

SELECT

guid, code, name, superguid,   province, year

FROM gl_t_account_subject

WHERE code = '2011'

AND acctsystypeguid =   'DCD3A09596DF4B339F3406107871A7B4'

AND province = '610324' AND year = 2020

UNION ALL

SELECT

k.guid, k.code, k.name, k.superguid,   k.province, k.year

FROM gl_t_account_subject k

INNER JOIN result c

ON c.superguid = k.guid

WHERE k. acctsystypeguid = 'DCD3A09596DF4B339F3406107871A7B4'

AND k.province = '610324'

AND k.year = 2020

)

SELECT

guid, code, name

FROM   result

WHERE   province = '610324' AND year = 2020

ORDER   BY code ;

四、附件

附件1：查看执行计划

查看SQL执行计划时，仅需在SQL语句前面加上explain关键字，在数据库中执行就会输出SQL语句的执行计划(不会导致SQL语句的实际执行)。

explain

SELECT

*

FROM epay_vw_pay_voucher_bill

WHERE billno =   '6100001022204000007'

AND province = '610000' AND   year = 2019;

附件2：查看执行信息

添加explain关键字会显示SQL执行计划，但并不会实际执行sql语句，explain analyze会实际执行sql语句并返回执行信息。

查看执行信息时，需要在SQL语句前面加上explain analyze关键字，在数据库执行就会输出SQL语句的实际执行信息，每一个步骤为一个数据库运算符。

explain analyze

SELECT

*

FROM epay_vw_pay_voucher_bill

WHERE billno =   '6100001022204000007'

AND province = '610000' AND   year = 2019;

附件3：统计TOP SQL

为了保障系统稳定运行，SQL上线前都需要覆盖检查和优化，避免因不规范SQL导致系统运行卡顿或资源耗尽。因此，需要增强巡检和校验手段，识别出耗时、高频、后台临时线程较多等需优化的TOP SQL，并进行整改，测试后再上线使用，为测试充分性增加一道防护网。

本小节内容指导应用开发人员进行TOP SQL统计收集。

（一）开启SQL统计参数

开启SQL统计功能，然后进行业务连跑，数据库后台会自动记录SQL执行信息，业务连跑结束之后，查询active SQL视图，获取SQL执行信息，查找耗时、高频、后台临时线程较多等需优化的TOP SQL进行重点优化分析。

登陆任一数据节点，切换到omm用户，执行如下命令开启active SQL统计功能。

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_track = on"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_record = on"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_level = query"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_cost = 100"

gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_duration = 0"

（二）TOP SQL收集

（1）更新统计信息

在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。检测前需要进行全库统计信息收集。通过执行ANALYZE语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中，查询优化器会使用这些统计数据，以生成最有效的执行计划，以对postgres库执行analyze操作为例执行如下命令，其余数据库仅需修改-d后面的库名即可。

gsql -d postgres -p 25308 -c ‘analyze’

（2）统计表初始化

如果在检测前active SQL功能已经打开，需要执行以下动作清理历史SQL统计信息。

gs_ssh -c “gsql -d postgres -p 25308 -c ‘delete from gs_wlm_session_info’”

gsql -d postgres -p 25308 -c ‘vacuum full gs_wlm_session_info’

按照本章第1小节完成操作前准备，执行如下函数进行SQL检测，统计出TOP SQL。

（1）脚本准备

a.筛选subplan

登陆postgres数据库创建如下存储过程，统计执行计划中的subplan数量。

CREATE OR REPLACE FUNCTION public.subplan_count(text)

RETURNS integer

LANGUAGE sql

IMMUTABLE STRICT NOT FENCED

AS $function$

select ((length($1) -   length(replace($1, 'SubPlan', '')) )::int / length('SubPlan'))::int

$function$

;

b.筛选Stream算子

登陆postgres数据库创建如下存储过程，统计执行计划中的Stream算子数量。

CREATE OR REPLACE FUNCTION public.stream_count(text)

RETURNS integer

LANGUAGE sql

IMMUTABLE STRICT NOT FENCED

AS $function$

select ((length($1) -   length(replace(replace($1, 'Streaming(type: B', ''), 'Streaming(type: R',   ''))) / length('Streaming(type: B'))::int

$function$

;

C．筛选不下推SQL

登录postgres数据库创建如下存储过程，如果存储过程调用结果大于0，则该SQL为不下推SQL。

CREATE OR REPLACE FUNCTION public.bxt_count(text)

RETURNS integer

LANGUAGE sql

IMMUTABLE STRICT NOT FENCED

AS $function$

select ((length($1) -   length(replace($1, '_REMOTE_TABLE_QUERY_', '')) )::int /   length('_REMOTE_TABLE_QUERY_'))::int

$function$

;

（二）统计TOP SQL

登陆postgres数据库，通过sql语句统计Topsql列表。

select

substr(query, 1, 60) as   sub_query,                                --截取sql语句的1-60字段进行分组统计

dbname,                                             --数据库名

count(1) as count,                                      --sql调用频次

round(avg(duration), 2) as   avg_duration,                    --sql平均执行时间

public.stream_count(query_plan) as   stream_count,            --统计执行计划中stream算子数

public.subplan_count(query_plan) as   subplan_count,          --统计执行计划中subplan个数

public. bxt_count (query_plan) as bxt_count,                 --统计执行计划中不下推次数

max(queryid)   as query_id                                       --根据queryid查询具体SQL

from pgxc_wlm_session_info

where dbname in ('chw_pems')                                 --数据库名

and start_time > '2020-03-15   19:00:00'                         --开始时间

and finish_time < '2020-03-15   20:00:00'                        --结束时间

group by 1,2,5,6,7

having(stream_count > 0   or subplan_count > 0 or bxt_count>0)

order by stream_count desc;

SQL查询结果如下：

上述步骤截取sql语句的前60个字符，可根据queryid（图中max列信息） 查询完整的sql语句。

--使用上例sql查出来TOP SQL的queryid，查询完整的sql语句

select query from  pgxc_wlm_session_info where   queryid='xxxxx';

华为云社区论坛链接：https://bbs.huaweicloud.com/forum/forum-598-1.html

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。