理解索引和聚簇——效能調整手冊和參考

這一章首先介紹瞭如何選擇、建立和維護索引，然後介紹了這種特殊型別的索引和它們的使用範圍，最後介紹了索引聚簇和雜湊聚簇，並說明了它們的適用條件。

 

建立索引可以提高查詢的效能，但是由於必須同時維護資料和索引，會增加DML操作的代價。因此，應該有選擇性的建立索引。而對於被使用的索引應該及時清除。透過EXPLAIN PLAN可以檢視一條SQL語句是否使用了索引，採用這種方法可以幫助選擇並建立合適的索引。對於資料庫中已經存在的索引，可以使用ALTER INDEX indexname MONITORING USAGE，經過一段時間的監視，如果一些索引一直沒有使用，則可以從系統中清除掉。（注意控制好合適的監視週期）

 

在選擇索引的列時應該考慮：

在WHERE語句中經常使用的列；

經常被用於連線到其他表的列；

B樹索引的索引列應該具有較高的選擇性；

不要在只包含少量不同值的列或表示式上建立B樹索引；

不要索引經常修改的列；

如果一些列出現在WHERE語句中，但都是在函式或運算中使用，這些列不適合建立B樹索引；

為了提高併發訪問，外來鍵列應該建立索引；

選擇一個索引列時，應該從查詢的效能收穫和對於INSERT、UPDATE和DELETE操作的影響已經佔用空間兩個方面來進行權衡。

 

複合索引具有以下優點：

提高選擇性：和單列索引相比，複合索引的選擇性更高。

降低I/O：如果Oracle需要訪問的所有列都存在於複合索引中，則可以根據索引直接返回結果，避免表掃描。

複合索引應該選擇的列：

經常在WHERE語句中使用AND操作連線在一起使用，且組合起來比單個列具有更高的選擇性的列。

如果多個查詢包含相同列構成的結果集，可以考慮將這些列組合起來建立複合索引。

複合索引中確定列的順序：

在WHERE語句中使用的列應該放到前面；

頻繁出現在WHERE語句中的列應該放到複合索引的最前面，保證指定這些列的查詢可以使用索引；

如果所有列的使用機率相差不多，把選擇性高的列放到前面；

如果所有列的使用機率相差不多，而表的資料是按照某個列的鍵值順序進行物理儲存的，則將這一列放到最前面。

和一般索引相比，複合索引對DML的影響更大，而且佔用磁碟空間也更多，因此應該慎重使用。

 

Oracle推薦按照如下方式建表、轉載資料、建立索引並建立約束：

一：建立表和約束。NOT NULL約束可以不命名，並建立成ENABLE、VALIDATE狀態，其他約束（PRIMARY KEY, FOREIGN KEY，UNIQUE和CHECK）都應該命名，並建立為DISABLE狀態。

二：匯入資料。

三：建立索引的索引。

後建索引比先建索引然後插入資料效率高。

四：將約束置為ENABLE、NOVALIDATE狀態，先主鍵後外來鍵。

這個過程會鎖表，但是持續時間很短，操作完成後就可以保證後續插入資料的正確性了。

五：執行使用者訪問的修改表。

由於約束已經ENABLE，可以供使用者使用了。

六：對每個約束進行VALIDATE操作，先主鍵後外來鍵。

這一步花費時間較多，但是不會鎖表，不影響使用者訪問，而且這一步可以並行執行。

 

下面介紹幾種特殊型別的索引：

函式索引：將函式的表示式建立在索引中，使得對列的函式操作也可以使用索引。只有使用CBO才能利用函式索引。

索引組織表：其實是表的一種，表中的資料按照主鍵的順序儲存。適合對主鍵列的唯一掃描和範圍掃描。如果包含較多DML，則不適合建立為索引組織表。

BITMAP索引：建立在選擇性低的列上，對於多個列上的BITMAP索引的AND和OR操作具有很高的執行效率。BITMAP索引佔用空間小，建立速度快，對批次操作只處理一次，因此特別合適在資料倉儲中使用。但是BITMAP索引的鎖粒度較大，大量的併發DML操作會極大的影響效能，因此不適合OLTP系統。BITMAP索引也只會被CBO使用。

BITMAP連線索引：將表連線操作透過BITMAP索引儲存起來，當查詢符合條件，則只需要訪問BITMAP就可以得到正確的結構。特別適合於資料倉儲的星形模式。

Domain索引：使用者可以根據不同的使用情況自定製索引。

 

索引聚簇：將一張或多張表根據共同列（連線列）無論的儲存在以前，這些儲存在一起的表具有相同的物理屬性。

索引聚簇把經常在一起訪問的表儲存在一起，這樣查詢的時候不必在執行連線操作，這些表在物理上已經連線在一起了。

索引聚簇適合對聚簇的多個表透過連線列進行查詢，但是不適合對其中某個表進行全表掃描，因為這樣將掃描更多的BLOCK。而且對索引聚簇進行DML的開銷較大，因此包含較多DML操作的表，不適合進行索引聚簇。

 

雜湊聚簇：透過雜湊函式直接定位資料的實體地址。對於HASH列的定製訪問，只需要一個I/O即可讀出資料，效率很高。但是對HASH列不支援範圍掃描，而且需要預先確定表的大小才能保證HASH聚簇表具有很高的效率，一旦表的大小超過了預期的估計，對HASH聚簇表的訪問效率就會下降。

適合可以估計出大小的表，並且絕大多數查詢都是透過HASH列進行等值查詢。全表掃描效率較低，由於表在大小在建立時就確定了，因此當表中資料沒有填滿時，很多BLOCK沒有填滿，全表掃描要掃描很多空閒空間，效率比較低。HASH聚簇同樣不適合大量DML操作，因為修改可能導致儲存空間的位置發生變化。