基于SQL的数据库性能优化分析.doc

1、基于 SQL 的数据库性能优化分析摘 要 数据库性能调整与优化，对于提高数据库的稳定性、可靠性，保障业务高效运行有着重要意义。本文从数据库性能优化角度出发，探讨程序开发过程中，Oracle 数据库的 SQL 语句调整和优化技术。 关键词 SQL； 数据库； 性能优化 doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2013 . 15. 039 中图分类号 TP392 文献标识码 A 文章编号 1673 - 0194（2013）15- 0064- 02 0 引 言 随着计算机技术和网络技术的普及与广泛应用，数据库技术也得到了长足发展，并成为现代计算机信息与应

2、用系统的核心基础技术。Oracle 数据库是目前使用较为广泛的数据库系统，用户规模和应用范围不断扩展，由此带来的系统性能问题愈来愈突出，因此，对数据库优化技术方法的探索，对解决系统瓶颈，节约系统开销，确保数据库和业务系统稳定、高效地运行，有着至关重要的意义。 数据库的优化，主要从硬件、操作系统、数据库参数和应用程序等方面进行配置和调整。据统计，对硬件系统和数据库参数进行优化所获得的性能提升，只占数据库系统性能提升的 40%左右，另外的 60%系统性能提升来自于对应用程序的优化。其中，应用程序的优化通常可分为两个方面：源代码和 SQL 语句。一方面，由于涉及对程序逻辑的改变，源代码的优化在时间成

3、本和风险上代价很高，而对数据库系统性能的提升收效有限；另一方面，应用程序对数据库的操作，最终体现在 SQL 语句对数据库的操作，因此 SQL 语句的执行效率决定了数据库的性能。由此可见，应用程序的优化应着重于 SQL 语句的优化。在数据库应用系统中，相同功能的程序可以采用不同的 SQL 语句编写实现，而不同的 SQL 语句存在着性能及效率上的差异，这种差异在大型数据库环境中表现得尤为明显。因此，通过优化调整 SQL 语句，从而显著改善整个系统的性能，对提高数据库内存区的命中率、减少 I/O 访问、减少对网络资源的占用等有着重要意义。 1 SQL 优化技术分析及实现方法 要对 SQL 语句进行优

4、化，首先应该清楚 SQL 语句的执行过程。Oracle 会为每个用户进程分配一个服务器进程，当服务器进程接收到用户进程提交的 SQL 语句时，服务器进程会对 SQL 语句进行语法和词法分析。检查通过后，服务器进程会将 SQL 语句转变为 ASCII 码，并通过一个 Hash 函数将 ASCII 码生成 Hash 值，服务器进程会到系统全局区（System Global Area，SGA）的共享池（Shared Pool）中查询此 Hash值是否存在。如果存在，服务器进程会在共享池中读取已经解析好的语句来执行，这就是软解析；如果不存在，则需要通过优化器生成执行计划和生成执行编码，这就是硬解析，硬

5、解析完成后，Oracle 会将 SQL 语句本身代码、Hash 值、执行计划和所有与此语句相关的统计数据放到共享池中。 根据以上所述的 SQL 语句执行过程，分析优化 SQL 语句，应主要从以下几个方面来进行： （1） 共享 SQL 语句。 （2） 高质量的 SQL 语句。 （3） 使用索引。 1.1 共享 SQL 语句 为了不重复解析相同的 SQL 语句，在第一次解析之后，Oracle 将SQL 语句存放在内存中。这块位于系统全局区域的共享池中的内存可以被所有的数据库用户共享。如果用户提交的 SQL 语句和之前的执行过的语句完全相同，Oracle 就能很快获得已经被解析的语句以及最好的执行路

6、径，从而大大地提高了 SQL 的执行性能并节省了内存的使用。要共享 SQL语句，必须满足以下 3 个条件： （1） 当前被执行的语句和共享池中的语句必须完全相同。 （2） 两个语句所指的对象必须完全相同。 （3） 两个 SQL 语句中必须使用相同名字的绑定变量（bind variables） 。 1.2 高质量的 SQL 语句 对于海量数据，质量高的 SQL 语句和质量差的 SQL 语句的执行时间可能相差几百秒，可见对于应用系统来说，编写高质量的 SQL 语句将显著提高系统的速度和可用性。 1.2.1 使用 DECODE 函数来减少处理时间 使用 DECODE 函数可以避免重复扫描相同记录或重

7、复连接相同的表，例如： SELECT COUNT（*） ， SUM（SAL） FROM EMP WHERE DEPT_NO = 0200 AND ENAME LIKE SMITH% ； SELECT COUNT（*） ， SUM（SAL） FROM EMP WHERE DEPT_NO = 0300 AND ENAME LIKE SMITH% ； 使用 DECODE 函数，可以高效地得到相同结果： SELECT COUNT（DECODE（DEPT_NO， 0200， X， NULL） ） D0020_COUNT， COUNT（DECODE（DEPT_NO， 0300， X， NULL） ） D0

8、030_COUNT， SUM（DECODE（DEPT_NO， 0200， SAL， NULL） ） D0020_SAL， SUM（DECODE（DEPT_NO， 0300， SAL， NULL） ） D0030_SAL FROM EMP WHERE ENAME LIKE SMITH% ； 类似的，DECODE 函数也可以运用于 GROUP BY 和 ORDER BY 子句中。 1.2.2 用 TRUNCATE 替代 DELETE 当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被恢复的信息。如果用户没有提交事务，Oracle 会将数据恢复到删除之前的状

9、态（准确地说是恢复到执行删除命令之前的状况） ，而当运用 TRUNCATE 时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复，因此只有很少的资源被调用，执行时间也会很短。 1.2.3 多使用 COMMIT 在程序中应尽量多使用 COMMIT，这样程序的性能会得到提高，需求也会因为 COMMIT 所释放的资源而减少。COMMIT 可以释放的资源包括：回滚段上用于恢复数据的信息，被程序语句获得的锁，redo log buffer 中的空间，以及 Oracle 管理上述 3 种资源的内部花费。 1.2.4 用 EXISTS 替代 IN，用 NOT EXISTS 替代 NOT IN

10、在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用 EXISTS（或 NOT EXISTS）通常将提高查询的效率。例如： 低效： SELECT * FROM EMP （基础表） WHERE EMPNO 0 AND DEPTNO IN （SELECT DEPTNO FROM DEPT WHERE LOC = 在这种情况下） 高效： SELECT * FROM EMP （基础表） WHERE EMPNO 0 AND EXISTS （SELECT MPNO 0 ANPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB

11、） 在大数据量时，这个操作非常明显。相对来说，用 NOT EXISTS 替换NOT IN 将更显著地提高效率。 1.2.5 用 EXISTS 替换 DISTINCT 当提交一个包含一对多表信息（比如部门表和员工表）的查询时，避免在 SELECT 子句中使用 DISTINCT。一般可以考虑用 EXIST 替换。例如：低效： SELECT DISTINCT DEPT_NO，DEPT_NAME FROM DEPT D，EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效： SELECT DEPT_NO，DEPT_NAME FROM DEPT D WHERE EXISTS （SEL

12、ECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO） ； EXISTS 使查询更为迅速，因为 RDBMS 核心模块将在子查询的条件一旦满足后，立刻返回结果。 1.2.6 避免耗费资源的操作 带有 DISTINCT，UNION，MINUS，INTERSECT，ORDER BY 的 SQL 语句会启动 SQL 引擎执行耗费资源的排序（SORT）功能。 DISTINCT 需要一次排序操作， 而其他的至少需要执行两次排序。 例如，一个 UNION 查询，其中每个查询都带有 GROUP BY 子句， GROUP BY 会触发嵌入排序（NESTED SORT） ； 这

13、样， 每个查询需要执行一次排序， 然后在执行 UNION 时， 又一个唯一排序（SORT UNIQUE）操作被执行，而且它只能在前面的嵌入排序结束后才能开始执行，嵌入的排序的深度会大大影响查询的效率。 1.2.7 联合查询中，注意选取连接顺序 查询工作所包含表的数量在 5 个以上时，执行的连接效果会受到 From 语句中体现出来的次序影响。因为会选择不同的优化处理器，选择的基础表也会不同。如果采用的是 CBO，SQL 语句就会被优化器检查每一个物理大小的表，索引的工作状态，执行路径最终要选取最低消费。假如选择的是 RBO，全部的索引都要对应连接的条件，这时候那个位于From 语句中的表应该就是

14、基础表，因为解析器处理表名的顺序是从左至右。 1.3 使用索引 1.3.1 用索引提高效率 索引是表的一个概念部分，用来提高检索数据的效率。实际上，Oracle 使用了一个复杂的自平衡 B-tree 结构。通常，通过索引查询数据比全表扫描要快。当 Oracle 找出执行查询和 Update 语句的最佳路径时，Oracle 优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性验证。 除了那些 LONG 或 LONG RAW 数据类型，可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，在扫描小表时，使用索引同

15、样能提高效率。 虽然使用索引能得到查询效率的提高，但是也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE 将为此多付出 45 次的磁盘 I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。这就涉及用户所使用的系统是 OLTP 还是 OLAP 类型的，以此决定索引的建立。 1.3.2 避免在索引列上使用计算 当数据量很大时，在 SQL 语句中使用索引可将 SQL 语句的执行速度加快许多，但是不正确的 SQL 语句可能会使索引失效，所以必须注意

16、一些限制索引使用的操作，例如： WHERE 子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，从而引起索引失效，所以应该尽量把此类操作移至比较符号的右边。 SELECT * FROM DEPT WHERE SAL * 12 500000； 索引失效 SELECT * FROM DEPT WHERE SAL 500000/12； 索引有效 1.3.3 避免潜在的数据类型转换 当字符型数据与数值型数据比较时，Oracle 就会自动将字符型用TO_NUM-BER（）函数进行转换，这样就导致了全表扫描。 SELECT * FROM T_SALARY WHERE SALARY=1500

17、 ； SALARY 字段为 NUMBER 型，却与字符型比较，导致索引失效 1.3.4 避免在索引列上使用 IS NULL 和 IS NOT NULL 避免在索引中使用任何可以为空的列，Oracle 将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。例如： SELECT * FROM DEPT WHERE DEPT_CODE IS NOT NULL； 索引失效 SELECT * FROM DEPT WHERE DEPT_CODE =0； 索引有效 2 小 结 对大型数据库而言

18、，数据海量增长，使得数据库的优化调整成为一个重要问题。良好的系统架构设计、合理的资源配置和开发过程中高效SQL 语句编写，是提高数据库性能的关键因素。在应用系统开发设计中，通过对 SQL 的优化调整，可以显著地改善整个数据库系统的性能，降低系统响应时间，提高应用系统的运行效率，从而使 Oracle 数据库获得最优的性能。 主要参考文献 1 黄河. Oracle 9i for Windows NT/2000 数据库系统培训教程M. 北京：清华大学出版社，2003：275-292. 2 谢东. 基于 Oracle 的数据库安全策略J. 现代情报，2006，26（1）：119-120. 3 郭霞. 基于 Oracle 数据库的 SQL 语句优化分析J. 电脑知识与技术，2011，7（21）：5063-5065.