GaussDB(DWS)实践系列-SQL语句上线验收操作指导
SQL语句上线验收操作指导
一、摘要
为了最大限度提升应用开发人员的代码质量、减少业务SQL的性能风险、降低运维调优工作量，需要针对上线的SQL语句进行验收审核，并输出上线前验收Checklist，协助完成数据库开发规范自检。
二、DML语句验收CheckList
应用开发人员自检工作主要分为两个阶段，包括开发阶段和验收阶段：
开发阶段：开发人员严格按照设计规范进行代码开发，并通过验收checklist中的排查方法和标准对所负责模块的SQL进行自检。
验收阶段：业务人员进行系统全流程点击，根据第四章【附件3】的方法抓取TOP SQL，按照checklist排查方法验证是否符合验收标准，并汇总输出验收表，详细验收checklist如下所示：
三、DML语句验收标准
DML（Data Manipulation Language数据操作语言），用于对数据库表中的数据进行操作。如：插入、更新、查询、删除，此处的DML语句还包括视图定义、存储过程中的SQL语句。
标准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、准备工作
（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’
2、获取TOP SQL列表
按照本章第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
- 点赞
- 收藏
- 关注作者
评论（0）