数据库设计简述
数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程它是建立数据库应用系统的核心问题设计的关键是如何使设计的数据库能合理地存储用户的数据方便用户进行数据处理
数据库设计完全是人的问题而不是数据库管理系统的问题系统不管设计是好是坏照样运行数据库设计应当由数据库管理员和系统分析员一起和用户一道工作了解各个用户的要求共同为整个数据库做出恰当的完整的设计
数据库及其应用的性能和调优都是建立在良好的数据库设计的基础上数据库的数据是一切操作的基础如果数据库设计不好则其它一切调优方法提高数据库性能的效果都是有限的
数据的规范化
范式概述
规范化理论是研究如何将一个不好的关系模式转化为好的关系模式的理论规范化理论是围绕范式而建立的规范化理论认为一个关系数据库中所有的关系都应满足一定的规范(约束条件)规范化理论把关系应满足的规范要求分为几级满足最低要求的一级叫做第一范式(NF)在第一范式的基础上提出了第二范式(NF)在第二范式的基础上又提出了第三范式(NF)以后又提出了BCNF范式NFNF范式的等级越高应满足的约束集条件也越严格规范的每一级别都依赖于它的前一级别例如若一个关系模式满足NF则一定满足NF下面我们只介绍NFNFNF范式
NF
NF是关系模型的最低要求它的规则是
每一列必须是原子的不能分成多个子列
每一行和列的位置只能有一个值
不能具有多值列
例如果要求一个学生一行一个学生可选多门课则下面的学生表就不满足NF student(s－nos－nameclass－no)
其中s－no为学号s－name为学生姓名class－no为课程号因为一个学生可选多门课所以列class－no有多个值所以空不符合NF
规范化就是把它分成如下两个表学生表和选课表则这两个表就都满足NF了
student(s－nos－name)
stu－class(s－noclass－no)
NF
对于满足NF的表除满足NF外非主码的列必须依赖于所有的主码而不是组合主码的一部分如果满足NF的表的主码只有一列则它自动满足NF例下面的选课表不符合NF
stu－class(s－noclass－noclass－name)
其中class－name为课程名称因为词表的主码是(s－noclass－no)非主码列class－name依赖于组合主码的一部分class－no所以它不符合NF
对该表规范化也是把它分解成两个表选课表和课程表则它们就都满足NF了
stu－class(s－noclass－no)
class(class－noclass－name)
NF
NF的规则是除满足NF外任一非主码列不能依赖于其它非主码列 例下面的课程表不符合NF
class(class－noclass－nameteacher－noteacher－name)
其中teacher－no为任课教师号teacher－name为任课教师姓名因为非主码列teacher－name依赖于另一非主码列teacher－no所以它不符合NF其解决办法也是把它分解成两个表课程表和教师表则它们就都满足NF了
class(class－noclass－nameteacher－no)
teacher(teacher－noteacher－name)
小结
当一个表是规范的则其非主码列依赖于主码列从关系模型的角度来看表满足NF最符合标准这样的设计容易维护一个完全规范化的设计并不总能生成最优的性能因此通常是先按照NF设计如果有性能问题再通过反规范来解决
数据库中的数据规范化的优点是减少了数据冗余节约了存储空间相应逻辑和物理的I/O次数减少同时加快了增删改的速度但是对完全规范的数据库查询通常需要更多的连接操作从而影响查询的速度因此有时为了提高某些查询或应用的性能而破坏规范规则即反规范
数据的反规范
反规范的好处
是否规范化的程度越高越好?这要根据需要来决定因为分离越深产生的关系越多关系过多连接操作越频繁而连接操作是最费时间的特别对以查询为主的数据库应用来说频繁的连接会影响查询速度所以关系有时故意保留成非规范化的或者规范化以后又反规范了这样做通常是为了改进性能例如帐户系统中的帐户表B－TB它的列busi－balance(企业帐户的总余额)就违反规范其中的值可以通过下面的查询获得
select busi－codesum(acc－balance)
from B－TB
group by busi－code
如果B－TB中没有该列若想获得busi－name(企业名称)和企业帐户的总余额则需要做连接操作
select busi－namesum(acc－balance)
from B－TBB－TB
where B－TBbusi－code=B－TBbusi－code
group by busi－code
如果经常做这种查询则就有必要在B－TB中加入列busi－balance相应的代价则是必须在表B－TB上创建增删改的触发器来维护B－TB表上busi－balance列的值类似的情况在决策支持系统中经常发生
反规范的好处是降低连接操作的需求降低外码和索引的数目还可能减少表的数目相应带来的问题是可能出现数据的完整性问题加快查询速度但会降低修改速度因此决定做反规范时一定要权衡利弊仔细分析应用的数据存取需求和实际的性能特点好的索引和其它方法经常能够解决性能问题而不必采用反规范这种方法
常用的反规范技术
在进行反规范操作之前要充分考虑数据的存取需求常用表的大小一些特殊的计算(例如合计)数据的物理存储位置等常用的反规范技术有增加冗余列增加派生列重新组表和分割表
增加冗余列
增加冗余列是指在多个表中具有相同的列它常用来在查询时避免连接操作例如前面例子中如果经常检索一门课的任课教师姓名则需要做class和teacher表的连接查询
select class－nameteacher－name
from classteacher
whereclassteacher－no=teacherteacher－no
这样的话就可以在class表中增加一列teacher－name就不需要连接操作了
增加冗余列可以在查询时避免连接操作但它需要更多的磁盘空间同时增加表维护的工作量
增加派生列
增加派生列指增加的列来自其它表中的数据由它们计算生成它的作用是在查询时减少连接操作避免使用集函数例如前面所讲的账户系统中的表B－TB的列busi－balance就是派生列派生列也具有与冗余列同样的缺点
重新组表
重新组表指如果许多用户需要查看两个表连接出来的结果数据则把这两个表重新组成一个表来减少连接而提高性能例如用户经常需要同时查看课程号课程名称任课教师号任课教师姓名则可把表class(class－noclass－nameteacher－no)和表teacher(teacher－noteacher－name)合并成一个表class(class－noclass－nameteacher－noteacher－name)这样可提高性能但需要更多的磁盘空间同时也损失了数据在概念上的独立性
分割表
有时对表做分割可以提高性能表分割有两种方式
水平分割根据一列或多列数据的值把数据行放到两个独立的表中 水平分割通常在下面的情况下使用:A 表很大分割后可以降低在查询时需要读的数据和索引的页数同时也降低了索引的层数提高查询速度B 表中的数据本来就有独立性例如表中分别记录各个地区的数据或不同时期的数据特别是有些数据常用而另外一些数据不常用C 需要把数据存放到多个介质上 例如法规表law就可以分成两个表active－law和inactive－lawactivea－authors表中的内容是正生效的法规是经常使用的而inactive－law表则使已经作废的法规不常被查询水平分割会给应用增加复杂度它通常在查询时需要多个表名查询所有数据需要union操作在许多数据库应用中这种复杂性会超过它带来的优点因为只要索引关键字不大则在索引用于查询时表中增加两到三倍数据量查询时也就增加读一个索引层的磁盘次数
垂直分割把主码和一些列放到一个表然后把主码和另外的列放到另一个表中如果一个表中某些列常用而另外一些列不常用则可以采用垂直分割另外垂直分割可以使得数据行变小一个数据页就能存放更多的数据在查询时就会减少I/O次数其缺点是需要管理冗余列查询所有数据需要join操作
反规范技术需要维护数据的完整性
无论使用何种反规范技术都需要一定的管理来维护数据的完整性常用的方法是批处理维护应用逻辑和触发器批处理维护是指对复制列或派生列的修改积累一定的时间后运行一批处理作业或存储过程对复制或派生列进行修改这只能在对实时性要求不高的情况下使用数据的完整性也可由应用逻辑来实现这就要求必须在同一事务中对所有涉及的表进行增删改操作用应用逻辑来实现数据的完整性风险较大因为同一逻辑必须在所有的应用中使用和维护容易遗漏特别是在需求变化时不易于维护另一种方式就是使用触发器对数据的任何修改立即触发对复制列或派生列的相应修改触发器是实时的而且相应的处理逻辑只在一个地方出现易于维护一般来说是解决这类问题的最好的办法
结束语
数据库的反规范设计可以提高查询性能常用的反规范技术有增加冗余列增加派生列重新组表和分割表但反规范技术需要维护数据的完整性因此在做反规范时一定要权衡利弊仔细分析应用的数据存取需求和实际的性能特点
