数据库范式和反范式
考虑这样一个表:【联系人】(姓名,性别,电话)
如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。1NF 很好辨别,但是 2NF 和 3NF 就容易搞混淆。
考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。
因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。
可以把【OrderDetail】表拆分为【OrderDetail】(OrderID,ProductID,Discount,Quantity)和 【Product】(ProductID,UnitPrice,ProductName)来消除原订单表中UnitPrice,ProductName多次重复的情况。
考虑一个订单表【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)主键是(OrderID)。
其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID),所以符合 2NF。不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。
通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。
第二范式:非主键列是否依赖主键(包括一列通过某一列间接依赖主键),要是有依赖关系的就是第二范式;
第三范式:非主键列是否是直接依赖主键,不能是那种通过传递关系的依赖的。要是符合这种就是第三范式;
范式的优点和缺点
当为性能问题而寻求帮助时,经常会被建议对schema进行范式化设计,尤其是写密集的场景。因为下面这些原因,范式化通常能够带来好处:
范式化的更新操作通常比反范式化要快
当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据
范式化的表通常更小,可以更好地放在内存里,所以执行操作会很快
很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
范式化设计的schema的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策略无效。
例如范式化可能将列存放在不同的表中,而这些列如果在一个表中本可以属于同一个索引。
反范式
跟范式所要求的正好相反,在反范式的设计模式,我们可以允许适当的数据的冗余,用这个冗余去取操作数据时间的缩短。也就是利用空间来换取时间,把数据冗余在多个表中,当查询时可以减少或者是避免表之间的关联;
反范式化的scheme因为所有数据都在一张表中,可以很好的避免关联
不需要关联表,那即使表没有使用索引-是全表扫描。当数据比内存大时这可能比关联要快的多,因为这样避免了随机I/O(全表扫描基本上是顺序I/O,但也不是100%,跟引擎的实现有关)
单独的表也能使用更有效的索引策略。
场景分析:
如我们现在要对一个 学校的课程表进行操作,现在有两张表,一张是学生信息student(a_id,a_name,a_adress,b_id)表,一张是课程表 subject(b_id,b_subject),现在我们需要一个这样的信息,把选择每个课程的的课程名称和学生姓名输出来:
SQL语句为:select B.b_id,B.b_subject,A_a_name from student A ,subject B;
当上面的数据量不多时,我们这样去查询没有问题;当我们的两张表的数据都是在百万级的时候,我们去查上面的信息, 问题出现了,这个查询动不动就是几百毫秒,甚至更慢,这样的查询效率根本不能满足我们对于网页速度的要求(一般不能超过100毫秒),怎么办?当然要反范式,在课程表里面添加冗余字段——学生姓名,这样我们就可以通过下面的查询达到同样的目的:
SQL语句为:select b_id,b_subject,a_name from subject B;
将两个查询放在一起查看执行计划,就会发现,第一个查询开销占了92%,而第二个才8%,也就是说,第二个查询比起第一个查询,效率上优化了10倍以上,成果显著。
混用范式化和反范式化
实际情况下,完全的范式化和反范式化schema都是实验室才有的东西,在真实的世界很少会这么极端的使用
- 最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。在MySQL5.0以后,可以使用触发器更新缓存值
- 从父表冗余一些数据到子表另一个常见的理由是排序的需要。例如,在范式化的schema里通过作者的名字对消息做排序代价会非常高,但是如果在message表中缓存author_name字段并建好索引,则可以非常高效的完成排序。
- 缓存衍生值也是有用的。如果需要显示每个用户发了多少帖子,可以每次执行一个昂贵的子查询来计算并显示它;也可以在user表中建一个num_messages列,每当用户发新消息时更新这个值