c# - 在 DB 中自动聚合数据的最佳方法

Question

我们目前正在开发一个 Web 应用程序，用于处理位于数据库表中的大量存档数据。表中的数据行由一个唯一的行 ID、两个标识机器和数据点的 ID、一个值和一个时间戳组成。每当值更改超过给定阈值时，每台机器都会将其数据发送到此表。该表通常包含数百万到数亿个条目。

出于可视化的目的，我创建了一个存储过程，它采用识别机器和数据点所需的两个 ID，以及开始和结束日期时间。然后它将开始和结束之间的值聚合成可变长度的块(通常为 15 分钟、1 小时、7 天等)，并返回给定时间间隔内每个块的平均值、最小值和最大值。

该方法有效，但需要大量时间，即使进行了大量数据库优化和索引。所以在前端图表页面显示所选范围和机器的数据大约需要10到60秒，我认为这太多了。

所以我开始考虑创建一个新表，其中包含每个“块”的每台机器的预聚合数据。为了实现这一点，必须每隔 [chunksize] 自动调用聚合过程。每台机器的分钟/小时/天。然后可以很容易地从更细的块中创建更粗的块，等等。据我所知，这将大大加快整个过程。

问题是:实现定期聚合的最佳方法是什么？有没有办法让数据库自己完成这项工作？或者我是否必须在 ASP.NET MVC Web 应用程序中实现基于计时器的解决方案？后者需要 Web 应用程序始终运行，这可能不是最好的方法，因为它可能因各种原因而关闭。另一种选择是处理此任务的独立应用程序或服务。还有其他我没有想到的方法吗？你会如何处理这个问题？

Answer 1

在我们的系统中，我们有一张包含原始原始数据的表格。此原始数据汇总为每小时、每天和每周的时间间隔(每个时间间隔的原始值的总和、最小值、最大值)。

我们保留原始数据 30 天(4 周)，每小时保留 43 天(6 周)，每天保留 560 天(18 个月)，每周保留 10 年。每晚都会“清理”这四个表，并删除超过阈值的数据。每小时表大约有 30M 行，每天有 18M 行。一些报告/图表使用每小时数据，大多数使用每日数据。有时，我们需要查看原始数据以详细调查问题。

我有一个用 C++ 编写的专用应用程序，它在服务器上 24/7 全天候运行，并从大约 200 个其他服务器收集原始数据并将其插入中央数据库。在应用程序中，我定期(每 10 分钟)调用一个重新计算摘要的存储过程。如果用户想要查看最新数据，则该存储过程也可以由最终用户随时运行。通常运行大约需要 10 秒，因此最终用户通常会看到延迟摘要。因此，从技术上讲，服务器上可能会有一个计划作业，每 10 分钟运行一次该过程。当我通过应用程序执行此操作时，我可以更好地控制收集数据的其他线程。本质上，我会在汇总时暂停尝试插入新数据。但是，仅使用独立的存储过程就可以达到相同的效果。

在我的情况下，我可以使摘要的重新计算相当有效。

在这 10 分钟的窗口中，随着新数据流入数据库，我将原始数据直接插入到主表中。原始数据点永远不会更新，它们只会被添加(插入)。所以，这一步简单而有效。我使用带有表值参数的存储过程，并在一次调用中传递一大块新数据。因此，在一个 INSERT 语句中插入了许多行，这很好。

使用第二个存储过程每 10 分钟用新数据更新汇总表。必须更新一些现有行，添加一些行。为了有效地做到这一点，我有一个单独的“暂存”表，其中包含机器 ID、每小时日期时间、每日日期时间、每周日期时间列。当我将原始数据插入到主表时，我还将受影响的机器 ID 和受影响的时间间隔插入到这个临时表中。

因此，有两个主要的存储过程。应用程序使用多个线程循环访问 200 个远程服务器，并在无限循环中从每个服务器下载新数据。一旦从某个远程服务器下载了一批新数据，就会调用第一个存储过程。这种情况经常发生。此过程将一批原始数据按原样插入原始表，并将受影响的时间间隔列表插入“暂存”表。

假设传入的一批原始数据如下所示:

ID timestamp            raw_value
1  2015-01-01 23:54:45  123
1  2015-01-01 23:57:12  456
1  2015-01-02 00:03:23  789
2  2015-01-02 02:05:21  909

4 行按原样插入主表(ID、时间戳、值)。

将 3 行插入到临时表中(通常有很多值来自同一小时的时间戳，所以有很多原始行，但在临时表中很少):

ID hourlytimestamp     dailytimestamp      weeklytimestamp
1  2015-01-01 23:00:00 2015-01-01 00:00:00 2014-12-29 00:00:00
1  2015-01-02 00:00:00 2015-01-02 00:00:00 2014-12-29 00:00:00
2  2015-01-02 00:00:00 2015-01-02 00:00:00 2014-12-29 00:00:00

请注意，这里我将所有 ID 和时间戳整理/压缩/合并到唯一集合中，并且此临时表根本没有值，它仅包含受影响的 ID 和时间间隔( StatsToRecalc 是此临时表， @ParamRows 是具有一批包含新数据的行的存储过程):

DECLARE @VarStart datetime = '20000103'; -- it is Monday
INSERT INTO dbo.StatsToRecalc
    (ID
    ,PeriodBeginLocalDateTimeHour
    ,PeriodBeginLocalDateTimeDay
    ,PeriodBeginLocalDateTimeWeek)
SELECT DISTINCT
    TT.[ID],
    -- Truncate time to 1 hour.
    DATEADD(hour, DATEDIFF(hour, @VarStart, TT.PlaybackStartedLocalDateTime), @VarStart),
    -- Truncate time to 1 day.
    DATEADD(day, DATEDIFF(day, @VarStart, TT.PlaybackStartedLocalDateTime), @VarStart),
    -- Truncate time to 1 week.
    DATEADD(day, ROUND(DATEDIFF(day, @VarStart, TT.PlaybackStartedLocalDateTime) / 7, 0, 1) * 7, @VarStart)
FROM @ParamRows AS TT;

然后有简单的 INSERT 从 @ParamRows 到原始表中。

因此，在 10 分钟内，使用此过程从多个线程将许多 INSERTS 放入原始表和临时表中。

每 10 分钟就会调用一次重新计算摘要的程序。

它做的第一件事是启动一个事务并锁定临时表直到事务结束:

SELECT @VarCount = COUNT(*)
FROM dbo.StatsToRecalc
WITH (HOLDLOCK)

如果临时表 StatsToRecalc 不为空，我们需要做一些事情。由于该表被锁定，所有工作线程都不会干扰，并且会等到重新计算完成后再添加更多数据。

通过使用这个临时表，我可以快速确定需要重新计算哪些 ID 的小时、天和周。实际的汇总计算在 MERGE 语句中完成，该语句一次性处理所有受影响的 ID 和区间。我运行三个 MERGEs 将原始数据汇总为每小时汇总，然后每小时汇总为每日汇总，然后每天汇总为每周汇总。然后临时表被清空(每 10 分钟一次)，所以它永远不会变得太大。

每个 MERGE 首先列出自上次重新计算以来受影响的 ID 和时间戳(例如，从每小时更新每日表):

WITH
CTE_Changed (ID, PeriodBeginLocalDateTimeDay)
AS
(
    SELECT
        dbo.StatsToRecalc.ID
        , dbo.StatsToRecalc.PeriodBeginLocalDateTimeDay
    FROM 
        dbo.StatsToRecalc
    GROUP BY
        dbo.StatsToRecalc.ID
        ,dbo.StatsToRecalc.PeriodBeginLocalDateTimeDay
)

然后在 MERGE 中将小时表与此 CTE 连接起来:

MERGE INTO dbo.StatsDay AS Dest
USING 
(
    SELECT
        ...                 
    FROM 
        dbo.StatsHour
        INNER JOIN CTE_Changed ON 
            CTE_Changed.ID = dbo.StatsHour.ID AND
            CTE_Changed.PeriodBeginLocalDateTimeDay = dbo.StatsHour.PeriodBeginLocalDateTimeDay
)
...

为了帮助进行这种多阶段求和，我在原始表、小时表和日表中有辅助列。例如，hourly 表有一个 PeriodBeginLocalDateTimeHour 列，其中包含如下值:

2015-01-01 22:00:00
2015-01-01 23:00:00
2015-01-02 00:00:00
2015-01-02 01:00:00
...

，即一个小时的界限。同时，还有第二列包含这些“截断”到日期边界的时间戳: PeriodBeginLocalDateTimeDay ，它包含如下值:

2015-01-01 00:00:00
2015-01-02 00:00:00
...

，即一天的界限。第二列仅在我将小时累计为几天时使用 - 我不必即时计算日期时间戳，而是使用持久化的索引值。

我应该补充一点，在我的情况下，如果该专用 C++ 应用程序停机一段时间也没关系。这只是意味着数据会延迟10分钟以上，但不会丢失任何内容。