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小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。