本文介紹如何在列存表中使用排序鍵結合粗糙集索引,從而提高查詢效能。
- 儲存預留模式:資料庫核心版本為20200826版本之後的建立執行個體。
- 儲存彈性模式:資料庫核心版本為20200906版本之後的建立執行個體。
背景資訊
當您建立表的時候,可以定義一個或者多個列為排序鍵(SORTKEY)。資料寫入到表中之後,您可以對該表按照排序鍵進行排序重組。
表排序後可以加速範圍限定查詢,資料庫會對每固定行記錄每一列的min、max值。如果在查詢時使用範圍限定條件,ADBPG的查詢引擎可以根據min、max值在對錶進行掃描(SCAN)時快速跳過不滿足限定條件的資料區塊(Block)。
例如,假設一張表格儲存體了7年的資料,並且這張表的資料是按照時間欄位排序儲存的,如果我們需要查詢一個月的資料,那麼只需要掃描 1/(7*12) 的資料,也就是說有98.8%的資料區塊在掃描(SCAN)時可以被過濾掉。但是如果資料沒有按照時間排序的話,可能所有的磁碟上的資料區塊都要被掃描到。
- 組合排序:適用於限定條件是查詢的首碼子集或者完全包含排序鍵,更適合於查詢包含首列限定條件的情況。
- 多維排序:給每一個排序鍵分配相同的權重,更適合於查詢條件包含任意限定條件子集的情境。
效能對比
- 本節以組合排序給粗糙集索引帶來的效能提升為例,展示粗糙集索引相比全表掃描的效能提升。
以TPCH Lineitem表為例,表中儲存了7年的資料,我們比較資料未按照l_shipdate欄位排序和用l_shipdate欄位作為排序鍵並進行排序的限定條件查詢的效能。
說明 本文的TPC的實現基於官方TPC的基準測試,並不能與發行的TPC基準測試結果相比較,本文中的測試並不符合TPC基準測試的所有要求。測試步驟:- 建立一個32節點的執行個體。
- 對Lineitem寫入130億行記錄。
- 查詢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 REINDEX
MULTISORT操作來對錶進行重排序,從而保證粗糙集過濾的效能。
修改排序鍵
您可以根據業務的變化修改已經建立的列存表的排序鍵,命令文法如下:
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情境,由於不包含排序鍵的首列,組合排序依然起不到很好的效果,並且由於比較條件的增加,需要額外的比較開銷,時間更長,而多維排序表現出更好的效能,這是因為在查詢時,限定條件包含的多維排序鍵越多,效能越好。