数据存储:时序数据库建模与查询
本篇要点:能为一批设备设计出可维护的时序表结构,并对降采样、保留策略与乱序补传给出可验证的参数。 前置文章:[《接入平台:Broker 集群与水平扩展》 接入平台](/guide/iot/iot-broker-cluster-scaling.html)把消息甩到了下游;这一章处理它们的终点——一条读数究竟以什么形状落进磁盘。
为什么需要它
一条温度读数看起来很轻:一个时间戳、一个设备号、一个值。麻烦在于它是连续不断来的,而且不总是按时间顺序到。
一个真实负载是这样的:23 台边缘网关汇聚,上报频率分三档——电参量 5 秒、温湿度 60 秒、水表 15 分钟,折算日增约 8.6 亿行,单行有效载荷 3 到 12 个字段。关键特征不在量大,在写入形态:约 12% 的数据来自断网补传,到达时间戳与事件时间戳最大偏差 47 分钟。
这 12% 决定了乱序写入(out-of-order write)能力是硬指标,不是加分项。原来的 MySQL 8.0 按天分区表面向这种负载时,问题不在存不下,在写入路径和查询路径互相拖:批量导入期间查询变慢,补偿写入又要重算分区边界。
时序数据库(time-series database,TSDB)是把这类负载当第一等公民设计的系统:按时间组织物理存储、对重复度高的时间序列做列式压缩、把「设备是谁」这类静态属性单独抽出来。这一章用一次三选一的选型过程,讲清它到底特殊在哪里。
核心概念
时间线、标签与字段
一条时间线(time series)由「测量名 + 一组标签」唯一确定,线上挂着一串按时间排列的采样点,每个点有若干字段(field)。标签(tag)描述设备身份,比如站点、网关号、相位类型,取值重复度极高;字段(field)是真正随时间变化的值,比如电压、电流。
类比的抽屉:标签决定这条数据放进哪个抽屉,字段是抽屉里的内容。反例是把设备型号、站点名这类静态属性当字段存,每行重复一遍,压缩率立刻掉下来,按站点聚合时还要全表扫描。
超级表与子表
TDengine 用超级表(super table,STable)承载一类设备,每台设备对应一张子表(subtable),静态属性放在标签列。建表时官方限制要记住:一个超级表最多 4096 列(含标签列),标签列最多 128 个,全部标签总长度不超过 16 KB;第一个字段必须是 TIMESTAMP,系统自动把它设为主键。
TimescaleDB 走的是另一条路:超表(hypertable)本质是 PostgreSQL 表按时间切成 chunk,标签是普通列加索引。InfluxDB 3 的数据模型是 database → table → column,主键是「有序标签 + time」,底层 Parquet 文件按主键排序。
图 7-1 三种时序建模机制的结构差异
如图 7-1 所示,三者的差异不在 SQL 方言,在「设备」这个概念如何落到物理存储上。这一层选错,后面所有优化都是补偿。
乱序写入与写入放大
乱序写入指事件时间早于已落盘时间的记录。它对存储的影响是写入放大(write amplification):数据按时间分区合并,一条迟到 47 分钟的记录要落进已经写过的分区,系统得重排、重压缩那段数据,实际写盘量大于逻辑数据量。乱序比例越高,放大越明显。
降采样与保留策略
降采样(downsampling)把原始粒度聚合成更粗的粒度,保留策略(retention policy)决定各级数据留多久。常见组合是原始数据在线 180 天、1 分钟聚合 3 年、1 小时聚合 5 年。反例是把原始数据永久保留——存储成本线性增长,而查一年的趋势根本用不到秒级精度。
压缩比
压缩比(compression ratio)是落盘大小相对原始 CSV 的倍数。它由三件事决定:时间戳的等间隔性、字段值的重复度、标签的基数。同一份数据在不同产品上能差出两三倍,而压缩比会直接影响查询时要解压多少数据。
动手实践
第一步:按设备类型建超级表
CREATE STABLE IF NOT EXISTS iot.meter (
ts TIMESTAMP,
voltage FLOAT,
current FLOAT,
energy BIGINT
) TAGS (
station VARCHAR(32),
gateway_id VARCHAR(32),
phase_type TINYINT
);
INSERT INTO iot.meter_1001 USING iot.meter
TAGS ('WX-01', 'GW-07', 1)
VALUES (NOW, 220.4, 10.2, 84213);预期输出:第一条 INSERT 执行后,iot.meter_1001 子表自动创建,标签值随首条数据落库;SHOW iot.STABLES 能看到 meter 的子表数从 0 变 1。2 万点位按设备类型归并,大约 40 张超级表、2 万张子表,建模工作量集中在「设备描述文件 → 建表语句」的转换上,可以脚本化。
需要复合主键时用 COMPOSITE KEY 指定第二列,可选类型是 TIMESTAMP、整数或 VARCHAR。多数场景用不上——点位加时间已经能唯一确定一行。
第二步:降采样视图要显式刷新
TimescaleDB 侧用连续聚合(continuous aggregate,CAGG)生成 1 分钟粒度视图:
CREATE MATERIALIZED VIEW sensor_1m
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 minute', ts) AS bucket,
sensor_id,
avg(value) AS avg_value,
max(value) AS max_value,
count(*) AS sample_count
FROM sensor_data
GROUP BY bucket, sensor_id
WITH NO DATA;预期输出:视图创建成功但查询为空,因为 WITH NO DATA 不填数据;配置刷新策略后才有内容。这里有个版本坑:TimescaleDB 2.13 起实时聚合默认关闭,新建的 CAGG 查询默认不包含最新原始数据,必须显式设 timescaledb.materialized_only = false 才能查到当前分钟。评估时因为没注意这个默认值,出现过「看板比实际晚 1 小时」的误判。
TDengine 侧的对应机制是 TSMA(时间区间小物化聚合),语法为 CREATE TSMA name ON stb FUNCTION(...) INTERVAL(...),大窗口叠小窗口用 CREATE RECURSIVE TSMA。两条限制要提前知道:只能基于超级表或基本表创建,不能基于子表;函数参数必须是普通列,不能是标签列。
第三步:用实测数据做决策
三款产品在同一份数据切片(2 万点位、30 天、4.1 亿行,8C16G + NVMe)上的结果:
| 维度 | TDengine 3.4.3.9 | TimescaleDB 2.32.0 | InfluxDB 3.12.2 |
|---|---|---|---|
| 4.1 亿行占用空间 | 8.7 GB | 21.4 GB | 13.9 GB |
| 压缩比(相对原始 CSV) | 约 15.3 : 1 | 约 6.2 : 1 | 约 9.6 : 1 |
| 单点位 24 小时查询 P95 | 41 ms | 87 ms | 62 ms |
| 跨设备 1 小时聚合 P95 | 1.9 s | 3.4 s | 2.7 s |
| 单节点写入吞吐 | 41.2 万行/秒 | 18.6 万行/秒 | 26.3 万行/秒 |
| 标签可变更 | 可(ALTER STABLE) | 可(普通列) | 不可(建表后固定) |
表 7-1 三款产品在本项目数据切片上的实测对比
看这张表时要克制:三者都远高于本项目 1.0 万行/秒的峰值需求,写入吞吐不构成决策依据。真正起作用的是压缩比、查询延迟,以及「标签能不能改」。InfluxDB 3 的标签列定义在建表后不可变更,这个约束对实验环境没影响,对工程现场影响明确——这一年有 3 次点位属性调整需求(新增分区编号、合并两个测点分类),每次都要重建表并迁移历史数据。
还有一条要写进容量规划:TDengine 在乱序比例升到 25% 的加压测试中,写入吞吐降到 22.8 万行/秒,磁盘写入放大约 2.1 倍。本期 12% 的乱序比例下没触发,但断网补传比例一旦上升就会碰到。
常见误解
以为时序库就是「带时间戳的 MySQL」。 差别在物理层:时序库按时间分区、按列压缩、把标签与字段分开存。用关系库的建模习惯去套,会得到一张每行都重复站点名的宽表,压缩比和聚合查询都会退化成关系库的水平。
以为乱序只是边缘侧的问题。 补传是网关的行为,代价却落在存储上:写入放大、分区合并、聚合重算。能缓解的地方有两个——网关侧预聚合后上报,以及给写入链路加限流。指望存储层「自己扛住」,等于把风险留给最贵的环节。
以为压缩比越高越好、写入吞吐越高越该选。 压缩比要看查询代价:压得狠意味着查的时候解压得多。而写入吞吐在这类项目里普遍过剩(本项目峰值 1.0 万行/秒,最慢的一款也有 18.6 万),拿它当决策依据等于用不相关的指标排序。
以为标签可以随便加。 标签基数(tag cardinality)失控是时序库最常见的故障源:单超级表标签组合超过 5 万,元数据与内存一起膨胀。它通常来自把枚举值当标签——关系库里 6 张关联表、3 处历史遗留枚举,迁到标签列时如果不收敛,基数立刻爆。
小结
- 一类设备一张超级表,静态属性进标签、变化值进字段,标签在同一超级表内最多 128 个、总长不超 16 KB。
- 第一个字段必须是 TIMESTAMP 且自动成为主键;需要复合主键才用
COMPOSITE KEY。 - 乱序比例是容量参数,不是偶发噪声:12% 要按常态规划,25% 要按加压场景复核。
- 降采样的坑在默认值:TimescaleDB 2.13 起实时聚合默认关闭,
WITH NO DATA的视图不刷新就没有数据。 - 选型看压缩比、查询 P95 与标签可变性,不看写入吞吐。
《接入平台:Broker 集群与水平扩展》把数据从 broker 送到了下游,这一章把它们落成表。数据落稳之后,下一个问题是这些表和「设备」这个概念怎么对上——谁定义了某台设备有哪些属性、当前期望它是什么状态。
相关主题
[《设备管理:物模型、设备影子与指令下发》 设备管理](/guide/iot/iot-digital-twin-device-model.html)讲物模型与设备影子。本篇的超级表描述的是一类设备的数据形状,而云端要管的是每一台具体设备:它有哪些属性、期望值和上报值不一致时算谁的、一条下发指令超时之后该重发还是该放弃。这些都会反过来约束本篇的表结构。