一、B-Tree索引

1.??選擇索引字段的原則:

• 在WHERE子句中最頻繁使用的字段?
• ?聯接語句中的聯接字段
• 選擇高選擇性的字段(如果很少的字段擁有相同值,即有很多獨特值,則選擇性很好)
• ORACLE在UNIQUE和主鍵字段上自動建立索引
• 在選擇性很差的字段上建索引只有在這個字段的值分布非常傾斜的情況下才有益(在這種情況下,某一,兩個字段值比其它字段值少出現很多)
• 不要在很少獨特值的字段上建B-TREE索引,在這種情況下,你可以考慮在這些字段上建位圖索引.在聯機事務處理環境下,并發性非常高,索引經常被修改,所以不應該建位圖索引
• 不要在經常被修改的字段上建索引.當有UPDATE,DELETE,INSETT操作時,ORACLE除了要更新表的數據外,同時也要更新索引,而且就象更新數據一樣,或產生還原和重做條目
• 不要在有用到函數的字段上建索引,ORACLE在這種情況,優化器不會用到索引,除非你建立函數索引
• 可以考慮在外鍵字段上建索引,這些索引允許當在主表上UPDATE,DELETE操作時,不需要共享子表的鎖,這非常適用于在父表和子表上有很多并發的INSERT,UPDATE和DELETE操作的情況
• 當建立索引后,請比較一下索引后所獲得的查詢性能的提高和UPDATE,DELETE,INSERT操作性能上的損失,比較得失后,再最后決定是否需建立這個索引?

?2.??選擇建立復合索引

?復合索引的優點:

• 改善選擇性:復合索引比單個字段的索引更具選擇性?
• ?減少I/O:如果要查詢的字段剛好全部包含在復合索引的字段里,則ORACLE只須訪問索引,無須訪問表

什么情況下優化器會用到復合索引呢?

?????? (a) 當SQL語句的WHERE子句中有用到復合索引的領導字段時,ORACLE優化器會考慮用到復合索引來訪問.

?(b) 當某幾個字段在SQL語句的WHERE子句中經常通過AND操作符聯合在一起使用作為過濾謂詞,并且這幾個字段合在一起時選擇性比各自單個字段的選擇性要更好時,可

?????? 能考慮用這幾個字段來建立復合索引.

?????? (c) 當有幾個查詢語句都是查詢同樣的幾個字段值時,則可以考慮在這幾個字段上建立復合索引.

復合索引字段排序的原則:

• 確保在WHERE子句中使用到的字段是復合索引的領導字段?
• ?如果某個字段在WHERE子句中最頻繁使用,則在建立復合索引時,考慮把這個字段排在第一位(在CREATE INDEX語句中)?
• ?如果所有的字段在WHERE子句中使用頻率相同,則將最具選擇性的字段排在最前面,將最不具選擇性的字段排在最后面?
• ?如果所有的字段在WHERE子句中使用頻率相同,如果數據在物理上是按某一個字段排序的,則考慮將這個字段放在復合索引的第一位

二、位圖索引

什么情況下位圖索引能夠改善查詢的性能呢?

• WHERE子句包含多個謂詞于中低基數的字段?
• ?單個的謂詞在這些中低基數的字段上選取大量的行?
• ?已經有位圖索引創建于某些或全部的這些中低基數的字段上
• 被查詢的表包含很多行
• 可以在單一個表上建立多個位圖索引,因此,位圖索引能夠改善包含冗長WHERE子句的復雜查詢的性能,在合計查詢和星形模型的聯接查詢語句中,位圖索引也可以提供比較優良的性能

位圖索引與B-TREE索引的比較

• 位圖索引更節省存儲空間
• 位圖索引比較適用于數據倉庫環境,但不適于聯機事務處理環境.在數據倉庫環境,數據維護通常上通過批量INSERT和批量UPDATE來完成的,所以索引的維護被延遲直到DML操作結束.舉例:當你批量插入1000行數據時,這些插入的行被放置到排序緩存中(SORT BUFFER),然后批處理更新這1000個索引條目,所以,每一個位圖段在每一個DML操作中只需更新一次,即使在那個位圖段里有多行被更新
• 一個鍵值的壓縮位圖是由一個或多個位圖段所組成,每一個位圖段大約相當于半個BLOCK SIZE那么大,鎖的最小粒度是一個位圖段,在聯機事務處理環境,如果多個事務執行同時的更新(即并發的更新),使用位圖索引就會影響UPDATE,INSERT,DELETE性能了
• 一個B-TREE索引的條目只包含一個ROWID,因此,當一個索引條目被鎖定,即一行被鎖定.但是對于位圖索引, 一個索引條目潛在地有可能包含一段ROWID(即某一個范圍內的ROWID,有多個ROWID),當一個位圖索引條目被鎖定時,則這個條目包含的那一段ROWID都被鎖定,從而影響并發性.當一個位圖段內的ROWID的數量越多時,并發性就越差.雖然如此,對于BULK INSERT,UPDATE和DELETE,位圖索引的性能還是比B-TREE索引要好

• 三、索引和NULL

  ??????? NULL值在索引中是被看做一個獨特值的除非當一個索引的兩行或多行的NON-NULL值是相等的情況下.在那種情況下,行被看做是相等的,因此,唯一索引不允許行包含空值以怕被看做是相等的.但是,當所有的行都是空值時,這個規則就不適用.ORACLE并不索引所有健值都為NULL的表的行,除非是位圖索引或當主鍵字段值是NULL時

  四、建立索引常用的規則如下

  • 表的主鍵、外鍵必須有索引；
  • 數據量超過300的表應該有索引；
  • 經常與其他表進行連接的表，在連接字段上應該建立索引；
  • 經常出現在Where子句中的字段，特別是大表的字段，應該建立索引；
  • 索引應該建在選擇性高的字段上；
  • 索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引；
  • 復合索引的建立需要進行仔細分析；盡量考慮用單字段索引代替：

  ????????A、正確選擇復合索引中的主列字段，一般是選擇性較好的字段；

  ??????? B、復合索引的幾個字段是否經常同時以AND方式出現在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復合索引；否則考慮單字段索引；

  ??????? C、如果復合索引中包含的字段經常單獨出現在Where子句中，則分解為多個單字段索引；

  D、如果復合索引所包含的字段超過3個，那么仔細考慮其必要性，考慮減少復合的字段；

  ????????E、如果既有單字段索引，又有這幾個字段上的復合索引，一般可以刪除復合索引；

  • 頻繁進行數據操作的表，不要建立太多的索引；
  • 刪除無用的索引，避免對執行計劃造成負面影響；

  ??????? 以上是一些普遍的建立索引時的判斷依據。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應該經過仔細分析，要有建立的依據。因為太多的索引與不充分、不正確的索引對性能都毫無益處：在表上建立的每個索引都會增加存儲開銷，索引對于插入、刪除、更新操作也會增加處理上的開銷。另外，過多的復合索引，在有單字段索引的情況下，一般都是沒有存在價值的；相反，還會降低數據增加刪除時的性能，特別是對頻繁更新的表來說，負面影響更大。

Oracle 創建索引的基本規則總結