SQL優化（一）

優化SQL (一)

今天開發同事提出一些SQL執行較慢，我一看執行結果很少。執行時間近14秒！不能忍受，對其進行優化！

	執行時間	優化結果
優化前	Elapsed: 00:00:13.97
優化後	Elapsed: 00:00:00.07	效能提升近200倍

優化思想：

1、驅動表資料最小化原則（from最後的表為驅動表在RBO規則中，CBO中會自動選擇最小代價的表為驅動表）

2、過濾條件最大化原則（where 後過濾的資料依次越來越多）

3、建立索引走索引

背景說明：

OLTP系統，對這幾張有頻繁的insert,update。所以暫時不對系統加索引優化。所以系統只進行SQL寫法的調優。

實驗：

下面在測試伺服器上完全相同的資料及結構進行優化測試。

一、嘗試優化SQL的寫法

SQL1

select lf.*,ld.answer as tick_number,lt.answer as major_code,a.state from

(select lt.*,sf.state from skills.ns2012_state_first sf join skills.resultedit lt on lt.aclass='101' and sf.serialno=lt.serialno and lt.answer='12867' and lt.question=1) a

join skills.resultedit ld on ld.aclass=a.aclass and a.serialno=ld.serialno and ld.question=21

left join skills.resultedit lt on lt.question=20 and lt.aclass=a.aclass and a.serialno=lt.serialno

join skills.logininfo lf on lf.aclass=a.aclass and a.serialno=lf.serialno where lf.sign is null

and

lower(ld.answer) in (

select tick_number from (

select count(c.answer)as counts,(c.answer) as tick_number ,max(c.serialno)as serialno from (

select b.* from (select lt.* from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='1' and lt.aclass='101' and lt.answer='12867') a

join (select lt.aclass,lt.serialno,lt.question,lower(lt.answer) as answer from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='21' and lt.aclass='101' and lt.answer is not null ) b

on a.serialno=b.serialno and a.aclass=b.aclass

join skills.logininfo lf on lf.aclass=a.aclass and a.serialno=lf.serialno where lf.sign is null

)c group by c.answer

) where counts>=2

)

order by lower(ld.answer) desc;

優化後

SQL2

select a.indate,a.serialno,a.sip, ld.answer as tick_number,lt.answer as major_code,a.state from

(select lt.aclass,lt.serialno,lf.indate,lf.sip,sf.state from skills.ns2012_state_first sf

join skills.resultedit lt on lt.aclass=101 and sf.serialno=lt.serialno and lt.answer='12867' and lt.question=1

join skills.logininfo lf on lf.aclass=lt.aclass and lf.serialno=lt.serialno where lf.sign is null and to_char(lf.indate,'yyyy-MM-dd HH24:Mi:SS') between '2012-07-01 9:00:00' and '2012-07-30 09:00:00'

) a

join skills.resultedit ld on ld.aclass=a.aclass and a.serialno=ld.serialno and ld.question=21

left join skills.resultedit lt on lt.question=20 and lt.aclass=a.aclass and a.serialno=lt.serialno

join(

select tick_number from (

select count(c.answer)as counts,(c.answer) as tick_number ,max(c.serialno)as serialno from (

select x.aclass,x.serialno,n.answer from (select lt.aclass,lt.question,lt.serialno from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='1' and lt.aclass=101 and lt.answer='12867') x

join (select lt.aclass,lt.serialno,lt.question,lower(lt.answer) as answer from skills.ns2012_state_first sf join skills.resultedit lt on lt.serialno=sf.serialno and lt.question='21' and lt.aclass=101 and lt.answer is not null ) n

on x.serialno=n.serialno and x.aclass=n.aclass

join skills.logininfo lf on lf.aclass=n.aclass and lf.serialno=n.serialno where lf.sign is null and to_char(lf.indate,'yyyy-MM-dd HH24:Mi:SS') between '2012-07-01 9:00:00' and '2012-07-30 09:00:00'

)c group by c.answer

) where counts>=2

) m on lower(ld.answer)=m.tick_number

order by lower(ld.answer) desc;

執行計劃很多，這裡只提取我們所需要的部分，執行計劃均是2次執行後的記錄。這個是頻繁操作的！

SQL

SQL1

SQL2（優化寫法後）

time

Elapsed: 00:00:09.76

Elapsed: 00:00:00.08

2次執行的執行計劃

0 recursive calls

0 db block gets

3113107 consistent gets

0 physical reads

0 redo size

19011 bytes sent via SQL*Net to client

689 bytes received via SQL*Net from client

22 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

315 rows processed

0 recursive calls

0 db block gets

12714 consistent gets

0 physical reads

0 redo size

16918 bytes sent via SQL*Net to client

741 bytes received via SQL*Net from client

22 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

315 rows processed

二、新增索引

針對aclass裡面出現最多的列，新增了索引進行嘗試。並尋找重複最少的列進行加索引。

SQL> select index_name,table_name,status,ITYP_NAME from user_indexes where table_owner='SKILLS';

INDEX_NAME TABLE_NAME STATUS ITYP_NAME

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

PK_RESULTEDIT RESULTEDIT VALID

INDEX_2012 NS2012_STATE_FIRST VALID

LOG_INDEX LOGININFO VALID

紅色部分為後來新增的索引

新增索引後，然後執行SQL1和SQL2，看看執行效果，從執行時間上看我們沒有優化。

這個也說明了根據執行計劃來建立索引進行優化效果明顯，但是對於一個OLTP系統來說，以後用來維護索引的資源也是必須要考慮的，所以最後還是採用了優化寫法，沒有對系統加索引。

SQL

SQL1

SQL2（優化寫法後）

time

Elapsed: 00:00:00.05

Elapsed: 00:00:00.09

2次執行的執行計劃

0 recursive calls

0 db block gets

9154 consistent gets

0 physical reads

0 redo size

18957 bytes sent via SQL*Net to client

689 bytes received via SQL*Net from client

22 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

315 rows processed

0 recursive calls

0 db block gets

20655 consistent gets

0 physical reads

0 redo size

16914 bytes sent via SQL*Net to client

741 bytes received via SQL*Net from client

22 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

315 rows processed

總結SQL優化：

1、優化SQL先優化寫法及邏輯思維。（儘量減少不必要訪問表資料和排序！根據filter，考慮過濾條件）

2、 新增相應的索引（根據access，考慮加索引）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-739466/，如需轉載，請註明出處，否則將追究法律責任。

SQL優化（一）

相關文章