6.4 Schema 设计对系统的性能影响

　　前面两节中，我们已经分析了在一个数据库应用系统的软环境中应用系统的架构实现和系统中与数
据库交互的SQL 语句对系统性能的影响。在这一节我们再分析一下系统的数据模型设计实现对系统的性
能影响，更通俗一点就是数据库的Schema 设计对系统性能的影响。

　在很多人看来，数据库Schema 设计是一件非常简单的事情，就大体按照系统设计时候的相关实体对
象对应成一个一个的表格基本上就可以了。然后为了在功能上做到尽可能容易扩展，再根据数据库范式
规则进行调整，做到第三范式或者第四范式，基本就算完事了

　　数据库Schema 设计真的有如上面所说的这么简单么？可以非常肯定的告诉大家，数据库Schema 设计
所需要做的事情远远不止如此。如果您之前的数据库Schema 设计一直都是这么做的，那么在该设计应用
于正式环境之后，很可能带来非常大的性能代价。

　由于在后面的“MySQL 数据库应用系统设计”中的“系统架构最优化“这一节中会介较为详细的从
性能优化的角度来分析如何如何设计数据库Schema，所以这里暂时先不介绍如何来设计性能优异的数据
库Schema 结构，仅仅通过一个实际的示例来展示Schema 结构的不一样在性能方面所带来的差异。

　　需求概述：一个简单的讨论区系统，需要有用户，用户组，组讨论区这三部分基本功能
简要分析：1、需要存放用户数据的表；
2、需要存放分组信息和存放用户与组关系的表
3、需要存放讨论信息的表；

解决方案：
原始方案一：分别用用四个表来存放用户，分组，用户与组关系以及各组的讨论帖子的信息如
下：
user 用户表：
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| nick_name | varchar(32) | NO | | NULL | |
| password | char(64) | YES | | NULL | |
| email | varchar(32) | NO | | NULL | |
| status | varchar(16) | NO | | NULL | |
| sexuality | char(1) | NO | | NULL | |
| msn | varchar(32) | YES | | NULL | |
| sign | varchar(64) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| hobby | varchar(64) | YES | | NULL | |
| location | varchar(64) | YES | | NULL | |
| description | varchar(1024) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
groups 分组表：
+--------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| gmt_create | datetime | NO | | NULL | |
| gmt_modified | datetime | NO | | NULL | |
| name | varchar(32) | NO | | NULL | |
| status | varchar(16) | NO | | NULL | |
| description | varchar(1024) | YES | | NULL | |
+--------------+---------------+------+-----+---------+-------+
user_group 关系表：
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| user_id | int(11) | NO | MUL | NULL | |
| group_id | int(11) | NO | MUL | NULL | |
| user_type | int(11) | NO | | NULL | |
| gmt_create | datetime | NO | | NULL | |
| gmt_modified | datetime | NO | | NULL | |
| status | varchar(16) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
group_message 讨论组帖子表：
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| gmt_create | datetime | NO | | NULL | |
| gmt_modified | datetime | NO | | NULL | |
| group_id | int(11) | NO | | NULL | |
| user_id | int(11) | NO | | NULL | |
| subject | varchar(128) | NO | | NULL | |
| content | text | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
优化后方案二：
user 用户表：
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| nick_name | varchar(32) | NO | | NULL | |
| password | char(64) | YES | | NULL | |
| email | varchar(32) | NO | | NULL | |
| status | varchar(16) | NO | | NULL | |
+-------------+---------------+------+-----+---------+-------+
user_profile 用户属性表（记录与user 一一对应）：
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| sexuality | char(1) | NO | | NULL | |
| msn | varchar(32) | YES | | NULL | |
| sign | varchar(64) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| hobby | varchar(64) | YES | | NULL | |
| location | varchar(64) | YES | | NULL | |
| description | varchar(1024) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
groups 和user_group 这两个表和方案一完全一样
group_message 讨论组帖子表：
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| gmt_create | datetime | NO | | NULL | |
| gmt_modified | datetime | NO | | NULL | |
| group_id | int(11) | NO | | NULL | |
| user_id | int(11) | NO | | NULL | |
| author | varchar(32) | NO | | NULL | |
| subject | varchar(128) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
group_message_content 帖子内容表（记录与group_message 一一对应）：
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| group_msg_id | int(11) | NO | | NULL | |
| content | text | NO | | NULL | |
+--------------+---------+------+-----+---------+-------+
我们先来比较一下两个解决方案所设计的Schema 的区别。区别主要体现在两点，一个区别是在
group_message 表中增加了author 字段来存放发帖作者的昵称，与user 表的nick_name 相对应，另外一
个就是第二个解决方案将user 表和group_message 表都分拆成了两个表，关系分别都是一一对应。
方案二看上去比方案一要更复杂一些，首先是表的数量多了2 个，然后是在group_message 中冗余存
放了作者昵称。我们试想一下，一个讨论区系统，访问最多的页面会是什么？我想大家都会很清楚是帖
子标题列表页面。而帖子标题列表页面最主要的信息就是都是来自group_message 表中，同时帖子标题
后面的作者一般都是通过用户名成（昵称）来展示。按照第一种解决方案来设计的Schema，我们就需要
执行类似如下这样的SQL 语句来得到数据：
SELECT t.id, t.subject,user.id, u.nick_name
FROM (
SELECT id, user_id, subject
FROM group_message
WHERE group_id = ?
ORDER BY gmt_modified DESC LIMIT 20
) t, user u
WHERE t.user_id = u.id
但是第二中解决方案所需要执行的SQL 就会简单很多，如下：
SELECT t.id, t.subject, t.user_id, t.author
FROM group_message
WHERE group_id = ?
ORDER BY gmt_modified DESC LIMIT 20
　　两个SQL 相比较，大家都能很明显的看出谁优谁劣了，第一个是需要读取两个表的数据进行Join，
与第二个SQL 相比性能差距很大，尤其是如果第一个再写的差一点，性能更是非常糟糕，两者所带来的
资源消耗就更相差玄虚了。
　　不仅仅如此，由于第一个方案中的group_message 表中还包含一个大字段“content”，该字段所存
放的信息要占整个表的绝大部分存储空间，但在这条系统中执行最频繁的SQL 之一中是完全不需要该字
段所存放信息的，但是由于这个SQL 又没办法做到不访问group_message 表的数据，所以第一条SQL 在数
据读取过程中会需要读取大量没有任何意义的数据。
　　在系统中用户数据的读取也是比较频繁的，但是大多数地方所需要的用户数据都只是用户的几个基
本属性，如用户的id，昵称，密码，状态，邮箱等，所以将用户表的这几个属性单独分离出来后，也会
让大量的SQL 语句在运行的时候减少数据的检索量，从而提高性能。
可能有人会觉得，在我们将一个表分成两个表的时候，我们如果要访问被分拆出去的信息的时候，
性能不是就会变差了吗？是的，对于那些需要访问如user 的sign，msn 等原来只需要一个表就可以完成
的SQL 来说，现在都需要两条SQL 来完成，性能确实会有所降低，但是由于两个表都是一对一的关联关
系，关联字段的过滤性也非常高，而且这样的查询需求在整个系统中所占有的比例也并不高，所以这里
所带来的性能损失实际上要远远小于在其他SQL 上所节省出来的资源，所以完全不必为此担心

6.4 Schema 设计对系统的性能影响,布布扣,bubuko.com

6.4 Schema 设计对系统的性能影响

原文：http://www.cnblogs.com/isoftware/p/3707735.html