Hologres支援設定多種表屬性,不同的表屬性有不同的特性。本文為您介紹如何根據業務查詢情境設定合適的表屬性,從而使查詢在執行過程中掃描資料量更少、訪問檔案數更少、產生更少的I/O次數,進而使得查詢更快、查詢QPS更高。
確定表的儲存格式
Hologres支援行存、列存、行列共存三種儲存格式,具體原理及使用建議請參見表格儲存體格式:列存、行存、行列共存。
首先請參考下圖所示流程確定您表的儲存格式。如果您的業務情境尚未完全明確,請優先選擇行列共存,以兼顧更多可能出現的情境。
確定表的查詢屬性
在確定表的儲存格式後,您需要根據查詢情境確定表的屬性。
下文均為針對單情境設定的表屬性樣本。
樣本中均使用64 CU規格的Hologres執行個體對TPC-H 100GB中的Lineitem和Orders兩張表進行效果驗證。
TPC-H表介紹:TPC-H資料集類比的是零售情境。其中:
Orders表是訂單表(根據
o_orderkey
欄位可以確定一個訂單)。Lineitem表是訂單明細表(根據
o_orderkey
和l_linenumber
欄位可以確定一個訂單中的某個商品)。
本文的TPC-H的實現基於TPC-H的基準測試,並不能與發行的TPC-H基準測試結果相比較,本文中的測試並不符合TPC-H基準測試的所有要求。
如果您的表需要兼顧多個情境,如有不同的查詢過濾條件或有不同的JOIN欄位,則需要綜合考慮這些情境的查詢頻率與效能需求,從而設定最優的表屬性。
情境1:超高QPS點查
情境
如果您的查詢情境為萬級及以上QPS的點查,以TPC-H Orders表為例,通過
o_orderkey
欄位可以唯一確定一行資料。典型SQL範例如下:SELECT * FROM orders WHERE o_orderkey = ?;
設定建議
將點查時的過濾欄位設定為主鍵(Primary Key),Hologres支援通過Fixed Plan加速執行基於主鍵的點查,實現執行效率的成倍提升,原理請參見Fixed Plan加速SQL執行。
效果驗證
您可以將Orders表定義為行存表或行列共存表,分別對將
o_orderkey
欄位設為主鍵和不設主鍵兩種情況進行效果驗證,建表語句請參見情境1 DDL。驗證結果如下:
有主鍵:並發數500,平均QPS約為10.4萬,平均latency約為
4ms
。無主鍵:並發數500,平均QPS約為1.6萬,平均latency約為
30ms
。
情境2:高QPS的小資料量首碼掃描
情境
如果您的查詢情境滿足以下條件:
表由多個欄位組成複合主鍵(Primary Key)。
要求高QPS (萬級QPS)查詢。查詢條件為根據主鍵欄位組合中的某個欄位進行等值過濾,查詢結果集一般比較小(幾條或者幾十條)。
以TPC-H Lineitem表為例,Lineitem是訂單明細表(根據
l_orderkey
和l_linenumber
欄位可以確定一個訂單中的某個商品),現需要高QPS的根據某個訂單號(l_orderkey
)擷取此訂單下的所有商品,SQL樣式如下:SELECT * FROM lineitem WHERE l_orderkey = ?;
設定建議
將等值過濾條件的欄位設定為主鍵(Primary Key)的最左欄位,即Lineitem表的主鍵是
(l_orderkey, l_linenumber)
,而不是(l_linenumber, l_orderkey)
。將等值過濾條件的欄位設定為Distribution Key,保證需要掃描的資料都儲存在同一個Shard,減少訪問的Shard數,以提高QPS。即Lineitem表的
distribution_key
為l_orderkey
。將等值過濾條件的欄位設定為Clustering Key(行存表不需要,列存表和行列共存表需要),保證需要掃描的資料在檔案中連續,減少I/O次數。即Lineitem表的
clustering_key
設定為l_orderkey。
通過如上的表屬性設定,將查詢轉化成了單Shard的首碼掃描(PrefixScan)。Hologres支援通過Fixed Plan加速執行PrefixScan情境(需要開啟
hg_experimental_enable_fixed_dispatcher_for_scan
GUC ),詳情請參見Fixed Plan加速SQL執行。效果驗證
您可以將Lineitem表定義為行存表或行列共存表,分別對按上述組合設定表屬性和不按上述組合設定表屬性兩種情況進行效果驗證,建表語句請參見情境2 DDL。
驗證結果如下:
按上述組合設定表屬性:並發數500,平均QPS約為3.7萬,平均latency約為
13ms
。不按上述組合設定:並發數1,平均QPS約為60,平均latency約為
16ms
。
情境3:有時間過濾條件的查詢
情境
如果您的查詢情境帶有典型的時間過濾條件,以TPC-H Lineitem表為例,需要通過
l_shipdate
欄位進行時間過濾(如查詢語句Q1),適當修改時間過濾條件,SQL樣式如下:-- 原始Query SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty, sum(l_extendedprice) AS sum_base_price, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price, avg(l_discount) AS avg_disc, count(*) AS count_order FROM lineitem WHERE l_shipdate <= date '1998-12-01' - interval '120' day GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus; -- 修改後的Query SELECT ... FROM lineitem WHERE l_year = '1992' AND -- 僅分區表需要增加該時間過濾條件 l_shipdate <= date '1992-12-01' -- 適當縮小時間範圍以更好地驗證效果 ...;
設定建議
使用分區表,根據時間過濾條件進行分區。本情境針對Lineitem表增加
l_year
列並將其設為分區鍵,即為按年分區。您需要結合實際資料量等因素綜合考慮,決定是否使用分區表,或只設定event_time_column
。分區表使用限制與注意事項請參見CREATE PARTITION TABLE。將時間過濾欄位設定為
event_time_column
,保證Shard內的各檔案按event_time_column
值有序排列,減少掃描檔案數。即Lineitem表的event_time_column
為l_shipdate
。event_time_column
原理及使用請參見Event Time Column(Segment Key)。
效果驗證
您可以將Lineitem表定義為列存表,分別對按上述建議設定分區和
event_time_column
、不設定分區並將其他欄位設為event_time_column
兩種情況進行效果驗證,建表語句請參見情境3 DDL。驗證結果如下:
按上述建議設定分區和
event_time_column
:掃描分區數為1,掃描檔案數為80。不設定分區並將其他欄位設為
event_time_column
:未經過分區過濾,掃描檔案數為320。
說明可以通過執行
EXPLAIN ANALYZE
命令查看SQL的掃描分區數(Partitions selected)和掃描檔案數(dop)。
情境4:有非時間的單值過濾條件的查詢
情境
如果您的查詢情境帶有非時間類的單值過濾條件,以TPC-H Lineitem表為例,需要通過非時間欄位
l_shipmode
進行單值過濾(如按查詢語句Q1進行彙總計算),SQL樣式如下:SELECT ... FROM lineitem WHERE l_shipmode IN ('FOB', 'AIR');
設定建議
將單值欄位設定為Clustering Key,保證相同值的資料在檔案中連續,減少I/O次數。即Lineitem表的
l_shipmode
為Clustering Key。將單值欄位設定為Bitmap,加速定位到合格資料所在位置。即Lineitem表的
l_shipmode
為Bitmap_columns。
效果驗證
您可以將Lineitem表定義為列存表,分別對按上述建議設定表屬性、不將
l_shipmode
設為Clustering Key和Bitmap_columns兩種情況進行效果驗證,建表語句請參見情境4 DDL。驗證結果如下:
按上述建議設定表屬性:讀取資料行數1.7億行,查詢時間長度
0.71s
。不將
l_shipmode
設為Clustering Key和Bitmap_columns:讀取資料行數6.0億行(全表掃描),查詢時間長度2.41s
。說明可以通過慢Query日誌查看讀取資料行數(read_rows),詳情請參見慢Query日誌查看與分析。
可以通過執行計畫驗證是否通過Bitmap過濾,執行計畫中有
Bitmap Filter
關鍵字,說明查詢進行了Bitmap過濾。
情境5:有按某欄位彙總的查詢
情境
如果您的查詢情境為按某欄位彙總,以TPC-H Lineitem表為例,針對
l_suppkey
欄位進行分組彙總查詢,SQL樣式如下:SELECT l_suppkey, sum(l_extendedprice * (1 - l_discount)) FROM lineitem GROUP BY l_suppkey;
設定建議
將彙總欄位設定為Distribution Key,避免跨Shard的大量資料Shuffle。
效果驗證
您可以將Lineitem表定義為列存表,分別對將彙總欄位
l_suppkey
設為Distribution Key、將其他欄位設為Distribution Key兩種情況進行效果驗證,建表語句請參見情境5 DDL。驗證結果如下:
設定合適的Distribution Key:資料Shuffle量為 0.21 GB,執行時間長度
2.30s
。設定不合適的Distribution Key:資料Shuffle量為 8.16 GB,執行時間長度
3.68s
。
說明可以通過慢Query日誌查看Shuffle資料量(shuffle_bytes),詳情請參見慢Query日誌查看與分析。
情境6:多表JOIN查詢
情境
如果您的查詢情境為多表JOIN查詢,以TPC-H Lineitem表和Orders表為例,按查詢語句Q4進行JOIN查詢,SQL範例如下:
SELECT o_orderpriority, count(*) AS order_count FROM orders WHERE o_orderdate >= date '1996-07-01' AND o_orderdate < date '1996-07-01' + interval '3' month AND EXISTS ( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey -- JOIN查詢 AND l_commitdate < l_receiptdate) GROUP BY o_orderpriority ORDER BY o_orderpriority;
設定建議
建議將JOIN欄位設定為Distribution Key,實現Local Join,避免跨Shard的大量資料Shuffle。
效果驗證
您可以將Lineitem表和Orders表定義為列存表,分別對將JOIN欄位
l_orderkey
與o_orderkey
設為各自的Distribution Key、設定其他欄位為Distribution Key(比如將l_linenumber
欄位設為Lineitem表的Distribution Key、將Orders表的Distribution Key設為空白)兩種情況進行效果驗證,建表語句請參見情境6 DDL。驗證結果如下:
兩個表均設定合適的Distribution Key:資料Shuffle量為0.45 GB,執行時間長度
2.19s
。兩個表均設定不合適的Distribution Key:資料Shuffle量為6.31 GB,執行時間長度
5.55s
。
說明可以通過慢Query日誌查看Shuffle資料量(shuffle_bytes),詳情請參見慢Query日誌查看與分析。
(可選)確定表所屬的Table Group
如果您的執行個體規格較大(大於256 Core),並且業務情境較豐富,可以考慮規劃多個Table Group,並在建表時指定表所屬的Table Group。詳情請參見Table Group設定最佳實務。
附錄:建表語句
情境1 DDL:
-- 有主鍵表DDL如下。無主鍵表只需刪去O_ORDERKEY的PRIMARY KEY定義。 DROP TABLE IF EXISTS orders; BEGIN; CREATE TABLE orders( O_ORDERKEY BIGINT NOT NULL PRIMARY KEY ,O_CUSTKEY INT NOT NULL ,O_ORDERSTATUS TEXT NOT NULL ,O_TOTALPRICE DECIMAL(15,2) NOT NULL ,O_ORDERDATE TIMESTAMPTZ NOT NULL ,O_ORDERPRIORITY TEXT NOT NULL ,O_CLERK TEXT NOT NULL ,O_SHIPPRIORITY INT NOT NULL ,O_COMMENT TEXT NOT NULL ); CALL SET_TABLE_PROPERTY('orders', 'orientation', 'row'); CALL SET_TABLE_PROPERTY('orders', 'clustering_key', 'o_orderkey'); CALL SET_TABLE_PROPERTY('orders', 'distribution_key', 'o_orderkey'); COMMIT;
情境2 DDL:
-- 建立滿足上文表屬性群組合的Lineitem表。 DROP TABLE IF EXISTS lineitem; BEGIN; CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY INT NOT NULL, L_SUPPKEY INT NOT NULL, L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG TEXT NOT NULL, L_LINESTATUS TEXT NOT NULL, L_SHIPDATE TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ NOT NULL, L_SHIPINSTRUCT TEXT NOT NULL, L_SHIPMODE TEXT NOT NULL, L_COMMENT TEXT NOT NULL, PRIMARY KEY (L_ORDERKEY,L_LINENUMBER) ); CALL set_table_property('lineitem', 'orientation', 'row'); -- CALL set_table_property('lineitem', 'clustering_key', 'L_ORDERKEY,L_SHIPDATE'); CALL set_table_property('lineitem', 'distribution_key', 'L_ORDERKEY'); COMMIT;
情境3 DDL:
-- 建立Lineitem分區表。非分區表同情境2。 DROP TABLE IF EXISTS lineitem; BEGIN; CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY INT NOT NULL, L_SUPPKEY INT NOT NULL, L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG TEXT NOT NULL, L_LINESTATUS TEXT NOT NULL, L_SHIPDATE TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ NOT NULL, L_SHIPINSTRUCT TEXT NOT NULL, L_SHIPMODE TEXT NOT NULL, L_COMMENT TEXT NOT NULL, L_YEAR TEXT NOT NULL, PRIMARY KEY (L_ORDERKEY,L_LINENUMBER,L_YEAR) ) PARTITION BY LIST (L_YEAR); CALL set_table_property('lineitem', 'clustering_key', 'L_ORDERKEY,L_SHIPDATE'); CALL set_table_property('lineitem', 'segment_key', 'L_SHIPDATE'); CALL set_table_property('lineitem', 'distribution_key', 'L_ORDERKEY'); COMMIT;
情境4 DDL:
-- 建立設定不恰當表屬性的Lineitem表。對比情境只需將clustering_key和bitmap_columns改為恰當值。 DROP TABLE IF EXISTS lineitem; BEGIN; CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY INT NOT NULL, L_SUPPKEY INT NOT NULL, L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG TEXT NOT NULL, L_LINESTATUS TEXT NOT NULL, L_SHIPDATE TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ NOT NULL, L_SHIPINSTRUCT TEXT NOT NULL, L_SHIPMODE TEXT NOT NULL, L_COMMENT TEXT NOT NULL, PRIMARY KEY (L_ORDERKEY,L_LINENUMBER) ); CALL set_table_property('lineitem', 'segment_key', 'L_SHIPDATE'); CALL set_table_property('lineitem', 'distribution_key', 'L_ORDERKEY'); CALL set_table_property('lineitem', 'bitmap_columns', 'l_orderkey,l_partkey,l_suppkey,l_linenumber,l_returnflag,l_linestatus,l_shipinstruct,l_comment'); COMMIT;
情境5 DDL:
-- 將Group By欄位設為distribution_key的Lineitem表。 DROP TABLE IF EXISTS lineitem; BEGIN; CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY INT NOT NULL, L_SUPPKEY INT NOT NULL, L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG TEXT NOT NULL, L_LINESTATUS TEXT NOT NULL, L_SHIPDATE TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ NOT NULL, L_SHIPINSTRUCT TEXT NOT NULL, L_SHIPMODE TEXT NOT NULL, L_COMMENT TEXT NOT NULL, PRIMARY KEY (L_ORDERKEY,L_LINENUMBER,L_SUPPKEY) ); CALL set_table_property('lineitem', 'segment_key', 'L_COMMITDATE'); CALL set_table_property('lineitem', 'clustering_key', 'L_ORDERKEY,L_SHIPDATE'); CALL set_table_property('lineitem', 'distribution_key', 'L_SUPPKEY'); COMMIT;
情境6 DDL:
DROP TABLE IF EXISTS LINEITEM; BEGIN; CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY INT NOT NULL, L_SUPPKEY INT NOT NULL, L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG TEXT NOT NULL, L_LINESTATUS TEXT NOT NULL, L_SHIPDATE TIMESTAMPTZ NOT NULL, L_COMMITDATE TIMESTAMPTZ NOT NULL, L_RECEIPTDATE TIMESTAMPTZ NOT NULL, L_SHIPINSTRUCT TEXT NOT NULL, L_SHIPMODE TEXT NOT NULL, L_COMMENT TEXT NOT NULL, PRIMARY KEY (L_ORDERKEY,L_LINENUMBER) ); CALL set_table_property('LINEITEM', 'clustering_key', 'L_SHIPDATE,L_ORDERKEY'); CALL set_table_property('LINEITEM', 'segment_key', 'L_SHIPDATE'); CALL set_table_property('LINEITEM', 'distribution_key', 'L_ORDERKEY'); CALL set_table_property('LINEITEM', 'bitmap_columns', 'L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); CALL set_table_property('LINEITEM', 'dictionary_encoding_columns', 'L_RETURNFLAG,L_LINESTATUS,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT'); COMMIT; DROP TABLE IF EXISTS ORDERS; BEGIN; CREATE TABLE ORDERS ( O_ORDERKEY BIGINT NOT NULL PRIMARY KEY, O_CUSTKEY INT NOT NULL, O_ORDERSTATUS TEXT NOT NULL, O_TOTALPRICE DECIMAL(15,2) NOT NULL, O_ORDERDATE timestamptz NOT NULL, O_ORDERPRIORITY TEXT NOT NULL, O_CLERK TEXT NOT NULL, O_SHIPPRIORITY INT NOT NULL, O_COMMENT TEXT NOT NULL ); CALL set_table_property('ORDERS', 'segment_key', 'O_ORDERDATE'); CALL set_table_property('ORDERS', 'distribution_key', 'O_ORDERKEY'); CALL set_table_property('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT'); CALL set_table_property('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT'); COMMIT;