Oracle数据库的SQL语句优化

何文砚

摘 要：随着数据库应用系统中数据的增加， 系统的性能提高成为数据库系统中需要解决的主要问题， 在系统硬件不变的情况下， SQL语句优化成为系统性能提高的主要途径。本文针对Oracle数据库的SQL调整和优化，论述了SQL优化的目的和原则，并通过若干实例，介绍了SQL语句优化的一些方法。

关键词：Oracle；SQL；查询；优化；索引

对Oracle数据库的调优是进行有目的的调整组件以改善性能， 即增加吞吐量和减少响应时间。从整体上看调优是对各个组件的优化，包括应用程序优化、实例和数据库优化和环境优化。对应用程序的优化通常可分为两个方面：源代码的优化和SQL语句的优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高（尤其是对正在使用中的系统进行优化），另一方面，源代码的优化对数据库系统性能的提升收效有限，并且应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。所以本文从Oracle数据库执行SQL语句的过程入手，逐步研究优化SQL语句的方法。

1 SQL优化的必要性与目的

数据库系统是管理信息系统的核心，查询操作在各类数据库操作中占据比重最大，查询速度直接影响数据库的应用效率，对于大型数据库来说，显得更为重要，由于查询操作在SQL语句中比重最大，因此优化的查询语句可以大幅提高应用系统的性能与效率。

1.1 优化应用程序中SQL语句的必要性

SQL语句是对数据库中的数据进行操作的惟一途径，应用程序的执行最终要体现为SQL语句的执行，因此，SQL语句的效率在数据库系统的性能优化中起到了决定性的作用。根据统计，对硬件系统进行优化所获得的性能提升，只占数据库系统性能提升的40%左右，另外的60%系统性能提升来自于对应用程序的优化。对应用程序进行优化通常主要从源代码和SQL语句这两个方面进行。目前SQL是使用最为广泛的数据库语言，SQL语句执行将消耗70%至90%的数据库资源，而应用程序对数据库的操作，最终体现在用SQL语句对数据库的操作，因此SQL语句的执行效率决定了数据库的性能。通过对SQL语句以及访问数据库方法的优化调整，可以显著地改善整个系统的性能，对提高数据库内存区的命中率、减少I/O访问、减少对网络资源的占用等有着非常重要的意义。

程序员在使用SQL时往往会太多关注于SQL查询的结果是否正确，而忽略了不同的查询实现方法之间可能存在的性能效率上的差异，相同功能的SQL语句，使用不同的写法来实现，在性能上会产生非常大的差异，执行效率也会千差万别。

1.2 优化的实质与目的

在一个正确设计的数据库中，如果使用的SQL结构不合理，仍然会遇到性能问题。调整SQL的关键是使数据库寻找数据的路径最简化，主要通过调整SQL语句，来提高Oracle的性能。SQL语句优化的实质就是用优化器可以识别的语句，充分利用索引来减少表扫描的I/O次数，尽量避免表搜索的发生。优化的目的就是将性能低下的SQL语句转换性能优异的SQL语句，使数据查找的路径最佳化，并尽量保持CPU时间和I/O时间的平衡。在进行SQL语句优化时，根据系统需求找出最有可能提高性能的语句，例如执行频率高的语句、整体消耗资源最多的语句以及每行消耗资源最多的语句等，并对其进行优化。

2 SQL语句优化方法

数据库应用程序的执行最终将归结为数据库中的SQL语句执行。SQL语句消耗了数据库的大部分资源。因此SQL语句的执行效率最终决定ORACLE数据库的性能，也就决定的应用系统的性能。下面通过实例介绍SQL语句优化的若干方法。

2.1 建立有效的索引

Oracle服务器索引是一种模式对象，通过指针访问来提高检索数据的速度。如果没有为表建立索引，检索数据将会扫描整个表。建立索引后，对于索引列的访问，可以通过指针快速定位表中的数据，从而有效减少磁盘的I/0操作。如果多表之问经常联接，那么可以在外键表的外键列上创建索引。这样可以提高查询与主键表的主键列匹配记录的速度。

索引也不是越多越好，因为每一次对含有索引的表进行DML操作后就意味着索引必须更新，这也是要花费相当时间的。一般以下情况可以建立索引：

（1）经常用于WHERE子句或作为连接条件的列；

（2）所含数据值范嗣比较的列；

（3）含有大量空值的列；

（4）绝大多数情况F只查询出其总记录的2%-4%的表。

2.2 SELECT中尽量不要使用“*”

当我们想显示某表中的所有列时，一般在SELECT语句中使用“*”，这是一个比较方便的方法。例如select * from dept，但是这种方法非常低效。0RACLE服务器在解析的过程中，会通过数据字典将“*”依次转换成所有的列名，这意味着将耗费更多的时间来完成，所以建议使用select deptno，dname，loc from dept这个命令，书写起来似乎麻烦一些，但是执行效率高多了。

2.3 减少访问数据库的次数

每条SQL语句被执行时，Oracle服务器需要完成一系列任务：解析SQL语句、估算索引的利用率、绑定变量、读数据块等。因此减少数据库访问，就能减少服务器T作量。例如，要想查询deptno是20与40的这两个部门的信息时，用一条最高效的命令就是select deptno，dname from dept where deptno in（20，40），而不需要用两条查询命令或其他什么方法来完成，会无形中增加访问数据库的次数，从而降低效率。

2.4 尽量多使用COMMIT提交事务

Oracle对DDL语句是自动提交的，其他的比如DML语句是手工提交或者回滚事务。应用程序编写中，在进行insert、delete、update等复杂语境操作时，应适当的使用COMMIT进行事务的提交，以释放会话中所持有的锁，快速清理缓存中未修改的数据块，从而释放系统资源，提高系统性能。

2.5 用TRUNCATE替代DELETE

当执行DELETE这个语句时。其删除的内容通常情况下在回滚段（rollback segments）中暂时保存着，以便意外时需要恢复，直到执行COMMIT语句时才真正生效。而当执行TRUNCATE时，回滚段不保存任何可被恢复的信息，执行该命令后，表中所有记录被删除.数据不能被恢复，因此占用很少的资源，执行时间也会很短。因此如果删除表中所有记录时，建议使用TRUNCATE命令。

2.6 使用表的别名（Alias）

当要查询的信息来自多个表时，建议在SQL语句中使用表的别名，并把别名前缀加于每个列上。这样一来，就可以减少Oracle服务器解析的时间，也减少那些由列歧义引起的语法错误。例如： select e.empno，e.ename，d.deptno，d.dname from emp e，dept d where e.deptno=d.deptno；

2.7 用EXISTS替代IN

在多个基于基础表的查询中，往往需要对另一个表进行联接。由于在使用IN 的子查询中的表Oracle 将对其进行全表扫描，所以使用EXISTS（或NOT EXISTS）而替代IN（或NOT IN）通常能明显提高查询的效率。例如：

Select ename，job from emp where deptno in（select deptno from dept where dname=‘SALES3；（优化前，低效）

Select ename，job from emp where exists（select deptno from dept where emp.deptno=dept.deptno and dname=‘SALES3；（优化后，高效）

2.8 用修改语句（UPDATE）代替插入语句（INSERT）

如果需要向一个大型表中填入大量数据行（是已经能确定的几万条记录），一般常用的方法是使用INSERT语句，但事实证明，这并不足一个很好的选择。如果该表有主键约束且是聚族索引，并且插入的数据行并不能保证按主键值的顺利进行时。数据域达到上千条后，插入的速度将明显减慢。比较可取的方法是，先按主键值顺序将主键值插入表中，其他列值都为空，然后用修改语句来修改每个记录的值，用具体值替代空值。速度会明显加快。

2.9 避免在索引列上使用计算

在WHERE子句引用函数计算时，如果索引列是函数的一部分，优化器将不使用索引而是使用全表扫描。应避免在索引列上使用函数。

优化前：（FEE 列上的索引不被使用）

Select * from emp where fee*12>40000；

优化后：（fee 列上的索引被使用，从而提高了查询效率）

Select * from emp where fee>40000/12；

通过试验，由表1对比显示，采用改进后的SQL语句优化算法后，高速缓存命中率明显提高，程序运行的时间和CPU 成本也得到了改善，整个系统的性能比优化前得到了改进，实现了数据库系统总体性能的提高。

3 总结

信息量的不断增长使得数据库的优化调整成为一个重要问题，尤其是对大型数据库更为重要。良好的系统架构设计、合理的资源配置和开发过程中使用高效SQL语句，是影响数据库运行性能高低的关键。在实际应用中，遵从主要的一些SQL优化方法，通过不断的实践和总结，必将降低系统响应时间，提高应用系统的运行效率，使Oracle数据库的性能得到提高。

[参考文献]

[1]Hassan A.afyouni，著，吴越胜，张耀辉，等，译.Oracle9i数据库性能调整与优化[M].北京：清华大学出版社，2005.

[2]王海凤，王海亮，等，编著.Oracle 11g SQL和PL SQL从入门到精通[M].北京：中国水利出版社，2008.

[3]刘光霆.ORACLE中SOL查询优化研究[J].计算机与信息技术，2008，32（5）.

[4]盖国强，杨廷琨，主编.OracleDBA手记3 数据库性能优化与内部原理解析[M].北京：电子出版社，2011.