如何设计时序数据表

时序数据表可以为您提高查询和存储性能,本文介绍如何根据时序数据的来源和场景建立时序数据表。

时序数据示例

时序数据建模至关重要,需要仔细分析数据来源的特性和查询的场景,建立合理的数据表,以便达到最佳的存储和查询性能。以下图的空气检测数据为例:

时序数据表示意

每个时间点对应的数据记录可以详细分为几个部分:

  • 表(Table):代表一系列同类时序数据的集合。

  • 标签(Tags):表明指标项监测针对的具体对象属性。其中一个标签(Tag)由一个标签键(Key)和一个对应的标签值(Value)组成。时序引擎默认会为每一个标签的键-值对都建立标签与时间序列之间的索引。在特定场合下,标签也可能会有不同的叫法,例如可能被称为Labels或者Dimensions。

  • 时间戳(Timestamp):表示该数据记录对应的生成时间。

  • 字段(Field):一条数据记录可以有多个字段值,表示指标的不同方面。时序引擎不会为字段建立索引。

表设计的最佳实践

基于以上的数据模型,我们便可以将同一类时序数据的度量(如上图中的AQM)设计为表名,并按照SQL的CREATE TABLE语法进行表设计。

以上文的空气质量监测的时序模型为例,可以采用的表定义如下所示。

CREATE TABLE aqm (
    city        VARCHAR TAG,
    district    VARCHAR TAG,
    id          VARCHAR TAG,
    time        TIMESTAMP,
    pm2_5       DOUBLE,
    pm10        DOUBLE,
    so2         DOUBLE,
    no2         DOUBLE,
    PRIMARY KEY(id)  ////单机版实例不支持PRIMARY KEY,如果使用的是单机版实例,请删除此行
);

在此定义基础上,随后向表中写数据便可通过INSERT语句来进行:

INSERT INTO aqm (city, district, id, time, pm2_5, pm10, so2, no2) 
VALUES ('hangzhou', 'yuhang', 'HY00001', '2019-04-18 10:00:00', 31.0, 66.0, 10.0, 43.0);

INSERT INTO aqm (city, district, id, time, pm2_5, pm10, so2, no2) 
VALUES ('hangzhou', 'yuhang', 'HY00001', '2019-04-18 10:01:00', 31.2, 66.0, 10.5, 43.1);

也可以通过下述INSERT语句实现批量写入数据。

INSERT INTO aqm (city, district, id, time, pm2_5, pm10, so2, no2) 
VALUES ('hangzhou', 'yuhang', 'HY00001', '2019-04-18 10:02:00', 31.3, 66.0, 10.0, 42.9),  
('hangzhou', 'yuhang', 'HY00001', '2019-04-18 10:03:00', 31.2, 66.4, 10.3, 43.0);

说明

关键字TAG是时序引擎对SQL的语法扩展,用于将表中的部分列标示为“标签”。为避免在时序数据建表的过程中纠结于应该将哪些字段应定义为TAG,建议尝试遵循以下原则:

  • 能够标示数据采集源属性的字段定义为TAG,特别是能够唯一标示数据源的属性。

  • 应避免使用进程ID,时间关联属性等易变值作为标签,即便这类属性的类型是字符串,仍然建议将其定义为Field。如果使用此类易变属性作为标签,将会导致时间线数量急剧膨胀,并带来时间线索引大小剧增,反而不利于查询。

PRIMARY KEY设计的最佳实践

数据库会根据PRIMARY KEY进行存储分片与查询优化,指定PRIMARY KEY的查询请求将会非常高效,强烈建议建表时指定。

说明

单机版实例不支持PRIMARY KEY。

通常建议选择数据源的唯一标识作为PRIMARY KEY,场景示例如下:

  • 物联网、工业互联网场景,可以采用设备ID作为PRIMARY KEY。

  • 车联网场景,可以采用车辆唯一标识来作为PRIMARY KEY。

  • 监控场景,可以采用APP ID或者host:port等标识作为PRIMARY KEY。

以上述时序数据表aqm的示例,如果业务存在大量基于ID的查询,则指定ID作为PRIMARY KEY是最佳选择。

CREATE TABLE aqm (
    city        VARCHAR TAG,
    district    VARCHAR TAG,
    id          VARCHAR TAG,
    time        TIMESTAMP,
    pm2_5       DOUBLE,
    pm10        DOUBLE,
    so2         DOUBLE,
    no2         DOUBLE,
    PRIMARY KEY (id)   //单机版实例不支持PRIMARY KEY,如果使用的是单机版实例,请删除此行
);
重要

和传统的关系型数据库不同,在时序数据表中定义为PRIMARY KEY的列并不要求列的值遵循唯一性约束,但是需要出现在PRIMARY KEY列表中的列名必须是标签列。

数据类型选择的最佳实践

时序引擎当前支持的数据类型可参见数据类型。对于时序数据表的创建,各种列的数据类型选择的建议如下:

  • 标签(Tag)列

    声明为TAG的列固定为VARCHAR类型,无法更改。

  • 时间戳(Timestamp)列

    在存储引擎时间戳实际上会按照BIGINT类型统一存为Epoch时间戳以便实现更优的时间戳压缩。但用户可结合对时间戳的计算需求自行选择在建表时使用BIGINT类型或TIMESTAMP类型。

  • 字段(Field)列

    理论上字段列可选择使用所有数据类型。但出于数据压缩和查询计算的角度考虑,应尽量避免使用VARCHAR类型作为字段列的类型。