Hologres支持设置多种表属性,不同的表属性有不同的特性。本文为您介绍如何根据业务查询场景设置合适的表属性,从而使查询在执行过程中扫描数据量更少、访问文件数更少、产生更少的I/O次数,进而使得查询更快、查询QPS更高。
确定表的存储格式
Hologres支持行存、列存、行列共存三种存储格式,具体原理及使用建议请参见表存储格式:列存、行存、行列共存。
首先请参考下图所示流程确定您表的存储格式。如果您的业务场景尚未完全明确,请优先选择行列共存,以兼顾更多可能出现的场景。
确定表的查询属性
在确定表的存储格式后,您需要根据查询场景确定表的属性。
下文均为针对单场景设置的表属性示例。
示例中均使用64 CU规格的Hologres实例对TPC-H 100GB中的Lineitem和Orders两张表进行效果验证,表字段定义及数据导入方法请参见OLAP查询场景。
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,验证方法请参见Key/Value点查场景。验证结果如下:
有主键:并发数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,验证方法请参见Key/Value点查场景。
验证结果如下:
按上述组合设置表属性:并发数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,验证方法请参见OLAP查询场景。验证结果如下:
按上述建议设置分区和
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,验证方法请参见OLAP查询场景。验证结果如下:
按上述建议设置表属性:读取数据行数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,验证方法请参见OLAP查询场景。验证结果如下:
设置合适的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,验证方法请参见OLAP查询场景。验证结果如下:
两个表均设置合适的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内部表DDL语句的介绍详情,请参见: