操作符优化

IN 操作符

用 IN 写出来的 SQL 的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。 但是用 IN 的 SQL 性能总是比较低的，从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的 SQL 有以下区别：

ORACLE 试图将其转换成多个表的连接，如果转换不成功则先执行 IN 里面的子查询，再查询外层的表记录，

如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN 的 SQL 至少多了一个转换的过程。一般的 SQL 都可以转换成功，

但对于含有分组统计等方面的 SQL 就不能转换了。

推荐方案：在业务密集的 SQL 当中尽量不采用 IN 操作符。

NOT IN 操作符

此操作是强列推荐不使用的，因为它不能应用表的索引。

推荐方案：用 NOT EXISTS 或（外连接 + 判断为空）方案代替

<> 操作符（不等于）不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如

a<>0 改为 a>0 or a<0

a<>'' 改为 a>''

IS NULL 或 IS NOT NULL 操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为 B 树索引是不索引空值的。

推荐方案：

用其它相同功能的操作运算代替，如

a is not null 改为 a>0 或 a>'' 等。

不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）

> 及 < 操作符（大于或小于操作符） 大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，

但有的情况下可以对它进行优化，如一个表有 100 万记录，一个数值型字段 A ， 30 万记录的 A=0 ， 30 万记录的 A=1 ，

39 万记录的 A=2 ， 1 万记录的 A=3 。那么执行 A>2 与 A>=3 的效果就有很大的区别了，

因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较，而 A>=3 时 ORACLE 则直接找到 =3 的记录索引。

LIKE 操作符

LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，

如 LIKE ‘%5400%' 这种查询不会引用索引，而 LIKE ‘X5400%' 则会引用范围索引。 一个实际例子：用 YW_YHJBQK 表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%' 这个条件会产生全表扫描，

如果改成 YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%' 则会利用 YY_BH 的索引进行两个范围的查询，性能肯定大大提高。

UNION 操作符

UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表 UNION 。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个 SQL 在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用 UNION ALL 操作符替代 UNION ，因为 UNION ALL 操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

SQL 书写的影响

同一功能同一性能不同写法 SQL 的影响

如一个 SQL 在 A 程序员写的为

Select * from zl_yhjbqk

B 程序员写的为

Select * from dlyx.zl_yhjbqk （带表所有者的前缀）

C 程序员写的为

Select * from DLYX.ZLYHJBQK （大写表名）

D 程序员写的为

Select * from DLYX.ZLYHJBQK （中间多了空格）

以上四个 SQL 在 ORACLE 分析整理之后产生的结果及执行的时间是一样的，但是从 ORACLE 共享内存 SGA 的原理，

可以得出 ORACLE 对每个 SQL 都会对其进行一次分析，并且占用共享内存，如果将 SQL 的字符串及格式写得完全相同则 ORACLE 只会分析一次，

共享内存也只会留下一次的分析结果，这不仅可以减少分析 SQL 的时间，而且可以减少共享内存重复的信息，

ORACLE 也可以准确统计 SQL 的执行频率。

WHERE 后面的条件顺序影响

WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

Select * from zl_yhjbqk where dy_dj = ‘1KV以下‘ and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = ‘1KV以下‘

以上两个 SQL 中 dy_dj （电压等级）及 xh_bz （销户标志）两个字段都没进行索引，

所以执行的时候都是全表扫描，

第一条 SQL 的 dy_dj = ‘1KV以下‘ 条件在记录集内比率为 99% ，而 xh_bz=1 的比率

只为 0.5% ，在

第二条 进行第一条 SQL 的时候 99% 条记录都进行 dy_dj及xh_bz 的比较，而在进行

第二条 SQL 的时候 0.5% 条记录都进行 dy_dj及xh_bz 的比较，

第三条 以此可以得出第二条 SQL 的 CPU 占用率明显比第一条低。

查询表顺序的影响

在 FROM 后面的表中的列表顺序会对 SQL 执行性能影响，在没有索引及 ORACLE 没有对表进行统计分析的情况下 ORACLE 会按表出现的顺序进行链接，

由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析， ORACLE 会自动先进小表的链接，再进行大表的链接）

SQL 语句索引的利用

对操作符的优化（见上节） 对条件字段的一些优化

采用函数处理的字段不能利用索引，如： substr(hbs_bh,1,4)=' 5400' ，优化处理： hbs_bh like ‘5400%'

trunc(sk_rq)=trunc(sysdate) ， 优化处理：

sk_rq>=trunc( sysdate ) and sk_rq sysdate+1 )

进行了显式或隐式的运算的字段不能进行索引，如： ss_df+20>50 ，优化处理： ss_df>30

‘X'||hbs_bh>'X 5400021452' ，优化处理： hbs_bh>' 5400021542'

sk_rq+5=sysdate ，优化处理： sk_rq=sysdate-5

hbs_bh=5401002554 ，优化处理： hbs_bh=' 5401002554' ， 注： 此条件对 hbs_bh 进行隐式的 to_number 转换，因为 hbs_bh 字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如： ys_df>cx_df ，无法进行优化

qc_bh||kh_bh=' 5400250000' ，优化处理： qc_bh=' 5400' and kh_bh=' 250000' 应用 ORACLE 的 HINT （提示）处理

提示处理是在 ORACLE 产生的 SQL 分析执行路径不满意的情况下要用到的。它可以对 SQL 进行以下方面的提示

目标方面的提示： COST （按成本优化）

RULE （按规则优化）

CHOOSE （缺省）（ ORACLE 自动选择成本或规则进行优化）

ALL_ROWS （所有的行尽快返回）

FIRST_ROWS （第一行数据尽快返回）

执行方法的提示： USE_NL （使用 NESTED LOOPS 方式联合）

USE_MERGE （使用 MERGE JOIN 方式联合）

USE_HASH （使用 HASH JOIN 方式联合）

索引提示： INDEX （ TABLE INDEX ）（使用提示的表索引进行查询） 其它高级提示（如并行处理等等）

ORACLE 的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给 ORACLE 执行的一个建议，有时如果出于成本方面的考虑 ORACLE 也可能不会按提示进行。根据实践应用，一般不建议开发人员应用 ORACLE 提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了， ORACLE 在 SQL 执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。

第二篇：SQL语句优化初探

SQL语句优化初探

陈凯祥

优集学院 BU计算机101班 224000

【摘 要】

影响数据库性能的一个重要因素就是查询语句的低效率，为了编写出高质量的查询语句，提高数据库系统的性能，在此讨论了 查询语句优化的一些技巧，指出了关系数据库查询优化的一般原则，通过对优化方法和技巧的研究，在时间和空间上提高了系统的性能，在一定程度上提高了查询效率。

【关键词】索引 查询优化SQL

SQLStatements optimization que

Chenkaixiang

Youji college ， BU computer 101 class 224000 Abstract:

One of the important influence database performance factor is the low efficiency of the query to write out high quality query, improve the performance of the system, database is discussed in this query optimization some skills, points out the relational database query optimization of the general principles of the optimization methods and techniques through the study, on time and space improves the system performance, to a certain extent, improves query efficiency.

【 keywords 】SQL;optimization strategy;database performance;predicate 0 引 言

查询是数据库中最基本、最常用、最复杂的操作在数据库的管理信息系统中 ,查询操作是所有数据库操作中所占据比重最大的操作。当数据库系统积累到一定程度,若查询时采用单条顺序扫描 ,那么扫描一遍所有的记录可能就得花上几十分钟 ,甚至几小时 ,这样的系统就失去了现实的使用价值。采取什么样的查询策略 ,使查询时间降为几分钟或者几秒钟 ,如何更精确、简便的查询到结果，就是这里需要研究的查询优化问题。 1 优化原理

查询优化力图找出给定表达式等价 ,但执行效率更高的一个表达式 ,一个查询往往会有许多实现方法 ,关键是如何找出一个与之等价的且操作时间又少的表达式 ,查询优化关注的问题是怎样省时、省空间以及效率高。优化的核心问题是尽可能减少查询中各表的参与加工的数据量 ,从而达到优化时间和空间的目的。

2 SQL优化的方法

2.1 模糊匹配的避免

LIKE关键字支持通配符匹配 ,技术上称为正则表达式。但这种匹配特别耗费时间 ,尽量避免使用模糊匹配。例:

SELECT * FROM student WHERE rx score LIKE′4 ′即使在 rx score 字段上建立了索引 ,在这种情况下也还是采用顺序扫描的方式。可改写为:

SELECT * FROM student WHERE rx score >′400′这样 ,在执行查询时就会利用索引来查询 ,显然会大大提高速度。

2.2逻辑表达式的等价变换

由于执行引擎对各种谓词的处理方法不同 因此把逻辑表达式重写成等价的且效率较高的表达式是提高查询效率的有效方法 ,同时也是切实可行的 。通过查阅大量的文献资料以

及大量的实验 ,分析了 RDBMS执行引擎对各种谓词执行效率的不同 ,总结出以下几种逻辑表达式转换规则:

2.2.1 将多个 OR 连接的表达式转化为 ANY表达式

当条件表达式中同层次上出现由连接词 OR 连接的表达式 ,并且 OR 所连接的表达式的左表达式相同且谓词符号也相同时,那么可以将这些表达式合并为一个右表达式用 ANY来描述的表达式。例:

rx score >400 OR rx score > score + 50 OR rx score >score ×2可改写为: rx score > ANY(400 ,score +50 ,score ×2)2.2.2

将 ANY或 ALL 转化为简单的比较表达式当谓词的右表达式为 ANY或 ALL 的形式 ,并且ANY(ALL)包含的各表达式均有固定值 ,并且可以比较大小,则可根据谓词符号 (仅限于比较大小的操作符) 将 ANY(ALL)重写为简单的比较表达式。例:

x > ANY(100 ,200 ,300) 可改写为:x >100;

x > ALL(100 ,200 ,300) 可改写为:x >300

2.2.2将BETWEEN …AND 转化为 AND 连接的表达式

可以把由BETWEEN expr1 AND expr2 的形式重写为用 AND 连接的两个表达式 ,效率往往有一定的提高。例:

rx score BETW EEN 300 AND 400 可改写为:

rx score > =30 AND rx score < =40

2.3 子查询合并

子查询合并是将某些特定的子查询重写为等价的多个表的连接操作。子查询合并的作用在于能使查询语句的层次尽可能地减少 ,从而可提高查询的效率。子查询合并的一般规则为:

(1) 如果外层查询的结果没有重复,即 SELECT子句中包含主码 ,则可以合并其子查询 ,并且合并后的SELECT 子句前应加上DISTINCT标志;

(2) 如 果 外 层 查 询 的 SELECT 子 句 中 有DISTINCT标志 ,那么可以直接进行子查询合并;

(3) 如果内部子查询结果没有重复元组 ,则可以合并。用子查询的方法如下所示 ,例: SELECT student. sno ,sname ,age FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno =′002′)

可改写为:

SELECT student. sno , sname , age FROM student , sc WHERE student. sno = sc. sno AND cno =′002′

2.4 避免使用 In语句

当查询语句中有 IN 关键词时 ,优化器采用 OR并列条件。例如:

SELECT 3 FROM SC WHERE StudentNo in‘( 012001’‘, 012002’)将转化为:SELECT 3 FROM SC WHERE StudentNo =‘012001’or StudentNo =‘012002’数据库管理系统将对每一个OR从句进行查询,将所有的结果合并后去掉重复项作为最终结果,当可以使用 IN 或 EXISTS语句时考虑使用下面的原则:①IN 和 EXISTS:EXISTS远比 IN 的效率高。在操作中如果把所有的 IN 操作符子查询改写为使用EXISTS的子查询 ,这样效率更高。同理 ,使用 NOTEXIST代替NOT IN会使查询添加限制条件 ,由此减少全表扫描次数 ,从而加快查询的速度以达到提高数据库运行效率。②使用 NOT EXIST 或 NOT IN 代替 MNUS :MNUS(集合差)将从一个查询中返回一组行 ,这一组行不会出现在第二个查询返回的行中。使用 NOTEXIST或NOT In可使查询充分利用索引 ,减少子句所需的全表扫描次数。

2.5 用 WHERE代替 HAVING

HAVING子句仅在聚集 GROUP BY子句收集行之后才施加限制 ,这样导致全表扫描后再选择 ,而如果可以使用 WHERE子句来代替 HAVING,则在扫描表的同时就进行了选择 ,其查询效率大大提高了。但是当 HAVING 子句用于聚集函数时不能由WHERE代替时则必须使用 HAVING。

2.6避免对 WHERE 子句中的条件参数使用其它数学操作符

因为若 WHERE 子句中存在一个代数表达式 优化器就不能使用分布统计信息 如可将下面两条 SQL 语句SELECT name FROM employee WHERE SUBSTRING (id, 1, 1) = 'C' SELECT name FROM employee WHERE salary * 10 > 40000写成为:SELECT name FROM employee WHERE id like 'C%'

SELECT name FROM employee WHERE salary > 4000

2.7 用 EXISTS 替代 IN

在许多查询中 为了满足一个条件 往往需要对另一个表进行查询 在这种情况下 使用 EXISTS 比使用 IN 通常将提高查询的效率 例如：

Select ename , job from emp where deptno in( select deptno from dept where dname='SALES'); （低效）

Select ename , job from emp where exists (select deptno from dept where emp.deptno=dept.deptno and dname='SALES'); （高效）

2.8 注意事项：

（1）在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

（2）查询时不要返回不需要的行、列

（3）用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行

（4）在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

（5）一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：

（6） select的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快

3 结 语

查询优化要抓住关键问题 ,对于数据库应用程序 ,重点在于如何提高 SQL 的执行效率，所以选择什么样的查询语句是关键。在数据库的开发和维护过程中 ,查询的优化设计可以提高系统性能 ,对于数据量大的数据库系统尤为重要。以上介绍的几种优化策略使查询在时间和空间上提高了系统的性能 ,在一定程度上提高了查询效率。

【1】 薛永宁，Oracle数据库SQL语句优化初探，文章编号 1674-6708（2010）31-0213-02

【2】 徐秀勤，Oracle数据库中 语句优化SQL， （南京信息职业技术学院 软件学院 江苏 南京

210046， ），文章编号 1009-3044(2010)24-6679-03：

【3】 蔡柳萍 SQL查询语句的优化 广东技术师范学院天河学院计算机系

【4】 张 敏SQL 查询语句优化研究 (1.西北大学 陕西 西安 710069;2.西安翻译学院 计算机

教研室 陕西 西安 710105) 文章编号:1004 - 373X(2010)04 - 152 – 02

【5】 黄志真 杨武 数据库 SQL 查询优化方法的研究 重庆工学院 信息工程系 重庆 400050)

【6】 黄 欣 基于SQL Server 的嵌入式SQL 编程 ( 嘉兴南洋职业技术学院 电子信息工程系 浙江

嘉兴 314003 )

【7】 高攀，施蔚然 基于 数据库的 语句优化Oracle SQL （福建省气象信息中心，福州 ）350001

【8】 吴京慧 SQL 语句优化技术分析与探讨 (江西财经大学信息管理学院 , 南昌 330013) 文章

编号:1009 - 2552(2005)03 - 0057 – 03

【9】 杨隆平SQL语言在 VB中的优化应用 锦州师范高等专科学校经济管理系，辽宁锦州 121000）

文章编号：1009-8135（2010）03-0066-02

【10】 肖辉辉 段艳明，关系数据库 语句的设计优化研究SQL （河池学院 计算机与信息科学系 广

西 宜州 546300， ） 文章编号 1672-7800 2010 12－0177－02：

【11】 杨 姝 路 遥 马红霞SQL查询语句的优化方法研究 （二炮清河门诊部 北京 100085）文

章编号：1671－7597（2011）0120095－01

【12】 Scott Mayers.More Effective C [M].2nd Edition.Addision Wesley Lonman Inc. ,1996.

【13】 Bjarne Stroustrup. The C Programming Language[M].Special Edition(影印版).北京:高等

教育出版社 ,200