论文部分内容阅读
摘要:数据库规范化设计与反规范化设计是一对互相矛盾的设计思想。本文以数据规范化理论为出发点,从合理建立关系模型和提高操作性能角度,分析规范化和反规范化的优势和不足,并提出了几种合理有效的策略来平衡二者之间的矛盾关系。
关键字:数据库设计;规范化;反规范化;提高性能;策略
一、 数据的规范化
在设计数据库时,最重要的一点就是确保数据正确地分布到数据库的表中。而使用正确规范的数据结构,既方便对数据库的存取操作,也能相应地简化应用系统的部分内容,比如:查询和决策报表等。
规范化理论是围绕范式建立的,是将一个低一级范式的关系模式转化为若干个高一级范式的关系模式的集合的过程,是数据库设计的规范。要想设计出良好规范的数据库,我们需要按照数据库规范化理论来设计,满足这些规范的数据库结构是简洁清晰的,同时不会发生增删改查等方面的操作异常。
范式是对关系规范化的约束要求,如果一个关系满足某个特定的约束值,则称它属于某种特定的范式。规范化理论将关系应该满足的规范要求分成几级,主要包括第一范式(1NF)、第二范式(2 NF)、第三范式(3NF)、鲍依斯-科得范式(BC NF)、第四范式(4 NF)和第五范式(5 NF)等。在软件工程中第三范式和鲍依斯-科得范式应用最为广泛,推荐采用第三范式作为标准。
二、 数据的反规范化
在数据库系统应用中,并非采用规范化设计的程度越高越好,而是和实际系统应用的环境和要求密切相关的。对于经常需要查询统计的数据库应用系统,这种设计导致的后果是致命的。采用规范化设计的程度越高,对数据进行组织和拆分的程度就越深,进而产生的数据关系就越多;关系越多,连接操作相应的更为频繁,而连接是数据库操作最耗费时间的,频繁地连接直接会影响系统查询速度,特别是对于有海量数据、且需要经常使用查询功能的数据库应用系统,这种表现就越明显。例如,在ERP系统的销售管理子系统中,销售合同的总金额是根据合同明细表中(T_OrderDetail)的所有该合同子项的单价和数量进行计算得出的,Select OrderId,sum(Price*Quantity) From T_OrderDetail Group by OrderId。
如果在合同主要信息表(T_Order)中存在合同总金额(OrderTotal)列就违反数据库设计规范;如果合同主要信息表中不存在该列,那么若想得到合同号、合同名称和合同总金额,则需要连接操作,Select a.OrderNo,a.OrderName,sum(b.Price*b.Quantity) From T_Order a, T_OrderDetail b
Where a.OrderId=b.OrderId Group by a.OrderNo,a.OrderName。
因此,为了改进数据库应用系统的性能,通常需要在数据表规范化的基础上,通过在关系模式中增加扩展列等方式来达到系统要求,系统相应地需要在数据表上创建触发器或增加业务逻辑处理来维护扩展列的内容,这就是反规范化。
反规范化具有以下优点:有效降低连接操作的要求,降低数据外键和索引的数量,大大提高数据查询速度和效率,特别是对于性能要求比较高的系统,能有效地改善系统应用的性能;还可在某种程度上减少数据表的数量。因此,在运用数据库反规范时,需要考虑项目的实际情况、数据存储性能、查询性能等方面,并在此基础上权衡利弊,综合考虑,如果能够通过索引、存储过程等方法来解决的性能问题,并不一定非得使用反规范化。
通常,我们在运用反规范化技术前,需要充分考虑数据存储性能要求、查询频率和性能、常用的数据表的物理存储、数据量大小和是否有无复杂计算等方面;在此基础上,判断采用何种方法解决系统性能问题。目前常用的反规范方法有合理增加冗余列、派生列和合并表、分割表和增加重复表等几种,在这里我只简单介绍增加冗余列、派生列和合并表、分割表四种方法。
1. 增加冗余列
该方法指的是:把需要经常使用查询操作的相关数据表的查询数据列进行集中,并在主要的查询数据表中增加这些冗余数据列。例如,在ERP系统的采购管理子系统中,如果经常查询一种原材料的供应商名称,则需要在原材料表(Stock)和供应商表(Vendor)作连接查询:
Select a.StockCode,a.StockName,b.VenName From Stock a,Vendor b where a.VenCode=b.VenCode
针对这种情况,我们在原材料表中增加一列供应商名称,就不需要进行连接操作了。
这种方法通常用来在查询时有效降低数据表之间的连接操作,提高查询速度,特别适用于日常关键业务数据的查询操作,经过实际检验,该方法可以将查询速度提高40%。但是,这种方法相应增加了存储空间,并且增加了数据表的维护工作量,一般通过触发器或应用事务逻辑来实现数据维护工作。
2. 增加派生列
派生列是指:该列的数据来自于其他数据表,并且由这些数据计算后生成。例如,前面所讲的查询合同总金额时,合同主要信息表中的合同总金额就是派生列。
增加派生列可以降低查询时的连接操作,有效避免查询时使用聚集函数,经过实际检验,该方法可以将查询速度提高至少20%以上;该方法也需要增加存储空间,增加数据表的维护工作,一般也是通过触发器或应用事务逻辑来实现。
3. 合并表
该方法指的是:查询的数据是由两到三个数据列相对较少的数据表连接出来的结果,可以将这些数据表合并成一个数据表,进而达到降低连接操作、提高查询性能的效果。例如,在学生管理系统中,用户需要经常同时看到课程编号、课程名称、任课教师编号、任课教师姓名,因此我们可以把课程表中的课程编号、课程名称与教师表中的任课教师编号、任课教师姓名组成一个新的数据表。
这种方法虽然提高了系统性能,但是既增加了存储空间(需要增加主键列),又使数据的独立性弱化。因此,建议尽量少用,除非有特殊的需要。
4. 分割表
有些数据表随着时间的推移,数据量越来越大,特别是达到上百万行级甚至上千万行级的时候,即使是使用良好的索引也不能从根本上解决问题。在这些海量数据中,数据的使用情况也不一样:有些数据行常用,有些很少使用;有些数据列常用,有些很少使用。遇到这种情况,一般采用分割表的方法来解决。
分割表按照分割方式分为:水平分割和垂重分割。
(1)水平分割。该方法是指:在一个数据表中,根据一个或多个数据列的值,把这些数据行拆分后放到几个独立数据表中,这些数据表的结构完全相同。
该方法会给系统应用带来复杂度,需要在查询时使用多个数据表名,查询所有数据时需要使用union连接操作;同时系统在查询时也会增加读索引层的磁盘次数。
该方法适用于以下几种情况:
数据表中的数据有非常大的独立性,有些数据常用,有些不常用。比如,数据表中记录了不同地区或不同时期的业务数据。
数据表中的数据非常庞大,影响查询速度。通过分割可以降低查询时要读的数据和索引,提高查询速度。
数据表中的数据需要存放在不同的介质上。
(2)垂直分割。该方法是指:在一个数据表中,根据数据列的性质,把主键和一些列放到一个数据表中,把主键和另外的列放到一个数据表中。比如:根据列是否经常使用,把主键和常用列放到一个数据表中,把主键和另外不常用的列放到另外一个数据表中。
该方法可以使数据行减小,一个数据页上可以存放更多的数据行,同时在查询时能够有效减少I/O次数。但是随之带来的是,系统需要额外管理维护冗余列,查询时需要用join操作。
5. 反规范化适用的情况
(1)大量频繁的查询过程所涉及的表都需要进行连接;(2)主要的应用程序在执行时要将表连接起来进行查询;(3)对数据的计算需要临时表或进行复杂的查询。
6. 小结
反规范化带来的主要问题是增加了数据冗余度,影响数据库的完整性,降低了数据的更新速度,增加了存储表所占用的物理空间等。以上问题可通过建立触发器、应用事务逻辑、运行批处理等方法来解决。其中:
(1)使用触发器,可以对数据的任何修改操作立即触发对冗余列或派生列的相应修改;触发器是实时的,相应的处理逻辑只在一个地方出现,易于维护。
(2)应用事务逻辑也可以实现数据的完整性,它要求必须在同一事务中对所有涉及的表进行增、删、改操作。用这种方法实现数据的完整性风险较大,因为同一个事务逻辑需要在所有的应用中使用和维护,容易遗漏,也不易于以后的维护工作。
(3)运行批处理命令是指对冗余列或派生列的修改在累积到一定时间后,运行批处理命令或存储过程对冗余或派生列进行修改,这只能在对实时性要求不高的情况下使用。
反规范化的主要优点是减少了查询操作所需的连接,减少了外部键和索引的数量,可以预先进行统计计算,有效提高了查询时的响应速度。
三、 结束语
在数据库管理应用系统设计中,运用规范化设计能够有效消除数据冗余,理顺数据隶属关系,保持数据库的完整性,提高数据库的稳定性和扩展性,同时也带来了查询时增加连接操作、系统性能不高的问题,而使用反规范化设计带来的效果却恰恰相反。
在某种意义上,数据库的规范化与反规范化设计并不是对立的矛盾关系,关键在于我们如何解决数据库管理应用系统的实际需求,在平衡二者在设计上带来的便利和不足的前提下,权衡利弊,采用二者结合的方式也许是目前解决用户实际业务需求的最好的办法。
参考文献:
[1]周渝斌.基于ORACLE8i的大型数据库技术讲座之一数据库优 化篇[J].电脑编程技巧与维护,2002(4).
[2]Kevin Loney.Oracle8i 数据库管理员手册[M].北京:机械工业
出版社,2000.
(沧州职业技术学院信息工程系)
关键字:数据库设计;规范化;反规范化;提高性能;策略
一、 数据的规范化
在设计数据库时,最重要的一点就是确保数据正确地分布到数据库的表中。而使用正确规范的数据结构,既方便对数据库的存取操作,也能相应地简化应用系统的部分内容,比如:查询和决策报表等。
规范化理论是围绕范式建立的,是将一个低一级范式的关系模式转化为若干个高一级范式的关系模式的集合的过程,是数据库设计的规范。要想设计出良好规范的数据库,我们需要按照数据库规范化理论来设计,满足这些规范的数据库结构是简洁清晰的,同时不会发生增删改查等方面的操作异常。
范式是对关系规范化的约束要求,如果一个关系满足某个特定的约束值,则称它属于某种特定的范式。规范化理论将关系应该满足的规范要求分成几级,主要包括第一范式(1NF)、第二范式(2 NF)、第三范式(3NF)、鲍依斯-科得范式(BC NF)、第四范式(4 NF)和第五范式(5 NF)等。在软件工程中第三范式和鲍依斯-科得范式应用最为广泛,推荐采用第三范式作为标准。
二、 数据的反规范化
在数据库系统应用中,并非采用规范化设计的程度越高越好,而是和实际系统应用的环境和要求密切相关的。对于经常需要查询统计的数据库应用系统,这种设计导致的后果是致命的。采用规范化设计的程度越高,对数据进行组织和拆分的程度就越深,进而产生的数据关系就越多;关系越多,连接操作相应的更为频繁,而连接是数据库操作最耗费时间的,频繁地连接直接会影响系统查询速度,特别是对于有海量数据、且需要经常使用查询功能的数据库应用系统,这种表现就越明显。例如,在ERP系统的销售管理子系统中,销售合同的总金额是根据合同明细表中(T_OrderDetail)的所有该合同子项的单价和数量进行计算得出的,Select OrderId,sum(Price*Quantity) From T_OrderDetail Group by OrderId。
如果在合同主要信息表(T_Order)中存在合同总金额(OrderTotal)列就违反数据库设计规范;如果合同主要信息表中不存在该列,那么若想得到合同号、合同名称和合同总金额,则需要连接操作,Select a.OrderNo,a.OrderName,sum(b.Price*b.Quantity) From T_Order a, T_OrderDetail b
Where a.OrderId=b.OrderId Group by a.OrderNo,a.OrderName。
因此,为了改进数据库应用系统的性能,通常需要在数据表规范化的基础上,通过在关系模式中增加扩展列等方式来达到系统要求,系统相应地需要在数据表上创建触发器或增加业务逻辑处理来维护扩展列的内容,这就是反规范化。
反规范化具有以下优点:有效降低连接操作的要求,降低数据外键和索引的数量,大大提高数据查询速度和效率,特别是对于性能要求比较高的系统,能有效地改善系统应用的性能;还可在某种程度上减少数据表的数量。因此,在运用数据库反规范时,需要考虑项目的实际情况、数据存储性能、查询性能等方面,并在此基础上权衡利弊,综合考虑,如果能够通过索引、存储过程等方法来解决的性能问题,并不一定非得使用反规范化。
通常,我们在运用反规范化技术前,需要充分考虑数据存储性能要求、查询频率和性能、常用的数据表的物理存储、数据量大小和是否有无复杂计算等方面;在此基础上,判断采用何种方法解决系统性能问题。目前常用的反规范方法有合理增加冗余列、派生列和合并表、分割表和增加重复表等几种,在这里我只简单介绍增加冗余列、派生列和合并表、分割表四种方法。
1. 增加冗余列
该方法指的是:把需要经常使用查询操作的相关数据表的查询数据列进行集中,并在主要的查询数据表中增加这些冗余数据列。例如,在ERP系统的采购管理子系统中,如果经常查询一种原材料的供应商名称,则需要在原材料表(Stock)和供应商表(Vendor)作连接查询:
Select a.StockCode,a.StockName,b.VenName From Stock a,Vendor b where a.VenCode=b.VenCode
针对这种情况,我们在原材料表中增加一列供应商名称,就不需要进行连接操作了。
这种方法通常用来在查询时有效降低数据表之间的连接操作,提高查询速度,特别适用于日常关键业务数据的查询操作,经过实际检验,该方法可以将查询速度提高40%。但是,这种方法相应增加了存储空间,并且增加了数据表的维护工作量,一般通过触发器或应用事务逻辑来实现数据维护工作。
2. 增加派生列
派生列是指:该列的数据来自于其他数据表,并且由这些数据计算后生成。例如,前面所讲的查询合同总金额时,合同主要信息表中的合同总金额就是派生列。
增加派生列可以降低查询时的连接操作,有效避免查询时使用聚集函数,经过实际检验,该方法可以将查询速度提高至少20%以上;该方法也需要增加存储空间,增加数据表的维护工作,一般也是通过触发器或应用事务逻辑来实现。
3. 合并表
该方法指的是:查询的数据是由两到三个数据列相对较少的数据表连接出来的结果,可以将这些数据表合并成一个数据表,进而达到降低连接操作、提高查询性能的效果。例如,在学生管理系统中,用户需要经常同时看到课程编号、课程名称、任课教师编号、任课教师姓名,因此我们可以把课程表中的课程编号、课程名称与教师表中的任课教师编号、任课教师姓名组成一个新的数据表。
这种方法虽然提高了系统性能,但是既增加了存储空间(需要增加主键列),又使数据的独立性弱化。因此,建议尽量少用,除非有特殊的需要。
4. 分割表
有些数据表随着时间的推移,数据量越来越大,特别是达到上百万行级甚至上千万行级的时候,即使是使用良好的索引也不能从根本上解决问题。在这些海量数据中,数据的使用情况也不一样:有些数据行常用,有些很少使用;有些数据列常用,有些很少使用。遇到这种情况,一般采用分割表的方法来解决。
分割表按照分割方式分为:水平分割和垂重分割。
(1)水平分割。该方法是指:在一个数据表中,根据一个或多个数据列的值,把这些数据行拆分后放到几个独立数据表中,这些数据表的结构完全相同。
该方法会给系统应用带来复杂度,需要在查询时使用多个数据表名,查询所有数据时需要使用union连接操作;同时系统在查询时也会增加读索引层的磁盘次数。
该方法适用于以下几种情况:
数据表中的数据有非常大的独立性,有些数据常用,有些不常用。比如,数据表中记录了不同地区或不同时期的业务数据。
数据表中的数据非常庞大,影响查询速度。通过分割可以降低查询时要读的数据和索引,提高查询速度。
数据表中的数据需要存放在不同的介质上。
(2)垂直分割。该方法是指:在一个数据表中,根据数据列的性质,把主键和一些列放到一个数据表中,把主键和另外的列放到一个数据表中。比如:根据列是否经常使用,把主键和常用列放到一个数据表中,把主键和另外不常用的列放到另外一个数据表中。
该方法可以使数据行减小,一个数据页上可以存放更多的数据行,同时在查询时能够有效减少I/O次数。但是随之带来的是,系统需要额外管理维护冗余列,查询时需要用join操作。
5. 反规范化适用的情况
(1)大量频繁的查询过程所涉及的表都需要进行连接;(2)主要的应用程序在执行时要将表连接起来进行查询;(3)对数据的计算需要临时表或进行复杂的查询。
6. 小结
反规范化带来的主要问题是增加了数据冗余度,影响数据库的完整性,降低了数据的更新速度,增加了存储表所占用的物理空间等。以上问题可通过建立触发器、应用事务逻辑、运行批处理等方法来解决。其中:
(1)使用触发器,可以对数据的任何修改操作立即触发对冗余列或派生列的相应修改;触发器是实时的,相应的处理逻辑只在一个地方出现,易于维护。
(2)应用事务逻辑也可以实现数据的完整性,它要求必须在同一事务中对所有涉及的表进行增、删、改操作。用这种方法实现数据的完整性风险较大,因为同一个事务逻辑需要在所有的应用中使用和维护,容易遗漏,也不易于以后的维护工作。
(3)运行批处理命令是指对冗余列或派生列的修改在累积到一定时间后,运行批处理命令或存储过程对冗余或派生列进行修改,这只能在对实时性要求不高的情况下使用。
反规范化的主要优点是减少了查询操作所需的连接,减少了外部键和索引的数量,可以预先进行统计计算,有效提高了查询时的响应速度。
三、 结束语
在数据库管理应用系统设计中,运用规范化设计能够有效消除数据冗余,理顺数据隶属关系,保持数据库的完整性,提高数据库的稳定性和扩展性,同时也带来了查询时增加连接操作、系统性能不高的问题,而使用反规范化设计带来的效果却恰恰相反。
在某种意义上,数据库的规范化与反规范化设计并不是对立的矛盾关系,关键在于我们如何解决数据库管理应用系统的实际需求,在平衡二者在设计上带来的便利和不足的前提下,权衡利弊,采用二者结合的方式也许是目前解决用户实际业务需求的最好的办法。
参考文献:
[1]周渝斌.基于ORACLE8i的大型数据库技术讲座之一数据库优 化篇[J].电脑编程技巧与维护,2002(4).
[2]Kevin Loney.Oracle8i 数据库管理员手册[M].北京:机械工业
出版社,2000.
(沧州职业技术学院信息工程系)