10个SQL优化规则（sql的优化有哪几种）

优化规则总结：

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'