MySQL数据表优化设计（八）：范式和反范式数据库设计说的是啥？

这是我参与更文挑战的第7天，活动详情查看：更文挑战

在数据库设计规范中，范式和反范式经常被提到。了解范式的概念和原则对我们设计数据表很有帮助，然而，范式并不是完美的，在实际开发中，经常是依据范式设计，再根据实际业务情况加入反范式设计，形成混合模式。也就是实际上很少会有完全的范式设计或完全的反范式设计。

范式和反范式的区别

关于范式的概念，大家可以自行上网搜索，大部分情况下是讲前面的三大范式：

第一范式：每列都具有原子性，意即每一列的含义是不可再拆分的，不具备二义性。实际这个概念会依据也不同而不同，举个例子而言，姓名这个字段本身包含了姓和名，如果需要把二者当做不同的实体，那就需要拆分为两个字段；如果不需要那单独成一个字段也没问题。
第二范式：数据表每一列都都和主键相关。这意味着每个数据表不能保存多种实体数据，只保存与本实体相关的数据。这里的关键是是否需要冗余其他实体属性的字段。
第三范式：数据表每一列只和主键直接相关而不是间接相关。也就是数据表的列要与数据表主键代表实体的直接属性，而不是关联属性。

对于反范式而言，则允许信息冗余或者存放在多个不同的数据表。以经典的人员、部门和主管为例。最简单的设计是将三者直接放入同一张数据表（很多传统的 Excel 就是按这种方式记录数据）。

CREATE TABLE t_employees (
  employee VARCHAR(32),
  department VARCHAR(32),
  head VARCHAR(32)
);

这种方式一旦遇到数据修改时会出现不一致性。比如张三、李四和王五同在一个部门，张三的部门主管人员变了，需要同时更新李四和王五的数据。同时，部门必须依赖员工信息才存在，如果删除了一个部门的全部员工会导致部门信息也丢失。为避免这种问题，我们就需要建立两个实体表：

CREATE TABLE t_employees (
  employee VARCHAR(32),
  department VARCHAR(32)
);
CREATE TABLE t_department (
  department VARCHAR(32),
  head VARCHAR(32)
);

这样还只是满足第二范式，但是已经比之前的方式好多了。

范式设计的优缺点

通常在遇到性能问题的时候，会推荐使用范式设计，范式设计具有如下优点：

数据表更新相比反范式而言会更快。
由于没有冗余数据，因此需要更改的数据更少，单表存储空间也更小。
由于缺乏冗余数据，意味着使用 DISTINCT 和 GROUP BY 的查询的需求会更少，可以通过直接查询相关的主表完成这类操作。

范式表的缺点在于通常会需要至少一次的联表查询，甚至多张表联合查询。这种代价不但是高，还可能导致有些索引策略失效。

反范式设计的优缺点

反范式表最大的特点是同一张表包含了所有信息，因此避免了联合查询。如果不使用联合查询的话，大部分查询的最糟糕的情况是全表扫描（不使用索引的前提下）。即便是这样，也会比没有命中缓存的联合查询快，因为这样避免了随机 I/O 访问。反范式表的单表索引策略会更有效。假设一个 UGC 的应用，其中部分用户是VIP用户。然后，如果想查看VIP用户的最近的10条信息，如果使用范式数据表，并且在发布日期上使用了索引，查询可能是下面的样子：

SELECT content, user_name
FROM user_content
	INNER JOIN user on user_content.user_id=user.id
WHERE user.account_level='vip'
ORDER BY user_content.published DESC LIMIT 10;

执行这个查询的时候，MySQL 需要在 published 索引上进行扫描，查找到的每一行还需要从用户表检查这个用户是否是 VIP 用户。如果只有少部分用户是 VIP 的话，那会非常低效。而如果使用反范式设计的话，可以将用户账户类型冗余到用户内容表中，并且添加联合索引（account_level, published），这样就无需使用联表查询了：

SELECT content, user_name
FROM user_content
WHERE account_level='vip'
ORDER BY published DESC LIMIT 10;

当然，反范式设计也会有其缺点，一是数据表冗余后会存储空间会变大，二是如果冗余列对应的主表发生了变更，可能需要进行大量的数据行更新。例如上面的例子，如果用户等级从 VIP降为了普通用户，那对应的用户内容表该用户的数据都需要同步更新（当然，也取决于业务是否要进行同步更新）。

实际开发应用

范式设计和反范式设计都有优点和缺点，那该如何选择呢？实际上，完全的范式设计和反范式设计只能是实验室的测试品，而无法在实际中应用。在实际开发中，通常是二者的混合使用，通常是部分范式数据表、缓存表或其他技术。反范式数据设计最普遍的形式是冗余、缓存其他数据表的列。例如上面的用户内容表只冗余了用户账号等级，这避免了完全反范式的插入和删除时的同步问题。同时也使得用户内容表不至于过大，但是提升的效率很明显。但是，带来的副作用是更新用户的账号等级需要同时更新用户内容表，这个就取决于更新用户等级和查询的频率了。另一个将数据冗余的场景是排序。例如，用户内容如果需要按作者姓名排序的话，按范式设计代价将十分高昂，而如果在用户内容表冗余作者姓名的话并加上索引，则会非常高效。同样的，冗余一些附属表信息对主表查询也会很有帮助，例如假设我们想知道每个用户发表了多少条内容，就可以在用户表增设一个字段统计每个用户发布的内容条数，在用户每发布一条内容时更新该字段。这样如果需要查询用户的内容条数或者按用户内容条数排序时，就不需要每次都从用户内容表做一次 sum 操作了。

总结

范式和反范式数据库设计本身的理念是值得参考的，通过他们的理念我们可以更清楚地知道数据库该如何设计。在实际开发过程中，需要根据实际业务来决定主要遵循那种方式。这通常不是整个数据库遵循一个范式，而是在数据表层级上结合业务，融合二者的优缺点综合考虑。