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

飞天心宏的博客

依稀旧梦似曾见,相逢只恨缘太迟

 
 
 

日志

 
 
关于我

出身数学,爱好文学,从事软件开发工作。一个阳光、幽默、热爱生活的男孩子,在追求理想的路上风雨无阻,勇往直前,崇尚“梦想有多远,我们就走多远”!

网易考拉推荐

SQL 优化  

2009-09-17 17:04:33|  分类: SQL SERVER |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

备注:由兄弟落寞到天明总结,感觉总结的非常好,所以珍藏到博客中了

对应于数据库开发的系统,可能性能主要出现在SQL语句上,只要你Java代码,C#代码,或者其他写的没有很大的问题,如果出现了性能问题,那么你可能最需要检查的是你的SQL语句,不要把SQL执行器想象的很强大,他们也是按照一种规则去执行的,所以可能很简单的一个SQL语句,不同的写法,它的执行效率是相差非常大的.这里说明一下,如果你的表记录只有几百条甚至是几千条,可能是体验不到差别的.如果想知道里面的奥妙,你需要制造数据,十万,百万条,Oracle,我们组测效率一般是二百万条.其实有的时候五十万条就可以测试出来,但是我遇到过这样一件郁闷的事情,一次老大让我测试一个SQL语句,我制造了五十万条,性能正常,然后我就跑过去给老大说,OK.  可是后来在集成压力测试的时候,速度慢了许多,经过检查是那种写法的原因,原来在数据增加到70万条的时候,性能出现急剧下降.具体原因不得而知.所以平时多做些测试,多跟前辈学习,多积攒一些心得,对以后企业级开发应用时非常有好处的.

1 通过内部函数提高执行效率

SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)

FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H

WHERE H.EMPNO = E.EMPNO

AND H.HIST_TYPE = T.HIST_TYPE

GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;

通过调用下面的函数可以提高效率.

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2

AS

TDESC VARCHAR2(30);

CURSOR C1 IS

SELECT TYPE_DESC

FROM HISTORY_TYPE

WHERE HIST_TYPE = TYP;

BEGIN

OPEN C1;

FETCH C1 INTO TDESC;

CLOSE C1;

RETURN (NVL(TDESC,’?’));

END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2

AS

ENAME VARCHAR2(30);

CURSOR C1 IS

SELECT ENAME

FROM EMP

WHERE EMPNO=EMP;

BEGIN

OPEN C1;

FETCH C1 INTO ENAME;

CLOSE C1;

RETURN (NVL(ENAME,’?’));

END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)

FROM EMP_HISTORY H

GROUP BY H.EMPNO , H.HIST_TYPE;

 

备注:包括SQL ServerOracle中封装好了功能强大的函数,虽然经过不断更新和兼容,有了很多功能类似的功能,但不能不说其越来越强大,一般来说用封装好的函数来实现功能,效率一般都比我们实现的算法效率要高,所以多看些函数时非常有必要的。

 

2  减少数据库访问次数,争取一次搞定,毕竟数据库的关开以及分配资源是非常浪费时间的。

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

例如:

SELECT NAME

FROM EMP

WHERE EMP_NO = 1234;

SELECT NAME

FROM DPT

WHERE DPT_NO = 10 ;

SELECT NAME

FROM CAT

WHERE CAT_TYPE = ‘RD’;

上面的3个查询可以被合并成一个:

SELECT E.NAME , D.NAME , C.NAME

FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))

AND E.EMP_NO(+) = 1234

AND D.DEPT_NO(+) = 10

AND C.CAT_TYPE(+) = ‘RD’;

 

3. 使用DECODE函数

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

例如:

SELECT COUNT(*)SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE ‘SMITH%;

SELECT COUNT(*)SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE ‘SMITH%;

你可以用DECODE函数高效地得到相同结果 

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%’;

DECODE函数时非常强大的,一列变成多列,在数据比较以及行列转换中发挥着无与伦比的作用。该函数学习也是比较容易的,关键如何灵活运用,你会发现实现DECODE函数的人是多么的伟大。

4. SELECT子句中避免使用 *

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

而且个人认为使用 * 真的很讨厌,当过一段时间之后,你查询的什么列可能你都忘记了,特别是在试用临时表或者线程表之后,在几百行甚至上千行代码中找出你要查询的列,其痛苦程度可想而知,而我就遇到过,所以在这里强烈建议

 

5. 减少访问数据库的次数

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

例如,

以下有三种方法可以检索出雇员号等于03420291的职员.

方法1 (最低效)

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 291;

方法2 (次低效)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1;

END;

 等同于:

 SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO in( 291,342);

方法3 (高效)

SELECT A.EMP_NAME , A.SALARY , A.GRADE,

B.EMP_NAME , B.SALARY , B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

 

备注:方法上只适用唯一索引

个人喜欢方法二,虽然低效点,但是更容易阅读,虽然喜欢,但是后来学习到了另外一种更好的方法

 

6、尽量避免使用NOT IN及相关子查询(EXISTSNOT EXISTS),用外连接替换NOT INNOT EXISTS,用多表关联或IN替换EXISTS

SELECT DXID,DXMC

FROM XTGLDXIDMC

WHERE DXLXID NOT IN (SELECT XTGLDXLX WHERE TYBZ=1)

应写成(XTGLDXLX表中DXLXID唯一)

SELECT A.DXID,A.DXMC

  FROM XTGLDXIDMC A,XTGLDXLX B

  WHERE B.DXLXID(+)=A.DXLXID AND B.TYBZ(+)=1 AND B.DXLXID IS NULL;

 

如果外关联的表中关联列不唯一,上面的写法会有问题,此时应写成下面的格式(找出所有未设置帐户对应的底层科目)

 

SELECT A.IID,A.CAPTION

  FROM ZWITEMSYW A,

       (SELECT KMID FROM XTCAC GROUP BY KMID) B

  WHERE A.LEAF=1

AND B.KMID(+)=A.IID AND B.KMID IS NULL;

 

 相关子查询的处理方式,如:

SELECT POSTID FROM XTPOST A

  WHERE NVL(A.TYBZ,0)=0

        AND EXISTS (SELECT 'X' FROM XTPOSTOP B

WHERE B.YHDM=5045 AND B.POSTID=A.POSTID)

  ORDER BY A.POSTID;

 

可改写成下面的形式:

SELECT A.POSTID FROM XTPOST A,XTPOSTOP B

  WHERE NVL(A.TYBZ,0)=0

        AND B.POSTID=A.POSTID AND B.YHDM=5045

  ORDER BY A.POSTID;

语句:

 

SELECT A.* FROM TEMP_GETYWDJCX_193094 A

  WHERE NOT EXISTS (SELECT B.YWID FROM XTYWTOBILLMAIN B

WHERE B.YWID=A.GID)

可改写成下面的形式:

 

SELECT A.* FROM TEMP_GETYWDJCX_193094 A,

                (SELECT YWID FROM XTYWTOBILLMAIN GROUP BY YWID) B

  WHERE B.YWID(+)=A.GID AND B.YWID IS NULL

 

这个很有效啊,希望大家以后避免使用一些要循环的操作,因为那样太消耗性能了。嘎嘎

7 : WHERE 子句的顺序

 

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

例如:

 (低效,执行时间156.3)

SELECT …

FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER’

AND 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO);

(高效,执行时间10.6)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL > 50000

AND JOB = ‘MANAGER’;

 

时刻想想,Where子句如何排放,这个是最容易忽视的,可是也是最常见的性能缺陷。

8 Like用法总结like通用原则:

 

1、尽量不要使用 like '%..%',可以用函数instr代替 ;

2、对于 like '..%..' (不以 % 开头),Oracle可以应用 colunm上的索引;

3、对于 like '%...' 的 (不以 % 结尾),可以利用reverse + function index 的形式,变化成 like '..%',或创建基于函数的索引;

但是我们也知道,索引是把双刃剑,对于第三种情况,如果索引不能给你带来特别明显的效率,建议就直接使用like 走全表扫面。

sys@mescp> select reverse('123') from dual;           --看看reverse函数的作用

--------------------------------

321

1 row selected.

--建立测试表

create table test_like as select object_id,object_name from dba_objects;   --创建测试表

create index test_like__name on test_like(object_name);      --创建普通b-tree索引

create index test_like__name_reverse on test_like(reverse(object_name)); --创建反向键索引

analyze table test_like compute statistics for table for all indexes;   ---统计分析表

-- '常量开头的like , 会利用index '

select * from test_like where object_name like AS%';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655Bytes=15720)

2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME' (NON-UNIQUE) (Cost=2 Card=118)

--'开头和结尾都是 % ,建议用instr函数代替

select * from test_like where object_name like '%%';

Execution Plan

----------------------------------------------------------

 

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)

1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 ytes=15720)

此种情况建议使用函数instr,其虽然也不会使用索引,但是其结果返回整数,而不是字符,查询效率要优于like,特别是在比较的字符比较长的情况下。

-- '以常量结束,%开头的是不能应用index的'

sys@mescp> select * from test_like where object_name like '%S';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=655 Bytes=15720)

1 0 TABLE ACCESS (FULL) OF 'TEST_LIKE' (Cost=6 Card=655 Bytes=15720)

--'以常量结束的,加个reverse 函数,又可以用上index了'

sys@mescp> select * from test_like where reverse(object_name)like reverse('%AS');

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=655 Bytes=15720)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_LIKE' (Cost=2 Card=655 Bytes=15720)

2 1 INDEX (RANGE SCAN) OF 'TEST_LIKE__NAME_REVERSE' (NON-UNIQUE) (Cost=2 Card=118)

9: 、避免嵌套连接。

例如:B=A AND C=B AND D=C,而应该写成:B=A AND C=A AND D=A。

 

何必写成前面那个呢,那么难阅读

 

10 FROM子句

FROM子句中,以交叉表(被引用的表)放最前面,然后将表按数据量从多到少顺序排列,即数据量大的表放前面,数据量小的表放后面。如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法)

SELECT COUNT(*) FROM TAB1,TAB2 执行时间0.96秒

选择TAB1作为基础表 (不佳的方法)

SELECT COUNT(*) FROM TAB2,TAB1 执行时间26.09秒

通过ordered 的hint强制按顺序从左到右处理.这比较符合人的思维习惯(从左到右,从fact表到dimension表)

 

11 外连接

 

外连接不要使用left join或者Right Join,使用+

只是听数据库组的老大培训的时候讲过,用left join性能不佳,而且容易出现占用内存太大而报异常的问题,具体我也不太清楚。left outer join在执行的时候,把所有左联结的表的所有列都先聚合在一起,然后在这个结果集里再得到我们要查询的列。由于与查询无关的列也被聚合,因此,性能上就有一定的差异,这种差异在连接表较少时基本上看不出来,但在联接的表较多时,性能差异相当明显。从他贴出的内部执行语句看,也确实如此。

 

12   存储过程心得

存储过程,比较喜欢又比较头痛,喜欢的是执行效率确实高了不少,不喜欢的是太难维护了,可能这个星期你写了一个存储过程,OK,下个星期你还可以看的懂,可是两个月以后呢,记得之前完成一个统一算法不同版本的比较的存储过程,前几天改一个小功能,结果光熟悉就花了四十分钟的时间,所以我强烈的建议如下:

1:开头注释

如下:

--功能说明:

   --编写时间:

   --编写人:

   --参数说明:

   --变更说明:

   变量定义

 

这个很重要,因为系统不是你一个人维护,而且更重要的是,你不可能只写一个存储过程,所以写明这个存储过程的作用以及时间是非常有帮助的。

2:注释

千万不要把注释的时间说成是浪费,因为在公司,前几个月的时间是你自己维护自己的代码,而不是写好后就移交到维护组维护,想想如果没有注释。变量达到30到40个,代码达到千行的时候,你是多么的痛苦去修改功能啊,而且维护的时间可是给你的很少啊,感觉上千行的存储过程很少见,那你错了,在财务软件中,不同功能之间的穿透,以及升级再升级,千行代码很常见。用我们老大的一句话叫:功能简单的还用存储过程吗?

3:拼接SQL语句。拼接SQL语句一定要分清楚几段,我们想一下一个完整的SQL语句有哪几部分?是的,无非就是SELECT,FROM,WHERE..  那么不妨定义几个变量,分别表示这几个部分,有什么好处呢,很简单,我们只要调试的时候,分别看这几部分就可以了,你就不用看几十行的SQL语句。

4:函数。存储过程中定义函数是非常有必要的,函数说明功能以及参数,这样你的存储过程主体部分就会少很多,我这个函数是干什么的,输入什么,输出了什么,在你调试的时候,不用关心函数内部的实现,我只需要看到返回的值就可以了,如果不对,我们再进去调试,试想,你的函数上百行的代码很多时候就可以一带而过,是多么爽心的一件事情啊。在这里我得说一下,其实编程很无聊的,不过要从中找到自己的乐趣,那就是看似平常无奇的代码,却暗藏着你的技术,你的功底。

编写的不是代码,是寂寞,平淡无奇的代码.却暗藏着许多玄机,寂寞也在这些玄机中消失殆尽……

                                            ------ 整理:落寞到天明

                                               2009-08-28

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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