聚合还是不聚合，这是数据库模式设计的问题

Question

如果您正在进行最小/最大/平均值查询，您更喜欢使用聚合表还是仅查询原始表中一系列行？

这显然是一个非常开放的问题，没有一个正确的答案，所以我只是在寻找人们的一般建议。假设原始数据表由时间戳、数字外键（例如用户 ID）和一个十进制值（例如购买金额）组成。此外，请假设表中有数百万行。

我已经尝试过两种方法并感到矛盾。一方面，聚合表使我的查询速度显着提高，但代价是增加了大量表格。显示聚合范围的当前值要么需要完全返回到原始数据表，要么需要组合更细粒度的聚合。我发现，在应用程序代码中跟踪要查询的聚合表比想象的要多，同时模式更改将是必要的，因为最初的聚合范围将不可避免地不够用（“但我想看看我们过去 3 个付款周期的销售额！”）。

另一方面，从原始数据查询可能会非常缓慢，但可以让我在数据范围上非常灵活。当范围边界更改时，我只需更改查询而不必重新构建聚合表。同样，应用程序代码需要更新的次数较少。我怀疑，如果我的索引更聪明一些（即始终拥有良好的覆盖索引），我就能够减少选择原始数据所遭受的惩罚，但这并不是万全之策。

是否有可能兼得两全？

- pr1001

---

我通常使用MySQL，但希望大家的建议适用于所有SQL数据库。 - pr1001

@pr1001：这是一个普遍的问题，在一定程度上，但一些数据库提供机制使这个问题更容易（例如Oracle的“物化视图”），因此正确地处理这个问题在某种程度上将取决于具体的数据库。 - skaffman

Answer 1

我们曾经遇到了同样的问题，并遇到了你遇到的问题。最终我们将报告转换为了Analysis Services。学习MDX和Analysis Services本身需要一定的时间，但效果非常好。我们发现的一些好处包括：

1. 您可以以任何想要的方式进行灵活查询。在之前，我们必须构建特定的聚合，但现在一个立方体就可以回答我们所有的问题。
2. 立方体中的存储比详细数据要小得多。
3. 构建和处理立方体所需的时间更短，对数据库服务器的负载也更小。

一些CONS:

1. 需要花费一些时间来学习构建立方体和学习MDX。
2. 我们不得不创建一些工具来自动化处理立方体。

更新： 由于您正在使用MySql，您可以看看Pentaho Mondrian，这是一个支持MySql的开源OLAP解决方案。我从未使用过它，所以我不知道它是否适用于您。但如果它对您有用，我会很感兴趣知道。

- jvilalta

---

有些参与Pentaho开发的人员来自BI著名厂商Cognos。 +1提到了Pentaho。 - cethegeek

Answer 2

我总是倾向于原始数据。一旦聚合，就无法返回。
与删除无关 - 除非有最简单的聚合数据集，否则不能准确地将数据恢复/转置回原始数据。

理想情况下，我会使用物化视图（假设数据符合约束条件），因为它实际上是一个表。但是MySQL不支持它们，所以下一个考虑是带有计算列的视图，或者触发器来更新实际表。

- OMG Ponies

---

我错过了他建议聚合和删除原始数据的那部分内容吗？当然，原始数据需要保留。但是除了原始数据之外，一些聚合数据也可以储存。 - marcc

@marcc：我在哪里说过原始数据会被删除了？ - OMG Ponies

@Ponies：也许你说得没错，一旦聚合起来就无法回头了 :) - Daniel Vassallo

@Daniel：这与删除毫无关系。当我从SUM(col1 * col2)获取一个值时，数据集越大，我将其还原为组成它的值的可能性就越小。 - OMG Ponies

Answer 3

选择一个好的主键（例如[user_id，used_date，used_time]）有助于提高性能。对于常量user_id，可以非常快速地在used_date上执行范围条件。

但随着表格的增长，您可以通过聚合到像[user_id，used_date]这样的表格来减少表格大小。对于时间不重要的每个范围，您都可以使用该表格。减少表格大小的另一种方法是归档您不再需要查询的旧数据。

- Menno

Answer 4

一个历史悠久的问题，目前我发现this很有用，由MicroStrategy工程师回答。

顺便说一下，还有其他已经有解决方案的工具，比如（cube.dev/dremio），你不必自己动手。

- no7dw