oracle的索引

追求完美9196發表於2024-07-22
Oracle索引

1 B*TREE索引

1.1 什麼是B*TREE索引

B*TREE索引是oracle資料庫中最常見的索引。可以根據索引鍵值快速定位到表裡的某一行資料或者根據索引鍵範圍定位多行資料。

1.2 B*TREE索引結構

B*TREE索引的構造類似於二叉樹，最底層的塊稱作葉塊，葉塊由索引鍵以及rowid組成。葉塊之上的塊稱為分支塊，檢索資料時就是透過分支塊到達葉塊的。
例如，我們想在索引中找到值42,就要從樹頂開始，找到左分支的一個塊(分支塊)。 然後我們在這個分支塊當中，發現需要繼續去找範圍為“42...50”的(葉)塊，而這個葉塊就會帶領我們找到表中值為42的那一行或幾行資料。

葉塊這一層是一個雙向鏈結構，這意味著範圍搜尋時，不需要從樹根重新掃描，只需要向左或向右進行掃描。

1.3 B*TREE索引的唯一性和高度

BTREE索引的索引條目都是唯一的。如果索引鍵儲存重複值，就會追加rowid到索引鍵後面，使索引條目唯一。在唯一性的索引中，資料庫不會追加rowid到索引鍵上。
索引的高度是指從根塊到葉塊所需遍歷的塊。大多數BTREE索引的高度都是2或者3，即使幾百萬行的索引也是如此。這意味只需要2到3次I/O,我們就能找對應的葉塊。

1.4 什麼情況下應該使用B*TREE索引

  • 訪問表裡一小部分資料，就用BTREE索引。這一小部分資料的行數佔總表的行數不應該超過20%。例如訪問一個學生表學號為1的資料，學生表有10萬條資料。學號為1的這一條資料就佔很小的比例。所以，我們應該在學號這個欄位上建BTREE索引。
    為什麼不應該在訪問佔表大比例的資料上建B*TREE索引。例如表裡有1000條資料以及100個塊。如果我們要訪問表裡的200條資料，透過索引，我們就要掃描200次塊。但是我們不使用索引，使用全表掃描的方式，才掃描100次塊，比透過索引查詢資料更高效。
  • 要訪問大量資料時，不需要訪問表，可以直接透過索引就能得到時，就使用B*TREE索引。例如一個學生表的主鍵是學號（主鍵會自動建索引），我們需要統計學生表的學號的數量
    select count(學號) from 學生表;
    這時，資料庫不會去掃描表裡的資料統計數量，而是直接掃描學號的索引統計資料量。

2 點陣圖索引

B*TREE索引的索引條目和表裡的行是一對一的關係。但是點陣圖索引一個索引條目會指向表裡的多行資料。點陣圖索引適合在表裡高度重複的列建立。高度重複指相對於表裡的總行數而言，索引欄位只有少數幾個不同的值。例如一個學生表，有10萬資料，欄位性別只有男女兩個不同的值，性別欄位就適合建點陣圖索引。
點陣圖索引適合建立在只讀的資料中。在OLTP資料庫中建立點陣圖索引會導致以下併發問題。因為點陣圖索引一個索引條目指向表裡多行資料，如果修改索引列的資料，這個索引條目指向的多行資料會同時被鎖定。

3 基於函式的索引

3.1 什麼是基於函式的索引

我們可以使用基於函式的索引，對錶裡某些列的計算結果進行索引，這樣查詢時可以直接使用函式索引的結構。例如我們對學生表的學生名稱建立了轉換為大寫的索引。
create index stu_upper_idx on student(upper(sname));
當我們查詢select * from student where upper(sname)='JACK'時，資料庫就無需將sname轉換為大寫，再對資料進行搜尋，而是直接使用stu_upper_idx索引的資料進行搜尋。再根據索引儲存的ROWID找到對應的行資料。

3.2 只對部分行進行索引

對應B*TREE索引而言，對於索引列全為null的行是不會建立索引條目的。例如有如下索引
Create index I on t（a,b）;
如果一行資料的a，b都是null，那索引I中就不會有這一行的索引條目。我們可以基於這個原理，對部分行進行索引。例如學生表有欄位是否退學（is_dropout）。我們需要查詢已退學的學生，我們就可以建如下索引
Create index id_drop on student（case when is_dropout='是' then '是' end）;
那id_drop這個索引，只會建立is_dropout='是'的索引，不會建立is_dropout為其他值的索引。

3.3 實現有選擇的唯一性

我們有這麼一個需求，學生表中欄位是否退學（is_dropout），如果為'否'，則學生姓名(s_name)不允許重複。則我們可以建如下唯一性索引
Create unique index id_student_name On student ( case when is_dropout= '否' then s_name end );

4 索引常見的問題

4.1 可以在null建立索引嗎？

BTREE索引不會儲存索引鍵都是null的索引條目。而點陣圖和聚簇索引則會儲存。如果的確有'select * from t where name is null'這樣查詢某個欄位不為空的需求，而且null值的數量佔表總數量很低，則可以使用索引鍵上追加一個常量，給索引鍵的null值建立bTREE索引。
CREATE INDEX id_name ON t (name, '1');

  • 為什麼重要欄位不建議使用null
    [1] B*TREE索引不儲存索引鍵都是null值資料，影響查詢效率。
    [2] 不對null值處理後再參加聚合函式計算,容易造成聚合函式的統計產生錯誤的結果。
    例如統計資料，要先加1再合計。寫sum(length+1),有條資料時空，則統計結果會少1。因為null+1還是null。應該寫成sum(nvl(length,0)+1)
    [3] 不對null值處理後再參加NOT IN查詢，則會讓查詢查不出結果。
    例如查詢 where id not in(select id2 from student)，如果id2有空值，則不會查出任何資料

4.2 為什麼最佳化器沒有使用建立的索引

  • 索引建立在訪問表裡大部分資料的欄位上。例如，'select * from student where sex='男''。男性別數量佔表裡數量一半左右。即使在sex建立索引，資料庫最佳化器判斷透過索引搜尋資料的效率還不如全表掃描，資料庫就會直接透過全表掃描搜尋資料。
  • 類似於'SELECT COUNT(1) FROM T'的查詢。表T存在索引，但是索引列存在空值。因為索引鍵全為空的資料不會建B*TREE索引條目，使用索引統計會導致統計錯誤。所以資料庫不會使用索引統計資料量，而是選擇全表掃描。要讓統計全表資料量使用索引，可以在建立not null約束的欄位上建立索引。
  • 表上的統計資訊並不是最新的。所以可以嘗試收集一下表上的統計資訊，再次查詢資料。
  • 索引處於不可用狀態。查詢索引狀態，如果是'UNUSABLE',則需要重建索引。
  • 謂詞沒有使用索引的最前列。例如查詢 'SELECT * FROM T where y=1'。但是是在T(X,Y)上建立的索引。那最佳化器就很可能不會用到索引，因為謂詞中沒有用到X列。
  • 使用函式對索引列進行了轉化。例如查詢 'SELECT * FROM T where upper(y)='A''。即使在y列建立索引，也不會使用到索引。應該建立基於upper(y)函式的索引。
  • 查詢值型別和欄位型別不一致。例如 'SELECT * FROM T where a=1'。a欄位時varchar2型別，查詢的值是數字。這樣也使用不到索引。

相關文章