范式与反范式

对于任何给定的数据通常都有很多种表示方法，从完全的范式化到完全的反范式化，以及两者的折中。在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。
下面以经典的“雇员，部门，部门领导”的例子开始：

这个schema的问题是修改数据时可能发生不一致。假如Say Brown接任Accounting部门的领导，需要修改多行数据来反应这个变化，这是很痛苦的事并且容易引入错误。如果“Jones”这一行显示部门的领导跟“Brown”这一行的不一样，就没有办法知道哪个是对的。此外，这个设计在没有雇员信息的情况下就无法表示一个部门——如果我们删除了所有Accounting部门的雇员，我们就失去了关于这个部门本身的所有记录。要避免这个问题，我们需要对这个表进行范式化，方式是拆分雇员和部门项。拆分以后可以用下面两张表分别来存储雇员表：

和部门表：

这样设计的两张表符合第二范式，在很多情况下做到这一步已经足够好了。然而，第二范式只是许多可能的范式中的一种。

范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其是写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处：

• 范式化的更新操作通常比反范式化要快。
• 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
• 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
• 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。还是前面的例子：在非范式化的结构中必须使用DISTINCT或者GROUP BY才能获得一份唯一的部门列表，但是如果部门（DEPARTMENT）是一张单独的表，则只需要简单的查询这张表就行了。

范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列在一个表中本可以属于同一个索引。

反范式的优点和缺点

反范式化的schema因为所有数据都在一张表中，可以很好的避免关联。如果不需要关联表，则对于大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。
单独的表也能使用更有效的索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published，这个查询也许看起来像这样：

要更有效率地执行这个查询，MySQL需要扫描message表的published字段的索引。对于每一行找到的数据，将需要到user表里检查这个用户是不是付费用户。如果只有一小部分用户是付费账户，那么这是效率低下的做法。
另一种可能的执行计划是从user表开始，选择所有付费用户，获得他们所有的信息，并且排序。但这可能更加糟糕。
主要问题是关联，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引（account_type,published），就可以不通过关联写出这个查询。这将非常高效：

混用范式化和反范式化

范式化和反范式化的schema各有优劣，怎么选择最佳的设计？
事实是，完全的范式化和完全的反范式化schema都是实验室里才有的东西：在真实世界中很少会这么极端的使用。在实际应用中经常混用，可能使用部分范式化的schema、缓存表，以及其他技巧。
最常见的反范式化数据的方法是复制或者缓存，在不同的表中存储相同的特定列。在MySQL5.0和更新版本中，可以使用触发器更新缓存值，这使得实现这样的方案变得更简单。
在我们的网站实例中，可以在user表和message表中都存储account_type字段，而不用完全的反范式化。这避免了完全反范式化的插入和删除问题，因为即使没有消息的时候也绝不会丢失用户的信息。这样也不会把user_message表搞得太大，有利于高效的获取数据。
但是现在更新用户的账号类型的操作代价就高了，因为需要同时更新两张表。至于这会不会是一个问题，需要考虑更新的频率以及更新的市场，并和执行SELECT查询的频率进行比较。
另一个从父表冗余一些数据到子表的理由是排序的需要。例如，在范式化的schema里通过作者的名字对消息做排序的代价将会非常高，但是如果在message表中缓存author_name字段并且建好索引，则可以非常高效的完成排序。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的），可以每次执行一个昂贵的子查询来计算并显示它；也可以在user表中建一个num_messages列，每当用户发新消息时更新这个值。

作者：一任天然

来源：CSDN
原文：https://blog.csdn.net/yirentianran/article/details/79347767
版权声明：本文为博主原创文章，转载请附上博文链接！