数据库查询处理的性能优化

摘 要：数据库系统是管理系统的核心.在实际应用中,许多应用软件都需要频繁地对数据库进行查询操作.数据库的性能直接受到SQL语句、数据库结构的影响.查询优化对于数据库的性能,特别是对于大型数据库的性能至关重要.本文将与大家探讨,通过优化SQL语句、创建临时表、优化存储过程、优化索引等提高数据库查询效率的方法.

关键字：数据库；查询；优化

目前,随着计算机技术的发展,各种软件的应用和普及,几乎所有的应用程序都需要数据库的支持,数据库的重要性日益凸显.在数据库的开发过程中,如果在开始阶段不注重查询优化技术,随着数据库表中记录量越来越大,系统的响应速度会越来越慢,最终导致系统被淘汰.相反,良好的数据库设计会大大提高其运行效率,最大限度减少冗余,缩短查询时间.数据库的优化方法很多,不同的方法也会直接影响数据库的查询效率.笔者在此介绍几种常用的数据库查询优化方法,希望能与大家共同探讨.

一、优化SQL语句

对于SQL语句优化有以下的直接原因.SQL语句是数据库操作的唯一途径,应用程序的执行最终要归结为SQL语句的执行,SQL语句的执行效率对数据库系统的性能起到了重要的作用.SQL语句相对于程序设计逻辑是独立的,优化SQL语句对程序逻辑不会产生影响,在时间成本和风险上的代价都很低.SQL语句可以有不同的写法,不同的写法在性能上的差异可能很大.准确地使用SQL语句,可以大幅度提高索引中数据的查询速度,下面就举例说明.

（一）在查询时,查询值要等于返回值.在查询中不要过多使用“通配符”,从而减少对数据库中的表的访问量,缩小查询范围,节省时间.如：select*fromtable1语句,最小化查询到某行某列,如：selectcol1fromtable1；因为一般的查询是不必查询所有库内的数据的,如selecttop50col1fromtable1.

①selectsum（t1.c1）fromt1

where（（selectcount（*）fromt2wheret2.c2等于t1.c2）>0）

②selectsum（t1.c1）fromt1

whereexists（select*fromt2wheret2.c2等于t1.c1）

结果是不一样的,但②的效率比①高很多,因为②在查询中大量缩减了索引扫描范围.可见,选择准确的字句可大大提高查询效率.

（二）选择使用特殊SQL语句,如EXISTS,NOTEXISTS.尽量避免在where子句中使用in,notin,or或者hing.使用or会导致全表扫描.可以用EXIST和NOTEXISTS代替in和notin；union来代替or,hing可以用where代替,如果无法代替可以分两步处理.例如：

①selectcolumn1,column2fromtable1

wherecolumn5等于‘2010’orcolumn6>5900；

②selectcolumn1,column2fromtable1

wherecolumn5等于‘2010’

union

selectcolumn1,column2fromtable1

wherecolumn6>5900；

语句②可以代替①,而且比①效率高.

③selectsum（t1,c1）fromt1

where（（selectcount（*）fromt2wheret2.c2等于t1.c2）>0）

④selectsum（t1,c1）fromt1

whereexists（select*fromt2wheret2.c2等于t1.c2）

语句④的效率比③高很多,因为③的查询中大量缩减了索引扫描范围.

（三）一个列的标签如果在主查询和where条件子句的查询中同时出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次,查询嵌套层次越多,效率越低,因此应当尽量避免子查询.如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行.

(四)尽量避免在where子句中对字段进行null值判断和使用!等于或<>操作符,否则将放弃使用索引而进行全表扫描.

（五）关键字LIKE支持通配符匹配,但是这特别耗费时间.比如,select*fromtable1wheret1LIKE“98__”,即使在t1字段上建立了索引,在这种情况下,还是使用顺序扫描的方式.但是,如果语句改成：select*fromtable1wheret1>”98000”,在执行时候就会用索引来查询,大大提高了查询速度.

二、创建建临时表

使用临时表最明显的一个好处就是可以提高数据库的性能,特别是查询的性能.临时表是用来存放临时记录的,同时,临时表还可以进行预处理计算,如果发现基本表中的索引不合适,也可以在临时表中重新创建索引以优化原有的索引.特别是当需要多次访问某个表或者视图的时候,利用临时表来组织数据是一个提高效率的好方法.即使只是一个简单的查询,其效率的提升也是很明显的.另外使用临时表还可以减少中间表的产生.在进行某些操作时,本来往往需要一些中间表的帮助才可以完成,而现在数据库管理员可以让数据库在需要时自动生成中间表,并在用完后进行自动删除.如此的话,中间表的建立与删除就不需要数据库管理员人为的管理了,这样既减少数据库系统中的垃圾表,也可以降低用户的工作量.需要注意的是临时表的创建需要比主表行数少,减少输入和输出,降低临时表的访问量,提高其查询效率.三、使用用存储过程

存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数来执行它.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度.使用存储过程还能减少数据库和应用程序的交互次数,保证数据安全.在使用存储过程时,要尽量遵循以下原则：

（一）尽量使用数字型字段,若只含数值信息的字段尽量不要设置为字符型,这会增加存储的开销,并降低查询和连接的性能.因为在查询和链接时候,数据库会逐个字符比较,而对于数字型字段只需要一次比较.

尽可能使用varchar/nvarchar代替char/nchar,因为变长字段存储空间小,可以节约存储空间,在查询中,较小字段内的搜索效率显然高于较长字段.

（二）尽量避免使用游标,其效率低下.但对于小数据集时,游标通常比其他逐行处理方法来的好,尤其是需要在几个表中查询数据时,应该使用游标.

（三）就像上文提到的,适当使用临时表能够提高查询效率,但是在存储过程中需要避免频繁创建和删除临时表,减少系统表资源的消耗.如果生成了临时表,在存储过程的最后,务必将所有临时表删除,先truncatetable,然后droptable,避免系统表的较长时间锁定.

四、建立索引

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息,它的根本目的就是为了提高查询效率.创建适当的索引,有助于提高检索性能,从而对数据库工作效率有所帮助.对数据库中操作频繁的表、数据流量较大的表、经常需要与其他表进行连接等操作的表,选择适当的字段建立索引,将对SQL语句的性能产生重要影响.

在实际应用中,常用表中经常有的ID和日期字段.因为ID是自动生成的,我们并不知道每条记录的ID,所以我们在实践中很难用ID号来进行查询.而我们经常会查询一段时间内的数据.这就使让ID号这个主键作为索引成为一种资源浪费.如果将索引建立在日期字段,将会缩短查询时间.

建立索引可以提高数据库的查询速度,但过多或不当的索引则会导致系统低效.索引作为数据库中实际存在的对象,每个索引都要占用一定的物理空间.所以,对于索引的建立要考虑到必要性和实用性,使数据库能得到高性能的发挥.索引的使用要恰到好处,其使用原则如下：

在经常进行连接,但是没有指定外键的列上建立索引.

在频繁进行排序或分组的列上建立索引.

在条件表达式中经常用到的不同值较多的列上建立索引.

对于那些在查询中很少使用或者参考的列不应该创建索引,增加了索引,反而降低了系统的维护速度和增大了空间需求.

对于那些只有很少数据值的列不应该增加索引.由于列的取值很少,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大,增加索引,并不能明显加快检索速度.例如人事表的性别列就属于这种情况.

对于那些定义为text、image和bit数据类型的列不应该增加索引.这些列的数据量要么相当大,要么取值很少,不利于使用索引.

五、结束语

数据库查询优化是一项综合性工作,受到各种各样因素的制约.在实际工作中,设计者应该综合考虑,抓住关键问题,综合应用上述方法,并逐步改善查询方法,在实践中积累经验,力求最大限度地提高操作效率,在总体设计的基础上,实现数据库查询优化设计.

（作者单位：福建技师学院）

[2]许志清,赵博.精通SQLServer2005数据库系统管理,2007.10.

[3]王能斌.数据库系统原理[M].北京：电子工业出版社,2000.

[4]陶宏才.数据库原理及设计[M].北京：清华大学出版社,2004.