存储大量传感器数据记录

Question

我需要创建一个数据库来保存传感器数据，以便后续查询生成报告（显示给定时间范围内的图形和平均值/最大值/最小值）。

数据点看起来像这样：

CREATE TABLE [dbo].[Table_1](
    [time] [datetime] NOT NULL,
    [sensor] [int] NOT NULL,
    [value] [decimal](18, 0) NULL
)

数据可以按秒到分钟的时间间隔添加（取决于传感器）。

当多年的数据积累时，我应该担心我的数据库变得太大吗？（该数据库将在MS SQL Server 2008工作组版上运行）。

- grimmig

---

有多少个传感器？是1还是4000000000？ - Damien_The_Unbeliever

尚未确定，但绝对小于50。 - grimmig

Answer 1

有专门的历史数据库，如OSISoft的PI Historian，能够比关系型数据库更好地处理这种类型的数据。使用PI，您可以为每个数据点配置压缩偏差，以便只有在数据变化至少达到该压缩偏差时才会存档。当您查询给定点的历史数据时，即使您的时间段在存档值之间，您也可以请求PI进行插值，以得出指定时间的数值。

它还具有许多其他功能，但您需要自行探索，因为我不打算成为OSISoft销售员。但是，对于存储大量传感器数据，这绝对是您想要的方法。

- Brad Barker

Answer 2

这完全取决于您想要投入多少资源和精力。以每秒1行的速度，每个传感器每年的表格大小仍然不到0.5GB，非常小。如果您有成千上万个传感器，则可能需要考虑创建摘要表来帮助报告和分析数据。

像这样的传感器数据通常非常重复。有更方便的方法来存储重复值-例如通过存储一行具有时间范围而不是具有不同时间的多行。

有许多软件包可以帮助存储和管理这种时间序列数据。还有大量关于此主题的研究和文献，这可能会对您有所帮助。如果您还不熟悉它，请搜索诸如“过程历史记录器”、“复杂事件处理”和“SCADA”之类的术语。

- nvogel

---

实际上，数据来自SCADA系统。;-) 我选择上述表格设计，因为SQL服务器似乎能够处理大量数据，并且将简单的表结构与报告系统甚至办公应用程序联系起来很容易。 - grimmig

Answer 3

这取决于您如何使用数据，添加了哪些索引，有多少个传感器等。

如所示的那张表格，可以在约6GB的空间内存储1个传感器x每秒1次记录x 5年的150百万行数据（假设为堆）。文件大小限制为16TB，我不知道工作组版是否有任何限制。

- Damien_The_Unbeliever

---

我相信最终极限将是524PB（数据库限制，而非文件大小限制）。 - grimmig

@grimmig - 我最初设置了数据库大小限制，但由于您指定了工作组版，因此您无法使用分区表，因此如果您将其保存在一个表中，则该表只能存在于一个文件中。 - Damien_The_Unbeliever

好知道，谢谢。 在大多数情况下，我的应用程序仍将远低于限制，因为我不需要第二个间隔。 - grimmig

@grimmig - 实际上，我认为我可能错了。表被限制在一个文件组中，而不是一个文件。但正如你所说，使用50个传感器似乎远远没有达到限制。 - Damien_The_Unbeliever

Answer 4

如果你担心数据库会变得太大，那么我建议你可以创建一个具有相同结构的Archive_Table，并在一定时间间隔（如每月或6个月，完全取决于数据量）内归档数据。

这样，就能让您检查数据表中记录的数量。当然，需要时，存档表也可用于生成报告。

- pavanred