代码之家 › 专栏 › 技术社区 › theazureshadow

在数据库中,应该何时存储派生数据?

normalization mysql

theazureshadow · 技术社区 · 14 年前

我的问题是非规范化。在数据库中,什么时候应该将派生数据存储在它自己的列中,而不是每次需要时计算它?

例如,假设您的用户对他们的问题获得了赞成票。在用户的个人资料中显示用户的声誉。当用户被否决时,您应该增加他们的声誉,还是在检索他们的配置文件时计算声誉:

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具有一个权重,该权重取决于投出它的用户拥有多少upvot(而不是多少声誉)。检索他们声誉的查询突然爆发:

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

这与增量解决方案的难度相差甚远。什么时候规范化才值得呢?什么时候规范化的好处会失去非规范化的好处(在本例中是查询困难和/或性能)?

3 回复 | 直到 11 年前

DVK 11 年前

为了获得用户的声誉,查询需要占用多大的处理器资源,才能用自己的列增量地跟踪它?

这里有两个问题,一个是:(1)这一变化是否会提高绩效;(2)绩效改进是否值得付出努力?

至于绩效是否提高,这基本上是一个标准的利弊分析。

标准化的好处基本上是两方面的:

更简单的数据完整性
重新计算没有问题(例如,如果基础数据发生更改,则需要重新计算派生列)。

如果您使用一个可靠实现的解决方案(例如触发器、仅存储进程的数据更改以及已撤销的直接表更改权限等)来覆盖数据完整性,那么这将成为验证源数据更改是否符合派生的DA的直接计算。每次重新计算与重新计算派生数据。(注意:保持数据完整性的另一种方法是强制按计划重新计算派生数据,在这种情况下,数据可能不准确,并具有一定的时间公差。stackexchange采用这种方法处理一些数字)。

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

在一些罕见的情况下,底层数据经常更改,但派生数据却不经常检索,这样做可能有害。

现在,我们将讨论更重要的问题: 绩效改进是否值得努力?

请注意,与所有优化一样,最大的问题是“优化是否值得?”因此,需要考虑两个主要因素:

测量精确的性能差异,通常是仿形。
这个特定优化的上下文出现在系统的总体图中。

例如,如果查询性能的差异- 在进行优化时,必须首先测量 -在缓存的派生数据和计算的派生数据之间为2%,实现信誉缓存列的额外系统复杂性可能首先不值得。但是,关心和不关心的临界点是什么,因为边际改善取决于你的应用程序的大局。如果您可以在不同的地方采取步骤来提高10%的查询性能,那么请将精力集中在这一点上,而不是2%。如果你是谷歌,额外2%的查询性能需要20亿美元的额外硬件来承担,那么无论如何,它都需要优化。

Paul Abbott 14 年前

确实没有明确的答案,因为这取决于很多因素,比如网站的数量和你多久显示一次声誉(即只在他们的个人资料页上或他们的用户名的每个实例旁边,到处都是)。唯一真正的答案是“当速度太慢时”;换句话说,你可能需要测试两种情况,并获得一些真实世界的性能统计数据。

我个人会在这种特殊情况下取消规范化,在upvote表上有一个insert触发器,或者有一个定期更新查询来更新denromatedReputation列。会吗? 真的? 世界末日是不是有人的代表说“204”而不是“205”,直到页面刷新?

jlpp 14 年前

我只是想从另一个角度谈一下dvk在上面的响应中所涉及的数据完整性问题。想想其他系统是否需要访问/计算派生数据——甚至是像报告系统这样简单的系统。如果其他系统需要使用派生值或更新upvote值,那么对于如何重用计算代码或如何确保派生值在任何系统更改upvote时始终更新,您可能会有其他考虑。