列存表使用排序鍵和粗糙集索引加速查詢 - AnalyticDB for PostgreSQL

本文介紹如何在列存表中使用排序鍵結合粗糙集索引，從而提高查詢效能。

重要本文適用於：

儲存預留模式：資料庫核心版本為20200826版本之後的建立執行個體。
儲存彈性模式：資料庫核心版本為20200906版本之後的建立執行個體。

背景資訊

當您建立表的時候，可以定義一個或者多個列為排序鍵（SORTKEY）。資料寫入到表中之後，您可以對該表按照排序鍵進行排序重組。

表排序後可以加速範圍限定查詢，資料庫會對每固定行記錄每一列的min、max值。如果在查詢時使用範圍限定條件，ADBPG的查詢引擎可以根據min、max值在對錶進行掃描（SCAN）時快速跳過不滿足限定條件的資料區塊（Block）。

例如，假設一張表格儲存體了7年的資料，並且這張表的資料是按照時間欄位排序儲存的，如果我們需要查詢一個月的資料，那麼只需要掃描 1/(7*12) 的資料，也就是說有98.8%的資料區塊在掃描（SCAN）時可以被過濾掉。但是如果資料沒有按照時間排序的話，可能所有的磁碟上的資料區塊都要被掃描到。

ADBPG支援兩種排序方式：

組合排序：適用於限定條件是查詢的首碼子集或者完全包含排序鍵，更適合於查詢包含首列限定條件的情況。
多維排序：給每一個排序鍵分配相同的權重，更適合於查詢條件包含任意限定條件子集的情境。

更多詳情請參見組合排序和多維排序的效能對比。

效能對比
- 本節以組合排序給粗糙集索引帶來的效能提升為例，展示粗糙集索引相比全表掃描的效能提升。
  以TPCH Lineitem表為例，表中儲存了7年的資料，我們比較資料未按照l_shipdate欄位排序和用l_shipdate欄位作為排序鍵並進行排序的限定條件查詢的效能。
  說明本文的TPC的實現基於官方TPC的基準測試，並不能與發行的TPC基準測試結果相比較，本文中的測試並不符合TPC基準測試的所有要求。
  測試步驟：
  1. 建立一個32節點的執行個體。
  2. 對Lineitem寫入130億行記錄。
  3. 查詢1997-09-01到1997-09-30的資料。
    - 資料未按照l_shipdate排序。
    - 資料按照l_shipdate排序。

建立表時定義排序鍵

範例

create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) ORDER BY (volume);

文法

CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type  ...} ]
)
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ ORDER BY (column, [ ... ] )]

資料庫核心版本20210326之前，指定排序鍵文法為SORTKEY (column, [ ... ])

對錶進行排序

對資料進行組合排序
- ```
SORT [tablename]
```
- 資料庫核心版本20210326之前可以使用以下文法的語句：
```
VACUUM SORT ONLY [tablename]
```

對資料進行多維排序
- ```
MULTISORT [tablename]
```
- 資料庫核心版本20210326之前可以使用以下文法的語句：
```
VACUUM REINDEX [tablename]
```

當您對一張表執行過SORT或者MULTISORT之後，當前的資料會組織為按照排序鍵全表有序，但隨著表中不斷寫入新資料，未排序的部分就會不斷增加，這將有可能影響粗糙集過濾的效能。因此您需要周期性地執行SORT或者VACUUM REINDEXMULTISORT操作來對錶進行重排序，從而保證粗糙集過濾的效能。

修改排序鍵

您可以根據業務的變化修改已經建立的列存表的排序鍵，命令文法如下：

ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name SET ORDER BY (column, [ ... ] )

這個命令只會修改catalog，不會對資料立即排序，需要重新執行SORT table_name命令排序。

範例

ALTER TABLE test SET ORDER BY(high,low);

資料庫核心版本20210326之前可以使用以下文法的語句：

ALTER TABLE test SET SORTKEY(high,low);

如何選擇排序鍵和排序方式

當您的查詢SQL經常包含某一個列或者某幾個列的等值或者範圍限定條件查詢時，比如時間列等，可以考慮使用這些列作為排序鍵，從而利用資料排序並結合粗糙索引，加速這類SQL的查詢速度。

一般情況下建議使用組合排序，因為多維排序在排序過程中還需要做一些額外的資料群組織工作，多維排序VACUUM REINDEX的時間會長於組合排序VACUUM SORT ONLY的時間。

如果您的查詢SQL包含的限定條件經常不是總是包含某些列的，可以使用多維排序來加速查詢。多維排序最多支援8列。

組合排序和多維排序的效能對比

我們會對同一張表分別做組合排序和多維排序，從而比較兩種排序方式在不同的情境下，對不同查詢的效能影響。

在這個情境中，我們建立一張表test，其包含4列（id, num1, num2, value）。使用（id,num1,num2）作為排序鍵。這張表一共包含一千萬條記錄。對於ADBPG來說並不算是一張特別大的表，但是其可以顯示出組合排序和多維排序的效能差異，在更大的資料集中，兩者的效能差異也會更明顯。

測試步驟：

建立測試表並設定表的排序鍵。
寫入測試資料。
分別對這張表做組合排序和多維排序。
對比同樣的SQL情境，組合排序和多維排序的點查效能。
對比同樣的SQL情境，組合排序和多維排序的範圍查詢效能。

建立測試表並設定表的排序鍵

CREATE TABLE test(id int, num1 int, num2 int, value varchar) 
with(APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY(id)
ORDER BY(id, num1, num2);

CREATE TABLE test_multi(id int, num1 int, num2 int, value varchar) 
with(APPENDONLY=TRUE, ORIENTATION=column)
DISTRIBUTED BY(id)
ORDER BY(id, num1, num2);

寫入一千萬行資料

INSERT INTO test(id, num1, num2, value) select g, 
(random()*10000000)::int, 
(random()*10000000)::int,
(array['foo', 'bar', 'baz', 'quux', 'boy', 'girl', 'mouse', 'child', 'phone'])[floor(random() * 10 +1)]
FROM generate_series(1, 10000000) as g;

INSERT INTO test_multi SELECT * FROM test;

adbpgadmin=# SELECT count(*) FROM test;
  count
----------
 10000000
(1 row)

adbpgadmin=# SELECT count(*) FROM test_multi;
  count
----------
 10000000
(1 row)

對兩張表分別進行組合排序和多維排序
- ```
SORT test;
MULTISORT test_multi;
```

點查詢比較效能

包含首列排序鍵限定條件。

-- Q1 包含首列限定條件
select * from test where id = 100000;
select * from test_multi where id = 100000;

包含第二列限定條件。

-- Q2 包含第二列限定條件
select * from test where num1 = 8766963;
select * from test_multi where num1 = 8766963;

包含二三列限定條件。

-- Q3 包含二三列限定條件
select * from test where num1 = 100000 and num2=2904114;
select * from test_multi where num1 = 100000 and num2=2904114;

表 1. 效能對比結果
排序方式	Q1	Q2	Q3
組合排序	0.026s	3.95s	4.21s
多維排序	0.55s	0.42s	0.071s

範圍查詢比較效能

包含首列排序鍵限定條件。

-- Q1 包含首列限定條件
select count(*) from test where id>5000 and id < 100000;
select count(*) from test_multi where id>5000 and id < 100000;

包含第二列限定條件。

-- Q2 包含第二列限定條件
select count(*) from test where num1 >5000 and num1 <100000;
select count(*) from test_multi where num1 >5000 and num1 <100000;

包含二三列限定條件。

-- Q3 包含二三列限定條件
select count(*) from test where num1 >5000 and num1 <100000; and num2 < 100000;
select count(*) from test_multi where num1 >5000 and num1 <100000 and num2 < 100000;

表 2. 效能對比結果
排序方式	Q1	Q2	Q3
組合排序	0.07s	3.35s	3.64s
多維排序	0.44s	0.28s	0.047s

結論
- - 對於Q1情境，由於包含排序鍵的首列，所以組合排序的效果非常好，而多維排序則會相對效能弱一些。
  - 對於Q2情境，由於不包含排序鍵的首列，組合排序基本上失效了，而多維排序依然能維持比較穩定的效能提升。
  - 對於Q3情境，由於不包含排序鍵的首列，組合排序依然起不到很好的效果，並且由於比較條件的增加，需要額外的比較開銷，時間更長，而多維排序表現出更好的效能，這是因為在查詢時，限定條件包含的多維排序鍵越多，效能越好。