关系数据库性能的优化研究

段艳明，肖辉辉

(河池学院 计算机与信息科学系，广西 宜州 546300)

关系数据库性能的优化研究

段艳明，肖辉辉

(河池学院 计算机与信息科学系，广西 宜州 546300)

数据库管理是数据管理的重要部分，是计算机学科的重要分支。数据库性能的好坏直接决定着数据库应用系统的运行效率。在阐述影响数据库性能的主要因素的基础上，结合实际探讨了优化数据库性能的一些策略，分析和比较了不同策略可能对数据库性能的具体影响。

关系数据库;数据库性能;优化

0 引言

数据是目前最重要的信息载体之一，而数据库则是实现数据存储和应用的手段，数据库的作用越来越受到重视。如何更好地优化数据库的性能，使之发挥快捷而有效的作用，是数据库应用的更高要求。目前，大部分应用系统的数据库主要是关系数据库，其性能优化问题一直是数据库应用的研究重点。数据库性能优化是指进行有目的地调整组件结构和改善组件性能，增加数据库的吞吐量，减少数据库操作的处理时间。本文主要从数据库结构、数据库访问、数据库查询和数据库测试等方面对数据库性能进行优化。

1 数据库结构的优化

要提高数据库的性能，最关键的是要有一个好的数据库结构，一个好的数据库结构的设计能为优化数据库性能打下良好的基础。因此，提高数据库应用系统的性能首先应从数据库结构的优化设计开始。

1.1 基本表的规范化和反规范化

数据库系统中的数据都是以基本表的形式存储，基本表的结构设计在很大程度上决定着数据库系统的性能。

1.1.1 规范化

规范化的基本思想就是逐步消除数据依赖中不合适的部分，使各关系模式达到某种程度的“分离”。规范化能消除数据冗余，避免一定的更新异常，提高数据库的完整性，从而提高数据库的可维护性和可靠性，但是，规范化也会把一个全局关系分解为多个规范化关系，会导致连接操作效率低，从而影响整个关系数据库的查询效率。因此，要综合各种因素适当地规范化数据库基本表。

在数据库设计中，应该尽量使基本表达到第三范式模式［1］。基于第三范式的数据库基本表设计具有很多优点:

①能消除数据冗余、节省磁盘存储空间;

②能良好的限制数据完整性，使数据容易维护、移植和更新;

③数据的可逆性好，在连接查询或者合并表时不遗漏、不重复;

④消除了数据冗余列，使查询时每个数据页存储的数据行增多，这样能有效地减少逻辑I/O和物理I/O;

⑤在运行大多数事务时性能好;

⑥物理设计的机动性和扩展性较大，能满足日益增长的用户需求。

1.1.2 反规范化

一般数据库设计时要满足规范化，但是否数据库基本表的规范化程度越高越好呢?规范化越高，产生的关系就越多，过多的关系会导致表之间的连接操作频繁，而表之间的连接操作性能较低，进而直接影响到查询的速度［2］。所以对于查询操作较多的应用，系统就需要根据实际情况运用反规范化对数据库基本表进行设计，通过反规范来提高查询的性能，进而提高整个数据库的性能。因此，适当降低数据库规范化程度可以简化复杂处理进而提高数据库的性能。

根据数据库在性能方面的不同，运用不同的方法进行反规范化。以下反规范化设计经实践验证能提高数据库的性能。

①在数据库基本表中加入重复属性(列)来表示许多四路或更多路合并的关系。

②存储常用的计算字段到数据库实体中。

如某一个项目的工资管理系统中有工资表，其字段为:员工编号、基本工资、岗位津贴、补发工资、扣发工资等，而实发工资(基本工资+岗位津贴+补发工资－扣发工资)是员工经常需要在查询和报表中用到的，在表中的记录量也很大，这时有必要把实发工资作为一个独立的字段加入到表中。这里可以采用触发器来保持客户端的数据一致性。

③重新定义实体来减少外部属性数据或行数据的开支。一般是把基本表按元组或属性分割成多个表，这样能分开频繁被访问的数据和较少被访问的数据，以及频繁被访问的历史数据和较少被访问的历史数据。

1.2 建立正确的索引

索引是数据库应用系统设计和开发中的一个重要的方面，使用索引能够快速找到某个特定数据块，它可以大大减少找到数据行所需的I/O操作。因此，索引的建立及其正确与否对应用系统的性能有着至关重要的影响。合理使用索引文件，对于改善数据查询速度有着举足轻重的作用，可以很大程度地改善数据库应用程序的性能。

一般地，设计索引时应遵循如下原则:

①避免在经常被更新的列上建立索引;

②不宜在同一张表上过多建立索引，一张表上的索引过多会影响该表的更新性能，因为一旦发生更新行为，该表所有的索引都必须作相应的调整，这将会延误运行时间;

③对小型表不必建立索引，因为对于小表来说直接扫描往往更快而且维护费用低;

④在经常要做查询的列上建立相应的索引，尤其是在where语句中涉及的列;

⑤在经常做连接的列上分别建立相应的索引，这样会提高连接的速度;

⑦建立簇索引，在经常做order by或group by操作且更新很少的列上建立簇索引;

⑧在重复值非常大的列上建立位图索引，而不是建立普通索引，如果在这样的列建立普通索引，其查询效率不高，反而增加了索引的维护代价。

1.3 SQL语句的优化

数据库应用系统是通过SQL语句来对数据库中的数据进行操作。因此，SQL语句的优化对数据库系统性能的提高起着决定性的作用。通过实践编程证明，以下措施能在很大程度上优化SQL语句。

(1)SQL语句的结构尽量简单。在查询时，不要过多地使用通配符“*”，要用到几列就选择几列。如语句“SELECT*FROM student”，应该改写为“SELECT Name，Age FROM student”。另外，在可能的情况下尽量限制结果集的行数，因为有些情况下用户不需要那么多的数据。如可用语句“SELECT TOP 100 Name，Grade FROM student”来限制从student表中获得前100名学生的“Name”和“Grade”。

(2)用EXISTS代替IN，用NOT EXIST代替NOT IN。EXISTS远比IN的效率高，使用NOT EXIST会使查询添加限制条件，由此减少全表扫描次数，从而加快查询的速度以达到提高数据库运行效率。

(3)尽量避免在带WHERE的子句中对字段进行函数或表达式的操作，这将导致数据库引擎放弃索引而进行全表扫描。如语句“SELECT Name from student WHERE average(Grade)=‘90’”，可以在表中增加一个平均分的字段“avg”，SELECT语句再改为“SELECT Name from student WHERE avg=‘90’”。

(4)用WHERE代替HAVING。HAVING子句会导致全表扫描后再选择，而WHERE子句则是在扫描表的同时就进行了选择，大大提高了查询效率。但是当HAVING子句用于聚集函数时不能由WHERE代替。

(5)避免使用!=或＜＞、IS NULL或IS NOT NULL、IN或NOT IN等操作符，因为这些操作符会使系统无法使用索引，即优化器将无法通过索引来确定将要命中的行数，而只能直接扫描表中的所有行，导致查询效率低。例如语句“SELECT Number form student WHERE Number!=‘2009%’”。

(6)尽量不使用游标。如果在不必要的情况下使用游标，会占用空间，导致增加系统资源。若某些必须使用游标的情况，可以将符合条件的数据行存入临时表中，再对临时表定义游标进行操作，可使数据库效率得到明显提高。

2 数据库连接优化

2.1 避免重复连接数据库

重复连接数据库的时间，要比在数据库中执行一条命令的时间长得多。因此，数据库连接后保持其连接状态，不要用完就关闭连接，应尽量避免重复连接数据库［3］。若应用程序中所有操作都重复建立数据库连接，将严重影响应用程序性能。

2.2 选择合适高效的数据库连接方法

应用程序连接数据库的方法有多种，我们要针对不同的数据库系统选择恰当的数据库连接方法。应用程序与关系数据库的连接一般采用ODBC(Open Database Connectivity)、DAO(Data Access Objects)、ADO(ActiveX Data Objects)和OLE DB(Object L inking and Embedding)等进行数据库连接。当然，不同的应用程序有各自不同的连接数据库的方式，像用JAVA连接数据库主要有两种方式，一是用JDBC－ODBC桥来连接，二是用连接池来连接。对于Web应用系统，在Web和数据库进行连接时，每一次Web请求都要建立一次数据库连接，既费时又浪费了系统的内存资源。一般可以采用数据库连接池技术来减少数据库连接。数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”，然后预先在缓冲池中建立一定数量的连接，当应用程序需要访问数据库时，只需从“缓冲池”中取出一个连接，用完之后再放回去。同时，我们可以通过规定连接池中最大连接数来防止应用系统无限制地与数据库连接。更为重要的是我们可以通过连接池的管理机制监视数据库的连接数量、使用情况，为系统开发、测试及性能调整提供依据［4－5］。

3 数据库查询优化

在各种数据库的操作中，查询操作所占的比重最大。因此，查询操作的优化将在很大程度上决定着整个数据库系统的性能优化。查询优化的重点环节是使数据库服务器尽量少从磁盘中读数据，以及尽量减少全表扫描的操作［6］。主要可以从下面几点来优化查询。

3.1 消除对大型表行数据的顺序存取操作

虽然在前面基本表的优化中已经建立了索引，但某些WHERE子句依然会强迫优化器使用顺序存取。例如语句“SELECT*FROM student WHERE(stud_no=200910 AND stud_grade ＞90)OR stud_grad=95”。我们可以用并集来避免顺序存取，即实现利用索引路径处理查询。如上面语句修改成“SELECT*FROM student WHERE stud_no=200910 AND stud_grade ＞90 UNION SELECT ＊FROM student WHERE stud_grad=95”。

3.2 使用视图加速查询操作

把基本表的一个子集进行排序并创建视图能加速查询。视图中的行要比主表中的行少的多，而且其物理顺序就是所要得到的顺序。这样，减少了磁盘I/O，避免多重排序操作和简化优化器工作，查询的效率可

以得到大幅提高。

3.3 强制查询优化器使用某个索引

在某种情况下，我们可以强制查询优化器来使用特定的索引。例如语句“SELECT* FROM student WHERE stud_no=10 AND stud_age＞20”，可以修改成“SELECT*FROM student(INDEX=IX_NoID)WHERE stud_no=10 AND stud_age＞20”。这样，查询优化器会强行利用索引IX_NoID来执行查询。

3.4 含有OR和IN的WHERE子句

含有不连续连接词(OR和IN)的WHERE子句的性能不太好。这种情况下，我们可以先使优化器生成1个工作表，包含每个可能匹配的标志符，优化器再把这些标志符(页号和行号)看成指向基本表中相匹配的行的“动态索引”。这样，优化器只需扫描工作表，获得每一个行标志符，再从基本表中取得相应的行数据。

3.5 存储衍生数据

对一些涉及到大量重复性计算的过程而言，当重复计算过程得到的结果相同，或当计算牵扯多行数据需额外的磁盘I/O开销，或当计算复杂需要大量的CPU时间时，就考虑存储计算结果。

①当对表中或多行进行重复计算时，可以通过在表内增加列来存储结果;

②当按类对表进行重复计算时，可以通过增加新表存储相关结果。

当然，在表内增加列或增加新表有利于加快访问速度，但造成了数据冗余，违反了第三范式理论，这会增加维护数据完整性的代价，同时必须用触发器立即更新，或利用存储过程或应用代码批量更新，以维护数据的完整性。所以，存储衍生数据在一定程度上优化了数据库性能，同时也在一定程度上造成了一些额外开销，我们在设计数据库时要根据实际情况利用存储衍生数据这种优化数据库基本表的方法。

上面是一些基本的提高查询速度的措施，但在更多的情况下，往往需要反复试验比较来得到最佳优化方案。最好的方法是测试，比较实现相同功能的SQL语句执行的时间。

4 数据库测试优化

在进行了数据库结构、数据库访问、数据库查询操作等方面优化之后，我们再对数据库进行测试其优化程度。数据库测试进行的好坏直接体现了全面的优化工作，一般从以下几个方面来优化数据库的测试。

4.1 用大量的数据进行测试

数据库系统使用一段时间后，数据库的性能一般会发生变化，而且数据库中如果数据量很少，是比较不出来的。这时可以把实现相同功能的多条SQL语句放到查询分析器上，查看对性能影响最大的所利用的索引和表扫描次数来比较性能优化程度。

4.2 对足够多的用户进行并发测试

大多数情况下，一个测试用户不能真实地反映数据库系统在真实使用情况下的负载情况。必须测试在多个并发用户的情况下，数据库系统是否会引起死锁(deadlock)以及性能下降等问题。例如当两个模块以同样的方式向基本表中插入记录，并查询该表的数据的操作。当这两个模块同时被访问，会破坏数据完整性。只有经过多个用户的同时操作测试，才可能发现数据不一致的问题。

4.3 选择合适的测试软件进行测试

对数据库进行测试的工具有多种，若能利用好测试工具将加快测试速度和提高测试效率。目前，软件BF(Benchmark Factory for Databases)能很好地完成数据库系统的测试，BF内置的标准测试脚本AS3AP可用于结构化查询语言(SQL)关系型数据库的测试。BF通过一台独立的控制台控制多台服务器充当的客户端来产生数据库测试压力。

5 结束语

数据库性能优化是一项复杂的工程，各种因素相互影响，彼此矛盾［7］。数据库性能的优化是一个系统工程，应贯穿于数据库系统的整个生命周期。一个好的数据库必须从数据库的结构设计开始，再从数据库的连接、数据库的查询和数据库的测试等方面进行优化。同时，影响数据库性能的因素和策略多且不固定，在不同的系统不同的环境中，决定数据库性能的因素和策略都会变。因此，没有一个通用的数据库性能优化的方案，我们必须不断通过实验和测试来尽量找到合适的数据库性能优化策略。

［1］萨师煊，王珊.数据库系统概论(第四版)［M］.北京:高等教育出版社，2007.

［2］叶忠杰，石建军，戎成.优化查询的数据库非规范化设计［J］.浙江交通职业技术学院学报，2008，(3).

［3］王泽.一般关系数据库多表智能连接算法及应用［J］.计算机工程与设计，2007，(23).

［4］周彩兰，陈才贤.基于Java的Web数据库连接池高效管理策略［J］.武汉理工大学学报(信息与管理工程版)，2004，26(5):38－41.

［5］邹雯奇，戚宇林.数据库连接池技术在WEB系统开发中的应用［J］.微计算机信息，2006，(15):185－186.

［6］柳佳刚，刘高嵩.数据库查询性能优化的探讨［J］.福建电脑，2005，(9).

［7］范孝良，杜亚维.ERP环境下关系数据库的性能优化［J］.东北电力技术，2005，(5):50－52.

［8］樊新华.关系数据库的查询优化技术［J］.计算机与数字工程，2009，(12).

A Research into Optimization to Relational Database Performance

DUAN Yan-ming，XIAO Hui-hui
(Department of Computer and Information Science，Hechi University，Yizhou，Guangxi 546300，China)

Database management is both an important part of the data management，and an important branch of computer science.The database performance directly decides the operation efficiency of the database application system.This paper expounds the main factors that affect database performance，discusses some strategies for optimizing the database performance on the basis of reality，and analyses and compares the influences of different strategies on the specific database performance.

relational database;database performance;optimization

TP311.1

A

1672－9021(2011)02－0052－05

段艳明(1978－)，女，江西永新人，河池学院计算机与信息科学系讲师，主要研究方向:计算机应用技术、数据库技术、人工智能。

2010－10－08

［责任编辑 刘景平］