摘要：那些會導緻執行效率低下的SQL語句及其執行方式，我們稱之為SQL中的“壞味道”。
◆ 什麼是SQL中的壞味道
SQL語言是關系型資料庫（RDB）的标準語言，其作用是将使用者的意圖翻譯成資料庫能夠了解的語言來執行。人類之間進行交流時，同樣的意思用不同的措辭會産生不同的效果。類似地，人類與資料庫交流資訊時，同樣的操作用不同的SQL語句來表達，也會導緻不同的效率。而有時同樣的SQL語句，資料庫采用不同的方式來執行，效率也會不同。那些會導緻執行效率低下的SQL語句及其執行方式，我們稱之為SQL中的“壞味道”。
下面這個簡單的例子，可以說明什麼是SQL中的壞味道。
圖1-a 用union合并集合
在上面的查詢語句中，由于使用了union來合并兩個結果集，在合并後需要排序和去重，增加了開銷。實際上符合dept_id = 1和dept_id > 2的結果間不會有重疊，是以完全可以用union all來合并，如下圖所示。
圖1-b 用union all合并集合
而更高效的做法是用or條件，在掃描的時候直接過濾出所需的結果，不但節省了運算，也節省了儲存中間結果所需的記憶體開銷，如下圖所示。
圖1-c 用or條件來過濾結果
可見完成同樣的操作，用不同的SQL語句，效率卻大相徑庭。前兩條SQL語句都不同程度地存在着“壞味道”。
對于這種簡單的例子，使用者可以很容易發現問題并選出最佳方案。但對于一些複雜的SQL語句，其性能缺陷可能很隐蔽，需要深入分析才有可能挖掘出來。這對資料庫的使用者提出了很高的要求。即便是資深的資料庫專家，有時也很難找出性能劣化的原因。
GaussDB在執行SQL語句時，會對其性能表現進行分析和記錄，通過視圖和函數等手段呈現給使用者。本文将簡要介紹如何利用GaussDB提供的這些“第一手”資料，分析和定位SQL語句中存在的性能問題，識别和消除SQL中的“壞味道”。
◆ 識别SQL壞味道之自診斷視圖
GaussDB在執行SQL時，會對執行計劃以及執行過程中的資源消耗進行記錄和分析，如果發現異常情況還會記錄告警資訊，用于對原因進行“自診斷”。使用者可以通過下面的視圖查詢這些資訊：
• gs_wlm_session_info
• pgxc_wlm_session_info
• gs_wlm_session_history
• pgxc_wlm_session_history
其中gs_wlm_session_info是基本表，其餘3個都是視圖。gs_開頭的用于檢視目前CN節點上收集的資訊，pgxc_開頭的則包含叢集中所有CN收集的資訊。各表格和視圖的定義基本相同，如下表所示。
表1 自診斷表格&函數字段定義
其中的query字段就是執行的SQL語句。通過分析每個query對應的各字段，例如執行時間，記憶體，IO，下盤量和傾斜率等等，可以發現疑似有問題的SQL語句，然後結合query_plan（執行計劃）字段，進一步地加以分析。特别地，對于一些在執行過程中發現的異常情況，warning字段還會以human-readable的形式給出告警資訊。目前能夠提供的自診斷資訊如下：
◇多列/單列統計資訊未收集
優化器依賴于表的統計資訊來生成合理的執行計劃。如果沒有及時對表中各列收集統計資訊，可能會影響優化器的判斷，進而生成較差的執行計劃。如果生成計劃時發現某個表的單列或多列統計資訊未收集，warning字段會給出如下告警資訊：
Statistic Not Collect:
schemaname.tablename(column name list) 此外，如果表格的統計資訊已收集過（執行過analyze），但是距離上次analyze時間較遠，表格内容發生了很大變化，可能使優化器依賴的統計資訊不準，無法生成最優的查詢計劃。針對這種情況，可以用pg_total_autovac_tuples系統函數查詢表格中自從上次分析以來發生變化的元組的數量。如果數量較大，最好執行一下analyze以使優化器獲得最新的統計資訊。
◇SQL未下推
執行計劃中的算子，如果能下推到DN節點執行，則隻能在CN上執行。因為CN的數量遠小于DN，大量操作堆積在CN上執行，會影響整體性能。如果遇到不能下推的函數或文法，warning字段會給出如下告警資訊：
SQL is not plan-shipping, reason : %s ◇Hash連接配接大表做内表
如果發現在進行Hash連接配接時使用了大表作為内表，會給出如下告警資訊：
PlanNode[%d] Large Table is INNER in HashJoin \"%s\" 目前“大表”的标準是平均每個DN上的行數大于100，000，并且内表行數是外表行數的10倍以上。
◇大表等值連接配接使用NestLoop
如果發現對大表做等值連接配接時使用了NestLoop方式，會給出如下告警資訊：
PlanNode[%d] Large Table with Equal-Condition use Nestloop\"%s\" 目前大表等值連接配接的判斷标準是内表和外表中行數最大者大于DN的數量乘以100，000。
◇資料傾斜
資料在DN之間分布不均勻，可導緻資料較多的節點成為性能瓶頸。如果發現資料傾斜嚴重，會給出如下告警資訊：
PlanNode[%d] DataSkew:\"%s\", min_dn_tuples:%.0f, max_dn_tuples:%.0f 目前資料傾斜的判斷标準是DN中行數最多者是最少者的10倍以上，且最多者大于100,000。
◇代價估算不準确
GaussDB在執行SQL語句過程中會統計實際付出的代價，并與之前估計的代價比較。如果優化器對代價的估算與實際的偏差很大，則很可能生成一個非最優化的計劃。如果發現代價估計不準确，會給出如下告警資訊：
"PlanNode[%d] Inaccurate Estimation-Rows: \"%s\" A-Rows:%.0f, E-Rows:%.0f 目前的代價由計劃節點傳回行數來衡量，如果平均每個DN上實際/估計傳回行數大于100,000，并且二者相差10倍以上，則認定為代價估算不準。
◇Broadcast量過大
Broadcast主要适合小表。對于大表來說，通常采用Hash+重分布（Redistribute）的方式效率更高。如果發現計劃中有大表被廣播的環節，會給出如下告警資訊：
PlanNode[%d] Large Table in Broadcast \"%s\" 目前對大表廣播的認定标準為平均廣播到每個DN上的資料行數大于100,000。
◇索引設定不合理
如果對索引的使用不合理，比如應該采用索引掃描的地方卻采用了順序掃描，或者應該采用順序掃描的地方卻采用了索引掃描，可能會導緻性能低下。
索引掃描的價值在于減少資料讀取量，是以認為索引掃描過濾掉的行數越多越好。如果采用索引掃描，但輸出行數/掃描總行數>1/1000，并且輸出行數>10000（對于行存表）或>100（對于列存表），則會給出如下告警資訊：
PlanNode[%d] Indexscan is not properly used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f 順序掃描适用于過濾的行數占總行數比例不大的情形。如果采用順序掃描，但輸出行數/掃描總行數<=1/1000，并且輸出行數<=10000（對于行存表）或<=100（對于列存表），則會給出如下告警資訊：
PlanNode[%d] Indexscan is ought to be used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f ◇下盤量過大或過早下盤
SQL語句執行過程中，因為記憶體不足等原因，可能需要将中間結果的全部或一部分轉儲的磁盤上。下盤可能導緻性能低下，應該盡量避免。如果監測到下盤量過大或過早下盤等情況，會給出如下告警資訊：
• Spill file size large than 256MB
• Broadcast size large than 100MB
• Early spill
• Spill times is greater than 3
• Spill on memory adaptive
• Hash table conflict
下盤可能是因為緩沖區設定得過小，也可能是因為表的連接配接順序或連接配接方式不合理等原因，要結合具體的SQL進行分析。可以通過改寫SQL語句，或者HINT指定連接配接方式等手段來解決。
使用自診斷視圖功能，需要将以下變量設成合适的值：
▲ use_workload_manager（設成on，預設為on）
▲ enable_resource_check（設成on，預設為on）
▲ resource_track_level（如果設成query，則收集query級别的資訊，如果設成operator，則收集所有資訊，如果設成none，則以使用者預設的log級别為準）
▲ resource_track_cost（設成合适的正整數。為了不影響性能，隻有執行代價大于resource_track_cost語句才會被收集。該值越大，收集的語句越少，對性能影響越小；反之越小，收集的語句越多，對性能的影響越大。）
執行完一條代價大于resource_track_cost後，診斷資訊會存放在記憶體hash表中，可通過pgxc_wlm_session_history或gs_wlm_session_history視圖檢視。
視圖中記錄的有效期是3分鐘，過期的記錄會被系統清理。如果設定enable_resource_record=on，視圖中的記錄每隔3分鐘會被轉儲到gs_wlm_session_info表中，是以3分鐘之前的曆史記錄可以通過gs_wlm_session_info表或pgxc_wlm_session_info視圖檢視。
◆ 發現正在運作的SQL的壞味道
上一節提到的自診斷視圖可以顯示已完成SQL的資訊。如果要檢視正在運作的SQL的情況，可以使用下面的視圖：
• gs_wlm_session_statistics
• pgxc_wlm_session_statistics
類似地，gs_開頭的用于檢視目前CN節點上收集的資訊，pgxc_開頭的則包含叢集中所有CN收集的資訊。兩個視圖的定義與上一節的自診斷視圖基本相同，使用方法也基本一緻。 通過觀察其中的字段，可以發現正在運作的SQL中存在的性能問題。
例如，通過“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”可以查詢目前運作的SQL中，已經執行時間最長的10個SQL。如果時間過長，可能有必要分析一下原因。
圖2-a 通過gs_wlm_session_statistics視圖發現可能hang住SQL
查到queryid後，可以通過query_plan字段檢視該SQL的執行計劃，分析其中可能存在的性能瓶頸和異常點。
圖2-b 通過gs_wlm_session_statistics視圖檢視目前SQL的執行計劃
再下一步，可以結合等待視圖等其他手段定位性能劣化的原因。
圖2-c 通過gs_wlm_session_statistics視圖結合等待視圖定位性能問題
另外，活動視圖pg_stat_activity也能提供一些目前執行SQL的資訊。
◆ Top SQL——利用統計資訊發現SQL壞味道
除了針對逐條SQL進行分析，還可以利用統計資訊發現SQL中的壞味道。另一篇文章“Unique SQL特性原理與應用”中提到的Unique SQL特性，能夠針對執行計劃相同的一類SQL進行了性能統計。
與自診斷視圖不同的是，如果同一個SQL被多次執行，或者多個SQL語句的結構相同，隻有條件中的常量值不同。這些SQL在Unique SQL視圖中會合并為一條記錄。是以使用Unique SQL視圖能更容易看出那些類型的SQL語句存在性能問題。
利用這一特性，可以找出某一名額或者某一資源占用量最高/最差的那些SQL類型。這樣的SQL被稱為“Top SQL”。例如，查找占用CPU時間最長的SQL語句，可以用如下SQL：
select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。 Unique SQL的使用方式詳見https://bbs.huaweicloud.com/blogs/197299。
◆ 結論
發現SQL中的壞味道是性能調優的前提。GaussDB對資料庫的運作狀況進行了SQL級别的監控和記錄。這些打點記錄的資料可以幫助使用者發現可能存在的異常情況，“嗅”出潛在的壞味道。從這些資料和提示資訊出發，結合其他視圖和工具，可以定位出壞味道的來源，進而有針對性地進行優化。
點選關注，第一時間了解華為雲新鮮技術~