Oracle優化之B樹索引

B樹索引

目前資料庫中最常用的索引，構造類似于二叉樹，能根據鍵值提供一行或一個行集的快速通路，其中的’B’代表平衡, 通常使用在頻繁使用查詢謂詞的列上,一般這類列的選擇度都較高。

使用場景

1. 當我們希望從表中隻傳回少量的資料（占比很小，這個比例通常經驗值是5%,不過根據表的不同也有不用，一個瘦表（通常隻有幾列）可能在20%-30%，一個胖表（列很多或列很寬）可能在2%-3%）時會使用索引。如下例：

T1表插入從1到10w的數字隻有一列，t3_2利用下例中的t3建表，建表sql

create table t3_2 as select id,name,name||name||name||name c3,name||name||name||name||name c4 from t3

分别在id列建立索引，收集統計資訊

以上兩個查詢中的數值分别是全表掃描與索引範圍查詢的臨界值

1. 當我們想要查詢大量資料，但是隻要傳回索引的列或者隻通過索引列就能得到結果的話，索引也會起到作用

3、資料在磁盤上的實體組織也會對索引的使用有影響，如以下的例子，我們建立兩個實驗表t2/t3，向t2中順序的插入10w條資料，同時生成一組随機資料。将t2按照随機數排序插入到t3中，目的是打亂資料的實體存儲位置。

begin

  for  i in 1..100000 loop

    insert into t2 values(i,rpad(dbms_random.random,75,'*'));

    end loop;

    commit;

end;

create table t3 as select * from t2 order by name

create index idx_1 on t2(id)

create index idx_2 on t3(id)

begin

  dbms_stats.gather_table_stats

  ('scott','t2');

begin

  dbms_stats.gather_table_stats

  ('scott','t3');

  end;

  end;

在對表進行收集統計資訊後，分别對t2/t3查詢相同範圍的資料

select * from t2 where id between 87 and 1000

select * from t3 where id between 87 and 1000

以上三個查詢的結果是相同的，但是相同的查詢資料庫的開銷與io上升的差異十分明顯。

原因是當向一個表中填充資料時如果按照行主鍵或者建立索引的列順序填充，序号相鄰的行存儲位置一般也會相鄰，當你發出一個範圍查詢的時候你想要的行通常也在同樣的塊上，即使你要查找大量的行，通過索引範圍掃描的讀取的塊裡也許就包含了你想要的行。如下圖，相同數量的行數在未打亂順序的t2中分布在11個資料塊，在順序被打亂的t3中分布在631個塊。

如果行被分散的存儲在不同位置上，此時強制使用索引範圍掃描就會是個災難，使用全表掃描反而更好。

總結

1. 通過索引通路表中的資料占比越少越有效
2. 如果能使用索引列回答問題（隻用到索引列不用通路表）那麼傳回資料占比很大索引也是有效的
3. 資料的實體組織有時未按照索引列或主鍵列有序的填充表，會影響索引的使用
4. B樹索引經過大量的插入删除操作以後一個是容易使樹不平衡，再一個是删除後空間不回收。是以定期重建索引非常有必要。
5. 空值會影響索引的使用，在有空值的列上通過與虛拟列建立組合索引，可以使優化器選擇索引。而且索引的大小并沒有明顯變化

create table t5 as select u.OBJECT_NAME,u.DATA_OBJECT_ID from user_objects u

create index t5_1 on t5(DATA_OBJECT_ID)

create table t6 as select * from t5

create index t6_1 on t6(DATA_OBJECT_ID,0)

begin

  dbms_stats.gather_table_stats

  ('scott','t5');

dbms_stats.gather_table_stats

  ('scott','t6');

  end;