Oracle SQL的优化规则.docx

Oracle SQL的优化规则.docx

《Oracle SQL的优化规则.docx》由会员分享，可在线阅读，更多相关《Oracle SQL的优化规则.docx（18页珍藏版）》请在冰豆网上搜索。

OracleSQL的优化规则

OracleSQL的优化规则

一、in和exists

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。

一直以来认为exists比in效率高的说法是不准确的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：

表A（小表），表B（大表）

1：

select*fromAwhereccin（selectccfromB）效率低，用到了A表上cc列的索引；select*fromAwhereexists（selectccfromBwherecc=A.cc）效率高，用到了B表上cc列的索引。

2：

相反的select*fromBwhereccin（selectccfromA）效率高，用到了B表上cc列的索引；select*fromBwhereexists（selectccfromAwherecc=B.cc）效率低，用到了A表上cc列的索引。

notin和notexists如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；

而notextsts的子查询依然能用到表上的索引。

所以无论那个表大，用notexists都比notin要快。

in与=的区别:

selectnamefromstudentwherenamein（'zhang','wang','li','zhao'）;

与selectnamefromstudentwherename='zhang'orname='li'orname='wang'orname='zhao'的结果是相同的。

例子：

索引示例图1

--in语句（用到了主表tbl_user的id字段的聚集索引）

createclusterindexidx_userontbl_user（id）

selectnamefromtbl_user

whereidin（selectuser_idfromtbl_passport）

--exists语句（用到了子表tbl_passport的user_id字段的索引）

createindexidx_user_idontbl_passport（user_id）

selectnamefromtbl_usera

whereexists（selectidfromtbl_passportwherea.id=user_id）

--notin语句（不能使用索引，内外表皆为全表扫描）

selectnamefromtbl_user

whereidnotin（selectuser_idfromtbl_passport）

--notexists语句（用到了子表tbl_passport的user_id字段的索引）

selectnamefromtbl_usera

wherenotexists（selectidfromtbl_passportwherea.id=user_id）

二、用>、<替代<>、!

=

不用“<>”或者“!

=”操作符。

对不等于操作符的处理会造成全表扫描，可以用“<”or“>”代替

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

       推荐方案：

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

       1）a<>0改为a>0ora<0

       2）a<>’’改为a>’’

例子：

--<>不会用到tbl_user的id字段上的索引，造成全表扫描

selectnamefromtbl_user

whereid<>9

--用>和<代替<>或!

=，会用到id字段的索引，不会造成全表扫描

selectnamefromtbl_user

whereid>9orid<9

三、避免使用isnull和isnotnull

Where子句中出现ISNULL或者ISNOTNULL时，Oracle会停止使用索引而执行全表扫描。

可以考虑在设计表时，对索引列设置为NOTNULL。

这样就可以用其他操作来取代判断NULL的操作。

ISNULL或ISNOTNULL操作（判断字段是否为空）

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

推荐方案：

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

       1）aisnotnull改为a>0或a>’’等。

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

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

四、Like通配符

当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会被使用。

LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE‘%5400%’这种查询不会引用索引，而LIKE‘X5400%’则会引用范围索引。

一个实际例子：

用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描，如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

例子：

--第一个通配符为%或_，则不会使用索引，全表扫描

selectnamefromtbl_user

wherenamelike'%changxiao%'

--会使用name字段上的索引

selectnamefromtbl_user

wherenamelike'changxiao%'

五、where字句中的索引字段使用函数或计算

如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。

Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。

对数据类型不同的列进行比较时，会使索引失效。

例子：

--对where字句中的索引字段pass_name使用函数则不会使用索引，造成全表扫描

selectpass_namefromtbl_passport

wheresubstr（pass_name,1,9）='changxiao'

--替代方案

selectpass_namefromtbl_passport

wherepass_namelike'changxiao%'

--对where字句中的索引字段id使用计算则不会使用索引，造成全表扫描

selectnamefromtbl_user

whereid+1=2

--替代方案

selectnamefromtbl_user

whereid=2-1

六、用>=替代>

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有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的记录索引。

      推荐方案：

用“>=”替代“>”。

索引示例图2

例子:

--造100万条数据存放到临时表

createtabletemp2_tbl_passportasselect*fromtbl_passport

insertintotemp2_tbl_passportselect*fromtemp2_tbl_passport

--在a字段上创建索引

createindexidx_aontemp2_tbl_passport（a）

--测试效率

SQL>desctemp2_tbl_passport

SQL>clearbuffer;

SQL>settimingon;

SQL>selectidformtemp2_tbl_passportwherea>2;

...

已用时间:

00:

00:

11.11

SQL>clearbuffer;

SQL>selectidfromtemp2_tbl_passportwherea>=3;

...

已用时间:

00:

00:

10.21

七、UNION操作符

1、UNION和UNIONALL

UNION指令的目的是将两个SQL语句的结果合并起来。

从这个角度看，UNION和JOIN有些类似，因为这两个指令都可以由多个表格中获取资料。

UNION的一个限制是两个SQL语句所产生的栏位需要是同样的资料种类。

另外，当我们用UNION这个指令时，我们只会看到不同的资料值（类似selectdistinct）。

例子：

--union（合并且去除重复）

selectidfromtbl_user

union

selectuser_idfromtbl_passport

UNIONALL这个指令的目的也是将两个SQL语句的结果合并在一期，但只是简单的合并。

UNIONALL和UNION不同之处在于UNIONALL会将每一笔符合条件的资料都列出来，无论资料值有无重复。

例子：

--unionall（简单合并且不去除重复）

selectidfromtbl_user

unionall

selectuser_idfromtbl_passport

推荐方案：

在不需要考虑重复记录合并时候用UnionAll来代替Union，因为UNIONALL操作只是简单的将两个结果合并后就返回，而UNION在合并后会先取出两个表的结果，再用排序空间进行排序删除重复的记录。

2、INTERSECT操作符

和UNION指令类似，INTERSECT也是对两个SQL语句所产生的结果做处理的。

不同的地方是，UNION基本上是一个OR（如果这个值存在于第一句或是第二句，它就会被选出），而INTERSECT则比较像AND（这个值要存在于第一句和第二句才会被选出）。

UNION是联集（合并），而INTERSECT是交集。

例子：

--intersect（取两个结果集交集）

selectnamefromtbl_usera

whereexists（

selectidfromtbl_passport

wherea.id=user_id

intersect

selectpass_idfromtbl_pass_app

）

3、MINUS操作符

MINUS指令是运用在两个SQL语句上。

它先找到第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。

如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。

如果第二个SQL语句所产生的结果并没有存在与第一个SQL语句所产生的结果内，那这笔资料就被抛弃。

例子：

--minus（取两个结果集补集）

selectnamefromtbl_usera

whereexists（

selectidfromtbl_passport

wherea.id=user_id

minus

selectpass_idfromtbl_pass_app

）

八、SQL书写的影响

共享SQL语句可以提高操作效率,同一功能同一性能不同写法SQL的影响。

SQL：

A程序员写的为

   Select*fromzl_yhjbqk

   B程序员写的为

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

   C程序员写的为

   Select*fromDLYX.ZLYHJBQK（大写表名）

   D程序员写的为

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

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

   推荐方案：

不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。

九、where后面的条件顺序影响

Oracle从右到左处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。

例子:

--where后面的条件顺序影响

--先执行连接再进行过滤（效率低）

selecta.idfromtbl_userajointemp_tbl_passportb

onb.user_id=8anda.id=b.user_id

--10.25s

--先过滤再执行连接（效率高）

selecta.idfromtbl_userajointemp_tbl_passportb

ona.id=b.user_idandb.user_id=8

--10.09s

一十、查询表顺序的影响

Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。

（只在采用RBO优化时有效）

      在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。

（注：

如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）。

例子:

--查询表顺序的影响

--小表在左，大表在右（效率低）

selecta.idfromtbl_usera,temp_tbl_passportb

wherea.id=b.user_id

--1.31.51m

--大表在左，小表在右（效率高）

selecta.idfromtemp_tbl_passportb,tbl_usera

wherea.id=b.user_id

--46.95s

一十一、orderby语句中使用非索引列

OrderBy语句中的非索引列会降低性能，可以通过添加索引的方式处理。

严格控制在OrderBy语句中使用表达式。

一十二、使用表别名

当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。

这样可以减少解析时间。

一十三、count（*）、count

（1）和count（col）

count（*）和count

（1）的执行效率是完全一样的,count（*）只是返回表中行数，因此Oracle在处理count（*）的时候只需要找到属于表的数据块块头，然后计算一下行数就行了，而不用去读取里面数据列的数据。

而对于count（col）就不一样了，为了去除col列中包含的NULL行，Oracle必须读取该col的每一行的值，然后确认下是否为NULL，然后在进行计数。

因此count（*）应该是比count（col）快的。

例子：

--count（*）

selectcount（*）fromtemp2_tbl_passport

--0.03s

--count

（1）

selectcount

（1）fromtemp2_tbl_passport

--0.03s

--count（col）

selectcount（a）fromtemp2_tbl_passport

--0.03s

一十四、尽量避免使用select*

避免使用Select*,因为系统需要去帮你将*转换为所有的列名，这个需要额外去查询数据字典。

一十五、创建视图

视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。

视图是存储在数据字典里的一条select语句。

通过创建视图可以提取数据的逻辑上的集合或组合。

视图的优点：

1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

2.用户通过简单的查询可以从复杂查询中得到结果。

3.维护数据的独立性，试图可从多个表检索数据。

4.对于相同的数据可产生不同的视图。

视图分为简单视图和复杂视图：

1、简单视图只从单表里获取数据，复杂视图从多表；

2、简单视图不包含函数和数据组，复杂视图包含；

3、简单视图可以实现DML操作，复杂视图不可以。

视图的创建：

CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview_name

[（alias[,alias]...）]

ASsubquery

[WITHCHECKOPTION[CONSTRAINTconstraint]]

[WITHREADONLY]

其中：

ORREPLACE   ：

若所创建的试图已经存在，ORACLE自动重建该视图；

FORCE：

不管基表是否存在ORACLE都会自动创建该视图；

NOFORCE ：

只有基表都存在ORACLE才会创建该视图：

alias：

为视图产生的列定义的别名；

subquery ：

一条完整的SELECT语句，可以在该语句中定义别名；

WITHCHECKOPTION ：

插入或修改的数据行必须满足视图定义的约束；

WITHREADONLY ：

该视图上不能进行任何DML操作。

例如：

CREATEORREPLACEVIEWdept_sum_vw（name,minsal,maxsal,avgsal）

ASSELECTd.dname,min（e.sal）,max（e.sal）,avg（e.sal）

FROM   empe,deptd

WHEREe.deptno=d.deptno

GROUPBYd.dname;

视图的定义原则：

1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；

2.在没有WITHCHECKOPTION和READONLY的情况下，查询中不能使用ORDERBY子句；

3.如果没有为CHECKOPTION约束命名，系统会自动为之命名，形式为SYS_Cn；

4.ORREPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。

例子：

--创建复杂视图

createorreplaceviewview_user_app

as

selecta.name,b.app_namefromtbl_usera,tbl_appb

whereexists（selectidfromtbl_passportcwherec.user_id=a.id

andexists（selectidfromtbl_pass_appdwhered.pass_id=c.idandd.app_id=b.id））

unionall

selecta.name,nullasapp_namefromtbl_usera

whereexists（selectidfromtbl_passportbwhereb.user_id=a.id

andnotexists（selectidfromtbl_pass_appcwherec.pass_id=b.id））

orderbyname

--查询视图

selectcount

（1）fromview_user_app

一十六、附件

Oracle存储过程编写经验和优化措施：

1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。

   2、开发人员在提交SP前，必须已经使用setshowplanon分析过查询计划，做过自身的查询优化检查。

   3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：

   a）SQL的使用规范：

   i.尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

   ii.尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

   iii.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

   iv.注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

   v.不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

   vi.尽量使用exists代替selectcount

（1）来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count

（1）比count（*）更有效率。

   vii.尽量使用“>=”，不要使用“>”。

viii.注意一些or子句和union子句之间的替换。

   ix.注意表之间连接的数据类型，避免不同类型数据之间的连接。

   x.注意存储过程中参数和数据类型的关系。

   xi.注意insert、update操作的数据量，防止与其他应用冲突。

如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。

   b）索引的使用规范：

   i.索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

   ii.尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过indexindex_name来强制指定索引

   iii.避免对大表查询时进行tablescan，必要时考虑新建索引。

   iv.在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

   v.要注意索引的维护，周期性重建索引，重新编译存储过程。

   c）tempdb的使用规范：

   i.尽量避免使用distinct、o