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

更新统计信息

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

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

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

  • tmp1表的数据量为1000万行,tmp表的数据量为1000行。 Hologres默认统计信息中的行数为1000行,通过执行explain SQL语句,如下展示结果所示,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万行,表明统计信息已经更新。顺序

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

推荐您在如下情况下运行analyze tablename
  • 在导入数据之后。
  • 在执行大量的INSERT、UPDATE以及DELETE操作之后。
  • 内部表、外部表均需要ANALYZE。
  • 分区表针对父表做ANALYZE。
  • 如果遇到以下问题,您需要先执行analyze table,再运行导入任务,可以系统的提升效率:
    • 多表JOIN超出内存OOM。

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

    • 导入效率较低。

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

您也可以通过如下方式判断统计信息是否更新:
  • 查询系统表hologres.hg_table_properties中的analyze_tuple列,确认数据的行数是否正确。您也可以直接查看Scan节点中rows的值。
  • 查询系统表hologres.hg_stats,显示每一列的直方图、平均宽度及不同值的数量等信息。如下图所示。其他

查看并更改扩容后的Shard数

Shard数代表查询执行的并行度。Shard个数对查询性能影响至关重要,Shard数设置少,会导致并行度不足。Shard数设置过多,也会引起查询启动开销大,降低查询效率。设置与实例规格匹配的Shard数,可以改善查询效率。

Hologres为每个实例设置了默认的Shard数,Shard数等于实例中用于核心查询的Core数。这里的core数,略小于实际购买的Core数(实际购买的Core会被分配给不同的节点,包括查询节点、接入节点、控制节点和调度节点等)。不同规格实例默认的Shard数,请参见实例规格概述。当实例扩容后,扩容之前旧的DB对应的默认Shard数不会自动修改,需要根据实际情况修改Shard数,扩容后新建DB的Shard数为当前规格的默认数量。

如下场景需要修改Shard数:
  • 扩容后,因业务需要,原有业务有规模增长,需要提高原有业务的查询效率。此时,您需要创建新的Table Group,并为其设置更大的Shard数。原有的表和数据仍然在旧的Table Group中,您需要将数据重新导入新的Table Group中,完成Resharding的过程。
  • 扩容后,需要上线新业务,但已有业务并不变化。此时,建议您创建新的Table Group,并为其设置适合的Shard数,并不调整原有表的结构。
说明 一个DB内可以创建多个Table Group,但所有Table Group的Shard总数之和不应超过Hologres推荐的默认Shard数,这是对CPU资源的最有效利用。
您可以按照如下操作修改Shard数:
  1. 查看Table Group。
    首先查看这张表在哪个Table Group中,一个Table Group中的表Shard数相同。如下示例中,您需要将table_name修改为实际需要查询的表名称。
    select property_value from hologres.hg_table_properties where table_name = 'tmp' and property_key = 'table_group';
  2. 查看该Table Group对应的Shard数。
    查询结果中,is_default_tg代表默认的Table Group,shard_count代表当前的Shard数。
    select * from hologres.hg_table_group_properties;
    shard数
  3. 修改Shard数。
    您可以执行如下命令更改Table Group的Shard数。其中,Shard数可以根据实际情况进行调整。
    call hg_update_database_property('shard_count', '60');
    该语句执行成功后,将会新建一个Table Group,并将其设置为该DB的默认Table Group,其Shard数为60。同时:
    • 此后新建的表都会放到该Table Group下,除非再次修改Table Group。
    • 原来的表和数据还会在原Table Group中,不会默认迁移到新的Table Group中。
    • 需要将原来表和数据都迁移至新的Table Group或者删除,原Table Group才会失效。

创建表时选择合适的分布列(Distribution Key)

分布列(Distribution Key)用于将数据划分到多个Shard,划分均衡可以避免数据倾斜。多个相关的表设计为相同的Distribution Key,可以起到Local Join的加速效果。创建表时,您可以通过如下原则选择合适的分布列:
  • 选择JOIN查询时的连接条件列作为分布列。
    例如设置Distribution Key,表tmp和tmp1做Join,通过执行explain SQL语句看到执行计划中有Redistribution Motion,说明数据有重分布,没有Local Join,导致查询效率比较低。您需要重新建表并同时设置Join Key为Distribution Key,避免多表连接时数据重分布带来的额外开销。motion重新建表后两个表的DDL示例语句如下。
    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,再次执行explain SQL语句,数据按照相同的Hash Key分布于Shard中。因为数据分布相同,Motion算子被优化(上图中红框内的算子),表明数据不会重新分布,从而避免了冗余的网络开销。设置DKHologres包含四种Motion Node,分别对应四种数据重分布场景,如下表所示。
    类型 描述
    Redistribute Motion 数据通过哈希分布或随机分布,Shuffer到一个或多个Shard。
    Broadcast Motion 复制数据至所有Shard。

    仅在Shard数量与广播的表的数量均较少时,Broadcast Motion的优势较大。

    Gather Motion 汇总数据至一个Shard。
    Forward Motion 用于联邦查询场景。外部数据源或执行引擎与Hologres执行引擎进行数据传输。
    结合explain SQL语句执行结果您可以注意如下事项:
    • 如果Motion算子耗时较高,则您可以重新设计分布列。
    • Broadcast Motion只有在Shard数较少,且广播表的数量较少的场景下有优势。
    • 如果统计信息错误,导致生成Gather Motion或Broad Cast Motion,则您可以通过analyze tablename命令将其修改为更高效的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类型字段较多时,建议关闭Dictionary_encoding功能可以改善性能。

当表的String类型字段较多时,按需选择,可以不用将所有的String类型都加入Dictionary_encoding。示例语句如下:
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操作的性能较好。
    大范围的查询、全表扫描及聚合等操作性能较差。
    列存储 适用于多列按范围查询、单表聚合及多表连接等数据分析场景。 UPDATE和DELETE操作及无索引场景下的点查询性能慢于行存储。
  • 选择合适的数据类型。
    Hologres支持多种数据类型,您可以根据业务场景以及需求选择合适的数据类型,原则如下:
    • 尽量选用存储空间小的类型。
      • 优先使用INT类型,而不是BIGINT类型。
      • DECIMAL的精度尽量小。
      • Group By的列不建议使用Float类型。
      • 优先使用TEXT,而不是VARCHAR(n)和CHAR(n),n的取值尽量小。
      • 日期类型使用TIMESTAMPTA、DATE,避免使用TEXT。
    • 使用一致的数据类型。

      进行多表关联时,不同列尽量使用相同的数据类型。避免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查询列作为聚簇索引列。索引过滤具备左匹配原则,建议设置不超过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';
    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;