如何根据业务查询场景设置合适的表属性 - 实时数仓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：场景化建表调优指南