Oracle SQL最佳化總結

maohaiqing0304發表於2015-06-04



標題:Oracle SQL最佳化總結 

作者:lōττéry©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]



***  SQL 最佳化也做了一段時間了,特此總結本人在工作中遇到的各個點,見笑了~


一、縮小範圍    減少額外io/cpu/物理/邏輯等效能消耗

①、縮小範圍(全掃能否加過濾條件最好是索引條件’)

②、分割槽表儘量加分割槽條件

執行計劃區別:(PARTITION RANGE ALL分割槽全掃)  (PARTITION RANGE SINGLE分割槽範圍)”

③、是否存在沒意義的關聯條件
例如1A.id in (Select id from a1) and A.id in (Select id from a1 where id=1)

      此時可以註釋掉①部分避免沒必要的關聯和查詢。

例如2:業務功能類似,功能1 需要範圍abcde5張表,功能2範圍abc3張表;

排除的/欄位都等需求都相同,開發可能為了省事改寫,直接將功能 2 sql直接使用功能1 sql

最終最外層將不需要欄位篩選掉。

④、left join 確認能否改成join

left join 關聯方式 存在選錯驅動表被驅動表,某些時候可能還會存在量小應該走索引時不走索引。

且產生大量不必要的中間結果,產生大量物理/邏輯讀;Join可以過濾資料(過濾條件)

二、執行計劃

①、小表/小分割槽 變大表/大分割槽,不統計引起的 執行計劃錯誤問題;

②、特殊情況可加hint driving_sitefullno_indexuse_hash ..)語句加hint需要和dba協商

資料庫統計資訊沒問題時,大多數沒特殊情況都是不需要加hint指定執行計劃的;

③、善於用繫結變數;

④、使用標量子查詢要量力而行;

⑤、能傳入定值部分儘量傳入定值

若某欄位傳一定值,凡是用到這個傳定值欄位的部分都儘量直接傳值 (例如:關聯條件,group by)

oracle在生成執行計劃時要透過關聯條件等衡量2個表哪個驅動被驅動表,若直接寫定值,

資料庫會更準確的選擇最好的執行計劃去資料庫中讀取資料,group by 排序部分為了減少資源消耗。

執行計劃出現如下情況需要嚴重注意的:

①、CARTESIAN,笛卡爾乘積,需要確認是否有落下的表沒有和其他表做關聯;

②、filter,驅動表量越大越會引起效能問題;

③、Nest loop 驅動表或被驅動表全掃時
最糟糕的情況:驅動表和被驅動表都是全掃,且隨著量的增加後續會存在效能問題..

大表做迴圈--&gt加索引

選擇性很差的索引迴圈--&gt刪索引

--&gt2大表做nest loop迴圈時,需要衡量使用頻繁度且儘量和dba協商,來適當加索引;

--&gtnest loop是選擇性很差(distinct key 很小)的索引(且執行時間較慢)時,需要dba分析該索引;

若刪除索引,建議做個統計確認這幾個月是否真的沒人用再進行刪除;

參考部落格:http://blog.itpub.net/28602568/viewspace-1362044/

三、經驗點

①、update 和merge的選擇參考 Oracle 如何選擇update、merge 

②、去重 distinct、group by 的選擇;

子查詢裡包含group by 可能存在子查詢外層資料量很小傳不到子查詢內層的情況;參考Oracle 針對子查詢裡有group by 表全掃的最佳化 

③、不要使用(id = 1 or in 子查詢 )形式

原因:執行計劃會選擇filter,驅動表過濾後條目很多的話,被驅動表會產生熱點塊;

部落格參考:Oracle 'or exists/in'結合使用引起的filter執行計劃 的最佳化

④、exists 和 in 的選擇(避免filter)

exists 比 in 產生filter執行計劃機率大,所以當sql慢,且按F5發現執行計劃中有filter 可以試著改成in
若出現
in 子查詢裡表全掃或者還存在慢的情況,可以考慮將in (子查詢) A與外層Bjoin關聯,
若還存在問題,確認是否存在本文提到的情況,針對整體衡量去最佳化。
補充:[not] exists 子查詢裡不存在與外層關聯的條件,最終結果有無資料的現象   
參考部落格:http://blog.itpub.net/28602568/viewspace-1666675/

⑤、分頁:rownumrow_number的選擇

部落格參考:Oracle rownum 分頁引起的效率問題及最佳化思路

⑥、適當選擇使用rowid...

部落格參考:Oracle 透過rowid秒優SQL

⑦、儘量不寫is null 等會引起不走索引的條件,“索引不記錄空值

若欄位有nvl函式索引可以寫NVL(欄位, 0)=0來當做is null判斷,可走函式索引

⑧、程式碼If count >0.. 做判斷時,若rownum=1能查出資料就滿足count>0
就不必全部取出再判斷count>0...所以這樣的判斷可以統一加 rownum=1
全部做更改後dba需要跟蹤sql確認是否有存在rownum缺點引起的問題
Oracle rownum 分頁引起的效率問題及最佳化思路
mysql 使用limit 1oracle 使用rownum=1 來取資料中一行。

⑨、 合理使用臨時表解決大量下載功能

 Oracle SQL最佳化了解業務多重要 http://blog.itpub.net/28602568/viewspace-1685600/

⑩、 臨時用的表: 臨時屬性表?普通表?

臨時表:不可統計,create/insert大量後,再呼叫臨時表可能存在選擇索引情況引起效能問題可能;

普通表:create as select ... 不統計,再呼叫時,資料庫會使用動態取樣去產生正確的執行計劃;若是insert需要及時統計,否則可能會存在和臨時表的問題。


  【源於本人筆記】 若有書寫錯誤,表達錯誤,請指正...



此條目發表在   SQL、SQL最佳化篇  分類目錄。將固定連線加入收藏夾。


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

相關文章