ORACLE中SQL语句的优化研究

2022-09-10

大多数情况下, 系统运行缓慢不是由于所有部件都饱和引起的, 而是由于系统中的某个部分限制了整体的性能, 这部分称为瓶颈。通常影响ORACLE数据库性能指标的三个瓶颈主要是:CPU、内存、I/O。数据库性能的优化主要是ORACLE数据库参数的调整、磁盘I/O调整、应用程序SQL语句分析及设计、网络性能调整等。本文主要通过优化SQL语句来提高ORACLE数据库的性能。

1 SQL语句处理流程

1.1 打开游标

从上图可以看出处理SQL语句的第一步就是要打开一个游标, 事实上, 一个完整的SQL处理过程就是一个游标的生命周期。

1.2 共享SQL

O r a c l e内存中有一个区叫S H A-RED_POOL, 这个区的主要作用就是将SQL语句存放在这个区内, 当客户发出一个新的SQL语句, 数据库引擎首先会到这个区查找是否有相同的SQL, 如果有, 则避免了解析、分析索引、制定执行计划等一系列的动作。如果没有则需要进行hard parse。

1.3 绑定变量

如果在SQL中指定了绑定变量, 需要在这个阶段给SQL附上绑定变量的值。

1.4 并行处理

如果SQL需要进行并行处理, 在这一阶段需要把整个SQL分割成多个并行的部分。

1.5 执行查询

Oracle按照执行计划指定的方式执行SQL, 执行UPDATE和DELETE语句时, 必须将行锁定, 以免其他用户修改。Oracle先从数据库缓冲区中寻找是否存在所要的数据块, 如果存在, 就直接读或修改, 否则从物理文件中读到数据库缓冲区中。

1.6 返回结果

对SELECT语句需要返回结果的语句, 首先看是否需要排序, 需要, 则排序后返回给用户, 然后根据内存的大小不同, 可以一次取出一行数据, 也可以一次取一组数据。这时, 可能要用到数据结构中的外部排序, 并归排序等算法, 所以如内存允许的话, 会在很大程度上提高性能。

2 影响SQL性能的原因

影响SQL性能的因素很多, 如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序 (CBO) 的正确判断等, 这些往往和DBA密切相关。纯粹从SQL语句出发, 笔者认为影响SQL性能不外乎以下四个重要原因。

(1) 在大记录集上进行高成本操作, 如使用了引起排序的谓词等。

(2) 过多的I/O操作 (含物理I/O与逻辑I/O) , 最典型的就是未建立恰当的索引, 导致对查询表进行全表扫描。

(3) 处理了太多的无用记录, 如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。

(4) 未充分利用数据库提供的功能, 如查询的并行化处理等。

3 SQL优化解决方案

通常, 一个关系数据库服务器上的SQL进程要使用该服务器60%~90%的资源。一般来说, 数据库应用上60%的性能问题是因为运行着效率很低的SQL语句, 而其中30%SQL语句的性能是可以被显著改进提升的。

3.1 避免使用<>和!=

<>和!=都是不等于的意思, 但在SQL开发过程中尽量不要使用, 因为这个关键字会造成索引失效, 降低查询效率。下面是使用表client_info (客户信息表) 进行测试的情况, 其中在cl_no (客户编号) 列上建立了索引。

3.2 避免隐式转换

隐式转换是最容易被开发人员忽视的一点, 因为它并不影响查询结果, 但它却影响了查询效率, 在查询过程中, 由于数据类型的不符合, 造成隐式的调用to_char或to_number函数。下面采用USER_INFO (用户信息表) 来测试这一过程, 如表1所示。

◆查询用户名称为101的客户信息, 下面是没有隐式转换的查询:

从测试结果很容易看出:第二个查询中索引失效了, 这是因为ORACLE数据库在解析过程中后台进行了隐式转换user_name=to_char (101) 造成的。

3.3 选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名, 因此FROM子句中写在最后的表 (基础表) 将被最先处理, 在FROM子句中包含多个表的情况下, 必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时, 会运用排序及合并的方式连接首先, 扫描第一张表 (FROM子句中最后的那个表) 并对记录进行排序, 然后扫描第二张表 (FROM子句中最后第二个表) , 最后将所有从第二张表中检索出的记录与第一个表中合适记录进行合并。下面是对这一理论的验证, 其中用户信息表user_info有999条数据, 合同信息表cont_info有32000条数据。

这一结果表明, 合理的表的连接顺序对数据库性能的提高是很有帮助的。

4 结语

SQL的优化与调整是花费最少且效果最明显的提升数据库性能的方法, 但同时也是非常重要和复杂的内容。数据库性能的提升不光是数据库管理员的职责, 它需要开发人员的配合与支持, 并采用正确的方法, 才能最终达到优化的目的。

摘要:本文主要介绍了SQL语句的处理流程, 并给出了SQL优化的解决方案, 以实例证明优化SQL能够明显的提升数据库性能。

关键词:SQL,处理流程,优化