[收藏]数据库设计中的反规范技术探讨

 

数据库设计中的反规范技术探讨

 

注：这是很久以前在一个论坛看到的文章，觉得写的不错；本着与众多业内人士交流、传播思想的目的展示在此，原论坛的名称已经不记得了，当时亦是转载。你可以任意转载此文，但由此引起的任何道德、法律纠纷与http:blog.csdn.net/aceplus无关，且http:blog.csdn.net/aceplus保证没有将此文用作任何商业和非法用途；如果您是本文的原作者，认为http:blog.csdn.net/aceplus转载损害了您的权益，请邮件联系我：aceplus@263.net

 

原标题:浅谈数据库设计中的反规范

 
1.   摘要
 

  　本文从提高数据库性能的角度，介绍了数据库设计中几种常用的反规范方法，并对这些方法的优缺点以及使用时的注意事项做了较为深入的论述。　

关键词：　数据库设计　　反规范　　提高性能

 

 
2.   数据库设计简述
    数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程，它是建立数据库应用系统的核心问题。设计的关键是如何使设计的数据库能合理地存储用户的数据，方便用户进行数据处理。　

  　数据库设计完全是人的问题，而不是数据库管理系统的问题。系统不管设计是好是坏，照样运行。数据库设计应当由数据库管理员和系统分析员一起和用户一道工作，了解各个用户的要求，共同为整个数据库做出恰当的、完整的设计。　　

    数据库及其应用的性能和调优都是建立在良好的数据库设计的基础上，数据库的数据是一切操作的基础，如果数据库设计不好，则其它一切调优方法提高数据库性能的效果都是有限的。　　

数据的规范化
2.1.   范式概述
　　 　　规范化理论是研究如何将一个不好的关系模式转化为好的关系模式的理论，规范化理论是围绕范式而建立的。规范化理论认为，一个关系数据库中所有的关系，都应满足一定的规范(约束条件)。规范化理论把关系应满足的规范要求分为几级，满足最低要求的一级叫做第一范式(1NF)，在第一范式的基础上提出了第二范式(2NF)，在第二范式的基础上又提出了第三范式(3NF)，以后又提出了BCNF范式，4NF，5NF。范式的等级越高，应满足的约束集条件也越严格。规范的每一级别都依赖于它的前一级别，例如若一个关系模式满足2NF，则一定满足1NF。下面我们只介绍1NF，2NF，3NF范式。　　
2.2.   1NF
    1NF是关系模型的最低要求，它的规则是： 　　

每一列必须是原子的，不能分成多个子列。

每一行和列的位置只能有一个值。

不能具有多值列。

例：如果要求一个学生一行，一个学生可选多门课，则下面的“学生”表就不满足1NF： student(s－no,s－name,class－no)

其中：s－no为学号，s－name为学生姓名，class－no为课程号。因为一个学生可选多门课，所以列class－no有多个值，所以空不符合1NF。

　　规范化就是把它分成如下两个表：“学生”表和“选课”表，则这两个表就都满足1NF了。

student(s－no,s－name)

stu－class(s－no,class－no) 　　
2.3.   2NF
　　对于满足2NF的表，除满足1NF外，非主码的列必须依赖于所有的主码，而不是组合主码的一部分。如果满足1NF的表的主码只有一列，则它自动满足2NF。　　例：下面的“选课”表，不符合2NF。

stu－class(s－no,class－no,class－name)

其中：class－name为课程名称。因为词表的主码是：(s－no,class－no),非主码列class－name依赖于组合主码的一部分class－no,所以它不符合2NF。    

对该表规范化也是把它分解成两个表：“选课”表和“课程”表，则它们就都满足2NF了。

stu－class(s－no,class－no)

class(class－no,class－name) 　　
2.4.   3NF
 　　3NF的规则是除满足2NF外，任一非主码列不能依赖于其它非主码列。 　　例：下面的“课程”表，不符合3NF。

class(class－no,class－name,teacher－no,teacher－name)

其中：teacher－no为任课教师号，teacher－name为任课教师姓名。因为非主码列teacher－name依赖于另一非主码列teacher－no,所以它不符合3NF。　　其解决办法也是把它分解成两个表：“课程”表和“教师”表，则它们就都满足3NF了。 　　

class(class－no,class－name,teacher－no)

teacher(teacher－no,teacher－name) 　
2.5.   小结
　　当一个表是规范的，则其非主码列依赖于主码列。从关系模型的角度来看，表满足3NF最符合标准，这样的设计容易维护。一个完全规范化的设计并不总能生成最优的性能，因此通常是先按照3NF设计，如果有性能问题，再通过反规范来解决。　　

    数据库中的数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度，但是对完全规范的数据库查询，通常需要更多的连接操作，从而影响查询的速度。因此，有时为了提高某些查询或应用的性能而破坏规范规则，即反规范。
3.   数据的反规范
 
3.1.   反规范的好处
    是否规范化的程度越高越好?这要根据需要来决定，因为“分离”越深，产生的关系越多，关系过多，连接操作越频繁，而连接操作是最费时间的，特别对以查询为主的数据库应用来说，频繁的连接会影响查询速度。所以，关系有时故意保留成非规范化的，或者规范化以后又反规范了，这样做通常是为了改进性能。例如帐户系统中的“帐户”表B－TB01，它的列busi－balance(企业帐户的总余额)就违反规范，其中的值可以通过下面的查询获得：　

select busi－code,sum(aclearcase/" target="_blank" >cc－balance) 　

from   B－TB06 　　

group by busi－code

　　如果B－TB01中没有该列，若想获得busi－name(企业名称)和企业帐户的总余额，则需要做连接操作：　　

               select busi－name,sum(acc－balance) 　　

               from B－TB01,B－TB06 　　

               where B－TB01.busi－code=B－TB06.busi－code 　　

               group by busi－code

如果经常做这种查询，则就有必要在B－TB01中加入列busi－balance，相应的代价则是必须在表B－TB06上创建增、删、改的触发器来维护B－TB01表上busi－balance列的值。类似的情况在决策支持系统中经常发生。 　

     反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其它方法经常能够解决性能问题，而不必采用反规范这种方法。　　  

 

 
3.2.   常用的反规范技术
在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算(例如合计)、数据的物理存储位置等。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。　

 
3.2.1.    增加冗余列
 

增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作。例如前面例子中，如果经常检索一门课的任课教师姓名，则需要做class和teacher表的连接查询：

        　　select class－name,teacher－name 　　

            from   class,teacher 　　

            where  class.teacher－no=teacher.teacher－no 　　

这样的话就可以在class表中增加一列teacher－name就不需要连接操作了。

增加冗余列可以在查询时避免连接操作，但它需要更多的磁盘空间，同时增加表维护的工作量。
3.2.2.    增加派生列
增加派生列指增加的列来自其它表中的数据，由它们计算生成。它的作用是在查询时减少连接操作，避免使用集函数。例如前面所讲的账户系统中的表B－TB01的列busi－balance就是派生列。派生列也具有与冗余列同样的缺点。
3.2.3.    重新组表
重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。例如，用户经常需要同时查看课程号，课程名称，任课教师号，任课教师姓名，则可把表class(class－no,class－name,teacher－no)和表teacher(teacher－no,teacher－name)合并成一个表class(class－no,class－name,teacher－no,teacher－name)。这样可提高性能，但需要更多的磁盘空间，同时也损失了数据在概念上的独立性。　　

 
3.2.4.    分割表
有时对表做分割可以提高性能。表分割有两种方式：　

   1水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。 　　水平分割通常在下面的情况下使用:A 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。B 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。C 需要把数据存放到多个介质上。 　　例如法规表law就可以分成两个表active－law和inactive－law。activea－authors表中的内容是正生效的法规，是经常使用的，而inactive－law表则使已经作废的法规，不常被查询。水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。

　　2垂直分割：把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要join操作。　　
4.   反规范技术需要维护数据的完整性
　　无论使用何种反规范技术，都需要一定的管理来维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器。批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题的最好的办法。　　
5.   结束语
 　 　　数据库的反规范设计可以提高查询性能。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。但反规范技术需要维护数据的完整性。因此在做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点。 

原文转自：http://www.ltesting.net