Event Time Column(Segment Key)

如果您需对大规模数据集使用基于主键的UPDATE或含范围过滤条件的查询,可以考虑为表设置Event Time Column(Segment Key),系统将数据文件基于Event Time Column范围排序后进行合并,减少文件之间的重叠,使得查询时能够过滤掉尽可能多的文件,从而提升查询效率。合理地应用Event Time Column有助于提高数据库的处理效率、查询速度和整体性能,本文为您介绍在Hologres中为表设置分段键Event_time_column。

Event Time Column介绍

Event_time_column原名为Segment Key,在Hologres V0.9版本默认改名为Event_time_column,Segment Key依旧向下兼容使用。

Segment Key主要适用的场景如下:

  • 含范围过滤条件(包括等值条件)的查询场景。

  • 基于主键的UPDATE。

Event_time_column在设置时,需要在建表时指定,语法如下:

-- Hologres V2.1版本起支持的语法
CREATE TABLE <table_name> (...) WITH (event_time_column = '[<columnName>[,...]]');

-- 所有版本支持的语法
BEGIN;
CREATE TABLE <table_name> (...);
call set_table_property('<table_name>', 'event_time_column', '[<columnName> [,...]]');
COMMIT;

参数说明:

参数

说明

table_name

设置分段键的表名称。

columnName

设置分段键的列。

使用建议

  • Event_time_column适用于数据为单调递增或单调递减的有序字段,例如时间戳字段。非常适用于日志、流量等和时间强相关的数据,合理设置可极大提升性能。完全无序的Event_time_column会导致合并之后每个文件缺乏区分度,达不到任何文件过滤效果。

  • 如果表不存在明显的单调递增或单调递减的字段,可以选择额外扩充一列update_time,每次UPSERT时将当前时间写入该新增字段。

  • Event_time_column具备左匹配原则,因此不建议将多个字段设置为Event_time_column,否则使得查询场景受限,达不到加速效果,一般情况建议选择设置两个或者两个以内字段设置为Event_time_column。

使用限制

  • Event_time_column必须为not nullable的列或者列组合,可以不设置,但不能设置为空。Hologres V1.3.20~1.3.27版本支持Event_time_column对应列的约束为nullable,从V1.3.28版本开始不支持Event_time_column对应列的约束为nullable,为nullable的Event_time_column可能会影响数据正确性,如果业务有强需求设置Event_time_column为null,可以在SQL前添加如下参数:

    set hg_experimental_enable_nullable_segment_key = true;

    您可以使用如下SQL检查当前数据库是否有属性为nullable的Event_time_column(Segment Key):

    WITH t_base AS (
        SELECT
            *
        FROM
            hologres.hg_table_info
        WHERE
            collect_time::date = CURRENT_DATE
    ),
    t1 AS (
        SELECT
            db_name,
            schema_name,
            table_name,
            jsonb_array_elements(table_meta::jsonb -> 'columns') cols
        FROM
            t_base
    ),
    t2 AS (
        SELECT
            db_name,
            schema_name,
            table_name,
            cols ->> 'name' col_name
        FROM
            t1
        WHERE
            cols -> 'nullable' = 'true'::jsonb
    ),
    t3 AS (
        SELECT
            db_name,
            schema_name,
            table_name,
            regexp_replace(regexp_split_to_table(table_meta::jsonb ->> 'segment_key', ','), ':asc|:desc$', '') segment_key_col
        FROM
            t_base
        WHERE
            table_meta::jsonb -> 'segment_key' IS NOT NULL
    )
    SELECT
        CURRENT_DATE,
        t3.db_name,
        t3.schema_name,
        t3.table_name,
        jsonb_build_object('nullable_segment_key_column', string_agg(t3.segment_key_col, ',')) as nullable_segment_key_column
    FROM
        t2,
        t3
    WHERE
        t3.db_name = t2.db_name
        AND t3.schema_name = t2.schema_name
        AND t3.table_name = t2.table_name
        AND t2.col_name = t3.segment_key_col
    GROUP BY
        t3.db_name,
        t3.schema_name,
        t3.table_name;
    
  • 不支持修改Event_time_column,如需修改请重新建表。

  • 行存表不能设置Event_time_column。

  • 列存表默认将表中的第一个非空的Timestamp或Timestamptz类型字段作为Event_time_column,如果不存在这样的字段,则默认将第一个非空的Date类型字段作为Event_time_column(Hologres V0.9之前的版本默认为空)。

  • 不支持Decimal、Numeric、Float、Double、Array、Json、Jsonb、Bit、Money及其他复杂数据类型。

技术原理

以一个Shard为例,数据写入时的步骤如下图所示:分段键技术原理

  1. 在一个Shard内,数据会先写入至一个内存表,为了保证写入效率最高,会使用Append Only的方式写入。内存表有一定的大小,当内存表写满之后,系统会将内存表中的数据逐渐异步Flush到文件。

  2. 写入时为了追求写入性能,都为Append Only方式写入,文件数会越来越多。因此系统会在后台周期将文件进行合并。如果设置了Event_time_column(Segment Key),系统将文件基于Segment Key范围排序后,选择Segment Key范围相邻的文件进行合并,减少文件之间的重叠,这样就使得查询时能够过滤掉尽可能多的文件,从而提升查询效率。

  3. 文件基于Segment Key排序,因此Segment Key也具有左匹配原则,即abc三个字段设置了Segment Key,查询时查a,b,c或者查a,b可以命中Segment Key,如果查a,c则只有a可以命中Segment Key,查b,c则无法命中。

从以上的介绍中可以看出Segment Key可以对以下场景进行加速:

  • 含范围过滤条件(包括等值条件)的查询场景。

    如果查询字段设置为Segment Key,那么Hologres在扫描数据时,会将范围查询条件同文件内列的统计信息(min/max)进行匹配,快速过滤出所需的文件,加速查询。

  • 基于主键的UPDATE。

    Hologres的UPDATE命令原理是由DELETE命令和INSERT命令组合实现。在基于主键的UPDATEINSERT ON CONFLICT(UPSERT)场景中,会先根据主键找到目标表(旧数据)的Segment Key值,再根据旧数据的Segment Key找到旧数据所在文件,最终定位旧数据所在位置进而标记为DELETE。如果设置了合理的Segment Key,那么就会快速定位到旧数据的文件,提高写入性能。相反,如果这张列存表没有配置Segment Key、Segment Key配置了不合理的字段或者Segment Key对应的字段在写入时没有与时间有强相关性(比如基本乱序),那在查找旧数据时需要扫描的文件将会非常多,不仅会有大量的IO操作,而且会大量占用CPU,影响写入性能和整个实例的负载。

使用示例

  • 在建表时创建一个Event_time_column。

    • V2.1版本起支持的建表语法:

      CREATE TABLE tbl_segment_test (
          a int NOT NULL,
          b timestamptz NOT NULL
      )
      WITH (
          event_time_column = 'b'
      );
      
      INSERT INTO tbl_segment_test values
      (1,'2022-09-05 10:23:54+08'),
      (2,'2022-09-05 10:24:54+08'),
      (3,'2022-09-05 10:25:54+08'),
      (4,'2022-09-05 10:26:54+08');
      
      EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';
    • 所有版本支持的建表语法:

      BEGIN;
      CREATE TABLE tbl_segment_test (
          a int NOT NULL,
          b timestamptz NOT NULL
          );
      CALL set_table_property('tbl_segment_test', 'event_time_column', 'b');
      COMMIT;
      
      INSERT INTO tbl_segment_test VALUES 
      (1,'2022-09-05 10:23:54+08'),
      (2,'2022-09-05 10:24:54+08'),
      (3,'2022-09-05 10:25:54+08'),
      (4,'2022-09-05 10:26:54+08');
      
      EXPLAIN SELECT * FROM tbl_segment_test WHERE b > '2022-09-05 10:24:54+08';

    同时通过查询执行计划(explain SQL),如果出现Segment Filter,说明有查询命中Event_time_column。分段键执行计划

  • 建表时创建多个Event_time_column。

    • V2.1版本起支持的建表语法:

      CREATE TABLE tbl_segment_test_2 (
          a int NOT NULL,
          b timestamptz NOT NULL
      )
      WITH (
          event_time_column = 'a,b'
      );
      
      INSERT INTO tbl_segment_test_2 VALUES 
      (1,'2022-09-05 10:23:54+08'),
      (2,'2022-09-05 10:24:54+08'),
      (3,'2022-09-05 10:25:54+08'),
      (4,'2022-09-05 10:26:54+08')
      ;
      
      --不可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
      
      --可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';
    • 所有版本支持的建表语法:

      BEGIN;
      CREATE TABLE tbl_segment_test_2 (
          a int NOT NULL,
          b timestamptz NOT NULL
          );
      CALL set_table_property('tbl_segment_test_2', 'event_time_column', 'a,b');
      COMMIT;
      
      INSERT INTO tbl_segment_test_2 VALUES 
      (1,'2022-09-05 10:23:54+08'),
      (2,'2022-09-05 10:24:54+08'),
      (3,'2022-09-05 10:25:54+08'),
      (4,'2022-09-05 10:26:54+08')
      ;
      
      --不可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE b > '2022-09-05 10:24:54+08';
      
      --可命中segment key
      SELECT * FROM tbl_segment_test_2 WHERE a = 3 and b > '2022-09-05 10:24:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b < '2022-09-05 10:26:54+08';
      SELECT * FROM tbl_segment_test_2 WHERE a > 3 and b > '2022-09-05 10:24:54+08';

相关文档

根据业务查询场景设置合适的表属性指南,请参见场景化建表调优指南