分析oracle數據庫索引全面詳解

對于數據庫來說，索引是一個必選項，但對于現在的各種大型數據庫來說，索引可以大大提高數據庫的性能，以至于它變成了數據庫不可缺少的一部分。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。 在關系數據庫中，索引是一種與表有關的數據庫結構，它可以使對應于表的SQL語句執行得更快。

索引分類：

邏輯分類

single column or concatenated 　　 對一列或多列建所引

unique or nonunique 　　 唯一的和非唯一的所引，也就是對某一列或幾列的鍵值（key）是否是唯一的。

Function-based 　　基于某些函數索引，當執行某些函數時需要對其進行計算，可以將某些函數的計算結果事先保存并加以索引，提高效率。

Doman 　　索引數據庫以外的數據，使用相對較少

物理分類

B-Tree ：normal or reverse key B-Tree索引也是我們傳統上常見所理解的索引，它又可以分為正常所引和倒序索引。

Bitmap ： 位圖所引，后面會細講

B-Tree 索引

　　B-Tree index 也是我們傳統上常見所理解的索引。B-tree （balance tree）即平衡樹，左右兩個分支相對平衡。

B-Tree index

Root為根節點，branch 為分支節點，leaf 到最下面一層稱為葉子節點。每個節點表示一層，當查找某一數據時先讀根節點，再讀支節點，最后找到葉子節點。葉子節點會存放index entry （索引入口），每個索引入口對應一條記錄。

Index entry 的組成部分：

Indexentry entry header 　　存放一些控制信息。

Key column length 　　某一key的長度

Key column value 　　 某一個key 的值

ROWID 　　指針，具體指向于某一個數據

創建索引：

用戶登錄：
SQL> conn as1/as1
Connected.

創建表：
SQL> create table dex (id int,sex char(1),name char(10));
Table created.

向表中插入1000條數據
SQL> begin
2 for i in 1..1000
3 loop
4 insert into dex values(i,'M','chongshi');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

查看表記錄
SQL> select * from dex;
ID SE NAME
---------- -- --------------------
... . .....

991 M chongshi
992 M chongshi
993 M chongshi
994 M chongshi
995 M chongshi
996 M chongshi
997 M chongshi
998 M chongshi
999 M chongshi
1000 M chongshi

1000 rows selected.

創建索引：
SQL> create index dex_idx1 on dex(id);
Index created.
注：對表的第一列（id）創建索引。

查看創建的表與索引
SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX

　　索引分離于表，作為一個單獨的個體存在，除了可以根據單個字段創建索引，也可以根據多列創建索引。Oracle要求創建索引最多不可超過32列。

SQL> create index dex_index2 on dex(sex,name);
Index created.

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEX

這里需要理解：

　　編寫一本書，只有章節頁面定好之后再設置目錄；數據庫索引也是一樣，只有先插入好數據，再建立索引。那么我們后續對數據庫的內容進行插入、刪除，索引也需要隨之變化。但索引的修改是由oracle自動完成的。

上面這張圖能更加清晰的描述索引的結構。

跟節點記錄0至50條數據的位置，分支節點進行拆分記錄0至10.......42至50，葉子節點記錄每第數據的長度和值，并由指針指向具體的數據。

最后一層的葉子節是雙向鏈接，它們是被有序的鏈接起來，這樣才能快速鎖定一個數據范圍。

如：

SQL> select * from dex where id>23 and id<32;

ID SE NAME
---------- -- --------------------
24 M chongshi
25 M chongshi
26 M chongshi
27 M chongshi
28 M chongshi
29 M chongshi
30 M chongshi
31 M chongshi

8 rows selected.

　　如上面查找的列子，通過索引的方式先找到第23條數據，再找到第32條數據，這樣就能快速的鎖定一個查找的范圍，如果每條數據都要從根節點開始查找的話，那么效率就會非常低下。

位圖索引

　　位圖索引主要針對大量相同值的列而創建。拿全國居民登錄一第表來說，假設有四個字段：姓名、性別、年齡、和身份證號，年齡和性別兩個字段會產生許多相同的值，性別只有男女兩種值，年齡，1到120（假設最大年齡120歲）個值。那么不管一張表有幾億條記錄，但根據性別字段來區分的話，只有兩種取值（男、女）。那么位圖索引就是根據字段的這個特性所建立的一種索引。

Bitmap Index

　　從上圖，我們可以看出，一個葉子節點（用不同顏色標識）代表一個key , start rowid 和 end rowid規定這種類型的檢索范圍，一個葉子節點標記一個唯一的bitmap值。因為一個數值類型對應一個節點，當時行查詢時，位圖索引通過不同位圖取值直接的位運算（與或），來獲取到結果集合向量（計算出的結果）。

舉例講解：

假設存在數據表T，有兩個數據列A和B，取值如下，我們看到A和B列中存在相同的數據。

對兩個數據列A、B分別建立位圖索引：idx_t_bita和idx_t_bitb。兩個索引對應的存儲邏輯結構如下：

Idx_t_bita索引結構，對應的是葉子節點：

Idx_t_bitb索引結構，對應的是葉子節點：

對查詢“select * from t where b=1 and (a=’L’ or a=’M’)”

[page]

分析：位圖索引使用方面，和B*索引有很大的不同。B*索引的使用，通常是從根節點開始，經過不斷的分支節點比較到最近的符合條件葉子節點。通過葉子節點上的不斷Scan操作，“掃描”出結果集合rowid。

而位圖索引的工作方式截然不同。通過不同位圖取值直接的位運算（與或），來獲取到結果集合向量（計算出的結果）。

針對實例SQL，可以拆分成如下的操作：

1、a=’L’ or a=’M’

a=L：向量：1010

a=M：向量：0001

or操作的結果，就是兩個向量的或操作：結果為1011。

2、結合b=1的向量

中間結果向量：1011

B=1：向量：1001

and操作的結果，1001。翻譯過來就是第一和第四行是查詢結果。

3、獲取到結果rowid

目前知道了起始rowid和終止rowid，以及第一行和第四行為操作結果�？梢酝ㄟ^試算的方法獲取到結果集合rowid。

位圖索引的特點：

1.Bitmap索引的存儲空間節省

2.Bitmap索引創建的速度快

3.Bitmap索引允許鍵值為空

4.Bitmap索引對表記錄的高效訪問

創建位圖索引：

查看表記錄
SQL> select * from dex;
...................
ID SEX NAME
---------- -- --------------------
991 M chongshi
992 M chongshi
993 G chongshi
994 G chongshi
995 G chongshi
996 M chongshi
997 G chongshi
998 G chongshi
999 G chongshi
1000 M chongshi

1000 rows selected.

對于上面表來說sex（性別）只有兩種值，最適合用來創建位圖所引
創建索引：
SQL> create bitmap index my_bit_idx on dex(sex);

Index created.

查看創建的所引
SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX INDEX

創建索引的一些規則

1、權衡索引個數與DML之間關系，DML也就是插入、刪除數據操作。

這里需要權衡一個問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會影響插入、刪除數據的速度，因為我們修改的表數據，索引也要跟著修改。這里需要權衡我們的操作是查詢多還是修改多。

2、把索引與對應的表放在不同的表空間。

當讀取一個表時表與索引是同時進行的。如果表與索引和在一個表空間里就會產生資源競爭，放在兩個表這空就可并行執行。

3、最好使用一樣大小是塊。

Oracle默認五塊，讀一次I/O，如果你定義6個塊或10個塊都需要讀取兩次I/O。最好是5的整數倍更能提高效率。

4、如果一個表很大，建立索引的時間很長，因為建立索引也會產生大量的redo信息，所以在創建索引時可以設置不產生或少產生redo信息。只要表數據存在，索引失敗了大不了再建，所以可以不需要產生redo信息。

索引常見操作

改變索引：

SQL> alter index employees_last _name_idx storage(next 400K maxextents 100);

索引創建后，感覺不合理，也可以對其參數進行修改。詳情查看相關文檔

調整索引的空間：

新增加空間
SQL> alter index orders_region_id_idx allocate extent (size 200K datafile '/disk6/index01.dbf');

釋放空間
SQL> alter index oraers_id_idx deallocate unused;

索引在使用的過程中可能會出現空間不足或空間浪費的情況，這個時候需要新增或釋放空間。上面兩條命令完成新增與釋放操作。關于空間的新增oracle可以自動幫助，如果了解數據庫的情況下手動增加可以提高性能。

重新創建索引：

所引是由oracle自動完成，當我們對數據庫頻繁的操作時，索引也會跟著進行修改，當我們在數據庫中刪除一條記錄時，對應的索引中并沒有把相應的索引只是做一個刪除標記，但它依然占據著空間。除非一個塊中所有的標記全被刪除的時，整個塊的空間才會被釋放。這樣時間久了，索引的性能就會下降。這個時候可以重新建立一個干凈的索引來提高效率。

SQL> alter index orders_region_id_idx rebuild tablespace index02;

通過上面的命令就可以重現建立一個索引，oracle重建立索引的過程：

1、鎖表，鎖表之后其他人就不能對表做任何操作。

2、創建新的（干凈的）臨時索引。

3、把老的索引刪除掉

4、把新的索引重新命名為老索引的名字

5、對表進行解鎖。

移動所引：

其實，我們移動索引到其它表空間也同樣使用上面的命令，在指定表空間時指定不同的表空間。新的索引創建在別位置，把老的干掉，就相當于移動了。

SQL> alter index orders_region_id_idx rebuild tablespace index03;

在線重新創建索引：

上面介紹，在創建索引的時候，表是被鎖定，不能被使用。對于一個大表，重新創建索引所需要的時間較長，為了滿足用戶對表操作的需求，就產生的這種在線重新創建索引。

SQL> alter index orders_id_idx  rebuild  online;

創建過程：

1、鎖住表

2、創建立臨時的和空的索引和IOT表用來存在on-going DML。普通表存放的鍵值，IOT所引表直接存放的表中數據；on-gong DML也就是用戶所做的一些增刪改的操作。

3、對表進行解鎖

4、從老的索引創建一個新的索引。

5、IOT表里存放的是on-going DML信息，IOT表的內容與新創建的索引合并。

6、鎖住表

7、再次將IOT表的內容更新到新索引中，把老的索引干掉。

8、把新的索引重新命名為老索引的名字

9、對表進行解鎖

整合索引碎片：

如上圖，在很多索引中有剩余的空間，可以通過一個命令把剩余空間整合到一起�！　�

SQL> alter index orders_id_idx  coalesce;

刪除索引：

SQL> drop  index  hr.departments_name_idx;

分析索引

　　檢查所引的有效果，前面介紹，索引用的時間久了會產生大量的碎片、垃圾信息與浪費的剩余空間了。可以通過重新創建索引來提高所引的性能。

可以通過一條命令來完成分析索引，分析的結果會存放在在index_stats表中。

查看存放分析數據的表：
SQL> select count(*) from index_stats;

COUNT(*)
----------
0
執行分析索引命令：
SQL> analyze index my_bit_idx validate structure;

Index analyzed.

再次查看 index_stats 已經有了一條數據
SQL> select count(*) from index_stats;

COUNT(*)
----------
1

把數據查詢出來：
SQL> select height,name,lf_rows,lf_blks,del_lf_rows from index_stats;

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------------------------------------------------------------------- ---------- -----------
2 MY_BIT_IDX 1000 3 100

分析數據分析：

（HEIGHT）這個所引高度是2 ，（NAME）索引名為MY_BIT_IDX ，（LF_ROWS）所引表有1000行數據，（LF_BLKS）占用3個塊，（DEL_LF_ROWS）刪除100條記錄。

　　這里也驗證了前面所說的一個問題，刪除的100條數據只是標記為刪除，因為總的數據條數依然為1000條，占用3個塊，那么每個塊大于333條記錄，只有刪除的數據大于333條記錄，這時一個塊被清空，總的數據條數才會減少。

分析索引的碎片
 

Oracle 10g

先建立個統計表

create table T_ANALYZ_MONITOR_INDEX
(
  F_INDEX_NAME  VARCHAR2(50),
  F_DEL_LF_ROWS NUMBER,
  F_LF_ROWS     NUMBER,
  F_RATE        NUMBER(4,2),
  F_MONITOR_DATE DATE default sysdate not null
);

再建個歷史表

create table t_analyz_index_stats as select * from index_stats

做個分析過程  查出表并且 分析 插入歷史表 統計刪除比率到 統計表

create or replace PRocedure P_ANALYZ_DAY_INDEX_SATAS is
    v_sql varchar2(100);
Begin

  for a in (Select INDEX_NAME   From User_Indexes  Where index_type<>'LOB') loop
    v_sql := ' analyze index ' a.index_name ' validate structure';
    execute immediate v_sql;
   
    Insert Into T_ANALYZ_INDEX_STATS
     Select * From Index_Stats;
       
    insert into T_ANALYZ_MONITOR_INDEX(F_INDEX_NAME, F_DEL_LF_ROWS, F_LF_ROWS, F_RATE)
     select name,del_lf_rows,lf_rows, round(del_lf_rows * 100 / decode((lf_rows + del_lf_rows),0,1), 2)
     from index_stats;               
        
  End loop;
   
end;

注意所查到的索引是在同個用戶名下的