大型数据库设计方法的研究

摘要：大型数据库的执行效率一直是困扰系统用户的最大问题。该文将从从逻辑数据库设计、索引设计、查询设计和表的优化设计四个方面探讨数据库设计的方法,从而使数据库在结构设计时就能规避不良设计方式，提高数据库执行效率。

关键词：大型数据库；数据库设计；执行效率；索引；查询

中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)26-6321-03

Method Research of Large-scale Database

HUA Yan

(Department of Information, Higher Normal School of Wuxi, Wuxi 214021, China)

Abstract：The implementation efficiency of large databases is always the biggest problem to the users. This paper will discuss four methods of database design: logical database design, index design, query design and table optimization ，and by these ways，the database structure can be designed to avoid poor design and to improve the implementation efficiency.

Key words: large database; database design; implementation efficiency; index; query

随着计算机应用系统的扩大,大型数据库已成为大中型企业管理应用的首选数据库平台。而大型数据库的执行效率一直是困扰系统用户的最大问题。软件项目在设计时，由于测试用例的数据量很小，很多有关执行效率的问题都反映不出来。但当项目交付使用并运行一段时间后，随数据量的增大，执行效率将成为突出的问题，进而影响系统实际运行的性能。本文将从对大型数据库系统执行效率带来影响的结构设计进行综合研究，从逻辑数据库设计、索引设计、查询设计和表的优化设计四个方面探讨数据库优化设计的方法，从而使数据库在结构设计时就能规避不良设计方式，提高数据库执行效率。

1 逻辑数据库的设计

在数据库逻辑设计过程中，为了保证数据库的一致性和完整性，数据库要按照关系数据库的规范化要求设计。

以函数依赖为基础的关系模式的规范化等级主要有五种: 1NF、2NF、3NF、BCNF和4NF，满足这些范式条件的关系模式可以在不同程度上避免冗余、插入和更新异常问题。在基于表驱动的系统中,基本表的设计规范是第三范式3NF。但是，满足3NF的数据库设计，往往不是最好的设计。没有冗余的数据库可以设计出来，但是，没有冗余的数据库未必是最好的数据库。有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

合理使用冗余会为查询带来很大的好处，如经常被查询的汇总数据,可以在平时工作中就累加好，不需要到查询时再使用如sum之类的函数。

比如：一个学生管理系统中有成绩表，其字段有学号SNO , 课程号CNO , 成绩GRADE，而进行平均成绩统计时，是用户经常要在查询和报表中用到的。在表的记录量很大时，有必要把平均分作为一个独立的字段加入到表中，这里可以采用触发器以保持数据的一致性,从而提高数据库的执行效率。

2 索引设计

索引即将表数据按索引要求而产生有序的数据副本。在关系数据库的表上建立合适的索引，可以提高数据库数据查询的速度，改善数据库的性能。除了聚集索引，每一索引的使用都以磁盘容量作为代价，当使用一个索引，数据库引擎必须执行两个数据读取，这两个数据读取是数据库记录所必需的，第一个数据被读取到实际数据指针的索引，第二个数据被读入到指针指定的位置。因此创建索引时必须要与实际应用系统的查询需求密切结合，在提高查询速度和节省存储空间之间寻求最佳的平衡点:

2.1 在合适的列上建立索引

1）在经常用作过滤器或者查询频率较高字段上建立索引；

2）为包含了大量的空值列建立索引，使包含空值的记录集中排在表的末端，数据从无序变得有序, 可减少对这部分数据的遍历，提高查询效率。

3）有一列或多列经常被使用在where或join条件里,则为该列或多列建立简单或复合索引以提高查询效率。

4）在频繁进行排序(group by) 或分组（order by）的列上建立索引。

2.2 不需要创建索引的情况

1）如果表很小,包含的数据量很少,则无须建立索引。

2）列不经常被用在查询条件里, 无须建立索引。

3）不同值少的列，比如在学生表的“性别”列上只有“男”与“女”2 个不同值, 就无必要建立索引;

4）由文本、图像等数据类型定义的列。

5）表频繁被更新, 这样如果建立了索引，开销会很大,还会降低DML(INSERT、UPDATE、DELETE)操作执行的效率, 所以此种情况无须建立索引。

2.3 聚集索引和非聚集索引

聚集索引是指行的物理顺序与行的索引顺序相同的索引。一个表只能有一个聚集索引。非聚集索引是指定表的逻辑顺序索引，行的物理顺序与索引顺序不尽相同，每个表可以有多个非聚集索引。缺省情况下建立的是非聚集索引，但是在一些特定的情况下建立非聚集索引会极大的缩短查询的时间。建立索引时, 应考虑对两者的选择。

1）对有大量重复值、且经常有范围查询（between，>，=，

2）对于频繁修改的列、或者返回小数目的不同值的情况应避免建立聚集索引。

3）当以某字段作为查询条件，需要回传局部范围的大量数据时，应在此字段上建立聚集索引，而当查询所获得的数据量较少时，有必要在此字段上建立非聚集索引。

比如：回传2010年1月1日到2011年1月1日这个时间段之间的数据，可考虑在日期字段上建聚集索引，那么数据本来就是按照日期的顺序排列的，只要找到开始和结尾日期的数据就可以了，可以极大的节省时间。而如果使用非聚集索引，必须查到这个时间段中每个日期对应的位置，然后在根据位置存取数据，明显效率很低。

在实际应用中,要综合各要素点具体分析，以达到系统的性能综合最优。

3 查询设计

从大多数系统的应用实例来看, 查询操作在各种数据库操作中所占据的比重最大。许多程序员在开发数据库应用程序时，只注重用户界面的华丽，并不重视查询语句的效率问题，导致所开发出来的应用系统效率低下，资源浪费严重。因此,如何设计高效合理的查询语句就显得非常重要。

3.1 正确地使用索引

索引作为数据库中的重要数据结构，它的根本目的就是为了提高查询的效率。建立适合关系数据库系统的有用索引，这样就可以避免表扫描，并减少因为查询而造成的输入输出开销，有效提高数据的查询速度，优化数据库性能。

比如，在学生表中,如果创建学号为单列索引, 那么查询时WHERE 子句中应使用学号这个字段,使之成为有用索引。如果使用了其他字段, 那么学号这个索引就是无用索引:

SELECT SNO , SNAME, SEX

FROM S

WHERESNO = ’S1’

使用复合索引时, 必须保证在条件子句中首先使用复合索引的第一列。比如：在成绩表中,如果创建学号SNO和课程号CNO为复合索引, 那么在查询语句的WHERE子句中应这样使用：

SELECT SNO , GRADE

FROM SC

WHERESNO = ’S3’ANDCNO = ’C1’

否则，下列复合索引的使用是没用的, 系统仍然采用顺序扫描方式:

SELECT SNO , GRADE

FROM SC

WHERECNO = ’C1’ANDSNO = ’S3’

3.2 模糊匹配的避免

LIKE关键字支持通配符匹配，技术上称为正则表达式。但这种匹配特别耗费时间，应尽量避免使用这种模糊匹配。

比如: SELECT SNO FROM SC WHERE CNOLIKE′4 ′

即使在CNO字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。

可改写为: SELECT SNOFROM SC WHERE CNO >′400′

这样,在执行查询时就会利用索引来查询，显然会大大提高速度。

3.3 子查询合并

子查询合并是将某些特定的子查询重写为等价的多个表的连接操作。子查询合并的作用在于能使查询语句的层次尽可能地减少,从而可提高查询的效率。子查询合并的一般规则为:

1) 如果外层查询的结果没有重复,即SELECT子句中包含主码,则可以合并其子查询,并且合并后的SELECT 子句前应加上DISTINCT 标志；

2) 如果外层查询的SELECT 子句中有DISTINCT标志,那么可以直接进行子查询合并；

3) 如果内部子查询结果没有重复元组,则可以合并。

比如：查询选修201号课程的学生基本信息。

SELECT S.SNO , SNAME ,AGEFROM SWHERE SNOIN( SELECTSNOFROM CNOWHERECNO =′201′)

3.4 善于使用存储过程

存储过程是存储在数据库中的一段程序,它可以接受参数、返回状态值和参数值，并且还可以嵌套调用，它是在建立时就已经编译和优化的程序。另外存储过程是一种模式化的程序设计，通过将公共集合编写为合理的存储过程，可避免冗余代码，减少程序员的工作量。因此善于使用存储过程会提高大型数据库的执行效率。

4 表的优化设计

基于第三范式设计的库表虽然有其优越性，然而在实际应用中有时不利于系统运行性能的优化，比如：需要部分数据时而要扫描整表，许多过程同时竞争同一数据，反复用相同行计算相同的结果，过程从多表获取数据时引发大量的连接操作，这都消耗了磁盘I/O和CPU时间。针对这些情况，可通过引入临时表来简化查询。

比如：查询每个系中年龄最大的学生的"学号"。

SELECT SNO FROM S AS s1 WHERE AGE=(SELECT MAX(AGE) FROM S AS s2 WHERE s1.SDEPT=s2.SDEPT)

以上的查询对于外层的年龄关系s1中的每一个元组，都要对内层的整个年龄关系s2进行检索，因此查询效率不高。可以构建临时关系提高查询效率。

SELECTMAX（AGE） AS maxage，SDEPTINTO tempFROMS GROUP BY SDEPT

SELECT SNO FROM S，temp WHERE AGE=maxage AND S.SDEPT=temp.SDEPT

又如，查询有最多男生的系的名称。使用单条查询语句获得查询结果较为困难，则可建立临时表 TEMPS (院系(SDEPT )、人数(NUMBER) ) ,先将各院系男生人数的统计结果写入此表, 再在表TEMPS中查出人数最多的院系名称。通过分解操作过程，使解决办法得以简化。

使用临时表时要注意对它的更新操作，以保持与原始表之间数据的一致性。使用完毕后，应对其删除，释放其所占用的空间。

总之，数据库的优化设计工作对提高系统执行效率起着重要的作用，但它又是一项综合性的工作，受到各种各样因素的制约，有些要求往往是彼此矛盾的。因此，设计结果常常是有得有失，设计者必须根据实际情况，将上述几个方面的优化策略有机地结合起来，尽可能使系统效率达到最优。

参考文献：

[1] 萨师煊,王珊.数据库系统概论[M].4版.北京:高等教育出版社,2007.

[2] 杨学全.SQL Server 实例教程[M].3版.北京:电子工业出版社,2010.

[3] 黄明辉.大型数据库的性能优化方法[J].计算机时代,2010(6):33-34.

[4] 柳佳刚,刘高嵩.数据库查询性能优化的探讨[J].福建电脑,2005(9):58-59.

[5] 魏威,马国峰.基于索引的关系数据库查询优化[J].洛阳大学学报,2007(6):84-86.