在数据库中存储具有可变数量字段的测量值的最有效方法是什么？

Question

我们有一个数据收集系统，可以从环境传感器收集测量结果，这些传感器测量流经河流或渠道的水流速度。每次测量都会生成固定数量的值（例如日期、时间、温度、压力等）以及速度值列表。
最初，传感器提供了三个速度值，因此我只是将每个值存储在 FireBird 数据库中单个表的自己的列中。后来引入的传感器可以输出最多九个速度值，所以我简单地添加了六列。尽管大多数传感器使用的值少于 9 个，但我认为如果大多数列只包含零，那不会有问题。
但现在我面临着新一代，它可以输出从 1 到 256 个值的任何值，并且我认为添加另外 247 列不会非常有效，特别是因为大多数测量值仍然只包含 3 到 9 个值。
由于测量结果每 10 分钟收集一次，并且数据库包含 30 到 50 个传感器的所有数据，因此几年后数据总量相当可观，但必须能够生成任何随机时间段的概览/图表。

那么存储变量值列表的最有效方法是什么？
由于每个记录都有其自己的唯一 ID，因此我假设我可以将所有速度值存储在单独的表中，每个值都标有其记录 ID。我只是有一种感觉，这不会很有效，而且一段时间后会变得非常慢。

Answer 1

如果使用高效索引，数据库可以处理表中的大量数据。所以你可以使用这个表结构：

create table measurements (
     id,
     seq integer, -- between 1 and 256
     ts timestamp, -- Timestamp of the measurement
     value decimal(...)
)

在id、id、seq和ts上创建索引。这将使您能够有效地搜索数据。如果您不信任您的数据库，只需插入几百万行并运行几次选择来查看它的表现如何。

作为比较：我这里有一个 Oracle 数据库，有 1.12 亿行，我可以在 120 毫秒（0.12 秒）内按时间戳或 ID 选择记录

Answer 2

您可以将序列化数据保存在文本字段中，例如将测量结果 JSON 编码为：

[<velocity-value-1>, <velocity-value-2>, ...]

然后，在代码中，查询后反序列化值。

如果您仅按其他字段而不是按保存的值过滤查询，那么这应该很有效。如果您确实按值进行过滤，那么在 WHERE 子句中使用它们将是一场噩梦。

Answer 3

我会选择第二个表：

table measurements (Id, DateTime, Temperature, Pressure)
table velocity (Id, MeasurementId, Sequence, Value)

Velocity.MeasurementId 引用 Measurements.Id。
Velocity.Sequence 是该测量的速度值索引 (1-256)。

使用尽可能接近真实世界的数据填充这些表，并测试 SQL 语句以找到最佳索引。