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

查看执行计划

您可以通过explain关键字与查询SQL语句相结合的方式，展示查询的分布式物理执行计划。执行计划由一系列算子构成，如下所示：
  • 数据扫描算子：Seq Scan、Table Scan及Index Scan等。
  • 连接算子：Hash Join及Nested Loop。
  • 聚集算子：Hash Aggregate及Streaming Aggregate。
  • 分布式算子：Redistribute Motion、Broadcast Motion及Gather Motion等。
  • 其他算子：Hash、Sort、Limit及Append等。

更新统计信息

统计信息决定是否能够生成正确的执行计划，您可以执行analyze命令收集统计信息。

您需要先检查explain的信息，查看explain中包含的统计信息是否正确。统计信息中每个算子的rows和width表示该算子的行数和宽度。

如果统计信息有误，请先运行analyze table，再尝试导入任务，获取统计信息。
说明 请在大规模导入数据后再执行analyze命令。

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

tmp1表的数据量为1000万行，tmp表的数据量为1000行。 Hologres默认统计信息中的行数为1000行，如下展示结果所示，tmp1表的行数与实际的行数不符，表明该展示结果未及时更新统计信息。
Seq Scan on tmp1 (cost=0.00..5.01 rows=1000 width=1)
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万行，表明统计信息已经更新。顺序
您也可以通过如下方式判断统计信息是否更新：
  • 查询系统表hologres.hg_table_properties中的analyze_tuple列，确认数据的行数是否正确。您也可以直接查看Scan节点中rows的值。
  • 查询系统表hologres.hg_stats，显示每一列的直方图、平均宽度及不同值的数量等信息。如下图所示。其他
如果遇到以下问题，您需要先执行analyze table，再运行导入任务，可以系统的提升效率：
  • 多表JOIN超出内存OOM。

    通常会产生Query executor exceeded total memory limitation xxxxx: yyyy bytes used报错。

  • 导入效率较低。

    在Hologres查询或导入数据时，效率较低，运行的任务长时间不结束。

创建表时选择合适的分布列（Distribution Key）

创建表时，您可以通过如下原则选择合适的分布列：
  • 选择JOIN查询时的连接条件列作为分布列。
    如果Motion耗时较高，您可以通过colocate with属性，放置多个表于同一个Table Group中。同时设置Join Key为Distribution Key，避免多表连接时数据重分布带来的额外开销，详细操作请参见设置Table Group与Shard Count。motion示例语句如下。
    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;

select count(1) from tmp join tmp1 on tmp.a = tmp1.b //设置分布列为Join Key。
    通过重新设置表的Distribute Key，数据按照相同的Hash Key分布于Shard中。因为数据分布相同，Motion算子被优化（上图中红框内的算子），表明数据不会重新分布，从而避免了冗余的网络开销。设置DKHologres包含四种Motion Node，分别对应四种数据重分布场景，如下表所示。
    类型 描述
    Redistribute Motion 数据通过哈希分布或随机分布，Shuffer到一个或多个Shard。
    Broadcast Motion 复制数据至所有Shard。

    仅在Shard数量与广播的表的数量均较少时，Broadcast Motion的优势较大。
    Gather Motion 汇总数据至一个Shard。
    Forward Motion 用于联邦查询场景。外部数据源或执行引擎与Hologres执行引擎进行数据传输。
    如果explain analyze的结果显示Motion算子耗时较高，则您可以重新设计分布列。

    如果统计信息错误，导致生成Gather Motion或Broad Cast Motion，则您可以通过analyze命令修改使用更高效的Redistribute Motion分布方式。

    您可以设置如下参数，禁止生成Motion算子，再对比查询耗时，示例语句如下。
    set optimizer_enable_motion_broadcast = off; //禁止生成Broadcast Motion。
set optimizer_enable_motion_redistribute = off; //禁止生成Redistribute Motion。
  • 选择Group By频繁的列作为分布列。
    Group By Key会导致数据按照分组列的Key重新分布数据，如果Group By耗时较高，您可以将Group By的列重新设置为分布列。
    select a from t1 group by a; //数据按照a列的值进行Hash重分布。
  • 选择数据分布均匀离散的列作为分布列。
    数据在多个Shard上分布不均匀会导致查询速度较慢，您可以通过如下语句判断数据分布是否存在倾斜。
    select count(1) from t1 group by hg_shard_id;
    如果数据存在倾斜，则需要更改Distribute_key，选择数据分布均匀离散的列作为分布列。
    说明 更改Distribute_key需要重新创建表并导入数据。

关闭Dictionary_encoding

对于String类型的相关查询，Dictionary Encoding或Decoding会减少比较字符串的耗时，但是会带来大量的Decode或Encode开销。

Hologres默认对所有的String列建立Dictionary_encoding，您可以设置Dictionary_encoding_columns为空，或关闭部分列的自动Dictionary_encoding功能。

您可以通过上文获取执行后的统计信息获取Decode算子的耗时，如果耗时较高，则请关闭Decode。当表的String类型字段较多时，建议关闭String类型的Decode。示例语句如下。
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;

其他优化项

  • 选择合适的存储类型。
    Hologres支持行存储和列存储两种存储模式，您可以根据业务场景选择合适的存储类型，如下表所示。
    类型 适用场景 缺点
    行存储
    • 按主键进行高QPS的点查询场景。
    • 一次能读取所有列，并且对UPDATE、DELETE及INSERT操作的性能较好。
    • Blink维表必须使用行存储。
    		 大范围的查询、全表扫描及聚合等操作性能较差。
    列存储 适用于多列按范围查询、单表聚合及多表连接等数据分析场景。 UPDATE和DELETE操作及无索引场景下的点查询性能慢于行存储。
  • 选择合适的数据类型。
    Hologres支持多种数据类型，您可以根据业务场景以及需求选择合适的数据类型，原则如下：
    • 尽量选用存储空间小的类型。
      • 优先使用INT类型，而不是BIGINT类型。
      • DECIMAL的精度尽量小。
      • Group By的列不建议使用Float类型。
      • 优先使用VARCHAR(n)，而不是CHAR(n)，n的取值尽量小。
    • 使用一致的数据类型。

      进行多表关联时，不同列尽量使用相同的数据类型。避免Hologres将不同类型的列进行隐示类型转换，造成额外的开销。

    • UNION或Group By等操作使用DECIMAL类型。

      UNION或Group By等操作暂不支持DOUBLE PRECISION和FLOAT数据类型，需要使用DECIMAL类型。

  • 选择合适的主键。
    主键（Primary Key）主要用于保证数据的唯一性，适用于主键重复的导入数据场景。您可以在导入数据时设置option选择去重方式，如下所示：
    • ignore：忽略新数据。
    • update：新数据覆盖旧数据。

    合理的设置主键能帮助优化器在某些场景下生成更好的执行计划。例如，查询为group by pk,a,b,c的场景。

    但是在列存储场景，主键的设置对于写入数据的性能会有较大的影响。通常，不设置主键的写入性能是设置主键的3倍。

  • 选择合适的分区表。
    Hologres当前仅支持创建一级分区表。合理的设置分区会加速查询性能，设置分区适用的场景如下：
    • 删除整个子表的分区，不影响其他分区数据。DROP语句的性能高于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;
  • 选择合适的索引。
    Hologres支持设置多种索引，不同索引的作用不同。您可以根据业务场景选择合适的索引，提升查询性能。索引类型如下表所示。
    类型 名称 描述 使用建议 示例查询语句
    Clustering_key 聚簇索引 文件内聚簇索引，数据在文件内按该索引排序。

    对于部分范围查询，Hologres可以直接通过聚簇索引的数据有序属性进行过滤。

    		 将范围查询或Filter查询列作为聚簇索引列。 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 分段键 文件索引，数据按该索引划分文件。

    您可以通过Segment Key快速索引到目标文件。

    		 您需要先通过Segment_key进行快速过滤，再通过Bitmap或Cluster索引进行文件内范围或等值查询。

    建议将常用的等值或范围查询作为Segment_key。

    		 select sum(a) from tb1 where ts > '2020-01-01' and a < '2020-03-02';
    Cluster索引和Segment索引都需要满足传统数据库（例如MySQL）的最左前缀匹配原则，即按照Index书写的最左列排序进行索引。如果最左列为有序的场景，则按照左边第二列进行排序。示例如下。
    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索引。
    Bitmap Index支持多个列的and及or查询，示例如下。
    call set_table_property('tmp', 'bitmap_columns', 'a,b,c');
select * from tmp where a = 1 and b = 2   //可以使用Bitmap索引。
select * from tmp where a = 1 or b = 2   //可以使用Bitmap索引。
    说明 Bitmap索引可以在创建表后添加，Cluster索引和Segment索引则在创建表时已经指定，后续无法再添加。

    查看是否使用index。

    创建table tmp表并指定索引字段，语句如下。
    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;
    • 查看是否使用cluster index，语句如下。
      explain select * from tmp where a > 1;
      cluster
    • 查看是否使用bitmap index，语句如下。
      explain select * from tmp where c = 1;
      bitmap
    • 查看是否使用segment key，语句如下。
      explain select * from tmp where b > 1;
      segment
  • 单阶段Agg优化为多阶段Agg。

    如果Agg算子耗时过高，您可以检查是否没有做Local Shard级别的预聚合。

    通过在单个Shard内先进行本地的Agg操作，可以减少最终聚合操作的数据量，提升性能。具体如下：
    • 三阶段聚合：数据先进行文件级别的聚合计算，再聚合单个Shard内的数据，最后汇总所有Shard的结果。三阶段聚合
    • 两阶段聚合：数据先在单个Shard内进行聚合计算，再汇总所有Shard的结果。两阶段聚合
    您可以强制Hologres进行多阶段聚合操作，语句如下。
    set optimizer_force_multistage_agg = on;

优化SQL语句

您可以通过优化相应的SQL语句来提高查询效率，具体如下：
  • 避免联邦查询。

    如果SQL语句中包含Hologres执行引擎不支持的算子，则系统会将该算子发送至Postgres的执行引擎执行。此时查询的性能较差，需要修改相关查询语句。

    示例

    Hologres不支持not in，则会将not in操作转到Postgres执行。同时，将not in修改为not exists。优化前的SQL语句如下。
    explain select * from tmp where a not in (select a from tmp1);
    External算子代表该部分SQL语句是在Postgres的执行引擎中执行的。post
    优化后的SQL语句如下。
    explain select * from tmp where not exists (select a from tmp1 where a = tmp.a);
    优化后的SQL
  • 避免模糊查询。

    模糊查询（例如Like操作）不会创建索引。

  • 关闭查询的缓存。
    Hologres默认对相同的查询或子查询结果进行缓存，重复执行会命中缓存结果。您可以关闭缓存，避免对性能测试的影响，示例语句如下。
    set hg_experimental_enable_result_cache = off;

获取更新后的统计信息

Hologres的计算模型中每个执行算子都包含open()和getNext()两种操作，数据自下而上在算子间流式传输。

explain analyze语句可以获取整个查询执行过程中的详细信息。

如果您的查询执行速度较慢，可以使用explain analyze语句定位耗时较高的算子。示例语句如下。
explain analyze select sum(a) from tmp group by a;
每个算子的[]中展示了查询的执行信息。示例结果如下图。更新后的统计信息explain analyze结果的主要参数如下表所示。
参数 描述
Row_count 当前算子输出的行数。
Count 当前算子扫描的文件数。
Record_batch_producer 当前算子输出的数据量大小。
(Max/Min/Avg)_open_time 当前算子初始化所花的大致时间。
(Max/Min/Avg)_get_next_time 当前算子等待下游算子的数据所花的大致时间。
Physical_reads 当前算子的IO次数。
Record_batches_produced 当前算子记录的输出大小。
整个查询计划的执行过程耗时项如下表所示。
参数 描述
Get the first block cost: 后端执行查询计划的过程中，最上层节点获取首个block数据的时间。
Get result cost： 前端获取后端返回数据的整体耗时。
Start query cost: 前端分发执行计划的耗时。
Optimizer cost: 前端优化器优化查询SQL语句的耗时。

示例结果中，前端获取后端数据以及聚合操作耗时较多。您可以根据生成的统计信息优化查询性能。