馮祖洪轉 oracle sql最佳化總結

 

這兩天閑著沒事情，項目也快上線了，就把以前在項目裡寫的SQL語句都看了一遍。

參照往年最佳化SQL的經驗和網上對SQL語句最佳化的方法總結，加上oracle這麼強大的SQL分析工具，把寫的不是很優的SQL拿來最佳化了。

 

總結如下：

一、固定的SQL書寫習慣，相同的查詢盡量保持相同

二、預存程序的效率較高。
三、編寫與其格式一致的語句，包括字母的大小寫、標點符號、換行的位置等都要一致 。

　　　　sql語句用大寫的；因為oracle總是先解析sql語句，把小寫字母轉換成大寫的再執行 .
四、寫查詢語句的時候，盡量不要出現 ' * '，把需要的列查詢出來就可以。

1.盡量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替、not in 用not EXISTS代替.

特別注意：IN適合於外表大而內表小的情況；EXISTS適合於外表小而內表大的情況。

不是說所有的in或者not in 都可以用EXISTS和not EXISTS代替.

用IN來替換OR 
這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．　
低效:
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效
SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);

用UNION替換OR (適用於索引列)

通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描.

注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低.

 

在下面的例子中, LOC_ID 和REGION上都建有索引.
高效:
SELECT LOC_ID , LOC_DESC , REGION  FROM LOCATION WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.

2.不用“<>”或者“!=”操作符。對不等於操作符的處理會造成全表掃描，可以用“<” or “>”代替 。
例如：
a<>0 改為 a>0 or a<0 
a<>’’ 改為 a>’’

3.Where子句中出現IS NULL或者IS NOT NULL時，Oracle會停止使用索引而執行全表掃描。

可以考慮在設計表時，對索引列設定為NOT NULL。這樣就可以用其他動作來取代判斷NULL的操作

用其它相同功能的操作運算代替，如：
      1、a is not null 改為 a>0 或a>’’等。
      2、不允許欄位為空白，而用一個預設值代替空值，如業擴申請中狀態欄位不允許為空白，預設為申請。
      3、 建立位元影像索引（有分區的表不能建，位元影像索引比較難控制，如欄位值太多索引會使效能下降，多人更新操作會增加資料區塊鎖的現象）
如果某列存在空值，即使對該列建索引也不會提高效能。任何在WHERE子句中使用is null或is not null的語句最佳化器是不允許使用索引的。

4、當萬用字元“%”或者“_”作為查詢字串的第一個字元時，索引不會被使用 .
例如： select u.name from user u where u.name like '%huang%'
應該改為： select u.name from user u where u.name like 'huang%'

5、對於有串連的列“||”，最後一個串連列索引會無效。盡量避免串連，可以分開串連或者使用不作用在列上的函數替代。

6、如果索引不是基於函數的，那麼當在Where子句中對索引列使用函數時，索引不再起作用。
例如：creadate有索引
查詢：select a.paicUmNum from ps_NumBer a where to_char(a.creadate,'yyyy-MM-dd')>'2010-10-12'不使用索引
select a.paicUmNum from ps_NumBer a where a.creadate>to_date('2010-10-12','yyyy-MM-dd')使用索引

7、Where子句中避免在索引列上使用計算，否則將導致索引失效而進行全表掃描。
例如：select s.numer from student s where s.age-20 = 50;
應該改為：select s.numer from student s where s.age = 30或者(50-20);

8、對資料類型不同的列進行比較時，會使索引失效。
例如：age為number類型  ，score 為varcher類型
select s.* from student s where s.age>score 在age和score 上的索引將會失效

避免改變索引列的類型.:
當比較不同資料類型的資料時, ORACLE自動對列進行簡單的類型轉換.
假設 EMPNO是一個數實值型別的索引列.
　　　　SELECT …  FROM EMP  WHERE  EMPNO = ‘123'
實際上,經過ORACLE類型轉換, 語句轉化為:
　　　　SELECT …  FROM EMP  WHERE  EMPNO = TO_NUMBER(‘123')
幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.

現在,假設EMP_TYPE是一個字元類型的索引列.
　　　　SELECT …  FROM EMP  WHERE EMP_TYPE = 123
這個語句被ORACLE轉換為:
　　　　SELECT …  FROM EMP  WHERE TO_NUMBER(EMP_TYPE)=123
因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的類型轉換, 最好把類型轉換用顯式表現出來.

注意當字元和數值比較時, ORACLE會優先轉換數實值型別到字元類型

9、採用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合并後就返回。UNION，在運行時先取出兩個表的結果，再用排序空間進行排序重複資料刪除的記錄，最後返回結果集，如果表資料量大的話可能會導致用磁碟進行排序。

10.總是使用索引的第一個列：
如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,最佳化器才會選擇使用該索引. 這也是一條簡單而重要的規則，當僅引用索引的第二個列時,最佳化器使用了全表掃描而忽略了索引

11、在含有子查詢的SQL語句中,要特別注意減少對錶的查詢.

例子：
SELECT  EMP_NO FROM EMP WHERE (GROUP,NAME) = ( SELECT
COLUMN1,COLUMN2 FROM  TEST WHERE  TEST_ID = 604)

以上對sql的最佳化建議，都是通過本人親身經曆和點點滴滴總結出來的。如有不對的地方請指教。

　　　　　　　　　　　　　　　　　　　　順便收藏了一些好東西，拿出來分享下
　　　　
1、最高效的重複資料刪除記錄方法 ( 因為使用了ROWID)

例子：
DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID)
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

2、參數用法很好

<!-- 查詢已存在的審批實體生產架構的流水號 -->
<select id="searchAppEntityDeptId" parameterClass="java.util.Map"
resultClass="appEntityDeptDTO">
<![CDATA[
     select t.entity_id appTempletId
       from oms_entity_dept t
]]>
<isNotEmpty prepend="where" property="userList">
<iterate property="userList" conjunction="OR">
　　　　(t.dept_no = #userList[].deptNo# and t.entity_no = #userList[].entityNo#)
</iterate>
</isNotEmpty>
</select>

3、ibatis調用proc
<!-- 調用預存程序，寫人員生產資訊表 -->
<parameterMap id="swapParameters" class="map">
<parameter property="paicUniqueDeptid" jdbcType="VARCHAR"  javaType="java.lang.String" mode="IN" />
<parameter property="paicUmNum" jdbcType="VARCHAR"  javaType="java.lang.String" mode="IN" />
</parameterMap>
　　　　<procedure id="swapEmailAddresses" parameterMap="swapParameters">
　　　　{call oms_sync_ps_pkg.proc_oms_prd_emp_io(?, ?)}
</procedure>

另附索引的一些概念（轉載）

一、索引的概念
        索引就是加快檢索表中資料的方法。資料庫的索引類似於書籍的索引。在書籍中，索引允許使用者不必翻閱完整個書就能迅速地找到所需要的資訊。在資料庫中，索引也允許資料庫程式迅速地找到表中的資料，而不必掃描整個資料庫。

二、索引的特點
    1.索引可以加快資料庫的檢索速度
    2.索引降低了資料庫插入、修改、刪除等維護任務的速度
    3.索引建立在表上，不能建立在視圖上
    4.索引既可以直接建立，也可以間接建立
    5.可以在最佳化隱藏中，使用索引
    6.使用查詢處理器執行SQL語句，在一個表上，一次只能使用一個索引

三、索引的優點
    1.建立唯一性索引，保證資料庫表中每一行資料的唯一性
    2.大大加快資料的檢索速度，這也是建立索引的最主要的原因
    3.加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。
    4.在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
    5.通過使用索引，可以在查詢的過程中使用最佳化隱藏器，提高系統的效能。

四、索引的缺點
    1.建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加
    2.索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大
    3.當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，降低了資料的維護速度

五、索引分類
    1.直接建立索引和間接建立索引
    直接建立索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
    間接建立索引：定義主鍵約束或者唯一性鍵約束，可以間接建立索引
    2.普通索引和唯一性索引
    普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
    唯一性索引：保證在索引列中的全部資料是唯一的，對聚簇索引和非聚簇索引都可以使用
    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
    3.單個索引和複合索引
    單個索引：即非複合索引
    複合索引：又叫複合式索引，在索引建立語句中同時包含多個欄位名，最多16個欄位
    CREATE INDEX name_index ON username(firstname,lastname)
    4.聚簇索引和非聚簇索引(叢集索引，群集索引)
   聚簇索引：物理索引，與基表的物理順序相同，資料值的順序總是按照順序排列
    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
    ALLOW_DUP_ROW(允許有重複記錄的聚簇索引)
   非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

六、索引的使用
        應該在這些列上建立索引，例如：
1在經常需要搜尋的列上，可以加快搜尋的速度；
2在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
3在經常用在串連的列上，這些列主要是一些外鍵，可以加快串連的速度；
4在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
5在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
6在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

不應該建立索引的的這些列具有下列特點：
第一，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。

第二，對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。

第三，對於那些定義為text, image和bit資料類型的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。

第四，當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當修改效能遠遠大於檢索效能時，不應該建立索引。