sql - 大传感器数据最佳选择。表 SQL 与 Azure 表

Question

关闭。此题需要 details or clarity 。目前不接受答案。

---

想要改进这个问题吗？通过 editing this post 添加详细信息并澄清问题.

已关闭 1 年前。

Improve this question

我想要一些有关最佳选择的建议，因为我在基于 Web 的 API 上检索数据的速度很慢(超过 30 秒)。

我有多个 IoT 传感器(超过 100 个)，它们也会将 TX 更新的传感器数据增长到我的 IoT 中心，然后保存到数据库或存储中。

以前我曾经将所有传感器数据保存到 SQL 表中，但随着数据的增长，我发现它变得非常慢，因此我使用了 Azure 表存储。每个Sensor都有自己独立的表存储，分区键是月份和年份(例如202012)，行键是时间戳(例如0002518033824243332546)。

事实证明，这要快得多，因为传感器数据量减少了，因为每个传感器都有自己的表，但随着任何特定传感器的表增长，我需要在更长的时间内检索数据(1月)这又变得非常慢。每个传感器 TX 每 1 分钟更新一次，因此每天会产生 1400 条记录，1 个月大约会产生 44,640 条记录。

有没有更好的解决方案来满足我的要求？

为每个传感器创建一个单独的 SQL 表是一个好主意吗？ SQL存储中最多可以有多少个表？

谢谢

Answer 1

您可以考虑使用 Azure SQL 数据库(高级层 P6 或更高版本)来存储来自冷路径(存储帐户)或来自需要直接保存到数据库的热路径的 IoT 数据。

首先考虑使用提到的列存储索引来优化数据存储here 。这样您就可以将数据压缩高达 20 倍，并快速将更多数据检索到内存中。

CREATE TABLE Telemetry (
    _id bigint IDENTITY,
    sensorData nvarchar(max),
   INDEX cci CLUSTERED COLUMNSTORE
)

其次你可以考虑使用In-Memory OLTP和 JSON functions在 Transact-SQL 中。这样，您可以将 JSON 格式的数据视为任何其他 SQL 数据类型，并从 JSON 文本中提取值以在 SELECT 列表或搜索谓词中使用 JSON 数据。由于基于列存储的表针对扫描和聚合而不是键查找查询进行了优化，因此您还可以基于 JSON 函数创建计算列，然后将原始 JSON 列中的特定属性公开为常规关系列，从而简化查询设计和开发。您可以通过在计算列上创建常规(基于行)非聚集索引来支持关键查询和访问路径，从而进一步优化数据检索。虽然这些会稍微增加总体存储需求，但它们将帮助查询处理器过滤键查找和范围扫描的行，并且还可以帮助执行聚合等其他操作。请注意，您可以随时添加计算列和相关索引。

如果您的 JSON 结构稳定且预先已知，最好的选择是设计我们的关系架构以容纳 JSON 数据中最相关的属性，并在插入新数据时利用 OPENJSON 函数将这些属性转换为行字段。这些将是完全关系列(具有优化的 SQL 数据类型)，可用于各种检索和分析目的，从复杂的过滤到聚合，并且您只需创建适当的索引来支持各种访问路径。但是，如果需要进一步处理，您仍然可以决定保留整个 JSON 片段并将其存储在同一个表的 VARCHAR(max) 字段中。

如果您可以将 IoT 数据保存为具有 SQL 数据类型的行字段，请考虑使用基于时间的表分区。将所有数据放在一张表中，但按日期范围进行分区，如所述 here .