如何提高SQL中average方法的性能？

Question

我正在遇到一些性能问题，当记录数量增加时，计算列平均值的SQL查询会逐渐变慢。是否有一种索引类型可以添加到该列中，以便更快地进行平均值计算？

所涉及的DB是PostgreSQL，我知道特定的索引类型可能不可用，但我也对理论答案感兴趣，即是否可以在没有某种缓存解决方案的情况下实现这一点。

更具体地说，所涉及的数据本质上是具有以下定义的日志：

table log {
  int duration
  date time
  string event
}

我正在执行查询，类似于

SELECT average(duration) FROM log WHERE event = 'finished'; # gets average time to completion
SELECT average(duration) FROM log WHERE event = 'finished' and date > $yesterday; # average today

第二个查询速度通常较快，因为它有一个更严格的WHERE子句，但总平均持续时间查询是导致问题的查询类型。我知道我可以使用OLAP或其他方法缓存值，但我的问题是是否有一种方式可以完全通过数据库端优化（例如索引）来解决这个问题。

- Sindri Traustason

Answer 1

计算平均值的性能随着记录数量的增加而变慢，因为它必须从结果中使用每个记录的值。

如果索引包含的数据比表本身少，索引仍然可以起到帮助作用。通常，创建要获取平均值的字段的索引并不是有帮助的，因为您只想尽可能高效地获取所有数据，而不是进行查找。通常情况下，您会在查询已经使用的索引中添加该字段作为输出字段。

- Guffa

---

为什么要点踩呢？如果你不解释哪些地方有问题，那该回答就无法得到改进。 - Guffa

我只是评论慢了。我投了反对票，因为我觉得你的答案只是解释了典型的B树索引如何帮助过滤。所有查找列和我正在计算平均值的列都有索引，并且查找很快。由于要计算的值的数量，平均值计算速度很慢。我想知道的是创建一个B树的数据结构的名称，该数据结构将一列的B树与另一列的总和存储在与B树中给定节点匹配的行中，或者声明这样的事情不可能存在。 - Sindri Traustason

@Sindri Traustason：所以你希望有一个可以根据某些字段进行分组，并为每个组保留另一个字段的运行总数的索引？我认为常规关系数据库不支持这样的功能。对于您在问题中添加的示例，您将为字段event和data创建一个索引，并将duration作为输出字段。这样查询就可以使用索引的输出进行平均计算。 - Guffa

接受的答案在Guffas上面的第二条评论中。 - Sindri Traustason

Answer 2

加速聚合通常是通过保留额外的表来完成的。

假设有一个相当大的表 detail(id, dimA, dimB, dimC, value)，如果您想使 AVG（或其他聚合函数）的性能几乎与记录数无关，则可以引入一个新表 dimAavg(dimA, avgValue)

• 该表的大小仅取决于 dimA 的不同值的数量（此外，该表在设计中可能是有意义的，因为它可以保存 detail 中可用于 dimA 的值的域（以及与域值相关的其他属性；您可能/应该已经拥有这样的表）
• 仅当您按 dimA 进行分析时，此表才有用，一旦您需要按 dimA 和 dimB 进行 AVG(value)，它就变得无用了。因此，您需要知道要快速分析哪些属性。在多个属性上保持聚合所需的行数为 n(dimA) x n(dimB) x n(dimC) x ...，这可能会很快增长，也可能不会。
• 维护此表会增加更新的成本（包括插入和删除），但是您可以采用进一步的优化...

例如，让我们假设该系统主要进行插入操作，仅偶尔进行更新和删除操作。

进一步假设您只想通过dimA进行分析，并且id是递增的。那么，拥有以下结构将会很有用：

dimA_agg(dimA, Total, Count, LastID) 

可以在不对系统造成大影响的情况下提供帮助。这是因为您可以有触发器，不会在每次插入时触发，而是在每100次插入时触发。这样，您仍然可以从该表和详细表中获取准确的聚合数据。

SELECT a.dimA, (SUM(d.value)+MAX(a.Total))/(COUNT(d.id)+MAX(a.Count)) as avgDimA
FROM details d INNER JOIN
     dimA_agg a ON a.dimA = d.dimA AND d.id > a.LastID 
GROUP BY a.dimA

上述查询若使用适当的索引，将从 dimA_agg 中获取一行数据，并且只会从 detail 中获取不到 100 行数据 - 这将在近似常数时间内执行（~log_fanoutn），而且不需要为每次插入更新 dimA_agg （减少更新惩罚）。

这里只是举例说明了 100 的值，您应该自己找到最优值（甚至可以保持它的可变性，但在那种情况下仅触发器是不够的）。

维护删除和更新必须在每次操作时触发，但你仍然可以检查将要删除或更新的记录的 ID 是否已经在统计信息中，以避免不必要的更新（可以节省一些 I/O）。

注意：本分析是针对离散属性领域进行的；处理时间序列时情况会更加复杂 - 您必须决定要在哪个领域的粒度中保留摘要。 编辑

还有物化视图, 2, 3

- Unreason

---

我很好奇：如何制作一个触发器，每次触发只会触发n次（就像你的例子）？ - DrColossos

@DrColossos，严格来说，它必须每次触发，但你可以使触发器只在“id % 100 = 0”时执行一些实际的工作。 - Unreason

序列不保证没有空洞，因此存在空洞是可能的，并且可能会缺少一轮（或多轮）（这取决于您有多少次调用nextval('foo_key_seq')而实际上没有插入行），在实践中，这不会破坏系统，但只会改变性能（平均而言，您应该能够找到适合您的目标插入数量）。 - Unreason

Answer 3

取决于你在做什么？如果你没有过滤数据，那么除了按顺序设置聚集索引外，数据库如何计算列的平均值呢？

有些系统执行在线分析处理（OLAP），可以对你想要检查的信息进行累加求和和平均值等操作。这完全取决于你所做的事情和你对“慢”的定义。

例如，如果你有一个基于Web的程序，也许你可以每分钟生成一次平均值，然后将其缓存，一遍又一遍地向用户提供缓存的值。

- Spence

---

有一些你可以在索引视图中做的狡猾的事情，但我们需要一个更具体的例子来帮助你。 - Spence

在这种情况下，我对慢的定义是：已经明显变慢，并且随着时间的推移会变得更慢。 - Sindri Traustason

Answer 4

仅仅是猜测，但索引不会有太大帮助，因为平均值必须读取所有记录（以任何顺序），索引对于查找行的子集非常有用，但如果您必须迭代所有行而没有特殊的排序，则索引无法提供帮助...

- smichaud

Answer 5

这可能不是你想要的，但如果您的表格有一种方式来对数据进行排序（例如按日期），那么您只需进行增量计算并存储结果即可。

例如，如果您的数据有一个日期列，您可以计算记录1-Date1的平均值，然后将该批次的平均值与Date1和您平均化的记录数量一起存储。下一次计算时，您将查询限制为结果Date1..Date2，并添加记录数，并更新上次查询的最后日期。您拥有计算新平均值所需的所有信息。

在执行此操作时，显然有助于在日期或用于排序的任何列上建立索引。

- JohnD