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

如果你不熟悉范式，你应该加强学习了。关于范式，你可以通过一些书和网上资源来学习。在这里，我们主要介绍这一章中你应该明白的知识。让我们看看经典的例子，那就是employee,departments,和department heads.

EMPLOYEE 　 　 　 　 　 　 DEPARTMENT 　 　 　 　 　 　 　 　 　HEAD
Jones 　 　 　 　 　 　 　 Accounting 　 　 　 　 　 　 　 　 　Jones
Smith 　 　 　 　 　 　 　 Engineering 　 　 　 　 　 　 　 　 Smith
Brown 　 　 　 　 　 　 　 Accounting 　 　 　 　 　 　 　 　 　Jones
Green 　 　 　 　 　 　 　 Engineering 　 　 　 　 　 　 　 　 　Smith

这种设计问题在于当数据更改的时候，这数据模型就会变得不正常了。如果Brown接管了Accounting部门，我们必须更新多条语句来反映出这个改变，并且这些更新可能还使数据的状态不一致。如果Jones行的HEAD和Brown行的HEAD不同的话，没有方法知道到底那个HEAD是正确的。就像那句老话一样：一个人有两块表，就不会知道准确的时间。更进一步的说，没有员工的时候就不能展现DEPARTMENT.如果我们删除了全部员工，DEPARTMENT的信息也同样的被删除了。为了避免这个问题，我们把这个表分为EMPLOYEE和DEPARTMENT两个实体。结果为两张表：

EMPLOYEE_NAME 　 　 　 　DEPARTMENT
Jones 　 　 　 　 　 　 　 Accounting
Smith 　 　 　 　 　 　 　 Engineering
Brown 　 　 　 　 　 　 　 Accounting
Green 　 　 　 　 　 　 　 Engineering

DEPARTMENT 　 　 　 　 　 HEAD
Accounting 　 　 　 　 　 Jones
Engineering 　 　 　 　 　Smith

这些表属于第二范式，对于大部分需求已经足够了。第二范式只是众多范式的其中一个。

一个范式数据模型的缺点和优点

那些对性能要求较高的需求，推荐用范式化的数据模型。尤其对于写操作较多的需求。使用范式化模型的好处如下：

范式化的更新速度要快于非范式化的更新。

当数据很好的范式化之后，就有很少或者没有重复的数据。因此也就会有很少的数据需要更新。

范式化的表常常很小，因此它们可以放到内存中，使性能变得更好。

没有多于的数据就意味着，当获取值的列表的时候，就不需要太多的DITINCT或者GROUP BY 的查询。考虑下以前的例子：如果不使用DISTINCT或者GROUP BY在非范式化的表查询唯一列表的部门是不可能的，但是如果DEPARTMENT是独立表，仅仅是个获取查询而已。

范式化数据模型的缺点就是获取数据。在很好的范式化模型上做一些复杂的查询，至少都需要连接一张表，也可能更多。这样的消耗不仅很大，并且使有些索引策略变为不可能。比如，范式化把列放到不同的表中，这些表都会从相同的索引中得到好处。

非范式化数据模型的缺点和优点

一个非范式化的模型可能工作的很好，以为任何数据都放在相同的表中，这样避免了连接。

如果你不需要连接表，来看个最坏情况下的查询-即使没有使用索引，那就是个全表的扫描。在数据没有在内存中的时候，这样的查询都要快于连接表的查询。因为它避免了随机的IO。

一个单独的表也允许使用更高效的索引策略。假使你有个用户发送消息的网站，并且一些用户是付费用户。现在你想查看来自付费用户最新的10条信息。如果你已经范式化数据模型了并且索引了message的发布时间，这个查询可能如下：

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;

（编辑：济南站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!