如何根據業務查詢情境設定合適的表屬性 - Hologres

Hologres支援設定多種表屬性，不同的表屬性有不同的特性。本文為您介紹如何根據業務查詢情境設定合適的表屬性，從而使查詢在執行過程中掃描資料量更少、訪問檔案數更少、產生更少的I/O次數，進而使得查詢更快、查詢QPS更高。

確定表的儲存格式

Hologres支援行存、列存、行列共存三種儲存格式，具體原理及使用建議請參見表格儲存體格式：列存、行存、行列共存。

首先請參考下圖所示流程確定您表的儲存格式。如果您的業務情境尚未完全明確，請優先選擇行列共存，以兼顧更多可能出現的情境。 image..png

確定表的查詢屬性

在確定表的儲存格式後，您需要根據查詢情境確定表的屬性。

說明

下文均為針對單情境設定的表屬性樣本。
樣本中均使用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 = ?;
```
設定建議
1. 將等值過濾條件的欄位設定為主鍵（Primary Key）的最左欄位，即Lineitem表的主鍵是(l_orderkey, l_linenumber)，而不是(l_linenumber, l_orderkey)。
2. 將等值過濾條件的欄位設定為Distribution Key，保證需要掃描的資料都儲存在同一個Shard，減少訪問的Shard數，以提高QPS。即Lineitem表的distribution_key為l_orderkey。
3. 將等值過濾條件的欄位設定為Clustering Key（行存表不需要，列存表和行列共存表需要），保證需要掃描的資料在檔案中連續，減少I/O次數。即Lineitem表的clustering_key設定為l_orderkey。
通過如上的表屬性設定，將查詢轉化成了單Shard的首碼掃描（PrefixScan）。Hologres支援通過Fixed Plan加速執行PrefixScan情境（需要開啟hg_experimental_enable_fixed_dispatcher_for_scanGUC ），詳情請參見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'  -- 適當縮小時間範圍以更好地驗證效果
...;

設定建議
1. 使用分區表，根據時間過濾條件進行分區。本情境針對Lineitem表增加l_year列並將其設為分區鍵，即為按年分區。您需要結合實際資料量等因素綜合考慮，決定是否使用分區表，或只設定event_time_column。分區表使用限制與注意事項請參見CREATE PARTITION TABLE。
2. 將時間過濾欄位設定為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');
```
設定建議
1. 將單值欄位設定為Clustering Key，保證相同值的資料在檔案中連續，減少I/O次數。即Lineitem表的l_shipmode為Clustering Key。
2. 將單值欄位設定為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;

Hologres：情境化建表調優指南