細說GaussDB(DWS)的2種查詢優化技術

本文分享自華為雲社區《GaussDB(DWS)查詢優化技術大揭秘》，作者：胡辣湯。大數據時代，數據量呈爆髮式增長，經常面臨百億、千億數據查詢場景，當數據倉庫數據量較大、SQL語句執行效率低時，數據倉庫性能會受到影響。本期《GaussDB(DWS)查詢優化技術大揭秘》的主題直播中，我們邀請到華為雲 ...

本文分享自華為雲社區《GaussDB(DWS)查詢優化技術大揭秘》，作者：胡辣湯。

大數據時代，數據量呈爆髮式增長，經常面臨百億、千億數據查詢場景，當數據倉庫數據量較大、SQL語句執行效率低時，數據倉庫性能會受到影響。本期《GaussDB(DWS)查詢優化技術大揭秘》的主題直播中，我們邀請到華為雲GaussDB(DWS)技術佈道師王躍老師，深入講解在GaussDB(DWS)中如何進行表結構設計，如何進行SQL優化，如何查找慢SQL和高頻SQL。

一、認識優化器

資料庫的優化器基本上有2種模式，基於規則的優化器(rbo)和基於成本的優化器(cbo)。當前比較通用的是CBO模型的優化器。

基於成本的優化器(cbo，cost based optimizer)：該優化器通過根據優化規則對關係表達式進行轉換，生成多個執行計劃，然後CBO會根據統計信息(Statistics)和代價模型(Cost Model)計算各種可能“執行計劃”的“代價”，即COST，從中選用COST最低的執行方案，作為實際運行方案。

優點：可以自動適應表數據量變化，計算量發生變化，自動調節，選擇較優的執行計劃。

缺點：依賴於COST計算模型重要的影響因數：統計信息，需要給優化器提供準確的統計信息，才能做出好的執行計劃。

SQL執行流程

執行計劃是查詢語句在資料庫中執行過程的描述，執行計劃描述了SQL引擎為執行SQL語句進行的操作，分析SQL語句相關的性能問題或僅僅質疑查詢優化器的決定時，必須知道執行計劃，所以執行計劃常用於SQL調優。要讀懂執行計劃，首先要知道資料庫執行運算元的概念：

二、調優流程

當前資料庫調優主要分為靜態調優和動態調優兩種，靜態調優是根據硬體資源和客戶的業務特征確定集群部署方案、表定義。執行態調優（動態調優）是根據SQL語句執行的實際情況採取針對性干預SQL執行計劃的方式來提升性能。

調優流程

三、靜態調優

本次直播主要從表定義角度介紹靜態調優的5種常用方法，幫助用戶根據業務場景選擇合適的調優方式，提高SQL語句的查詢性能。

3.1 表定義：集群部署有相關工作人員協助，用戶只需要關註表定義創建策略。GaussDB資料庫中，分散式框架下，數據分佈在各個DN上，一個或者幾個DN的數據存在一塊物理存儲設備上。好的表定義可以達到以下幾個目的：

表數據均勻分佈在各個DN上，防止單個DN數據過多導致集群有效容量下降。
表Scan壓力均勻分散在各個DN上，避免單DN的Scan壓力過大，形成Scan的單節點瓶頸。
減少掃描數據數據量，通過分區機制實現。
儘量減少隨機IO，通過聚簇/局部聚簇可以實現。
儘量避免數據shuffle，減小網路壓力。建議選擇join-condition或者group by列為分佈列。

3.2 存儲類型：進行資料庫設計時，表設計上的一些關鍵項將嚴重影響後續整庫的查詢性能，表設計對數據存儲也有影響，好的表設計能夠減少I/O操作及最小化記憶體使用，進而提升查詢性能。

行、列存選擇依據

3.3 分佈列：

分佈列決定了數據按哪一列拆分到各個DN上，好的分佈列會使用數據在各個節點上分佈均勻，減少數據重分發，充分發揮各個節點的性能。當前支持如下3種分佈方式：

複製 (Replication)

集群中每個DN實例上都有一份全量表數據；
Join操作可減小重分佈造成的網路開銷；
存在數據冗餘；
適用於小表、維表。

哈希 (Hash) -- 8.1.3之前預設分佈方式

數據通過Hash方式散列到集群的所有DN實例；
讀寫數據可充分利用各個節點IO資源，提升讀寫速度；
適用於數據量大的表。

輪詢 (RoundRobin) -- 8.1.3開始之後預設分佈方式

數據通過輪詢方式發放到集群內所有DN實例；
讀寫數據可充分利用各個節點IO資源，提升讀寫速度；
適用於數據量大的表，且各列都有嚴重傾斜的表。

如何選擇最佳分佈列：

列值應比較離散，以便數據能夠均勻分佈到各個DN，通常選擇表的主鍵為分佈列；
儘量不要選取存在常量等值過濾條件，避免DN剪枝後Scan集中到一個DN上；
選擇查詢中的連接條件為分佈列，以便Join任務能夠下推到DN中執行，且減少DN間的通信數據量；
根據上述原則儘量根據業務特征選擇hash分佈方式，無法確定時可以選擇roundbobin分佈。

3.4 局部聚簇：列存儲下一種通過min/max稀疏索引實現基表快速掃描的一種索引技術。

適用場景：
業務特征：大表大批量數據導入，每次導入數據量遠大於DN數 * 6W；
基表存在大量形如col op Const約束，其中col為列名，const為常量值，op為操作符 =、>、>=、<=、<；
選用選擇度比較高的簡單表達式的列上建pck。
選取原則：
受基表的簡單表達式約束。一般形如col op const,其中，col為列名，op為操作符=、>、>=、<=、<, const為常量值；
儘量選用選擇度比較高（可以過濾掉更多數據）的簡單表達式的列；
儘量把選擇度低的約束col放在局部聚簇中的前面；
儘量把枚舉類型的列放在PCK中的前面。

3.5 分區表：把邏輯上的一個大表按照某種策略分成幾塊物理塊進行存儲時，邏輯上的大表稱為分區表，每個物理塊則稱為一個分區。在查詢時，通過分區剪枝技術儘可能減少底層數據掃描。

適用場景：
數據規模：大表；
業務特征：通過剪枝縮小查詢範圍。
分區鍵的選擇：可以將數據均勻映射到各個分區的列，常見的分區鍵一般是時間列。

四、動態調優

動態調優，即執行態調優，分析其性能劣化點，加以優化的手段。包括如下3個步驟：

步驟1：收集SQL中涉及到的所有表的統計信息。

在資料庫中，統計信息是規劃器生成計劃的源數據。沒有收集統計信息或統計信息陳舊往往會造成執行計劃嚴重劣化，從而導致性能問題。從經驗數據來看，10%左右性能問題是因為沒有收集統計信息。

步驟2：通過查看執行計劃查找原因。

如果SQL長時間運行未結束，通過EXPLAIN命令查看執行計劃，進行初步定位。

如果SQL可執行結束，則執行explain performance命令收集詳細計劃，或者藉助日誌，進一步分析性能劣化點，比如，語句不下推、數據下盤，或數據分佈造成IO瓶頸點等等。

步驟3：針對分析得出的劣化原因，採取相應措施進行優化改進，從而提高性能。

4.1統計信息：GaussDB(DWS)的優化器是典型的基於代價的優化 (Cost-Based Optimization，簡稱CBO)。在這種優化器模型下，資料庫根據表的元組數、欄位寬度、NULL記錄比率、distinct值、MCV值（Most Comman Value）、HB值（直方圖，數據分佈概率區間）等表數據特征，結合代價計算模型，通過代價估算輸出估算的最優執行計劃，這些特征值就是稱之為統計信息。統計信息是查詢優化的核心輸入，準確的統計信息將幫助優化器選擇最合適的查詢計劃。

沒有收集統計信息或在統計信息陳舊往往會造成執行計劃嚴重劣化，從而導致性能問題。ANALYZE語句可以收集與資料庫中表內容相關的統計信息，統計結果存儲在系統表PG_STATISTIC中。查詢優化器會使用這些統計信息，生成最有效的執行計劃。

4.2 不下推分析：分散式集群相對於單機最顯著的優勢在於並行分散式計算能力，通過多節點、多實例並向計算，充分利用系統資源，提升查詢性能。優化器在分散式框架下有三種執行計劃規劃策略：下推語句計劃、分散式計劃、不下推計劃，一般來說不下推計劃會因為不能充分利用並行計劃能力而導致比較嚴重的性能問題。

下推語句計劃：指直接將查詢語句從CN發送到DN進行執行，然後將執行結果返回給CN。一般只有簡單的查詢語句才會走這種計劃。
分散式計劃：CN生成計劃樹，再將計劃樹發送給DN進行執行，DN執行完畢後把結果返回到CN。
不下推計劃：上述兩種方式都不可行時，優化器將部分查詢（多為基表掃描語句）下推到DN進行執行，獲取中間結果到CN，然後CN執行剩下的部分。

執行語句不下推通常是因為語句中含有shippable屬性為false的函數的語句。不下推問題的定位手段通常有兩種，通過日誌可以看到類似“”SQL can’t be shipped.“的LOG以及對不下推原因的初步信息。

4.3 Performance分析：explain performance可以收集詳細執行信息，並從中分析可能的性能問題，從而做出針對性優化。

4.4 Scan性能優化：Scan性能提升策略主要有2個，減少實際IO和分散Scan壓力到各個DN上。

4.5 Join性能優化：GaussDB(DWS)表連接（Join）是根據特定規則從兩個其他表（真實表活生成表）中派生出結果集。語法上，兩表做連接操作時需要引入Join運算元。Join性能提升策略有2個，選擇高效的Join方式和選擇合適的內外表。

4.6 SQL改寫：SQL改寫主要涉及相關子鏈接改寫、Join條件改寫、NOT IN改寫。

相關子鏈接改寫：當子查詢和子鏈接性能較差時，大部分場景，可提升為Join進行優化；小部分場景，需要用戶改寫SQL進行優化。改寫策略：在語義等價前提下，將子鏈接、子查詢的查詢語句提升到外層查詢進行關聯查詢
Join條件改寫：等值Join條件的Join列增加非空過濾條件，可以減小參與連接運算的數據量。
NOT IN改寫：當子鏈接輸出列上不存在NULL值，或者邏輯判斷語義上不需要比較NULL值時需要進行NOT IN改寫。優化原理：只輸出WHERE條件為true的結果、NULL和任何值的比較操作均為NULL、NULL和bool類型的邏輯運算。