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/，如需轉載，請註明出處，否則將追究法律責任。