關於Oracle btree索引初步認識

關於Oracle btree索引初步認識

今天研究下Oracle的btree索引，通過這篇文章你會瞭解到，Oracle btree索引都有哪幾種類型、Oracle btree索引的實現原理，Oracle通過btree索引檢索資料的過程、以及b*tree索引的限制，並且Oracle和mysql的btree索引的區別。

一：Oracle中 btree索引的子類型:

b*tree索引是Oracle乃至大部分其他資料庫中最常用的索引，b*tree的構造類似於二叉樹，但是這裡的“B”不代表二叉（binary），而代表平衡（balanced),b*tree索引有以下子類型：

1）索引組織表（index organized table): 索引組織表以B*樹結構儲存，我們知道Oracle預設的表是是堆表，堆表是以一種無組織的方式儲存的（只要有可用的空間，就可以放資料），而IOT與之不同，IOT中的資料按著主鍵的順序儲存和排序的，對於應用來說，IOT表現得和常規的堆表並無區別，需要使用sql來正確的來訪問IOT, IOT對資訊擷取、空間系統和OLAP應用最為有用，簡單的概述起來：索引組織表----索引就是資料，資料就是索引，因為資料就是按著B*樹結構儲存的。

2）b*tree聚簇索引（B*tree cluster index):基於聚簇鍵（如 age=27），在傳統的btree索引中，鍵都指向一行，而B*樹聚簇不同，一個聚簇鍵會指向一個塊，其中包含與這個聚簇鍵相關的多行，

3)降序索引：允許資料在索引結構中按“從大到小”的順序（降序）排序，而不是“從小到大的順序（升序）排序，當你查詢資料的時候，最後排序oder by A desc,B asc的時候，建立降序索引就能避免做昂貴的排序（sort order by ）操作，如下語句建立：

SQL>create index idex_name on table_name(A desc,B asc);

4)反向鍵索引（reverse key index):這也是 btree索引，只不過鍵的位元組會“反轉”，利用反向鍵索引，如果索引中填充的是遞增的值，索引條目在索引中可以得到更均勻的分布；主要是解決“右側”索引葉子塊的競爭，比如在一個Oracle RAC的環境中，某些列用一個序列值或者時間戳記填充，這些列上建立索引就屬於“右側”索引，也就是資料分布的相對比較集中。使用反向索引最大的優點莫過於降低索引葉子塊的爭用，減少索引熱點塊，提高系統效能。

1.反向索引應用場合

1）發現索引葉塊成為熱點塊時使用

通常，使用資料時（常見於批量插入操作）都比較集中在一個連續的資料範圍內，那麼在使用正常的索引時就很容易發生索引葉子塊過熱的現象，嚴重時將會導致系統效能下降。

2）在RAC環境中使用

當RAC環境中幾個節點訪問資料的特點是集中和密集，索引熱點塊發生的幾率就會很高。如果系統對範圍檢索要求不是很高的情況下可以考慮使用反向索引技術來提高系統的效能。因此該技術多見於RAC環境，它可以顯著的降低索引塊的爭用。

2.使用反向索引的缺點

由於反向索引結構自身的特點，如果系統中經常使用範圍掃描進行讀取資料的話（例如在where子句中使用“between and”語句或比較子“>”“<”等），那麼反向索引將不適用，因為此時會出現大量的全表掃描的現象，反而會降低系統的效能。

二：Oracle中BTree索引的實現原理：

一個經典的BTree索引的結構如：

每個節點佔用一個磁碟塊的磁碟空間，一個節點上有n個升序排序的關鍵字和（n+1)個指向子樹根節點的指標（中關鍵字為51,101,151.。。。。，然後0到50 對應一個指標，51到100對應一個指標），這個指標儲存的是子節點所在磁碟塊的地址（注意這裡的n是建立索引的時候，根據資料量計算出來的，如果資料量太大了，三層的可能就滿足不了，就需要四層的B+tree）,然後n個關鍵字劃分成（n+1)個範圍域，然後每個範圍域對應一個指標，來指向子節點，子節點又從新根據關鍵字再次劃分，然後指標指向葉子節點，並且所有的葉子節點都在樹的同一層上，這說明所有的從索引根節點到葉子節點的遍曆都會訪問同樣數目的塊，也就是說會執行同樣數目的I/O,換言之索引是高度平衡的，

就是 0...50對應一個指標，指向一個子節點；51...100對應一個指標，指向另一個子節點，然後子節點又根據關鍵字劃分地區，並由指標指向葉子結點，值得注意的是Oracle B*樹索引存資料的是葉子節點（或者叫葉子塊）；存的是索引索引值（或者叫索引的列值)和一個rowid(指向所索引的行的一個指標或者說叫物理位置），然後如所示，葉子節點之間有雙向鏈表，就是為了提高索引範圍掃描的效率，因為索引值的列值是有序的，找到了起始值後，直接就可以有序的去相鄰中找到下一個值，例如 where id between 10 and 20 ,Oracle 發現第一個最小索引值大於或等於10的索引葉子塊，然後水平地遍曆葉子節點鏈表，直到最後一個大於20的值；

需要注意的是：B*索引中不存在非唯一限制，也就是說非唯一列上也可以建立B*索引，但是在一個非唯一索引中，Oracle會把rowid作為一個額外的列（有一個長度位元組）追加到鍵上，使得鍵唯一，例如有一個create index index_name on table( x ,y)索引，從概念上說，他就是create unique index index_name on table( x ,y,rowid).在一個唯一索引中，根據你定義的唯一性，Oracle 不會再向索引鍵增加rowid,在非唯一索引中，你會發現，資料會首先按索引索引值排序，然後按rowid升序排序，而在唯一索引中，資料只按著索引索引值排序；

三：使用B*樹索引檢索資料的過程。

針對B+tree索引的原理（修改自網路）：

然後針對類比下 where id=29的具體過程：。

首先根據根節點找到磁碟塊1，讀入記憶體。【磁碟I/O操作第1次】

比較關鍵字29在區間（17,35），找到磁碟塊1的指標P2。

根據P2指標找到磁碟塊3，讀入記憶體。【磁碟I/O操作第2次】

比較關鍵字29在區間（26,30），找到磁碟塊3的指標P2。

根據P2指標找到磁碟塊8，讀入記憶體。【磁碟I/O操作第3次】

在磁碟塊8中的關鍵字列表中找到關鍵字29。

分析上面過程，發現需要3次磁碟I/O操作，和3次記憶體尋找操作。由於記憶體中的關鍵字是一個有序表結構，可以利用二分法尋找提高效率。而3次磁碟I/O操作是影響整個B-Tree尋找效率的決定因素。

四：Oracleb*tree索引的限制

1）在索引列上使用函數。如SUBSTR,DECODE,INSTR等，對索引列進行運算.需要建立函數索引就可以解決了。

例如：表dept，有col_1,col_2,現在對col_1做upper函數索引 如下：

CREATE INDEX index_name ON dept(upper(col_1));

函數索引是基於代價的最佳化方式-CBO，（在Oracle8及以後的版本,Oracle強列推薦用CBO的方式，而非RBO）,所以表必須經過analyze才可以使用，或者使用hints才可以 ；

2）建立的表還沒來得及產生統計資訊，分析一下就好了，我們知道Oracle最佳化器是基於統計資訊來判斷執行計畫的，如果統計資訊不準確，那麼Oracle可能會做出不走索引的執行計畫。

3）Oracle最佳化器cbo是基於cost的成本分析，訪問的表過小，使用全表掃描的消耗小於使用索引。

4）使用<>、not in 、not exist，對於這三種情況中大多數情況下認為結果集很大，一般大於5%-15%就不走索引而走全表掃描（FTS)。

5) like "%_" 百分比符號在前。

6) 單獨引用複合索引裡非第一位置的索引列,Oracle和mysql一樣，btree索引都是最左匹配原則，當你建立複合式索引（A，B，C）相當於建立了(A)、 (A,B)、（A，B，C)三個索引；

7）字元型欄位為數字時在where條件裡不添加引號，這裡Oracle內部使用函數做隱士轉換，所以可以歸結為第一類，使用函數導致索引失效，值得注意的是：VARCHAR2->NUMBER的隱式轉換，可以走索引；NUMBER->VARCHAR2的隱式轉換，就導致索引失效了。（VARCHAR2->NUMBER不會讓索引失效，可以理解成轉換為where id = to_number('123')。NUMBER->VARCHAR2會讓索引失效，我猜測是轉換為where to_number(name) = 123）

8）當變數採用的是times變數，而表的欄位採用的是date變數時.或相反情況。

9)索引失效（INVALID)，可以考慮重建索引，alter　index　index_name rebuild online;。

10)B-tree索引 is null不會走,is not null會走;

五：Oracle和mysql的btree索引的區別

其實Oracle和mysql的btree索引結構和原理很相似，只是Oracle葉子節點儲存的是索引值+rowid，mysql的索引葉子結點儲存的內容因儲存引擎不同而不同，還有主鍵索引和二級索引之分如下：

Oracle葉子節點儲存的是索引值+rowid

MyISAM引擎中leaf node儲存的內容:

主鍵索引 ：僅僅儲存行指標；

二級索引：僅僅是行指標；

InnoDB引擎中leaf node儲存的內容

主鍵索引 ：叢集索引儲存完整的資料（整行資料）（類似於Oracle的索引組織表）

二級索引：儲存索引列值+主鍵資訊

總結：

索引能提高檢索資料的效率，但是索引的建立必須謹慎，對每個索引的必要性都應該經過仔細分析，要有建立的依據。因為太多的索引與不充分、不正確的索引對效能都毫無益處：在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來說，負面影響更大。