大家好，欢迎来到IT知识分享网。
优化规则总结：
1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数 ,计算等。例外情况DBA会提前公布。
2.在业务逻辑及工具允许的条件下，采用正确的SQL嵌套方式，且把运算处理放在最外层。
3.详细的分析业务逻辑和数据结构，避免不必要的计算。如排重distinct， nvl检测等。
4.避免数据列类型和输入值类型不一致。
5.SQL语句中使用绑定变量。
6.理清业务逻辑，选择中间结果集最少为目标，减少SQL内部的操作运算。
7.减少数据库的调用次数。
8.正确高效的使用模糊匹配查询—右百分号。
9.分页排序时正确的使用rownum
10.避免使用耗费资源的操作–UNION,MINUS,INTERSECT，DISTINCT
优化规则举例说明：
规则1.通常不应对where语句检索条件的表列做任何处理。处理包括加函数,计算等 。例外情况DBA会提前公布。
举例(函数)：
Error:
select count(*) as col_0_0_
from B_LOG blog0_
where :”SYS_B_0″ =:”SYS_B_1″
and to_char(blog0_.OP_DATE,:”SYS_B_3″) >= :”SYS_B_4″
正确改法：
select count(*) as col_0_0_
from B_LOG blog0_
where :”SYS_B_0″ =:”SYS_B_1″
and blog0_.OP_DATE >= To_date(‘2007-01-0600:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)
and blog0_.OP_DATE <= to_date(‘2007-01-0823:59:59’, ‘yyyy-mm-dd hh24:mi:ss’)
举例(计算):
Error:
SELECT ENAME
FROM EMP
WHERE DEPTNO + 1 = 10 /*DEPTNO上的索引将失效*/
AND EMP_TYPE ||’$’ = ‘A’ /*EMP_TYPE上的索引将失效*/
正确改法：
SELECT ENAME
FROM EMP
WHERE DEPTNO = 10-1 /*DEPTNO上的索引将失效*/
AND EMP_TYPE = ‘A’ ||‘$’
规则2.在业务逻辑及工具允许的条件下，采用正确的SQL嵌套方式，且把运算处理放在最外层。
on_busin_chance a与Busin_long_info 关系上一一对应。
改成 嵌套方式， 最里层不要关联Busin_long_info， 用结果的20条 (rownum < :”SYS_B_11″) 去关联Busin_long_info 。
Error
—————————–PrjIframe.exe— begin ——————————-
Select title,
:”SYS_B_00″ || username || :”SYS_B_01″ || bc_id || :”SYS_B_02″ as linku,
…….
price,
trustvalue
from (Select a.title as title,
a.bc_id as bc_id,
…….
a.pricerange as price,
:”SYS_B_04″ as trustvalue
from on_busin_chance a, Busin_long_info b
where a.bc_id = b.bc_id
and a.pubdate > sysdate – :”SYS_B_05″
…….
and a.SortTag = :”SYS_B_10″
order by a.pubdate desc)
where rownum < :”SYS_B_11″
—————————–PrjIframe.exe— end ——————————-
错误的sql:
select A.bc_id,
A.title,
A.Pubdate ,
/*to_char(A.Pubdate, :”SYS_B_1″) as NEWPubdate,*\*/
A.picpath,
A.supcatid asCLSID,
A.username,
A.providerid,
C.NAME ascorname,
C.Memtypeid,
b.areaid
from (select bc_id,
title,
…..
providerid
frombusin_list
wheresorttag = ‘1’
order bypubdate desc) A,
(selectsupcatid, areaid
fromnewhc.new_iframe_class
where areaid= ‘005’
and clsid= ‘003034’) B,
provider_listC
where b.supcatid =A.supcatid
and C.providerid =A.providerid
and rownum < 20
正确的sql
select bb.to_char(A.Pubdate, :”SYS_B_1″) asNEWPubdate , xxxx,xxxx,
from (select aa.*
from(select A.*
from busin_list A, newhc.new_iframe_class B
where A.sorttag = ‘1’
and B.areaid = ‘005’
and B.clsid = ‘003034’
and B.supcatid = A.supcatid
orderby A.pubdate desc) aa
whererownum < 20) bb,
provider_listC
where c.providerid= bb.providerid
规则3.详细的分析业务逻辑和数据结构，避免不必要的计算。 如排重distinct，nvl检测等。
同时从设计方面提高数据的内在质量(约束，匹配，数据类型转换)，,避免SQL过多关联检测计算，多次类型转换。
select distinct bidinproce0_.NAME as col_0_0_,
bidsetting1_.ID as col_1_0_,
bidsetting1_.ID as ID409_,
…….
bidsetting1_.POSTENDDATE as POSTENDD7_409_,
bidsetting1_.STATE as STATE409_
from keyword.BID_IN_PROCESS bidinproce0_,
keyword.BID_SETTING bidsetting1_,
keyword.ON_COR_TABLE oncortable2_
where bidinproce0_.BIDSETTINGID =bidsetting1_.ID
and bidinproce0_.CURBID = ‘1’
and bidinproce0_.VIPPURCHASE = ‘0’
updateon_busin_chance
setoperstate =’0′,
searchstate =’0′,
yrepeated =’1′,
REPEATTIMES =nvl(REPEATTIMES,0) +1,
pubdate =sysdate,
enddate =sysdate+ validdate
wherebc_id = P_id;
检查发现REPEATTIMES列上有非空约束。
updateon_busin_chance
setoperstate =’0′,
searchstate =’0′,
yrepeated =’1′,
REPEATTIMES =REPEATTIMES +1,
pubdate =sysdate,
enddate =sysdate+ validdate
wherebc_id = P_id;
————改前————————————-
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
———- ———- ———— ———- ——————–
886 267 96,174 0.0 1.5 8k49v07aa7dhw
BEGIN RepeateInfo(:1,:2,:3); END;
nvl去掉后的效果
————改后————–11-14———————–
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
———- ———- ———— ———- ——————–
396 227 108,756 0.0 1.7 8k49v07aa7dhw
BEGIN RepeateInfo(:1,:2,:3); END;
null数据检测/转换
从数据进入源头，数据底层强制约束。
如重发中的nvl检测, nvl(B.SORTTAG,:”SYS_B_0000″)
规则4.避免数据列类型和输入值类型不一致。
举例
列为字符型varchar2 。
where var_col=’36837053′ 可以使用index
where var_col= 36837053 ; 不能用上其列index
规则5: SQL语句中使用绑定变量。
为将sql分析减少到最小，在SQL语句中使用绑定变量的方法实现,。这样，所有的用户都可以使用相同的SQL语句。
规则6:理清业务逻辑，选择中间结果集最少为目标，减少SQL内部的操作运算。
select*
from(selectrownumrowno, t.*
from(selectbd_id,
bd_state,
bd_title,
…….
bd_user,
fromt_bbs_data
wherebd_parent =0
orderbybd_lastdatedesc) t
whererownum<=20)
whererowno >=1
正确改法：
bd_lastdate加数据的检索条件时间范围限制。
规则7:减少数据库的调用次数。
举例：
减少对表的查询 （ 在含有子查询的SQL语句中,要特别注意减少对表的查询.）
SELECTTAB_NAME
FROMTABLES
WHERETAB_NAME = (SELECTTAB_NAMEFROMTAB_COLUMNSWHEREVERSION =604)
AND DB_VER = (SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION =604)
正确改法：
SELECTTAB_NAME
FROMTABLES
WHERE(TAB_NAME, DB_VER) =
(SELECTTAB_NAME, DB_VER)FROMTAB_COLUMNSWHEREVERSION =604)
规则8.正确高效的使用模糊匹配查询—右百分号。
一. 用户输入的查询关键字长度不能过短。Like ‘H%’
二． 判断结果集的数量是否属合理范围，否则要求用户的再次确认或取消此查询。
三． 前端避免用户输入的特殊关键字( %xxxxx, _)。 恶意匹配， 域名查询要求。
考虑： 此需求的实现 的确要对email的信息拆分。翻转index测试
规则9.分页排序时正确的使用rownum
下面的SQL多耗费了5倍的CPU,并会随数据量的增长而成增长
原sql
SELECT A.*
FROM(SELECT X.*, rownum rn
FROM (SELECT rowid as rowidx
From List_Provider_sup_1 X
WHERE X.L3CurCatID = ‘011’
AND X.L6CurCatID = ‘011002’
ORDER BY SORTVALUE ASC) X ) A
WHEREA.rn <=20
ANDA.rn > 0
应改写如下:
select * from (
selectA.*, rownum rn
from(SELECT rowid as rowidx
From List_Provider_sup_1 X
WHERE X.L3CurCatID = ‘011’
AND X.L6CurCatID = ‘011002’
ORDER BY SORTVALUE ASC) A
whererownum < 20 ) B
where B.rn > 0
规则10.避免使用耗费资源的操作—-
UNION,MINUS,INTERSECT，DISTINCT,
会启动SQL引擎执行耗费资源的排序(SORT)功能.。
DISTINCT需要一次排序操作,而其他的至少需要执行两次排序.
例如,一个UNION查询,其中每个查询都带有GROUP BY子句,GROUP BY会触发嵌入排序(NESTED SORT) ;这样,每个查询需要执行一次排序,然后在执行UNION时,又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行.嵌入的排序的深度会大大影响查询的效率.通常,带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。
建议:学会分析SQL语句执行计划。
建议对所有的SQL语句执行EXPLAIN_PLAN，并查看输出结果，然后调整相应的语句。如：
随着数据库的版本升级，有些书介绍的一些规则在新版中已不在适用。
如：表名顺序，OR的使用等。
选择TAB2作为基础表(最好的方法)
select count(*) from tab1,tab2执行时间0.96秒
选择TAB2作为基础表(不佳的方法)
select count(*) from tab2,tab1执行时间26.09秒
SQL> set autotrace on explain;
SQL> select * from dept;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Execution Plan
———————————————
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF ‘DEPT’
免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/6514.html
