詳解GaussDB(DWS) explain分布式執(zhí)行計劃的示例

摘要：本文主要介紹如何詳細(xì)解讀GaussDB(DWS)產(chǎn)生的分布式執(zhí)行計劃，從計劃中發(fā)現(xiàn)性能調(diào)優(yōu)點。前言

執(zhí)行計劃(又稱解釋計劃)是數(shù)據(jù)庫執(zhí)行SQL語句的具體步驟，例如通過索引還是全表掃描訪問表中的數(shù)據(jù)，連接查詢的實現(xiàn)方式和連接的順序等。如果 SQL 語句性能不夠理想，我們首先應(yīng)該查看它的執(zhí)行計劃。本文主要介紹如何詳細(xì)解讀GaussDB(DWS)產(chǎn)生的分布式執(zhí)行計劃，從計劃中發(fā)現(xiàn)性能調(diào)優(yōu)點。

1、執(zhí)行算子介紹

要讀懂執(zhí)行計劃，首先要知道數(shù)據(jù)庫執(zhí)行算子的概念：

下面重點介紹下基于sharing nothing的分布式計劃中最重要的一類算子——STREAM算子

三種類型的stream算子

1)Gather Stream(N:1) – 每個源結(jié)點都將其數(shù)據(jù)發(fā)送給目標(biāo)結(jié)點

2)Redistribute Stream(N:N) – 每個源節(jié)點將其數(shù)據(jù)根據(jù)連接條件計算Hash值，根據(jù)重新計算的Hash值進(jìn)行分布，發(fā)給對應(yīng)的目標(biāo)節(jié)點

3)Broadcast Stream(1:N) – 由一個源節(jié)點將其數(shù)據(jù)發(fā)給N個目標(biāo)節(jié)點

其中1）主要用于CN與DN間的數(shù)據(jù)交換，2）與3）主要用于DN間的數(shù)據(jù)交換

2、EXPLAIN用法

SQL執(zhí)行計劃是一個節(jié)點數(shù)，顯示執(zhí)一條SQL語句執(zhí)行時的詳細(xì)步驟。每一個步驟是一個數(shù)據(jù)庫運算符，也叫作一個執(zhí)行算子。使用explain命令可以查看優(yōu)化器為每個查詢生成的具體執(zhí)行計劃。

1) EXPLAIN的語法

其中，option中COSTS與NODES的默認(rèn)值為ON，其他參數(shù)默認(rèn)為OFF。

說明：

a) EXPLAIN + QUERY并不會真正執(zhí)行，只會將計劃打印出來，指定option中的ANALYZE可以進(jìn)行實際執(zhí)行

b) PERFORMANCE 選項默認(rèn)會將所有的選項置為ON，即顯示所有的執(zhí)行信息。

c) CPU/BUFFER/DETAIL 選項依賴于ANALYZE，只有ANALYZE置為ON的時候，才能使用這幾個選項。

d) DETAIL選項用來控制輸出，DETAIL 置為ON時，會顯示各個DN上具體的執(zhí)行信息；DATAIL 置為OFF時，顯示所有DN的匯總信息，即最大最小值信息。

2) EXPLAIN顯示格式

GaussDB中提供了兩種顯示格式（normal/pretty），通過設(shè)置參數(shù)explain_perf_mode進(jìn)行控制。其中，normal格式為默認(rèn)的顯示格式。

normal格式如下：

pretty格式如下：

改進(jìn)后的顯示格式，層次清晰，計劃包含了plan node id，性能分析會更加簡單直接。

使用之前可以使用show explain_perf_mode;來查看當(dāng)前數(shù)據(jù)庫使用的顯示風(fēng)格。

同時可以使用set explain_perf_mode=pretty/normal;來設(shè)置輸出的格式。

3、示例計劃解讀（每個算子資源消耗、耗時等等）

1） 四中常見類型計劃

建表語句：

a) FQS計劃，完全下推，下發(fā)query

兩表JOIN，且其連接條件為各表的分布列，在關(guān)閉stream算子的情況下，CN會直接將該語句發(fā)送至各DN執(zhí)行，最后結(jié)果在CN匯總。

b) 非FQS計劃，部分語句下推

兩表JOIN，且連接條件中包含非分布列，此時在關(guān)閉stream算子的情況下，CN會將基表掃描語句下發(fā)至各DN，然后在CN上進(jìn)行JOIN。

c) Stream計劃，DN之間無數(shù)據(jù)交換

兩表JOIN，且連接條件為各表的分布列，因此各DN無需數(shù)據(jù)交換。CN生成stream計劃后，將除Gather Stream的計劃下發(fā)給DN執(zhí)行，在各個DN上進(jìn)行基表 掃描，并進(jìn)行哈希連接后，發(fā)送給CN。

d) Stream計劃，DN之間存在數(shù)據(jù)交換

兩表JOIN，且連接條件包含非分布列，在開啟stream算子的情況下，會生成stream計劃，其DN間存在數(shù)據(jù)交換。此時對于tt02表，會在各DN進(jìn)行基表掃描，掃描后會通過Redistribute Stream算子，按照J(rèn)OIN條件中的tt02.c1進(jìn)行哈希計算后重新發(fā)送給各DN，然后在各DN上做JOIN，最后匯總到CN。

2) explain performance詳解

a) 執(zhí)行計劃

•id：執(zhí)行算子節(jié)點編號。

•operation：具體的執(zhí)行節(jié)點算子名稱。

•A-time:各DN相應(yīng)算子執(zhí)行時間，[]中左側(cè)為最小值，右側(cè)為最大值，包括下層算子執(zhí)行時間。

•A-rows：相應(yīng)算子輸出的全局總行數(shù)。

•E-rows：每個算子估算的輸出行數(shù)。

•Peak Memory：各DN相應(yīng)算子消耗內(nèi)存峰值，[]中左側(cè)為最小值，右側(cè)為最大值。

•E-memory：DN上每個算子估算的內(nèi)存使用量，只有DN上執(zhí)行的算子會顯示。某些場景會在估算的內(nèi)存使用量后使用括號顯示該算子在內(nèi)存源充足下可以自動擴(kuò)展的內(nèi)存上限。

•E-width：每個算子輸出元組的估算寬度。

•E-costs：每個算子估算的執(zhí)行代價。

b) 謂詞過濾

顯示對應(yīng)執(zhí)行算子節(jié)點的過濾條件

c) 內(nèi)存使用

主要顯示CN的最大內(nèi)存用量、DN最大內(nèi)存用量、各算子的最大內(nèi)存用量、各算子預(yù)估內(nèi)存用量、Stream線程的啟動以及收發(fā)時間。

d) Targetlist Information

各個算子對應(yīng)的輸出目標(biāo)列信息。

e) DN信息

各算子的執(zhí)行時間、Buffer、CPU信息

f) 自定義信息

CN與DN之間的建連信息、DN與DN之間的建連信息。

g) 匯總信息

DN執(zhí)行器開始時間，[min_node_name, max_node_name] : [min_time, max_time]DN執(zhí)行器結(jié)束時間，[min_node_name, max_node_name] : [min_time, max_time]Remote query poll time：接收結(jié)果時用于poll等待的時間CN執(zhí)行器開始、運行及結(jié)束時間網(wǎng)絡(luò)流量，stream算子發(fā)送的數(shù)據(jù)量優(yōu)化器執(zhí)行期時間查詢ID總執(zhí)行時間

h) 執(zhí)行時間介紹

每個算子的執(zhí)行信息都包含三個部分：

其中：

dn_6001_6002/dn_6003_6004 表示具體執(zhí)行的節(jié)點信息，括號中的信息是實際的執(zhí)行信息actualtime=0.013..2290.971 表示實際的執(zhí)行時間

第一個數(shù)字表示執(zhí)行時進(jìn)入當(dāng)前算子到輸出第一條數(shù)據(jù)所花費的時間

第二個數(shù)字為輸出所有數(shù)據(jù)的總執(zhí)行時間

注意：在整個計劃中，除了葉子節(jié)點的執(zhí)行時間是算子本身的執(zhí)行時間，其余算子的執(zhí)行時間均包含子節(jié)點的執(zhí)行時間。

在該計劃中，7號節(jié)點和9號節(jié)點為葉子節(jié)點，其余節(jié)點均為非葉子簡介。1號節(jié)點時頂層節(jié)點，所以該節(jié)點的執(zhí)行時間就可以作為整個查詢的執(zhí)行時間。

rows=2001550 表示當(dāng)前算子輸出數(shù)據(jù)為2001550行；loops=1 表示當(dāng)前算子的只執(zhí)行了一次，而對于分區(qū)表的掃描（7號節(jié)點）來說：

該層掃描算子的loops為7，對于分區(qū)表，每一個分區(qū)表的掃描就是一次完整的掃描操作，當(dāng)切換到下一個分區(qū)的時候，又是一次新的查詢操作，查詢該表定義如下：

Inventory表有7個分區(qū)，所以就執(zhí)行了7次表掃描操作，因此loops=7。

i) CPU信息介紹

每個算子執(zhí)行的過程都有CPU信息，其中cyc代表的是CPU的周期數(shù)，ex cyc表示的是當(dāng)前算子的周期數(shù)，不包含其子節(jié)點；inc cyc是包含子節(jié)點的周期數(shù)；ex row是當(dāng)前算子輸出的數(shù)據(jù)行數(shù)；ex c/r則是ex cyc/ex row得到的每條數(shù)據(jù)所用的平均周期數(shù)。

j) Buffer信息介紹

buffers顯示緩沖區(qū)信息，包括共享塊和臨時塊的讀和寫。

共享塊包含表和索引，臨時塊在排序和物化中使用的磁盤塊。上層節(jié)點顯示出來的塊數(shù)據(jù)包含了其所有子節(jié)點使用的塊數(shù)。

Buffers涉及的參數(shù)有兩種，分別為：shared和temp，及shared hit/read/dirtied/written以及temp read/write

Hit blocks：代表從磁盤里面讀到的數(shù)據(jù)塊數(shù)

Dirtied blocks：代表當(dāng)前查詢中被修改了的并且此前未被修改的數(shù)據(jù)塊數(shù)

Written blocks：代表當(dāng)前線程將shared bufer里被修改的數(shù)據(jù)寫回到磁盤的塊數(shù)

k) 執(zhí)行內(nèi)存

其中：

Peak Memory：5KB 表示當(dāng)前算子實際執(zhí)行時使用的峰值內(nèi)存；

Estimate Memory：1024MB 表示預(yù)估的內(nèi)存，為優(yōu)化器給出的預(yù)估值。

l) 其他執(zhí)行信息

（1）sort 算子，會顯示排序信息

Sort Method代表排序的方法，包括quicksort（快排）和disksort（外排）?？炫偶磧?nèi)存夠用時，所有的排序操作均在內(nèi)存中完成，外排說明當(dāng)前可用內(nèi)存不足，需要下盤。

（2）hashjoin算子

Buckets：代表hash表中實際使用的桶的個數(shù)

Batches：代表hashjoin中實際分塊的數(shù)量。如果Batches=1，則說明所有的數(shù)據(jù)全在內(nèi)存中，沒有下盤操作；反之則說明有下盤操作，Batches - 1代表臨時文件的個數(shù)。

Memory Usage：就是hashjoin中內(nèi)存的使用情況

（3）hashagg算子

如果發(fā)生數(shù)據(jù)下盤，會有File Num：512信息，顯示臨時文件的個數(shù)。

（4）stream算子

stream算子的會統(tǒng)計當(dāng)前算子處理數(shù)據(jù)的字節(jié)數(shù)，其從子線程獲取數(shù)據(jù)的時間（poll time）以及處理數(shù)據(jù)的時間（Deserialize Time）。

stream算子的子節(jié)點會統(tǒng)計發(fā)送端的時間信息，如下：

發(fā)送時間Send time，排隊時間Wait Quota time， OS發(fā)送時間以及數(shù)據(jù)處理的時間。

3) explain 調(diào)優(yōu)示例

一個查詢語句要經(jīng)過多個算子步驟才會輸出最終的結(jié)果。由于個別算子耗時過長導(dǎo)致整體查詢性能下降的情況比較常見。這些算子是整個查詢的瓶頸算子。通用的優(yōu)化手段是EXPLAIN ANALYZE/PERFORMANCE命令查看執(zhí)行過程的瓶頸算子，然后進(jìn)行針對性優(yōu)化。

基表掃描時，對于點查或者范圍掃描等過濾大量數(shù)據(jù)的查詢，如果使用SeqScan全表掃描會比較耗時，可以在條件列上建立索引選擇IndexScan進(jìn)行索引掃描提升掃描效率。如下示例：

上述例子中，全表掃描返回3360條數(shù)據(jù)，過濾掉大量數(shù)據(jù)，在sssolddate_sk列上建立索引后，使用IndexScan掃描效率顯著提高，從960毫秒提升到8毫秒。

結(jié)語：

在調(diào)優(yōu)過程中，熟練使用explain并能分析各部分?jǐn)?shù)據(jù)結(jié)果是非常重要的。本文中僅僅介紹了大多數(shù)字段的含義以及根據(jù)explain結(jié)果進(jìn)行調(diào)優(yōu)的一個小示例，還可以與plan hint結(jié)合使用找出執(zhí)行的最佳路徑，也可以定位傾斜程度等等。

到此這篇關(guān)于詳解GaussDB(DWS) explain分布式執(zhí)行計劃的文章就介紹到這了,更多相關(guān)GaussDB(DWS)分布式執(zhí)行計劃內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！