AnalyticDB：Beam排序最佳化（7.0版）

Beam支援在建立表時指定一個或者多個排序鍵，系統會按照排序鍵持續對寫入資料進行排序。當資料有序後，Beam在對錶進行掃描時可以根據資料區塊的最大和最小值跳過不滿足過濾條件的資料區塊，從而極大地減少I/O開銷。此外，定義排序鍵通常也能使資料獲得更好的壓縮比。目前Beam支援兩種排序鍵類型：組合排序鍵和多維排序鍵。

注意事項

僅v7.0.1.x及以上版本的AnalyticDB PostgreSQL 7.0版執行個體支援Beam排序最佳化功能。查看執行個體的核心版本，請參見查看核心小版本。

組合排序

定義組合排序鍵

組合排序鍵會按照定義排序鍵的順序，優先按照第一個排序鍵完全有序，再依次按照剩餘的排序鍵排序。組合排序鍵對包含排序鍵首碼的查詢過濾條件有很好的資料過濾效果。

建立帶有組合排序鍵的Beam表，樣本如下。

CREATE TABLE beam_example (
    id integer,
    name text,
    ftime timestamp
) 
USING beam 
DISTRIBUTED BY (id) 
ORDER BY(id);

維護組合排序鍵

建立組合排序鍵後，您新寫入的資料不會立即按照排序鍵的順序有序，而是後台進程會依照資料量和檔案數，自動地幫您產生最好的排序效果。如果您想讓表中的資料立即完全有序，可以執行Optimize命令使您寫入的資料完全有序。

使beam_example表中的資料立即完全有序，樣本如下。

OPTIMIZE beam_example;

多維排序

注意事項

• 僅v7.0.4.0及以上版本的AnalyticDB PostgreSQL 7.0版執行個體支援Beam多維排序。查看執行個體的核心版本，請參見查看核心小版本。

• 目前一個表僅支援設定2~8列的多維排序鍵，無法建立超出限制的多維排序Beam表。

• 不建議將遞增的資料列（例如日期、時間戳記）作為多維排序鍵的一部分。

定義多維排序鍵

多維排序鍵為排序鍵中的每個列賦予相同的權重進行排序，盡量保持每列資料的維度具有鄰近性。多維排序鍵對包含任意排序鍵列的查詢都有一定資料過濾效果。

建立帶有多維排序鍵的Beam表，樣本如下。

CREATE TABLE beam_example_interleaved (
    id integer,
    name text,
    ftime timestamp,
    region varchar,
    age integer
) 
USING beam 
DISTRIBUTED BY (id) 
ZORDER BY(name, region, age);

維護多維排序鍵

建立多維排序鍵後，您新寫入的資料不會立即進行多維排序，而是後台進程會依照資料量和檔案數，自動地幫您產生最好的排序效果。然而，隨著新資料的寫入，多維排序可能會因為資料範圍的變化逐漸產生傾斜現象，這時需要您手動對錶進行重新排序。

以按照name和region兩列多維排序的beam_example_interleaved表為例，查看多維排序列當前傾斜情況的語句如下。

SELECT * FROM adbpg_toolkit.pg_get_interleaved_skew('beam_example_interleaved'::regclass)
 relid | colname  | skew |  suggestion   
-------+----------+------+---------------
 17139 | name     | 0.46 | 
 17139 | region   | 0.54 | NEED OPTIMIZE
 17139 | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

查看當前資料庫所有多維排序表每列的傾斜情況的語句如下。

SELECT * FROM adbpg_toolkit.pg_stat_interleaved_skew;
 relid |         relname          | colname  | skew |  suggestion   
-------+--------------------------+----------+------+---------------
 17139 | beam_example_interleaved | name     | 0.46 | 
 17139 | beam_example_interleaved | region   | 0.54 | NEED OPTIMIZE
 17139 | beam_example_interleaved | OVER ALL | 0.54 | NEED OPTIMIZE
(3 rows)

返回結果各列資訊如下。

• skew：多維排序鍵傾斜資料量的比例。

• suggestion：當前對該多維排序鍵建議執行的操作。當出現NEED OPTIMIZE時，表示該列需要重新做排序。

• colname列中OVER ALL表示對各列排序鍵的綜合傾斜情況。如果您只對多維排序鍵某幾列的傾斜情況關注，可以只根據該列的傾斜情況觸發全表的排序。

如果您想讓表中的資料立即完全有序，可以運行Optimize命令使您寫入的資料完全有序。

使beam_example_interleaved表中的資料立即完全有序，樣本如下。

OPTIMIZE beam_example_interleaved;

添加或修改排序鍵

在建立表後，您還可以添加排序鍵或者修改排序鍵，或刪除已有的排序鍵。

注意事項

• 添加或修改排序鍵期間，該表會被鎖住無法進行讀寫操作。

• 修改排序鍵會導致全表的資料重寫，巨量資料量的表執行時間較長，請謹慎操作。

• 修改組合排序鍵後會立即對資料進行排序；修改多維排序鍵則不會立即對資料進行排序，因此帶有多維排序鍵的表修改排序鍵後，建議對錶手動執行Optimize命令。

樣本

• 添加或修改組合排序鍵。

  ALTER TABLE beam_example SET ORDER BY(id, name);

• 添加或修改多維排序鍵。

  ALTER TABLE beam_example_interleaved SET ZORDER BY(name, region);

• 刪除排序鍵。

  ALTER TABLE beam_example SET ORDER NONE;

如何選擇排序鍵

使用Beam儲存引擎選擇排序鍵的建議：

• 如果您經常對某列或某幾列進行範圍查詢或等值篩選，則指定該列作為排序鍵。

• 如果經常對多列進行範圍查詢或等值篩選，多列的查詢頻率和篩選率大致相同，且其中不包含自增列，考慮使用多維排序鍵；如果對某列的查詢頻率和篩選率較高，則優先考慮使用組合排序鍵。

• 設定組合排序鍵時，如果使用頻率相等，優先將低基數列放在組合排序鍵的前面。

• 設定多維排序鍵時，排序鍵列的基數不宜過高，同時資料量越大時，多維排序的效果越明顯。

Beam組合排序鍵和多維排序鍵查詢效果對比

以下是對資料量1 TB的SSB Benchmark中lineorder_flat Beam表分別建立的組合排序鍵和多維排序鍵後，對比不同情境篩選條件下的查詢效果，其中排序鍵為LO_ORDERDATE和P_BRAND。

從上面的查詢結果能夠看出，對於僅包含排序鍵首列的查詢以及對排序鍵首列篩選率較高的情境，使用組合排序鍵較好。如果是對排序鍵非首列有查詢或者包含多列排序鍵的查詢，多維排序鍵的效果更佳。

相關文檔

Beam排序最佳化僅適用於AnalyticDB PostgreSQL 7.0版執行個體，如果您需要在AnalyticDB PostgreSQL 6.0版執行個體中使用排序最佳化功能，請參見排序最佳化。