Oracle中B-TREE索引的深入理解(轉載)

標籤：blog   http   使用   os   io   strong   檔案   資料   

索引概述

索引與表一樣，也屬於段（segment）的一種。裡面存放了使用者的資料，跟表一樣需要佔用磁碟空間。只不過，在索引裡的資料存放形式與表裡的資料存放形式非常的不一樣。在理解索引時，可以想象一本書，其中書的內容就相當於表裡的資料，而書前面的目錄就相當於該表的索引。同時，通常情況下，索引所佔用的磁碟空間要比表要小的多，其主要作用是為了加快對資料的搜尋速度，也可以用來保證資料的唯一性。
但是，索引作為一種可選的資料結構，你可以選擇為某個表裡的建立索引，也可以不建立。這是因為一旦建立了索引，就意味著oracle對錶進行DML（包括INSERT、UPDATE、DELETE）時，必須處理額外的工作量（也就是對索引結構的維護）以及儲存方面的開銷。所以建立索引時，需要考慮建立索引所帶來的查詢效能方面的提高，與引起的額外的開銷相比，是否值得。
從物理上說，索引通常可以分為：分區和非分區索引、常規B樹索引、位元影像（bitmap）索引、翻轉（reverse）索引等。其中，B樹索引屬於最常見的索引，由於我們的這篇文章主要就是對B樹索引所做的探討，因此下面只要說到索引，都是指B樹索引。
B樹索引內部結構 
B樹索引是一個典型的樹結構，其包含的組件主要是：
1) 葉子節點（Leaf node）：資料行的索引值（key value）、索引值對應資料行的 ROWID。
2) 分支節點（Branch node）：最小的索引值首碼（minimum key prefix），用於在（本塊的）兩個索引值之間做出分支選擇，指向包含所尋找索引值的子塊（child block）的指標（）所有的 索引值-ROWID 對（key and ROWID pair）都與其左右的兄弟節點（sibling）向連結（link），並按照（key，ROWID）的順序排序
3) 根節點（Root node）：一個B樹索引只有一個根節點，它實際就是位於樹的最頂端的分支節點。

可以用一來描述B樹索引的結構。其中，B表示分支節點，而L表示葉子節點。

           

對於分支節點塊（包括根節點塊）來說，其所包含的索引條目都是按照順序排列的（預設是升序排列，也可以在建立索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）都具有兩個欄位。第一個欄位表示當前該分支節點塊下面所連結的索引塊中所包含的最小索引值；第二個欄位為四個位元組，表示所連結的索引塊的地址，該地址指向下面一個索引塊。在一個分支節點塊中所能容納的記錄行數由資料區塊大小以及索引索引值的長度決定。比如從一可以看到，對於根節點塊來說，包含三條記錄，分別為（0 B1）、（500 B2）、（1000 B3），它們指向三個分支節點塊。其中的0、500和1000分別表示這三個分支節點塊所連結的索引值的最小值。而B1、B2和B3則表示所指向的三個分支節點塊的地址。
對於葉子節點塊來說，其所包含的索引條目與分支節點一樣，都是按照順序排列的（預設是升序排列，也可以在建立索引時指定為降序排列）。每個索引條目（也可以叫做每條記錄）也具有兩個欄位。第一個欄位表示索引的索引值，對於單列索引來說是一個值；而對於多列索引來說則是多個值組合在一起的。第二個欄位表示索引值所對應的記錄行的ROWID，該ROWID是記錄行在表裡的物理地址。如果索引是建立在非分區表上或者索引是分區表上的本地索引的話，則該ROWID佔用6個位元組；如果索引是建立在分區表上的全域索引的話，則該ROWID佔用10個位元組。

知道這些資訊以後，我們可以舉個例子來說明如何估算每個索引能夠包含多少條目，以及對於表來說，所產生的索引大約多大。對於每個索引塊來說，預設的PCTFREE為10％，也就是說最多隻能使用其中的90％。同時9i以後，這90％中也不可能用盡，只能使用其中的87％左右。也就是說，8KB的資料區塊中能夠實際用來存放索引資料的空間大約為6488（8192×90％×88％）個位元組。
假設我們有一個非分區表，表名為warecountd，其資料行數為130萬行。該表中有一個列，列名為goodid，其類型為char（8），那麼也就是說該goodid的長度為固定值：8。同時在該列上建立了一個B樹索引。
在葉子節點中，每個索引條目都會在資料區塊中佔一行空間。每一行用2到3個位元組作為行頭，行頭用來存放標記以及鎖定類型等資訊。同時，在第一個表示索引的索引值的欄位中，每一個索引列都有1個位元組表示資料長度，後面則是該列具體的值。那麼對於本例來說，在葉子節點中的一行所包含的資料大致如二所示：

                      

從可以看到，在本例的葉子節點中，一個索引條目占 18 個位元組。同時我們知道 8KB 的資料區塊中真正可以用來存放索引條目的空間為 6488 位元組，那麼在本例中，一個資料區塊中大約可以放 360 （ 6488/18 ）個索引條目。而對於我們表中的130 萬條記錄來說，則需要大約 3611 （ 1300000/360 ）個葉子節點塊。

而對於分支節點裡的一個條目（一行）來說，由於它只需儲存所連結的其他索引塊的地址即可，而不需要儲存具體的資料行在哪裡，因此它所佔用的空間要比葉子節點要少。分支節點的一行中所存放的所連結的最小索引值所需空間與上面所描述的葉子節點相同；而存放的索引塊的地址只需要 4 個位元組，比葉子節點中所存放的 ROWID 少了 2 個位元組，少的這 2 個位元組也就是 ROWID 中用來描述在資料區塊中的行號所需的空間。因此，本例中在分支節點中的一行所包含的資料大致如三所示：

                      

從可以看到，在本例的分支節點中，一個索引條目占 16 個位元組。根據上面葉子節點相同的方式，我們可以知道一個分支索引塊可以存放大約 405 （ 6488/16 ）個索引條目。而對於我們所需要的 3611 個葉子節點來說，則總共需要大約 9 個分支索引塊。

這樣，我們就知道了我們的這個索引有 2 層，第一層為 1 個根節點，第二層為 9 個分支節點，而葉子節點數為 3611 個，所指向的表的行數為 1300000 行。但是要注意，在 oracle 的索引中，層級號是倒過來的，也就是說假設某個索引有 N層，則根節點的層級號為 N ，而根節點下一層的分支節點的層級號為 N-1 ，依此類推。對本例來說， 9 個分支節點所在的層級號為 1 ，而根節點所在的層級號為 2 。
注意：在Oracle中null被定義為無限大，且null不等於null，故在索引不會存有與null值對應的條目。如果不加其他限制條件的對錶進行is null掃描，將會是全表掃描，如果是is not null掃描將會是全索引掃描

這裡僅僅是作為研究來討論如何估算，學習一樣東西，我們當然要知其然，也知其所以然，實際環境中可以利用explain plan for 查看建立索引的執行計畫，從而對索引大小，建立時間進行預判，具體可參見

http://czmmiao.iteye.com/blog/1471756

B樹索引的訪問

當oracle進程需要訪問資料檔案裡的資料區塊時，oracle會有兩種類型的I/O操作方式：
1） 隨機訪問，每次讀取一個資料區塊（通過等待事件“db file sequential read”體現出來）。
2） 順序訪問，每次讀取多個資料區塊（通過等待事件“db file scattered read”體現出來）。
第一種方式則是訪問索引裡的資料區塊，而第二種方式的I/O操作屬於全表掃描。這裡順帶有一個問題，為
何隨機訪問會對應到db file sequential read等待事件，而順序訪問則會對應到db file scattered read等待事件呢？這似乎反過來了，隨機訪問才應該是分散（scattered）的，而順序訪問才應該是順序（sequential）的。其實，等待事件主要根據實際擷取物理I/O塊的方式來命名的，而不是根據其在I/O子系統的邏輯方式來命名的。下面對於如何擷取索引資料區塊的方式中會對此進行說明。
事實上在B樹索引雖然為一個樹狀的立體結構，但其對應到資料檔案裡的排列當然還是一個平面的形式，也就是像下面這樣。
/根/分支/分支/葉子/…/葉子/分支/葉子/葉子/…/葉子/分支/葉子/葉子/…/葉子/分支/.....
因此，當oracle需要訪問某個索引塊的時候，勢必會在這個結構上跳躍的移動。
當oracle需要獲得一個索引塊時，首先從根節點開始，根據所要尋找的索引值，從而知道其所在的下一層的分支節點，然後訪問下一層的分支節點，再次同樣根據索引值訪問再下一層的分支節點，如此這般，最終訪問到最底層的葉子節點。可以看出，其獲得物理I/O塊時，是一個接著一個，按照順序，串列進行的。在獲得最終物理塊的過程中，我們不能同時讀取多個塊，因為我們在沒有獲得當前塊的時候是不知道接下來應該訪問哪個塊的。因此，在索引上訪問資料區塊時，會對應到db file sequential read等待事件，其根源在於我們是按照順序從一個索引塊跳到另一個索引塊，從而找到最終的索引塊的。
那麼對於全表掃描來說，則不存在訪問下一個塊之前需要先訪問上一個塊的情況。全表掃描時，oracle知道要訪問所有的資料區塊，因此唯一的問題就是儘可能高效的訪問這些資料區塊。因此，這時oracle可以採用同步的方式，分幾批，同時擷取多個資料區塊。這幾批的資料區塊在物理上可能是分散在表裡的，因此其對應到db file scattered read等待事件。

DML對B樹索引的影響

INSERT 

在每個INSERT操作過程中，關鍵字必須被插入在正確分葉節點的位置。如果分葉節點已滿，不能容納更多的關鍵字，就必須將分葉節點拆分。拆分的方法有兩種：

1)如果新關鍵字值在所有舊分葉節點塊的所有關鍵字中是最大的，那麼所有的關鍵字將按照99:1的比例進行拆分，使得在新的分葉節點塊中只存放有新關鍵字，而其他的所有關鍵字（包括所有刪除的關鍵字）仍然儲存在舊分葉節點塊中。
2)如果新關鍵字值不是最大的，那麼所有的關鍵字將按照50:50的比例進行拆分，這時每個分葉節點塊（舊與新）中將各包含原始分葉節點中的一半關鍵字。
這個拆分必須通過一個指向新分葉節點的新入口向上傳送到父節點。如果父節點已滿，那麼這個父節點也必須進行拆分，並且需要將這種拆分向上傳送到父節點的父節點。這時，如果這個父節點也已滿，將繼續進行這個過程。這樣，某個拆分可能最終被一直傳送到根節點。如果根節點滿了，根結點也將進行分裂。根結點在進行分裂的時候，就是樹的高度增加的時候。根節點進行分裂的方式跟其他的的節點分裂的方式相比較，在物理位置上的處理也是不同的。根節點分裂時，將原來的根結點分裂為分支節點或分葉節點，儲存到新的塊中，而將新的根節點資訊儲存到原來的根結點塊中，這樣做的是為因為避免修改資料字典所帶來的相對較大的開銷。
注意：現在Oracle都是採用了平衡演算法，正常情況下即使索引關鍵字不斷增大，也不會產生不平衡樹。當索引關鍵字不斷增大，導致樹層級單方向增長時，Oracle會自動進行索引翻轉以維持索引的平衡，當然這種操作非常消耗資源 
在索引的每一個層次之間，每一個層最左邊的節點的block頭部都有一個指向下層最左邊的塊的指標，這樣有利於fast full scan 的快速定位最左邊的葉子節點。
每個拆分過程都是要花費一定的開銷的，特別是要進行物理硬碟I/O動作。此外，在進行拆分之前，Oracle必須尋找到一個空塊，用來儲存這個拆分。可以用以下步驟來進行尋找空塊的動作：
1) 在索引的自由列表(free-list, 又稱為空白閑列表) 中查到一個空閑塊，可以通過CREATE/ALTER INDEX命令為一個索引定義多個空閑列表。索引空閑列表並不能協助Oracle尋找一個可用來存放將要被插入的新關鍵字的塊。這是因為關鍵字值不能隨機地存放在索引中可用的第一個“空閑”分葉節點塊中，這個值必須經過適當的排序之後，放置在某個特定的分葉節點塊中。只有在塊拆分過程中才需要使用索引的空閑列表，每個空閑列表都包含有一個關於“空”塊的連結清單。當為某個索引定義了多個空閑列表時，首先將從分配給進程的空間列表中掃描一個空閑塊。如果沒有找到所需要的空閑塊，將從主空閑列表中進行掃描空閑塊的動作。
2) 如果沒有找到任何空閑塊，Oracle將試圖分配另一個擴充段。如果在資料表空間中沒有更多的自由空間，Oracle將產生錯誤ORA-01654。
3) 如果通過上述步驟，找到了所需的空閑塊，那麼這個索引的高水位標(HWM)將加大。
4) 所找到的空閑塊將用來執行拆分動作。
在建立B*樹索引時，一個需要注意的問題就是要避免在運行時進行拆分，或者，要在索引建立過程中進行拆分（“預拆分”），從而使得在進行拆分時能夠快速命中，以便避免運行時插入動作。當然，這些拆分也不僅僅局限於插入動作，在進行更新的過程中也有可能會發生拆分動作。
UPDATE 
索引更新完全不同於表更新，在表更新中，資料是在資料區塊內部改變的（假設資料區塊中有足夠的空間來允許進行這種改變）；但在索引更新中，如果有關鍵字發生改變，那麼它在樹中的位置也需要發生改變。請記住，一個關鍵字在B*樹中有且只有一個位置。因此，當某個關鍵字發生改變時，關鍵字的舊錶項必須被刪除，並且需要在一個新的分葉節點上建立一個新的關鍵字。舊的表項有可能永遠不會被重新使用，這是因為只有在非常特殊的情況下， Oracle才會重用關鍵字表項槽，例如，新插入的關鍵字正好是被刪除的那個關鍵字（包括資料類型、長度等等）。（這裡重用的是塊，但完全插入相同的值的時候，也不一定插入在原來的被刪除的位置，只是插入在原來的塊中，可能是該塊中的一個新位置。也正因為如此，在索引塊中儲存的的記錄可能並不是根據關鍵字順序排列的，隨著update等的操作，會發生變化。）那麼，這種情況發生的可能性有多大呢？許多應用程式使用一個數列來產生NUMBER關鍵字（特別是主關鍵字）。除非它們使用了RECYCLE選項，否則這個數列將不會兩次產生完全相同的數。這樣，索引中被刪除的空間一直沒有被使用。這就是在大規模刪除與更新過程中，表大小不斷減小或至少保持不變但索引不斷加大的原因。

DELETE

當刪除表裡的一條記錄時，其對應於索引裡的索引條目並不會被物理的刪除，只是做了一個刪除標記。當一個新的索引條目進入一個索引葉子節點的時候，oracle會檢查該葉子節點裡是否存在被標記為刪除的索引條目，如果存在，則會將所有具有刪除標記的索引條目從該葉子節點裡物理的刪除。
當一個新的索引條目進入索引時，oracle會將當前所有被清空的葉子節點（該葉子節點中所有的索引條目都被設定為刪除標記）收回，從而再次成為可用索引塊。
儘管被刪除的索引條目所佔用的空間大部分情況下都能夠被重用，但仍然存在一些情況可能導致索引空間被浪費，並造成索引資料區塊很多但是索引條目很少的後果，這時該索引可以認為出現片段。而導致索引出現片段的情況主要包括：
1、不合理的、較高的PCTFREE。很明顯，這將導致索引塊的可用空間減少。
2、索引索引值持續增加（比如採用sequence產生序號的索引值），同時對索引索引值按照順序連續刪除，這時可能導致索引片段的發生。因為前面我們知道，某個索引塊中刪除了部分的索引條目，只有當有索引值進入該索引塊時才能將空間收回。而持續增加的索引索引值永遠只會向插入排在前面的索引塊中，因此這種索引裡的空間幾乎不能收回，而只有其所含的索引條目全部刪除時，該索引塊才能被重新利用。
3、經常被刪除或更新的索引值，以後幾乎不再會被插入時，這種情況與上面的情況類似。

總結 
通過上面對B樹的分析，可以得出以下的應用準則：
1、避免對那些可能會產生很高的更新動作的列進行索引。
2、避免對那些經常會被刪除的表中的多個列進行索引。若有可能，只對那些在這樣的表上會進行刪除的主關鍵字與/或列進行索引。如果對多個列進行索引是不可避免的，那麼就應該考慮根據這些列對錶進行劃分，然後在每個這樣的劃分上執行TRUNCATE動作（而不是DELETE動作）。TRUNCATE在與DROP STORAGE短語一同使用時，通過重新設定高水位標來類比刪除表與索引以及重新建立表與索引的過程。
3、避免為那些唯一度不高的列建立B*樹索引。這樣的低選擇性將會導致樹節點塊的稠密性，從而導致由於索引“平鋪( flat)”而出現的大規模索引掃描。唯一性的程度越高，效能就越好，因為這樣能夠減少範圍掃描，甚至可能用唯一掃描來取代範圍掃描。
4）空值不儲存在單列索引中。對於複合索引的方式，只有當某個列不空時，才需要進行值的儲存。在為DML語句建立IS NULL或IS NOT NULL短語時，應該切記這個問題。
5）IS NULL不會導致索引掃描，而一個沒有帶任何限制的IS NOT NULL則可能會導致完全索引掃描。
本文未 進行 索引內部結構的轉儲實驗，全部為理論研究，感興趣的朋友可以自行搜尋網上的相關文檔進行研究學習

參考至：http://btxigua.itpub.net/post/34419/406433

             http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139

             http://space.itpub.net/?uid-9842-action-viewspace-itemid-312607
             http://space.itpub.net/?uid-9842-action-viewspace-itemid-324586

本文原創，轉載請註明出處、作者
如有錯誤，歡迎指正
郵箱:[email protected]