注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

清风幻影的博客

Where there is love, I will be there.

 
 
 

日志

 
 
关于我

同是天涯沦落人,相逢何必曾相识. 天生我材必有用,千金散尽还复来. 天若有情天亦老,人间正道是沧桑. 月影西斜人已去, 堤上梅花情依旧, 此情故已成追忆, 美人如玉夜留香

网易考拉推荐

ORACLE-SQL性能优化  

2013-08-01 13:54:30|  分类: oracle_1 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

http://z-xiaofei168.iteye.com/blog/1916256

ORACLE-SQL性能优化

  

作者:张小飞 iteye账号:z_xiaofei168

SQL语句优化是提高性能的重要环节

n  开发人员不能只注重功能的实现,不管性能如何

n  开发人员不能把Oracle当成一个黑盒子,必须了解其结构、处理SQL和数据的方法

n  必需遵守既定的开发规范

访问数据表的方式

全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。

通过ROWID访问表

ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能的提高。

SQL Tunning 的重点

n  SQL: insert, update, delete, select;

n  主要关注的是select

n  关注的是:如何用最小的硬件资源消耗、最少的响应时间定位数据位置

SQL Tunning Tips

sql 语句的编写原则和优化

随着数据库中数据的增加,系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据,劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍,对于一个系统不是简单地能实现其功能就可,而是要写出高质量的SQL语句,提高系统的可用性。

sql 语句的编写原则和优化

n  在编写SQL语句时我们应清楚优化器根据何种原则来使用索引,这有助于写出高性能的SQL语句。

n  SQL语句的编写原则和SQL语句的优化,请跟我一起学习以下几方面:

1.不要让Oracle做得太多;

2.给优化器更明确的命令;

3.减少访问次数;

4.细节上的影响;

一、不要让Oracle做得太多

1、避免复杂的多表关联,如:

select …from user_files uf, df_money_files dm, cw_charge_record cc

where uf.user_no = dm.user_no

and dm.user_no = cc.user_no

and ……

and not exists(select …)

2、避免使用“*

当你想在SELECT子句中列出所有的COLUMN,使用动态SQL列引用*是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将‘*依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意

味着将耗费更多的时间;

3避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BYSQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECTSQL语句都可以用其他方式重写.

4EXISTS替换DISTINCT,例如:

低效:

    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 ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

UNION-ALL 替换UNION ( if possible)

SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序.

低效:

   SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

高效:

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION ALL

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

二、给优化器更明确的命令

                   1避免在索引列上使用计算函数

         WHERE子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描.举例:

低效:

SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

2、避免使用前置通配符

WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421';

                 在这种情况下,ORACLE将使用全表扫描.

         在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。在下面的查询中索引得到了使用:

select * from employee where last_name like 'c%';

                   3、避免在索引列上使用NOT

通常,我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. ORACLE遇到NOT,他就会停止使用索引转而执行全表扫描.举例:

低效: (这里,不使用索引)

   SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

   SELECT … FROM DEPT WHERE DEPT_CODE > 0;

                   4避免在索引列上使用 IS NULLIS NOT NULL

         避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值(123,null)的记录(插入). 然而如果所有的索引列都为空,ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.任何在where子句中使用is nullis not null的语句优化器是不允许使用索引的。

三、减少访问次数

         当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.类比,工程实施。

四、细节上的影响

         1WHERE子句中的连接顺序

           ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时,WHERE子句中排在最后的表应当是返回行数可能最少的表,有过滤条件的子句应放在WHERE子句中的最后。

n  如:设从emp表查到的数据比较少或该表的过滤条件比较确定,能大大缩小查询范围,则将最具有选择性部分放在WHERE子句中的最后:

select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

n  如果dept表返回的记录数较多的话,上面的查询语句会比下面的查询语句响应快得多。

select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

2WHERE子句 ——函数、表达式使用

最好不要在WHERE子句中使用函或表达式,如果要使用的话,最好统一使用相同的表达式或函数,这样便于以后使用合理的索引

3Order by语句

n  ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

n  仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

4>= 替代 >

如果DEPTNO上有一个索引。

         高效:

          SELECT * ROM EMP HERE DEPTNO >=4

         低效:

        SELECT * ROM EMP HERE DEPTNO >3

5通过使用>=<=等,避免使用NOT命令

例子:select * from employee where salary <> 3000;

对这个查询,可以改写为不使用NOT

select * from employee where salary<3000 or salary>3000;

         虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oraclesalary列使用索引,而第一种查询则不能使用索引。

  评论这张
 
阅读(576)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018