淺談索引系列之基本原理

13811135929發表於2017-03-01
索引
注意：本文談論的索引僅限最常用的標準B*Tree索引，點陣圖索引，聚簇索引不在本文討論範圍內，以下原理並不適用。
      關於索引前段時間已經寫了淺談索引序列之是否可以儲存NULL值？淺談索引系列之聚簇因子(clustering_factor)兩篇博文，之所以再返回來寫基本原理是因為發現自己對索引的基本原理了解的並不是很深入，翻閱了相關資料後整理了幾個索引的重要知識點如下，和大家共享。
    1.B*Tree索引中索引條目是按照鍵值排序儲存的。
    2.B*Tree索引儲存空間可以進行預先估算。
    3.葉子節點之間相互指向，在結合1,因此索引條目按照鍵值構成了一個雙向有序連結串列
     下面透過具體的例子進行說明：


索引鍵值順序

      建立測試表和索引

點選(此處)摺疊或開啟

  1. drop table test;
  2. create table test as select * from all_objects order by OBJECT_NAME;
  3. create index test_pk on test(object_id);
      對索引進行treedump,為了便於閱讀關於treedump方法請參考淺談索引序列之是否可以儲存NULL值？博文，取trace日誌中任一葉子節點進行檢視
     leaf: 0x1002ff8 16789496 (3: nrow: 479 rrow: 479)

點選(此處)摺疊或開啟

  1. SQL> SELECT dbms_utility.data_block_address_file(16789496),dbms_utility.data_block_address_block(16789496) FROM dual;

  3. DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16789496) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16789496)
  4. ---------------------------------------------- -----------------------------------------------
  5.                                              4 12280
  6. SQL> alter system dump datafile 4 block 12280;
    開啟dump出來的trace檔案，限於篇幅只擷取部分，可以清晰的看到雖然表是按照OBJECT_NAME的依次插入記錄的，但是索引條目依然按照OBJECT_ID的順序儲存在塊中。
row#0[8019] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 58
col 1; len 6; (6):  01 00 2f bb 00 19
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 59
col 1; len 6; (6):  01 00 2f c2 00 43
row#2[7993] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5a
col 1; len 6; (6):  01 00 2f bb 00 17
row#3[7980] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5b
col 1; len 6; (6):  01 00 2f c2 00 47
row#4[7967] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5c
col 1; len 6; (6):  01 00 2f bb 00 1b
row#5[7954] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5d
col 1; len 6; (6):  01 00 2f c2 00 49
...
那麼我們看看再次插入OBJECT_ID=2088的記錄效果怎麼樣?

點選(此處)摺疊或開啟

  1. SQL> insert into test select * from dba_objects where object_id=2088;
  2. 1 row created.
  3. SQL> commit;
  4. Commit complete.
  5. SQL> alter system dump datafile 4 block 12280;
  6. System altered.
row#0[8019] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 58
col 1; len 6; (6):  01 00 2f bb 00 19
row#1[8006] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 59
col 1; len 6; (6):  01 00 2f c2 00 43
row#2[1797] flag: ------, lock: 2, len=13
col 0; len 3; (3):  c2 15 59
col 1; len 6; (6):  01 00 30 6b 00 00
row#3[7993] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5a
col 1; len 6; (6):  01 00 2f bb 00 17
row#4[7980] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5b
col 1; len 6; (6):  01 00 2f c2 00 47
row#5[7967] flag: ------, lock: 0, len=13
col 0; len 3; (3):  c2 15 5c
col 1; len 6; (6):  01 00 2f bb 00 1b
發現了什麼？索引中新增了一鍵值為c2 15 59（2088）的索引條目。無論記錄在表中是怎麼雜亂無章儲存的還是按照有序的，一旦建立索引後，索引條目都是按照索引列為順序來進行儲存的，這裡就引出了聚簇因子概念，具體聚簇因子的概念在這裡不再贅述。另外執行計劃中INDEX FULL SCAN也是基於索引鍵值有序這個原理。


儲存空間估算

      生產環境任何操作都可能存在隱患，導致業務無法正常對外服務。建立索引也不例外，如何預先評估索引佔用的空間，防止表空間被佔滿可能是每位DBA必備的技能，下面將結合索引條目的結構來說明一下索引的佔用情況。
葉子節點索引條目的資料結構如下：

分支節點索引條目的資料結構如下：
對於預設段，其預設的PCTFREE為10%,也就是說最多隻能使用其中的90%,因此對於一個大小為8K的塊來說僅有8192*90%=7372.8位元組可用。
1.首先計算出每個葉子節點索引條目佔用的位元組數byte_per_lnode，7372.8/byte_per_lnode進而計算出每個葉子節點儲存多少索引條目entries_per_lnode,表的總行數total_num/entries_per_lnode得到葉子節點的總個數lnodes，因此葉子節點佔用的總空間為:lnodes*8k
2.計算出每個分支節點索引條目佔用的位元組數byte_per_bnode，7372.8/byte_per_bnode進而計算出每個分支節點儲存多少索引條目entries_per_bnode,用步驟1中lnodes/byte_per_bnode得到分支節點的總個數bnodes,因此最底層分支節點佔用的總空間為bnodes*8k
3.檢查分支節點總個數是否超過了byte_per_bnode,如果大於byte_per_bnode說明至少還有兩層分支節點，需要再重複步驟2的情況。否則計算空間完成。
因此索引佔用的儲存空間為：lnodes*8k+bnodes*8k,注意儲存空間的大小僅為估算，加上一小部分管理空間，實際佔用空間可能會比預算空間大一些。

雙向有序連結串列

在trace出索引葉子塊的資訊後，有如下關鍵字
kdxlenxt 16789497=0x1002ff9
kdxleprv 16789495=0x1002ff7
其中kdxlenxt為下一個葉子節點的地址，kdxleprv為上一個葉子節點的地址。每個葉子節點都有此資訊標記了上一個以及下一個葉子節點的資訊，因此葉子節點構成了一個索引鍵值有序的雙向有序連結串列，這種有序結構某些情況下避免了再次排序，節省了oracle的硬體資源，提高了效率。





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29827284/viewspace-2134526/，如需轉載，請註明出處，否則將追究法律責任。

相關文章