基于数据库的语句优化经验之谈

摘要：该文是以Oracle数据库的语句为研究对象对象，由于其系统结构复杂, 性能受多方面因素影响，其中SQL语句的执行效率是影响其性能的关键因素之一。所以该文将围绕SQL语句优化策略进行阐述，探讨如何设计最佳的SQL语句，提高SQL语句在数据库中的解析和执行效率，并给出了相关实例分析介绍。

关键词：Oracle；数据库；SQL；优化

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

随着网络和电子商务迅速发展，在数据库应用方面也在不断扩大，数据库系统的性能优化问题也越来越受关注。Oracle是我国目前比较流行的一种大型数据库，由于其系统结构复杂，性能受多方面因素影响，如何提高其性能就成了一个十分关键的问题。Oracle数据库性能优化通常包括存储优化、运行性能优化和SQL语句优化等。下面向大家介绍如何提升Oracle数据库系统的性能。

1 Oracle数据库的SQL语句

对Oracle数据库进行查询、添加、删除等各种操作最终都是通过SQL语句来执行的，因此SQL语句的执行效率往往是Oracle数据库的性能高低的最终决定方面。

Oracle数据库在执行一条SQL语句时通常可以分成三个步骤：解析(Parse)，执行(Execute)和提取结果(Fetch)。SQI 语句的解析处理步骤一般以下7步：1) 计算语句值；2) 查看共享池中有无与此语句值相同的语句，若有则转到执行第7步；3) 准备要运行的SQL语句；4) 在共享池中为新语句创建空间；5) 将新语句存放在共享池中；6) 修改共享池图，标明语句的值和其在共享池中的位置；7) 执行准备好的SQL语句。

因为提取结果阶段的优化通常是通过硬件和Oracle系统的优化来实现的，我们讨论的SQL语句优化一般是在解析和执行阶段进行的。

2 SQL语句优化

SQL语句优化的就是在结果正确的前提下，用优化器可以识别的另外一种SQL语句，减少表扫描的I/O次数，尽量避免表搜索的发生。目的就是将性能低下的SQL语句转换成结果相同、性能优异的另外一种SQL语句，尽量保持处理器时间和I/O时间的平衡，并使数据查找的路径最简化。

2.1 SQL语句优化策略

一般SQL语句的优化策略可以根据以下原则：

1) 优化排序操作，创建合理必要的索引。

索引是一个重要且常用的数据库对象，用索引可以减少因查询而造成的I/O开销，避免表扫描，提高数据查询速度，改善数据库性能。但是也会增加系统的时间和空间开销。所以只有创建合理必要的索引，才能达到优化查询的目的。

在实际应用的过程中，如何判断索引的合理性和必要性是根据其是否对数据库的性能优化有帮助来定的。首先必须从所有的SQL语句中统计出那些常用，并且会对性能产生影响的部分语句，并且分析、归纳Where条件子句的字段和他们的各种组合方式，然后就可以初步判断出哪些表的哪些字段应该建立索引。其次，重点关注哪些经常被查询和操作的表，经常与其他表进行连接的表，数据量比较大的表等，对经常作的表和大表要建立索引。因为建立在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。

通常需要为索引创建一个单独的表空间，并保证这个索引表空间的数据文件与包含索引表的表空间的数据文件不在同一个磁盘上。建立了一些合理必要的索引之后，并不就意味着数据库性能优化已经完成，还要针对某些具体的SQL语句进行分析，如确定索引是否真正得到了使用，判断索引是否利用得合理等。

2) 减少排序语句操作。

我们可以用UNION ALL代替UNION，用Where代替HAVING。因为UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，而UNION ALL 操作只是简单的将两个结果合并后就返回。HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。 如果能通过Where 子句限制记录的数目，那就能减少这方面的开销。

3) 减少I/O操作。

过多的I/O操作会消耗大量内存、占用CPU时间。首先我们可以尽量避免对全表进行扫描，全表查询会顺序地访问表中的每一条记录，为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器；还可以调整Where 子句中的条件顺序，因为Oracle中从下到上处理Where子句中多个查询条件，Where子句后面的条件顺序对大数据量表的查询性能会产生直接的影响，所以表之间的连接必须写在其他Where 条件之前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾；表名顺序也会对效率产生比较大的影响的，在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表。

4) 减少表的访问次数。

执行SQL语句时，数据库内部要执行许多工作，对系统资源消耗严重。如果能将相关的SQL语句进行有效整合，可以大大降低数据库的访问次数，从而提高系统性能。

2.2 SQL语句优化实例

2.2.1 用UNION替换OR

对索引列使用OR将会引起全表扫描。通常情况下用UNION替换Where子句中的OR 将会提高效率。但是，此规则只针对多个索引列有效。如下例：

例1：

SELECT sno,sname

FROM TABLE1

Where sage>20

UNION

SELECT sno,sname

FROM TABLE1

Where ssex='f';

比下列语句效率高。

SELECT sno,sname

FROM TABLE1

Where sage>20 OR ssex='f';

2.2.2 选择最合适的表名顺序

在FROM子句中包含多个表的情况下，Oracle的解析器会按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表)将被最先处理。所以我们必须选择记录条数最少的表作为基础表，当Oracle处理多个表时，会运用排序及合并的方式连接它们。

例2：假设FROM后面有两个表，TABLE1有10000 条记录；表TABLE2有20 条记录；首先，扫描FROM子句中最后的那个表TABLE2，并对记录进行排序，然后扫描FROM子句中倒数第二个表TABLE1，最后将所有从倒数第二个表TABLE1中检索出的记录与第一个表TABLE2中合适记录进行合并。

若选择TABLE2作为基础表，SELECT count(*) FROM TABLE1, TABLE2执行时间2.50秒；若选择TABLE1作为基础表，SELECT count(*) FROM TABLE2,TABLE1执行时间23.79 秒。

若有3 个以上的表连接查询，那就需要选择那个被其他表所引用的表（交叉表）作为基础表。

2.2.3 用EXISTS 替代IN

在许多基于基础表的查询中，为了满足某些条件，需要对另一个表进行联接，通常使用EXISTS(或NOT EXISTS)比用IN（NOT IN）的查询效率要高些。

例3：

SELECT *

FROM TABLE1

Where sno>0

AND deptno IN (SELECT deptno FROM TABLE2

Where loc='CQ11');

和

SELECT sno

FROM TABLE1

Where cno NOT IN (SELECT cno FROM TABLE2

Where cname='DB');

以上两个SQL语句都没有下面的语句效率高：

SELECT *

FROM TABLE1

Where sno>0

AND EXISTS (SELECT * FROM TABLE2

Where TABLE2.deptno=TABLE1.deptno

AND loc='CQl1');

和

SELECT sno

FROM TABLE1

Where NOT EXISTS (SELECT * FROM TABLE2 Where o=o

AND cname='DB');

2.2.4 调节Where子句中的条件顺序

因为Oracle数据库中从下到上处理Where子句中多个查询条件，所以表之间的连接必须写在其他Where条件之前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。

例4：以下第一种SQL语句将比第二中SQL语句的执行效率要低。

第一种：

SELECT *

FROM TABLE1 T

Where gongzi>5000

AND prof='rank'

AND 50

Where rnk =T.tno);

第二种：

SELECT *

FROM TABLE1

Where 50

Where rnk=T.tno

AND gongzi>5000

AND prof='rank');

SQL语句优化策略有很多，如还可以通过将不需要的记录在GROUP BY之前过滤掉来提高GROUP BY语句的效率等等，这里就不一一列举。

3 结束语

SQL语句优化对Oracle数据库性能优化起到了主要的作用，高效SQL语句的编写，是Oracle数据库运行性能高低的关键。通过优化和整合低效的SQL语句，往往会带来意想不到的性能提高。SQL优化方法众多，该文提出了一些优化策略，Oracle数据库语句优化可以从该文提出的这些原则着手，使优化效果指标化，提高数据库性能，并在实际的性能管理工作中加强这方面的优化。

参考文献：

[1] 谢翔.ORACLE数据库SQL语句优化探讨[J].管理与技术,2004(2):20-25.

[2] 杨小艳,尹明,戴学丰. Oracle数据库查询优化方法研究[J].计算机与现代化,2008(4):4-7.

[3] 谷小秋,李得昌.索引调整优化Oracle9i工作性能的研究[J].计算机工程与应用,2005,(26):174-176.

[4] 杨厚云,龚汉明,武装. Oracle数据库性能优化方案[J].北京机械工业学院学报,2006,(4):55-59.