一、B-Tree索引

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

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

?2.??選擇建立復(fù)合索引

?復(fù)合索引的優(yōu)點(diǎn):

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

什么情況下優(yōu)化器會(huì)用到復(fù)合索引呢?

?????? (a) 當(dāng)SQL語句的WHERE子句中有用到復(fù)合索引的領(lǐng)導(dǎo)字段時(shí),ORACLE優(yōu)化器會(huì)考慮用到復(fù)合索引來訪問.

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

?????? 能考慮用這幾個(gè)字段來建立復(fù)合索引.

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

復(fù)合索引字段排序的原則:

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

二、位圖索引

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

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

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

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

• 三、索引和NULL

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

  四、建立索引常用的規(guī)則如下

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

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

  ??????? B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？如果是，則可以建立復(fù)合索引；否則考慮單字段索引；

  ??????? C、如果復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引；

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

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

  • 頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；
  • 刪除無用的索引，避免對執(zhí)行計(jì)劃造成負(fù)面影響；

  ??????? 以上是一些普遍的建立索引時(shí)的判斷依據(jù)。一言以蔽之，索引的建立必須慎重，對每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。因?yàn)樘嗟乃饕c不充分、不正確的索引對性能都毫無益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對于插入、刪除、更新操作也會(huì)增加處理上的開銷。另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，特別是對頻繁更新的表來說，負(fù)面影響更大。

Oracle 創(chuàng)建索引的基本規(guī)則總結(jié)