ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 分析索引快速获取索引信息

分析索引快速获取索引信息

原创 Linux操作系统 作者:realkid4 时间:2011-03-14 13:15:30 0 删除 编辑

 

索引的健康状态是我们非常关注的一个问题。健康的索引可以在保证有效使用空间的基础上,提供很好的搜索性能。同样,一些非健康的索引也会一定程度上影响系统运行的效率。

 

 

在一些数据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索引提供了一些便利。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-689379/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7676526