ORACLE SQL语句优化技术分析

类型：数据库类大小：42.1M语言：中文评分：4.2标签:立即下载为了让更多新手受益，我花时间整理了SQL语句的优化部分，希望大家一起进步。

1.操作员优化

1.输入运算符

用IN编写的SQL的优点是写起来简单明了，更适合现代软件开发的风格。然而，带有IN的SQL的性能总是很低。从Oracle执行的步骤来看，带IN的SQL和不带IN的SQL之间存在以下差异：

ORACLE试图将其转换为多个表的连接。如果转换不成功，请先在in中执行子查询，然后查询外层的表记录。如果转换成功，直接使用多个表的连接方式进行查询。可见，带IN的SQL至少多了一个转换过程。一般的SQL可以成功转换，但是带有分组统计的SQL无法转换。

推荐方案：尽量不要在业务密集型的SQL中使用IN运算符，而是使用EXISTS方案。

2.非IN运算符

不建议对强列执行此操作，因为它无法对表应用索引。

推荐方案：替换为不存在方案

3.为空或不为空(判断字段是否为空)

通常，判断字段是否为空不会应用索引，因为索引不会索引空值。

推荐方案：替换为其他功能相同的操作，如将a不为null改为a0或a ' '。该字段不允许为空，但使用默认值而不是空值。如果应用程序中的状态字段不允许为空，则默认值为应用程序。

4、和运算符(大于或小于运算符)

一般不需要调整大于或小于的运算符，因为有索引的话会按索引搜索，但在某些情况下可以优化，比如100万条记录的表，数值字段A，30万条记录的记录A=0，300，000条记录A=1，390，000条记录A=21，000条记录A=3。那么执行A2的效果和A=3的效果就大不相同了，因为执行A2的时候，ORACLE会先找到记录索引2再比较，而A=3的时候，ORACLE会直接找到记录索引=3。

5.LIKE运算符

通配符查询可以应用于LIKE运算符，其中通配符的组合几乎可以是任意的。但是，如果使用不好，就会造成性能问题。例如，like' t00% '不会引用索引，但like' x5400% '会引用范围索引。

一个实际的例子：像‘t00 %’这样的业务号YY_BH，可以用YW _ yhjbjk表中业务号后面的账号ID号查询，会产生全表扫描。如果改成像‘x 5400%’这样的YY _ BH或者像‘b 5400%’这样的YY _ BH，那么YY _ BH的索引会被用来在两个范围内进行查询，性能肯定会有很大的提升。

6.UNION运算符

UNION在表链接后过滤掉重复的记录，因此它在表链接后对结果集进行排序，删除重复的记录并返回结果。实际上，在大多数应用程序中不会生成重复记录，最常见的是进程表和历史表UNION。例如从GC _ dfys union中选择*从ls _ jg _ dfys中选择*这个SQL首先取出两个表的结果，然后用排序空间进行排序，删除重复的记录，最后返回结果集。如果表数据很大，可能会导致按磁盘排序。

推荐方案：使用UNION ALL运算符代替UNION，因为UNION ALL运算只是将两个结果合并，然后返回。

从gc_dfys联合中选择*从ls_jg_dfys中选择所有*

二、SQL编写的影响

1、相同的功能、相同的性能以及不同编写SQL的影响。

例如，一个SQL是由一个程序员在A中作为Select * from zl_yhjbqk编写的

b程序员从dlyx.zl_yhjbqk中写了Select *

c程序员从DLYX写了Select *。ZLYHJBQK(大写表名)

d程序员从DLYX写了Select *。ZLYHJBQK(中间有更多空格)

经过ORACLE的分析和整理，上述四个SQL的结果和执行时间是一样的，但是从ORACLE共享内存SGA的原理可以得出结论，ORACLE会对每个SQL分析一次，占用共享内存。如果SQL字符串和格式写得完全一样，那么ORACLE只会分析一次，共享内存只会留下一次分析结果，这样不仅可以减少分析SQL的时间，还可以减少共享内存的重复信息。ORACLE还可以准确统计SQL的执行频率。

2.WHERE后条件顺序的影响

WHERE子句后的条件顺序将直接影响大数据表的查询。例如，从ZL _ yhjbqk中选择*其中dy _ DJ=' 1kv以下'和xh_bz=1从ZL _ yhjbqk中选择*其中xh_bz=1和dy _ DJ=' 1kv以下'，上面两个SQL中的dy _ DJ(电压电平)和xh_bz(账户关闭标志)字段不会被索引，因此它们在执行时都会在整个表中被扫描。记录集中第一条SQL中dy _ DJ=以下'条件的比率为99。而xh_bz=1的比例仅为0.5%，执行第一条SQL时99%的记录与dy_dj、xh_bz进行比较，执行第二条SQL时0.5%的记录与dy_dj、xh_bz进行比较，由此可以得出第二条SQL的CPU利用率明显低于第一条SQL的结论。

3.查询表的顺序的影响

FROM后表中的列表顺序会影响SQL执行的性能。在没有索引和ORACLE对表的统计分析的情况下，ORACLE将按照表出现的顺序链接它们。因此，如果表的顺序不对，就会出现数据交叉，这会消耗服务器的资源。(注：如果对表进行统计分析，ORACLE会在链接大表之前自动推进小表的链接)

三、SQL语句索引的使用

1.操作员优化(同上)

2.条件域的一些优化

由函数处理的字段不能使用索引，例如：

Substr(hbs_bh，1，4)='5400 '，优化处理：hbs_bh喜欢' 5400%'% '

Trunc(sk_rq)=trunc(sysdate)，优化处理：sk _ rq=trunc (sysdate)和sk _ rqtrunc (sysdate 1)

具有显式或隐式操作的字段不能被索引，例如ss_df 2050和优化处理：ss_df30

X' | 'X' || hbs_bh'X5400021452 ' '，优化处理：hbs_bh'5400021542 '

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

Hbs_bh=5401002554，优化处理：hbs_bh=' 5401002554 '，注意：此条件隐式将Hbs_bh转换为_number，因为hbs_bh字段是字符类型。

当条件包含此表中的多个字段时，无法执行索引，例如ys_dfcx_df，并且无法执行优化。qc_bh || kh_bh='5400250000 '，优化处理：qc_bh='5400 '和kh_bh='250000 '

四.其他人

ORACLE的提示功能是一个强大的功能，也是一个复杂的应用，提示只是ORACLE执行的一个建议，有时ORACLE可能会因为成本的原因而不遵循提示。根据实际应用，一般不建议开发人员应用ORACLE prompt，因为每个数据库和服务器的性能都不一样，很可能一个地方的性能提高了，而另一个地方的性能下降了。ORACLE在SQL执行分析方面比较成熟。如果分析执行路径错误，首先要分析数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表和索引)的统计信息是否正确。