优化内部表的性能 - 实时数仓Hologres

本文为您介绍在Hologres中对内部表性能进行调优的最佳实践。

更新统计信息

统计信息决定是否能够生成正确的执行计划。例如，Hologres需要收集数据的采样统计信息，包括数据的分布和特征、表的统计信息、列的统计信息、行数、列数、字段宽度、基数、频度、最大值、最小值、长键值、分桶分布特征等信息。这些信息将为优化器更新算子执行预估COST、搜索空间裁剪、估算最优JOIN ORDER、估算内存开销、估算并行度，从而生成更优的执行计划。关于统计信息更多的介绍，请参见 Using Explain 。

统计信息的收集也存在一定局限，主要是 针对非实时、手动触发或者周期性触发，不一定反映最准确的数据特征 。您需要先检查 explain 的信息，查看 explain 中包含的统计信息是否正确。统计信息中每个算子的 rows 和 width 表示该算子的行数和宽度。

查看统计信息是否正确

通过查看执行计划

未及时同步统计信息导致生成较差的执行计划，示例如下：

tmp1 表的数据量为1000万行， tmp 表的数据量为1000行。 Hologres默认统计信息中的行数为1000行，通过执行 explain SQL语句，如下展示结果所示， tmp1 表的行数与实际的行数不符，该展示结果表明未及时更新统计信息。
```
Seq Scan on tmp1 (cost=0.00..5.01 rows=1000 width=1)
```
通过查看系统表
您可以通过查系统表查看行数、宽度等是否正确。
- 查询系统表 hologres.hg_table_properties 中的 analyze_tuple 列，确认数据行数是否正确。或者直接查看 Scan 节点中 rows 的值。
- 查询系统表 hologres.hg_stats 可看到每一列的直方图、平均宽度、不同值的数量等信息，如下图所示。

更新统计信息

tmp1 和 tmp 表Join时，正确的 explain 信息展示为数据量大的表tmp1在数据量小的表tmp上方，Hash Join应该采用数据量小的tmp表。因为 tmp1 表未及时更新统计信息，导致Hologres选择 tmp1 表创建Hash表进行Hash Join，效率较低，并且可能造成OOM（Out Of Memory，内存溢出）。因此，需要参与Join的两张表均执行 analyze 收集统计信息，语句如下。

analyze tmp;
analyze tmp1;

执行 analyze 命令后，Join的顺序正确。数据量大的表tmp1在数据量小的表tmp上方，使用数据量小的表tmp做Hash表，如下图所示。并且tmp1表展示的行数为1000万行，表明统计信息已经更新。

当发现explain返回结果中 rows=1000 ，说明缺少统计信息。一般性能不好时，其原因通常是优化器缺少统计信息，需要通过及时更新统计信息，执行 analyze <tablename> ，可以简单快捷优化查询性能。

优化器Join Order算法

当SQL Join关系比较复杂时，Join的表多时，优化器消耗在连接关系最优选择上的时间会更多，调整Join Order策略，在一定场景下会降低Query Optimization的时间，设置优化器Join Order算法语法如下。
```
set optimizer_join_order = '<value>'; 
```

参数说明

参数

说明

value

优化器Join Order算法，有如下三种。

query ：不进行Join Order转换，按照SQL书写的连接顺序执行，优化器开销最低。
greedy ：通过贪心算法进行Join Order的探索，优化器开销适中。
exhaustive ：通过动态规划算法进行Join Order转换，会生成最优的执行计划，但优化器开销最高。

默认值为exhaustive。

补充说明

使用默认的 exhaustive 算法可以全局探索最优的执行计划，但对于很多表的Join（例如表数量大于10），优化耗时可能较高。使用query或者greedy算法可以减少优化器耗时，但无法生成最优的执行计划。

设置适合的Shard数

Shard数代表查询执行的并行度。Shard个数对查询性能影响至关重要，Shard数设置少，会导致并行度不足。Shard数设置过多，也会引起查询启动开销大，降低查询效率，同时引起小文件过多，占用内存更多的元数据管理空间。设置与实例规格匹配的Shard数，可以改善查询效率，降低内存开销。

Hologres为每个实例设置了默认的Shard数，Shard数约等于实例中用于核心查询的Core数。这里的core数，略小于实际购买的Core数（实际购买的Core会被分配给不同的节点，包括查询节点、接入节点、控制节点和调度节点等）。不同规格实例默认的Shard数，请参见实例规格概述。当实例扩容后，扩容之前旧的DB对应的默认Shard数不会自动修改，需要根据实际情况修改Shard数，扩容后新建DB的Shard数为当前规格的默认数量。默认的Shard数是已经考虑扩容的场景，在资源扩容5倍以上的场景中，建议考虑重新设置Shard数，小于5倍的场景，无需修改也能带来执行效率的提升。具体操作请参见 Table Group设置最佳实践。

如下场景需要修改Shard数：

扩容后，因业务需要，原有业务有规模增长，需要提高原有业务的查询效率。此时，您需要创建新的Table Group，并为其设置更大的Shard数。原有的表和数据仍然在旧的Table Group中，您需要将数据重新导入新的Table Group中，完成Resharding的过程。
扩容后，需要上线新业务，但已有业务并不变化。此时，建议您创建新的Table Group，并为其设置适合的Shard数，并不调整原有表的结构。

begin;
create table tmp(a int, b int, c int);
call set_table_property('tmp', 'distribution_key', 'a');
commit;
begin;
create table tmp1(a int, b int, c int);
call set_table_property('tmp1', 'distribution_key', 'b');
commit;
-- 设置分布列为Join Key。
select count(1) from tmp join tmp1 on tmp.a = tmp1.b ;

类型	描述
Redistribute Motion	数据通过哈希分布或随机分布，Shuffle到一个或多个Shard。
Broadcast Motion	复制数据至所有Shard。仅在Shard数量与广播的表的数量均较少时，Broadcast Motion的优势较大。
Gather Motion	汇总数据至一个Shard。
Forward Motion	用于联邦查询场景。外部数据源或执行引擎与Hologres执行引擎进行数据传输。

-- 禁止生成Broadcast Motion。
set optimizer_enable_motion_broadcast = off; 
-- 禁止生成Redistribute Motion。
set optimizer_enable_motion_redistribute = off;

-- 数据如果按照a列的值进行分布，将减少数据运行时重分布，充分利用shard的并行计算能力。
select a, count(1) from t1 group by a;

-- hg_shard_id是每个表的内置隐藏列，描述对应行数据所在shard
select hg_shard_id, count(1) from t1 group by hg_shard_id;

begin;
create table tbl (a int not null, b text not null, c int not null, d int);
call set_table_property('tbl', 'dictionary_encoding_columns', '');
commit;

explain select * from tmp where a not in (select a from tmp1);

explain select * from tmp where not exists (select a from tmp1 where a = tmp.a);

Hologres原生引擎（HQE）不支持的函数	建议改写的函数	样例	备注
not in	not exists	select * from tmp where not exist (select a from tmp1 where a = tmp.a);	不涉及。
regexp_split_to_table(string text, pattern text )	unnest(string_to_array）	`select name,unnest(string_to_array(age,',')) from demo;`	regexp_split_to_table支持正则表达式。
substring	extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS'))	`select cast(substring(c1, 13, 2) as int) AS hour from t2;` 改写为： `select extract(hour from to_timestamp(c1, 'YYYYMMDD HH24:MI:SS')) from t2;`	Hologres部分V0.10版本及更早版本不支持substring。V1.3版本及以上版本中，HQE已支持substring函数的非正则表达式入参。
regexp_replace	replace	`select regexp_replace(c1::text,'-','') from t2;` 改写为： `select replace(c1::text,'-','') from t2;`	replace不支持正则表达式。
at time zone 'utc'	删除at time zone 'utc'	`select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') at time zone 'utc') from t2` 改写为： `select date_trunc('day',to_timestamp(c1, 'YYYYMMDD HH24:MI:SS') ) from t2;`	不涉及。
cast(text as timestamp)	to_timestamp	`select cast(c1 as timestamp) from t2;` 改写为： `select to_timestamp(c1, 'yyyyMMdd hh24:mi:ss') from t2;`	Hologres V2.0版本起HQE支持。
timestamp::text	to_char	`select c1::text from t2;` 改写为： `select to_char(c1, 'yyyyMMdd hh24:mi:ss') from t2;`	Hologres V2.0版本起HQE支持。

set hg_experimental_enable_result_cache = off;

命令

含义

参数取值范围

参数建议取值

set hg_experimental_scan_node_dop=<number>;

设置单个Shard内scan算子扫描的并发度，最大为单个Shard内扫描表的文件数。默认为性能最高的设置。适用于写入时出现OOM的场景。

0-512

使用 show hg_experimental_scan_node_dop 命令查看当前的并发度。
得到的结果除以2作为 hg_experimental_scan_node_dop 的参数值。

set hg_experimental_dml_bulkload_dop=<number>;

设置BulkLoad写入或更新的并发度，最大为单个Shard内写入表的文件数，最小为1。默认为性能最高的设置。适用于写入时出现OOM的场景。

0-512

使用 show hg_experimental_dml_bulkload_dop 命令查看当前的并发度。
得到的结果除以2作为 hg_experimental_dml_bulkload_dop 的参数值。

begin;
create table test_use_sort_1
          uuid           text not null,
          gpackagename   text not null,
          recv_timestamp text not null
call set_table_property('test_use_sort_1', 'orientation', 'column');
call set_table_property('test_use_sort_1', 'distribution_key', 'uuid');
call set_table_property('test_use_sort_1', 'clustering_key', 'uuid:asc,gpackagename:asc,recv_timestamp:desc');
commit;
--插入数据
insert into test_use_sort_1 select i::text, i::text, '20210814' from generate_series(1, 10000) as s(i);
--更新统计信息
analyze test_use_sort_1;

select uuid from test_use_sort_1 order by uuid limit 5;

- ```
create table test_count_distinct_implementation
    id int
    ,dim1 int
    ,dim2 text
    ,dim3 int8
    ,userid text
    ,deviceid text
    ,price float8
    ,ds text
insert into test_count_distinct_implementation select i, i % 17, (i % 13)::text, i % 37, 'user_' || round(i % 97723)::text, 'device_' || floor(i % 179357)::text, (sqrt(i % 24658)), '1' from generate_series(1, 5000)i;
insert into test_count_distinct_implementation select i, i % 19, (i % 13)::text, i % 37, 'user_' || round(i % 87723)::text, 'device_' || floor(i % 139557)::text, (sqrt(i % 38658)), '2' from generate_series(1, 4000)i;
analyze test_count_distinct_implementation;
```
- ```
select count(1), count(distinct userid), count(distinct deviceid), sum(distinct price::numeric) from test_count_distinct_implementation;
```

set optimizer_cte_inlining=off;

create table cte_reuse_test_t
    a integer not null,
    b text,
    primary key (a)
insert into cte_reuse_test_t values(1, 'a'),(2, 'b'), (3, 'c'), (4, 'b'), (5, 'c'), (6, ''), (7, null);
set optimizer_cte_inlining=off;
explain with c as (select b, max(a) as a from cte_reuse_test_t group by b)
select a1.a,a2.a,a1.b, a2.b
from c a1, c a2
where a1.b = a2.b
order by a1.b
limit 100;

set optimizer_force_multistage_agg = on;

类型	适用场景	缺点
行存储	按主键进行高QPS的点查询场景。一次能读取所有列，并且对UPDATE、DELETE及INSERT操作的性能较好。	大范围的查询、全表扫描及聚合等操作性能较差。
列存储	适用于多列按范围查询、单表聚合及多表连接等数据分析场景。	UPDATE和DELETE操作及无索引场景下的点查询性能慢于行存储。
行列共存	同时具备以上行列两种使用场景。	存储开销更高。

begin;
create table insert_partition(c1 bigint not null, c2 boolean, c3 float not null, c4 text, c5 timestamptz not null) partition by list(c4);
call set_table_property('insert_partition', 'orientation', 'column');
commit;
create table insert_partition_child1 partition of insert_partition for values in('20190707');
create table insert_partition_child2 partition of insert_partition for values in('20190708');
create table insert_partition_child3 partition of insert_partition for values in('20190709');
select * from insert_partition where c4 >= '20190708';
select * from insert_partition_child3;

类型	名称	描述	使用建议	示例查询语句
clustering_key	聚簇列	文件内聚簇索引，数据在文件内按该索引排序。对于部分范围查询，Hologres可以直接通过聚簇索引的数据有序属性进行过滤。	将范围查询或Filter查询列作为聚簇索引列。索引过滤具备左匹配原则，建议设置不超过2列。	`select sum(a) from tb1 where a > 100 and a < 200;`
bitmap_columns	位图列	文件内位图索引，数据在文件内按该索引列建立位图。对于等值查询，Hologres可以按照数值对每一行的数据做编码，通过位操作快速索引到对应行，时间复杂度为O(1)。	将等值查询列作为Bitmap列。	`select * from tb1 where a =100;`
segment_key（也称为event_time_column）	分段列	文件索引，数据按Append Only方式写入文件，随后文件间按该索引键合并小文件。 Segment_key标识了文件的边界范围，您可以通过Segment Key快速索引到目标文件。 Segment_key是为时间戳、日期等有序，范围类数据场景设计的，因此与数据的写入时间有强相关性。	您需要先通过Segment_key进行快速过滤，再通过Bitmap或Cluster索引进行文件内范围或等值查询。具备最左匹配原则，一般只有1列。建议将第一个非空的时间戳字段设置为Segment_key。	`select sum(a) from tb1 where ts > '2020-01-01' and a < '2020-03-02';`

call set_table_property('tmp', 'clustering_key', 'a,b,c');
select * from tmp where a > 1 ;  --可以使用Cluster索引。
select * from tmp where a > 1 and c > 2 ;   --只有a可以使用Cluster索引。
select * from tmp where a > 1 and b > 2 ;  --a,b均可以使用Cluster索引。
select * from tmp where a > 1 and b > 2 and c > 3 ; --a,b,c均可以使用Cluster索引。
select * from tmp where b > 1 and c > 2 ;   --b,c均不能使用Cluster索引。

call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
select * from tmp where a = 1 and b = 2 ;  -- 可以使用Bitmap索引。

begin;
create table tmp(a int not null, b int not null, c int not null);
call set_table_property('tmp', 'clustering_key', 'a');
call set_table_property('tmp', 'segment_key', 'b');
call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
commit;

```
explain select * from tmp where a > 1;
```
```
explain select * from tmp where c = 1;
```
```
explain select * from tmp where b > 1;
```

更新统计信息

查看统计信息是否正确

更新统计信息

推荐更新统计信息的场景

优化器Join Order算法

设置适合的Shard数

选择合适的分布列（Distribution Key）

Group By优化

数据倾斜处理

关闭Dictionary Encoding

SQL优化手段

采用Fixed Plan优化SQL写入和更新

PQE算子改写

避免模糊查询

结果缓存对查询的影响

OOM优化

Order By Limit场景优化

Count Distinct优化

With表达式优化（Beta）

单阶段Agg优化为多阶段Agg

建表属性优化

选择合适的存储类型

选择合适的数据类型

选择合适的主键

选择合适的分区表

选择合适的索引

查看是否使用Index