https://blog.csdn.net/biww620/article/details/73003880
目錄是索引的一個最好的例子，每條目錄包含對應章節的標題和頁碼，類比索引的每條索引項包含了資料記錄的某些鍵值組合并包含了對應資料塊的訪問路徑（rowid），目錄的存在就是為了快速定位到感興趣的內容，索引的存在也是問了加快對表資料的隨機訪問，
常常被提及的索引可能有單鍵索引、組合索引、唯一索引、B-Tree索引、位圖索引、函式索引、全域索引、區域索引等等，這里只是列舉出鏡率較高的索引型別，并沒有去做嚴格的劃分，各型別間有重疊，比如函式索引可以是B-Tree索引也可以是位圖索引，在Oracle中索引和表一樣屬于邏輯結構中的段（segment），每個索引都擁有獨立的結構，無論是從物理結構還是邏輯結構來看與其所關聯的表完全分開，即便索引失效也不會造成原有SQL無法執行，只是改變了執行計劃，降低了執行效率，
B-Tree索引
查找樹有完全二叉樹、二叉查找樹、平衡二叉樹、紅黑樹，B-Tree，B+-Tree，B*-Tree等，對于二叉樹其目的是要將查詢復雜度控制在O(lgN)以內，（注：這里的lgN表示log2N），查詢效率與樹的高度有關，在少量資料構造的二叉樹查詢是很高效的，但是在資料庫應用中，資料量巨大，如果構造二叉樹那么樹的高度將也很巨大，勢必增加讀取索引節點的I/O次數，影響查詢效率，于是B-Tree挺身而出，在很大的資料量范圍內能夠保持B-Tree樹的層級不會增加，
圖片來自網路
從上圖中可以看出在oraccle中B-Tree索引具有以下結構特點：
B-Tree索引包含根節點（Root Node）、分支節點（Branch Node）和葉子節點（Leaf Node），
索引樹高度一般都很低，上百億記錄的索引樹的高度也只有5，6層，
索引本身有序，葉子節點是一個雙向鏈表，因此可以按照索引的升序或降序進行索引掃描，
索引項包含鍵值資訊和ROWID，索引項由索引頭部、索引列的長度、索引值以及對應記錄的rowid，其中唯一索引對應的rowid是唯一的，非唯一索引對應的rowid是可能有多個（多個rowid是有序的），
索引列值全部為NULL的索引項是不會被記錄的，
B-Tree索引簡要分析
一、提高查詢效率
200w條記錄的表test_index_t1，查找條件col1 = 98765的記錄沒有索引的執行計劃如下：
在test_index_t1表的col1列添加索引
create index index_col1 on test_index_t1(col1);
再次執行查詢的執行計劃如下：
未建立索引時執行計劃是TABLE ACCESS FULL用時1100ms，建立索引后執行計劃是INDEX RANGE SCAN用時90ms，效率提高了10倍以上，這里test_index_t1的資料量不大，如果是大資料量的表執行效率的差距會更加明顯，
二、索引樹高度較低
通過以下sql可以查詢索引的統計資訊，其中BLEVEL表示索引樹的高度，高度為BLEVEL + 1
SELECT
index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
FROM
user_ind_statistics
WHERE
table_name = UPPER('test_index_t1');
對于200w條記錄的表test_index_t1執行索引統計資訊查詢后得到的結果為：
可以看出BLEVEL = 2也就是說索引樹的高度為3，構建了記錄數分別為10條，20w條和300w條的表并建立相同的索引，索引樹高度分別為2,2,3，因此可以看出B-Tree索引的高度是比較低的，能夠在大資料量的情況下保證樹高度值很低，在通過索引執行查詢時一個層級往往就代表一次I/O操作，因此保持索引樹高度較低對查詢性能有很大的好處，
三、索引包含鍵值
索引包含索引鍵值，單鍵或鍵組合，如果查詢所需的欄位均在索引項中則可以避免回表讀數，提高查詢性能，創建表test_index_t1包含三個欄位col1,col2,col3初始化為300w條記錄，并建立了（col1，col2）組合索引，
create index index_col1_col2 on test_index_t1(col1, col2);
1. 執行sql
select col1 from test_index_t1 where col1 between 10 and 20;
2. 執行sql
select col1, col2 from test_index_t1 where col1 between 10 and 20;
3. 執行sql
select * from test_index_t1 where col1 between 10 and 20;
從上面三次查詢結果可以看出：
（1） 三次執行SQL均用到了索引INDEX_COL1_COL2，索引執行方式為Index Range Scan
（2） 第一次和第二次查詢（col1）、（col1、col2）均未回表讀數，而第三次查詢存在TABLE ACCESS BY INDEX ROWID回表讀數，原因是組合索引INDEX_COL1_COL2中不包含列col3，因此通過索引掃描得到最終記錄的rowid后還會根據rowid到表中讀取col3，
總體來看，如果所需列包含于索引中那么可以通過索引避免回表讀數從而提高查詢性能，但需要注意的是索引本身也有性能消耗，并不是包含的列越多越好，一般建議索引列不超過3個，從實際的經驗來看5，6個也還是可以接受，
四、索引本身有序
在前面提到的索引結構中可以看出索引葉子結點本身是按照索引鍵升序排列，相當于一個雙向鏈表，可以進行升序或降序掃描，洗掉test_index_t1表的索引，再執行查詢
select col1, col2 from test_index_t1 where col1 between 10 and 20 order by col1;
從執行計劃和統計資訊中可以看出執行了排序程序并使用了記憶體空間，給test_index_t1表col1欄位加上索引后的執行計劃如下
執行計劃走索引后SORT ORDER BY不存在了，因此，如果因為排序導致查詢性能降低可以考慮在索引中包含需要排序的列，這樣利用索引本身的有序性可以避免排序帶來的性能損耗，
五、索引不保存索引鍵值全部為NULL的記錄
這個特點跟count，sum/avg，max/min的執行計劃息息相關，可以總結為以下兩點：
COUNT/SUM/AVG必須在索引列為非空的情況下才可以走到索引，（建表是列指定為Not Null或為主鍵或在where條件中指明為is not null），
MIN/MAX則不會受到空值的影響，均能走到索引，
表test_index_t1有300w條記錄，在col1上建立了索引，執行：
select count(1) from test_index_t1;
可以看出是走了全表掃描，在where條件中增加col1 is not null后的執行計劃為：
用INDEX FAST FULL SCAN的方式使用索引INDEX_COL1，最后col1添加屬性not null后的執行計劃為：
可以看出給列col1添加了not null屬性后執行計劃跟在where條件中指明is not null相同，這里不再對sum/avg,min/max做驗證，
————————————————
著作權宣告：本文為CSDN博主「biww620」的原創文章，遵循 CC 4.0 BY-SA 著作權協議，轉載請附上原文出處鏈接及本宣告，
原文鏈接：https://blog.csdn.net/biww620/java/article/details/73003880
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4090.html
標籤：Oracle
上一篇：Oracle中簡單的格式轉換