如何更好的解讀QianBase MPP資料庫執行計劃

1.執行計劃簡介

執行計劃（execution plan，也叫查詢計劃或者解釋計劃）是資料庫執行SQL語句的具體步驟。簡單而言，SQL的執行計劃類似於解數學題中不同的方法，好的最佳化器能找到良好的執行方法，並且選擇該方法將SQL執行完。當然，部分AI最佳化器還能做到執行計劃動態調整，能夠隨著執行過程中資料量以及資料分佈動態調整更合適的執行計劃然後重新執行。

QianBase MPP資料庫採用CBO（Cost-Based Optimization，基於代價）在不同執行計劃中選擇成本（Cost）較低的執行計劃，然後使用該計劃將SQL執行完並返回給客戶端。

注意：

1)首先最佳化器生成不同種類執行計劃

2)然後選擇代價 較低執行計劃

執行計劃所做的事包括（但不限於）：

• 掃描方式（索引掃描還是全表掃描）

• Join的方式（hash還是merge還是nested loop）

• Join的順序

• Join的實現（廣播或者重分佈）

• 聚合的方式

在檢視執行計劃時，QianBase MPP同大部分資料庫一樣，提供了explain命令，幫助使用者分析資料庫執行計劃如何執行、是否合理等等。

2.explain的使用

2.1explain介紹

解釋計劃是一份報告，它詳細描述了QianBase MPP資料庫最佳化器確定的執行查詢要遵循的步驟。計劃是一棵由結點構成的樹，應該從底向上閱讀，每一個結點表示計劃中的一個步驟，並且都會將其結果傳遞給其直接上層結點，單個結點對應的那行資訊標識了在該步驟中執行的操作，例如：

• 掃描

• 連線

• 聚集

• 排序

結點還標識了用於執行該操作的方法。例如，掃描操作的方法可能是順序掃描或者索引掃描，而連線操作可以執行雜湊連線或者巢狀迴圈連線。

2.2explain示例

下面是一個簡單查詢的解釋計劃。我們以TPCH測試的query3為例

執行計劃如下：

注意：

1)檢視執行計劃時，請先找到對應slice關鍵字；

2)每個slice包含自身行以及縮排行；

3)上述紅色框表示slice3、黃色框表示slice2、藍色框表示slice1。

2.3explain閱讀

我們自下而上地閱讀執行計劃：

• slice3

  對orders表進行順序掃描,過濾出o_orderdate < '1995-03-15'的資料，然後以o_custkey為分佈鍵進行redistribute motion以實現下一步與表customer的關聯（關聯條件：c_custkey = o_custkey）。

• slice2

  順序掃描customer表，過濾出c_mktsegment = 'BUILDING'的資料，與slice3的結果進行hash join，然後將這裡的join結果按o_orderkey為分佈鍵進行redistribute motion，以實現下一步與表lineitem的關聯（關聯條件：l_orderkey = o_orderkey）。

• slice1

  順序掃描lineitem表，過濾出l_shipdate > '1995-03-15'的資料，與slice2的結果進行hash join，並將該join結果按l_orderkey,o_orderdate,o_shippriority進行group by，最後再對group by的結果進行排序。

2.4explain總結

將query3的執行計劃抽象為下圖，可以看到整個查詢被兩次Redistribute Motion切分為3個slice，不同的slice之間並行處理可以 max化整個查詢的並行度，即3張表可以同時開始掃描。

其中有一些關於執行計劃的概念

• Motion：motion運算元負責在不同的segment例項之間移動資料。包括redistribute motion、broadcast motion、gather motion

• Slice：motion運算元將執行計劃切割成不同的slice，不同的slice之間可以並行處理

3.explain引數解釋

3.1ANALYZE

執行命令並顯示實際執行時間和其他統計資訊。 如果省略此引數，則預設為FALSE。 指定ANALYZE true可以啟用它。

3.2VERBOSE

顯示有關計劃的其他資訊。具體來說，包括計劃樹中每個結點的輸出列列表，模式限定表和函式名稱， 始終在表示式中使用範圍表別名標記變數，並始終列印要顯示其統計資訊的每個觸發器的名稱。 如果省略此引數，則預設為FALSE； 指定VERBOSE true啟用它。

3.3COSTS

包括有關每個計劃 結點的估計啟動成本和總成本以及估計的行數和估計的每行寬度的資訊。 如果省略此引數，則預設為TRUE； 指定COSTS false禁用它。

3.4BUFFERS

包括有關緩衝區使用情況的資訊。 具體來說，包括命中，讀取，弄髒和寫入的共享塊的數量，命中，讀取，弄髒和寫入的區域性塊的數量以及讀寫的臨時塊的數量。 命中表示避免讀取，因為在需要時已在快取記憶體中找到該塊。 共享塊包含來自常規表和索引的資料；本地塊包含來自臨時表和索引的資料； 臨時塊包含用於排序，雜湊，物化計劃 結點和類似情況的短期工作資料。 被弄髒的塊數表示此查詢已更改的先前未修改的塊數； 而寫入的塊數則表示此後端在查詢處理期間從快取中逐出的先前處理的塊數。 上級 結點顯示的塊數包括其所有子 結點使用的塊數。 在文字格式中，僅列印非零值。 僅當還啟用了ANALYZE時，才可以使用此引數。 如果省略此引數，則預設為FALSE； 指定BUFFERS true啟用它。

3.5TIMING

在輸出中包括實際的啟動時間和在每個 結點上花費的時間。 重複讀取系統時鐘的開銷可能會在某些系統上顯著降低查詢速度， 因此，當僅需要實際的行計數而不是確切的時間時，將此引數設定為FALSE可能會很有用。 即使使用此選項關閉了 結點級計時，也始終會測量整個語句的執行時間。 僅當還啟用了ANALYZE時，才可以使用此引數。 預設為TRUE。

3.6FORMAT

指定輸出格式，可以是TEXT，XML，JSON或YAML。 非文字輸出包含與文字輸出格式相同的資訊，但程式更易於解析。 此引數預設為TEXT。

3.7boolean

指定是開啟還是關閉所選選項。 您可以寫入TRUE，ON或1以啟用該選項， 而可以寫入FALSE，OFF或0以禁用該選項。 布林值也可以省略，在這種情況下，假定為TRUE。

3.8statement

您希望檢視其執行計劃的任何SELECT，INSERT，UPDATE， DELETE，VALUES，EXECUTE， DECLARE或CREATE TABLE AS語句。

4.執行計劃中的術語

以執行計劃中下述片段為例：

4.1掃描方式

在slice3中以Seq Scan的方式對orders表進行全表掃描，使用Filter: (o_orderdate < '1995-03-15'::date)條件對掃描結果進行過濾，Rows Removed by Filter: 21420749 顯示單個segment 結點上filter移除的行數。

4.2sql成本

樣例一：(cost=0.00..5790.87 rows=20370561 width=20)

• cost：最佳化器根據預設的引數評估出的SQL執行代價

• rows：最佳化器根據統計資訊評估出的資料行數

• width：評估的一行資料的寬度

樣例二：(actual time=0.836..6781.218 rows=20253520 loops=1)

• actual time：執行explain analyze時會顯示當前運算元的實際執行時間actual time，時間格式為“開始時間..結束時間”，時間單位為毫秒。

• rows：單個segment實際掃描的行數

• loops：當前運算元迴圈執行的次數

4.3motion操作

• gather motion

  將各segment 結點計算的資料結果收集到master 結點。比如在求某張表max值時，通常是所有segment執行完 max值之後再將結果彙總給master 結點，最後由master 結點統計出最終的結果。

• broadcast motion

  將資料廣播到所有segment 結點。比如在小表與大表關聯的時候，如果分佈鍵不同，最佳化器會選擇將小表的資料廣播到大表所在的segment上，然後每個segment對被廣播的小表資料再加上大表在該segment上的資料進行關聯操作。

• redistribute motion

  將資料根據新的分佈鍵在所有segment 結點上打散。在多表join時按照join條件進行重分佈，使同一個關聯鍵的資料分佈在同一個segment 結點上。比如處理分佈鍵不同的兩張大表的join操作，透過廣播的方式很明顯會加大網路流量與每個segment處理的資料量，所以系統會對兩張大表按照關聯鍵將資料進行重分佈到每個segment，然後每個segment各自處理對應資料後彙總給master 結點。

5.如何根據explain analyze發現sql的執行問題

5.1觀察執行計劃是否合理

由於SQL最佳化器本身演算法的限制，最佳化器選擇出的結果並不保證是全域性獨一無二的優解，適當的情況下可以人工進行干預。

5.2觀察整個SQL中執行時間長的部分是否正常

觀察每一步執行的actual time，找到整條SQL中的的瓶頸點，考慮有沒有更好的配置或者實現方式能夠更好地實現目的，另外即使在執行計劃是最完美的情況下，sql的執行還會受物理環境的限制，並不能說計劃合理便代表SQL執行的時間正常，因此需要對耗時長的步驟仔細評估。

5.3觀察是否存在配置不足導致的問題

充分地呼叫資源才能發揮出更好的資料庫效能，某些配置不合理的情況下，會在explain analyze中展示出來，比如如下的例子中出現了關聯時溢位到磁碟的操作，適當地增加記憶體分配消除此階段可以減少效能開銷。