分析索引快速獲取索引資訊

 

索引的健康狀態是我們非常關注的一個問題。健康的索引可以在保證有效使用空間的基礎上，提供很好的搜尋效能。同樣，一些非健康的索引也會一定程度上影響系統執行的效率。

 

 

在一些資料DML操作頻繁的系統中，索引是聯動進行更新，不斷組建成新的索引樹，與資料列相匹配。但是因為各種原因，索引的結構通常都是在不斷退化的趨勢上。比如：

 

使用堆表結構的時候，資料行是隨機進行插入操作，這樣引起資料表相同值離散程度高。這樣，索引的聚集因子clustering_factor就是顯著提高。這樣的索引，在使用的時候是有一些效能問題的。（詳細闡述參見：http://space.itpub.net/17203031/viewspace-680936）；

 

 

索引從邏輯結構上是一個B*樹的結構，由分支節點和葉子節點構成。索引路徑所提供的快速搜尋，就是根據索引列鍵值大小，直接從根節點經過幾個分支節點後，快速定位到鍵值所在資料行的實體地址rowid。在DML頻繁的資料表中，B*樹的結構是不斷的進行組合和演化，當高度和分支節點很高時，會影響效能。

 

 

此外，Oracle的索引樹是不能進行節點刪除的。對應rowid的鍵值分佈在葉子節點上，一旦對應的資料行刪除，葉子節點是不會被從樹上被刪去，而是被標記為刪除。這樣，隨著DML操作的繼續，索引樹是一個不斷膨脹的物理結構。在空佔有很大儲存空間的同時，一顆較大的B*樹進行搜尋的效率也是不高的。

 

 

那麼，維護一個健康的索引，是DBA應該關注的問題。那麼，接下來就是兩個問題。首先，我們如何知道某個索引已經結構惡化？其次，惡化後的索引如何處理？也就是本篇要介紹的方法。

 

分析索引健康程度

 

在Oracle中，提供了索引分析語句analyze index，用來分析指定的索引資訊。分析後的結果可以在檢視index_stats中檢視到。

 

 

首先，我們進行資料準備。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

//構建索引結構

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> select count(*) from t;

  COUNT(*)

----------

     53338

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

//為模擬DML操作頻繁，進行一些DML操作

SQL> delete t where wner='SCOTT';

24 rows deleted

 

SQL> delete t where wner='SYSTEM';

454 rows deleted

 

SQL> delete t where mod(object_id,7)=0;

7554 rows deleted

 

SQL> delete t where length(object_name)=10;

562 rows deleted

 

SQL> insert into t select * from dba_objects where object_id>40000;

 

13787 rows inserted

SQL> commit;

 

Commit complete

//將資料維持在5萬多條；

SQL> select count(*) from t;

 

  COUNT(*)

----------

     58531

 

 

首先，進行索引分析。

 

SQL> analyze index idx_t_id validate structure;

 

Index analyzed

 

 

SQL> desc index_stats;

Name                 Type         Nullable Default Comments            

-------------------- ------------ -------- ------- -----------------------------------------------------------------------

HEIGHT               NUMBER       Y                height of the b-tree     

BLOCKS               NUMBER       Y                blocks allocated to the segment

NAME                 VARCHAR2(30) Y                name of the index 

PARTITION_NAME       VARCHAR2(30) Y       name of the index partition, if partitioned

LF_ROWS              NUMBER       Y        number of leaf rows (values in the index)

LF_BLKS              NUMBER       Y                number of leaf blocks in the b-tree

LF_ROWS_LEN          NUMBER       Y              sum of the lengths of all the leaf rows  

LF_BLK_LEN           NUMBER       Y                useable space in a leaf block           

BR_ROWS              NUMBER       Y                number of branch rows     

BR_BLKS              NUMBER       Y                number of branch blocks in the b-tree  

BR_ROWS_LEN          NUMBER    Y   sum of the lengths of all the branch blocks in the b-tree  

BR_BLK_LEN           NUMBER       Y                useable space in a branch block       

DEL_LF_ROWS          NUMBER       Y            number of deleted leaf rows in the index    

DEL_LF_ROWS_LEN      NUMBER       Y         total length of all deleted rows in the index 

DISTINCT_KEYS        NUMBER       Y                number of distinct keys in the index          

MOST_REPEATED_KEY    NUMBER  Y     how many times the most repeated key is repeated

BTREE_SPACE          NUMBER       Y   total space currently allocated in the b-tree 

USED_SPACE           NUMBER       Y    total space that is currently being used in the b-tree     

PCT_USED             NUMBER       Y                percent of space allocated in the b-tree that is being used                                                                                         

ROWS_PER_KEY         NUMBER       Y                average number of rows per distinct key                                                                                                              

BLKS_GETS_PER_ACCESS NUMBER       Y                Expected number of consistent mode block gets per row. This assumes that a row chosen at random from the table is being searched for using the index

PRE_ROWS             NUMBER       Y                number of prefix rows (values in the index)                                                                                                         

PRE_ROWS_LEN         NUMBER       Y                sum of lengths of all prefix rows                                                                                                                   

OPT_CMPR_COUNT       NUMBER       Y                optimal prefix compression count for the index                                                                                                       

OPT_CMPR_PCTSAVE     NUMBER       Y                percentage storage saving expected from optimal prefix compression                                                                                   

 

 

 

使用analyze進行分析後，就可以檢視index_stats檢視。我們檢視該檢視的描述資訊，其中包括了對索引樹的分支和葉子節點資料塊、對應行數和長度等詳細資訊。其中篇幅原因，我們關注如下查詢結果。

 

SQL> col name for a15;

SQL> select name, height, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space from index_stats;

 

NAME                HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE

--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------

IDX_T_ID                 2        256      64846        175        174          1        6316     1408032    1029261

 

 

可以清楚看出，索引idx_t_id對應的樹高度為2，共佔用了256個資料塊。對應死葉節點為6316個，總葉行為64846個，比例近似為10%。請注意：當前記錄為5萬多個，多餘的基本上都是被刪除的葉節點記錄。

 

通常，我們判斷是否索引健康，可以關注高度和死葉節點比例。如果過高的樹高度或者死節點比例過高，就可能要考慮進行索引重建。

 

 

索引處理

 

對不適合的索引，我們能進行的操作只有是將索引重建rebuild。刪除原有結構，重建結構。

 

 

SQL> alter index idx_t_id rebuild;

 

Index altered

 

SQL> analyze index idx_t_id validate structure;

 

Index analyzed

 

SQL> select name, height, blocks, lf_rows, lf_blks, br_rows, br_blks, del_lf_rows, btree_space, used_space from index_stats;

 

NAME                HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS DEL_LF_ROWS BTREE_SPACE USED_SPACE

--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------

IDX_T_ID                 2        256      58530        130        129          1           0     1048032     929877

 

 

觀察可見，葉子節點個數已經與資料行數相匹配（58530），死節點個數為0。

 

 

注意：索引的重建與否、重建方式是一個需要仔細分析和研究的問題。索引隨著資料的不斷加入、刪除而不斷成長，定期進行維護是理所當然的事情。但是一些特殊的情況下（一些文獻資料中），的確存在舊索引結構更加適合應用需求的時候。同時，rebuild一個非常大的資料表索引，會將資料表鎖住一段時間。在生產環境下，需要格外注意rebuild操作對生產環境的影響。在Oracle11g中，完善了online維護索引的功能，這些都給rebuild索引提供了一些便利。