Schema的优化和索引 - 范式和非范式

核心提示： mysql> SELECT message_text, user_name -> FROM message -> INNER JOIN user ON message.user_id=user.id -> WHERE user.account_type='prem

mysql> SELECT message_text, user_name
　　 　-> FROM message
　　 　-> INNER JOIN user ON message.user_id=user.id
　　 　-> WHERE user.account_type='premium'
　　 　-> ORDER BY message.published DESC LIMIT 10;

这个查询的执行是有效率的，Mysql需要会扫描published索引。对于找到的每一行，它还需要到查看user表并且检查这个user是否是付费用户。如果只有一小部分帐户是付费的，那么这样的查询效率就低下了。另一个可能的查询是选择所有的付费用户，然后在获取它们所有的信息，做一个文件排序。这可能更糟糕。。

问题就出在连接上，这样你就不能在一个索引上使用排序和条件过滤。如果你非范式化这些数据，把这两张表整合并且在（account_type，published）添加索引，你就能写出一个不需要连接的查询。这个查询非常高效。

mysql> SELECT message_text,user_name
　　 　-> FROM user_messages
　　 　-> WHERE account_type='premium'
　　 　-> ORDER BY published DESC
　　 　-> LIMIT 10;

范式化和非范式化的混合使用

我们知道了范式化和非范式化的优点和缺点，那么怎样才能做到最佳的设计呢？

事实就是，完全范式和完全非范式的都像实验室的小白鼠：现实中，它们能做的非常少。在现实中，你需要混合这两种方式，可能使用一部分范式化模型，缓存表，和其他技术。

最常用的非范式化数据的方法是复制或者缓存，选择列从一张表到另一张表。在MySQL5.0以上版本，你可以使用触发器来更新缓存数据。这样实现起来也很简单。

在我们网站这个例子中，可以把account_type存储在user和message表中，这个方法可以替代上次说的那个完全非范式化的方法。这样做可以避免完全非范式化所引起的INSERT和DELETE的问题，因为即使没有MESSAGE也不会丢失USER的信息。它也不会使user_message表变得更大，还会使查询数据更为高效。