mysql - 在数据库中，什么时候应该存储派生数据？

Question

我的问题是关于非规范化。在数据库中，何时应该将派生数据存储在自己的列中，而不是每次需要时都计算？

例如，假设您的用户因其问题而获得支持。您在其个人资料上显示用户的声誉。当用户被点赞时，您应该增加他们的声誉，还是应该在检索他们的个人资料时计算它:

SELECT User.id, COUNT(*) AS reputation FROM User
LEFT JOIN Question
  ON Question.User_id = User.id
LEFT JOIN Upvote
  ON Upvote.Question_id = Question.id
GROUP BY User.id

获取用户声誉的查询必须占用多少处理器，才值得使用自己的列增量跟踪它？

继续我们的示例，假设 Upvote 的权重取决于投它的用户拥有多少 Upvote(而不是声誉)。检索他们的声誉的查询突然爆炸:

SELECT
  User.id AS User_id,
  SUM(UpvoteWeight.weight) AS reputation
FROM User
LEFT JOIN Question
  ON User.id = Question.User_id
LEFT JOIN (
  SELECT
    Upvote.Question_id,
    COUNT(Upvote2.id)+1 AS weight
  FROM Upvote
  LEFT JOIN User
    ON Upvote.User_id = User.id
  LEFT JOIN Question
    ON User.id = Question.User_id
  LEFT JOIN Upvote AS Upvote2
    ON
      Question.id = Upvote2.Question_id
      AND Upvote2.date < Upvote.date
  GROUP BY Upvote.id
) AS UpvoteWeight ON Question.id = UpvoteWeight.Question_id
GROUP BY User.id

这与增量解决方案的难度相去甚远。规范化何时值得，规范化的好处何时会失去非规范化的好处(在这种情况下是查询难度和/或性能)？

Answer 1

How processor intensive does the query to get a User's reputation have to be before it would be worthwhile to keep track of it incrementally with its own column?

这里确实有两个问题:(1) 此更改是否会提高性能以及 (2) 性能改进是否值得付出努力？

---

至于性能有无提升，这基本上是一个标准的利弊分析。

归一化的好处基本上有两方面:

• 更轻松的数据完整性

• 重新计算没有问题(例如，如果基础数据发生变化，派生列需要重新计算)。

如果您使用稳健实现的解决方案(例如触发器、Sstored-proc-only 数据更改以及已撤销的直接表更改权限等)来覆盖数据完整性，那么这将直接计算验证成本是否源数据更改是否保证派生数据重新计算与每次都重新计算派生数据。 (注意:保持数据完整性的另一种方法是强制按计划重新计算派生数据，其中数据可以承受一些时间容差的不准确。StackExchange 的一些数字采用了这种方法)。

在一个典型的场景中(更多的数据检索和更少的基础数据更改)数学很明显倾向于在表中保留非规范化的派生数据。

在极少数情况下，基础数据经常更改，但派生数据却没有那么频繁地检索，这样做可能是有害的。

---

现在，我们要解决更重要的问题:性能改进是否值得付出努力？

请注意，与所有优化一样，最大的问题是“优化是否值得？”，因此主要考虑两个方面:

1. 测量准确的性能差异和一般分析。

2. 此特定优化在系统全局中的上下文。

例如如果查询性能的差异——在优化时必须首先测量——缓存的派生数据和计算的数据之间的差异为 2%，那么实现信誉缓存列的额外系统复杂性可能不值得首先。但是，就边际改进而言，关心与不关心的阈值取决于您应用程序的总体情况。如果您可以采取措施在不同的地方将查询性能提高 10%，那么专注于此，而不是 2%。如果您是 Google，额外 2% 的查询性能需要 20 亿美元的额外硬件成本来承担，那么无论如何都需要对其进行优化。