全部產品
Search
文件中心

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

更新時間:Feb 05, 2024

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

重要 本文適用於:
  • 儲存預留模式:資料庫核心版本為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排序
        • 資料按照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來說並不算是一張特別大的表,但是其可以顯示出組合排序和多維排序的效能差異,在更大的資料集中,兩者的效能差異也會更明顯。

測試步驟:
  1. 建立測試表並設定表的排序鍵。
  2. 寫入測試資料。
  3. 分別對這張表做組合排序和多維排序。
  4. 對比同樣的SQL情境,組合排序和多維排序的點查效能。
  5. 對比同樣的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. 效能對比結果
      排序方式Q1Q2Q3
      組合排序0.026s3.95s4.21s
      多維排序0.55s0.42s0.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. 效能對比結果
      排序方式Q1Q2Q3
      組合排序0.07s3.35s3.64s
      多維排序0.44s0.28s0.047s
  • 結論

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