sql - 在 SQL 中存储/更新基于 Interval 的数据的最有效方法是什么？

Question

我有一个包含大约 7 亿行加上(呈指数增长)基于时间的数据的数据库表。

领域:

PK.ID,
PK.TimeStamp,
Value

我还有 3 个其他表，将这些数据分组为天、月、年，其中包含该时间段内每个 ID 的值的总和。这些表每晚由 SQL 作业更新，当基表中的数据更新时，表需要动态更新，但是一次最多可以更新 250 万行(不是很通常，通常大约每 5 分钟大约 200-500k)，这是否可能不会造成大量性能下降，或者实现这一目标的最佳方法是什么？

备注

如果需要，可以更改每日、每月、年份表，它们用于加快查询速度，例如“获取过去 5 年这 5 个 ID 的每月总计”，在原始数据中，大约有 1300 万行数据，从月表中它有 300 行。

我确实可以使用 SSIS。

在此过程中，我无法锁定任何表。

Answer 1

5 个月内 700M 记录意味着 5 年内 8.4B(假设数据流入没有增长)。
欢迎来到大数据的世界。这里很令人兴奋，我们每天都欢迎越来越多的新居民:)

我将描述您可以采取的三个渐进步骤。前两个只是暂时的 - 在某些时候，您将拥有太多数据并且必须继续前进。但是，每个人都需要更多的工作和/或更多的钱，因此一次采取一步是有意义的。

第 1 步:更好的硬件 - 向上扩展

更快的磁盘、RAID 和更多的 RAM 将为您提供一些帮助。放大，正如所谓的那样，最终会崩溃，但如果您的数据呈线性增长而不是指数增长，那么它会让您漂浮一段时间。

您还可以使用 SQL Server 复制在另一台服务器上创建数据库的副本。复制通过读取事务日志并将它们发送到您的副本来工作。然后，您可以在不会影响主服务器性能的辅助服务器上运行创建聚合(每日、每月、每年)表的脚本。

第 2 步:OLAP

既然您可以使用 SSIS，那么就开始讨论多维数据。凭借良好的设计，OLAP Cubes 将带您走很长的路。它们甚至可能足以管理数十亿条记录，您将能够在那里停留数年(已经这样做了，并且它为我们提供了两年左右的时间)。

第 3 步:横向扩展

通过在多台机器上分发数据及其处理来处理更多数据。如果做得好，这使您几乎可以线性扩展 - 拥有更多数据然后添加更多机器以保持处理时间不变。

如果您有 $$$，请使用 Vertica 或 Greenplum 的解决方案(可能还有其他选项，这些是我熟悉的选项)。

如果您更喜欢开源/byo，请使用 Hadoop，将事件数据记录到文件中，使用 MapReduce 处理它们，将结果存储到 HBase 或 Hypertable。这里有许多不同的配置和解决方案——整个领域还处于起步阶段。