MySQL 范式与反范式的选择

对于任何给定的数据通常有很多种表示方法，从完全的范式化到完全的反范式话，以及两者的折中。在范式化的数据库中，每个事实数据会出现并只出现一次。相反，在反范式化的数据库中，信息是冗余的。
如果不熟悉范式和反范式，可以通过 MySQL 三种范式以及反范式 上篇文章了解一下。下面以经典的“雇员、部门、部门领导”的例子开始：

这个 schema 的问题是修改数据时可能发生不一致。假如 “剑花”接任了 技术部的领导，需要通过修改多行数据来反映这个变化，这是件很痛苦的事情。

要避免这个问题，我们需要对这张表进行范式化，方式是拆分雇员和部门项，拆分可以用下面两张表分别来存储：

雇员表

部门表

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

范式的优点和缺点

当为性能问题而寻求帮助时，经常会被建议对 schema 进行范式化设计，尤其是写密集的场景。范式化设计有以下好处：

1）范式化的更新操作通常比反范式化要快

2）当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据

3）范式化的表通常很小，可以更好地放在内存里，所以执行操作要更快

4）很少有多余的数据意味着检索列表的数据时更少需要 DISTRICT 和 GROUP BY语句。

范式化设计的 schema 的缺点是通常需要关联，这不但代价昂贵，也可能使一些索引策略失效。

反范式的优点和缺点

反范式的 schema 因为数据都在一张表中，可以很好地避免关联。

单独的表也能使用更有效的索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查询付费用户最近的10条消息。如果是范式化的结果并且索引了发送日期字段 published ，这个查询可能需要这么写：

mysql > selct message_text, user_name
     -> from message
     -> inner join user on user.user_id = message.user_id
     -> where user.account_type = 'premiumv'
     -> order by message.published desc limit 10;

要更为有效地执行这个查询，MySQL  需要扫描 message 表的published 字段的索引。对于每一行找到的数据，将需要到 user 表里检查这个用户是不是付费用户。如果只有一小部分用户是付费用户，那么这是效率低下的做法。

主要是关联，使得需要在一个索引中又排序又过滤。如果使用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引（account_type, published），就可以不通过关联写出这个查询。这将非常高效：

mysql > select message_text , user_name
     -> form message_user
     -> where accout_type = 'premiumv'
     -> order by published desc
     -> limit 10;

混用范式化和反范式化

范式化和反范式化的 schema 各有优劣，怎么选择最佳的设计？

事实上，完全分范式化和反范式化 schema 都是实验室里才会出现的，在实际项目中，经常需要混用，可能会使用部分范式化的schema 、缓存表以及其他技巧。

如，在 user 表和 message 表中都存储 account_type 字段，而不同完全的反范式化。这避免了完全反范式话的插入和删除问题。即使没有消息的时候也不会丢失用户信息。但在需要更新用户账户类型时，操作代价就很高了，需要同时更新两种表了。