(转)关于索引的一点知识

索引可能是我们对于数据库性能优化最常用的一个手段。这里简单说下里面的几个方面的问题。

 

1、 索引是一个对象；

索引是一个独立的数据库对象，和数据表table一样。在Oracle中，数据库对象object都是通过段segment结构表示。我们在数据字典dba_segment中，可以使用索引的名称搜索出与segment_name相等的字典项目。

 

SQL> select segment_name, segment_type, bytes, blocks, extents from dba_segments where segment_name='IND_T_OWNER_NAME';

 

SEGMENT_NAME        SEGMENT_TYPE        BYTES    BLOCKS   EXTENTS

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

IND_T_OWNER_NAME    INDEX             3145728       384        18

 

上述就说明，该数据段对象IND_T_OWNER_NAME，对应的类型为索引。占据空间为3145728 bytes，约为3.16M的空间，对应384个数据块，包括在18个数据区中。

 

在堆表结构中，数据表和索引是可以分开进行存储的。通常，从性能角度考虑我们常将两者放置在不同的Tablespace中，这样做的目的主要是为了分散物理IO。

 

 

2、 索引是有代价的

索引的建立通常是为提高数据检索的效率。使用索引搜索的一个目的是避免全表扫描FTS，提高搜索效率。进行索引搜索时，实际上先进行一次索引结构读取，获取查询结果所在数据块的物理地址ROWID，之后进行第二次检索数据表块，直接获取到数据行信息。所以，在数据表较大，结果集较少的时候，通常进行索引检索的效率较好。反之，如果返回数据较多，例如返回整个大表的绝大部分记录行，这样往往是全表扫描的效果较好。

 

在现有的Oracle版本中，使用的CBO（基于成本的优化器）就是进行检索方案的选择确定。究竟是何种方案，要根据收集到的统计进行进行一系列的计算估算，最后确定执行计划。

 

当索引对象生效的时候，会实时保证和数据表的索引列同步。如果发生增加、修改和删除操作，索引也要进行相应的结构变化和修改，用来适应数据表结构的变化。所以，加入索引后，为了维护索引的完整性，增加、修改和删除等DML操作性能会受到一定程度的影响。

 

另一方面，作为一个独立的数据库对象，索引也是要消耗存储空间的。如果对应的数据表很大的话，相应索引的体积可以会达到一定程度。相应的，重建索引的效率也就成为不能忽视的一个问题。

 

总之，索引技术是一个使用空间和DML效率为交换，换高效检索的技术方案。

 

3、 索引的状态

 

从索引的数据字典上，我们可以看到数据索引的状态。

 

SQL> select index_name, index_type, status, leaf_blocks from dba_indexes where index_name='IND_T_OWNER_NAME';

 

INDEX_NAME                    INDEX_TYPE                 STATUS  LEAF_BLOCKS

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

IND_T_OWNER_NAME              NORMAL                     VALID           287

 

索引的状态只有两个，VALID和UNUSABLE。Valid表示当前索引正在生效，会实时保证与数据表的一致性，如果发生DML操作，其内部结构会自动进行调整。一些带有约束特定的索引，如unique，也会起到数据完整性保持的作用。

 

另一个索引状态Unusable，表示当前索引停用。索引是不会和数据表联动的更新结构，也不会起到数据完整性保持的作用。

 

实际工作中，我们可以选择暂时禁用索引功能，来提高数据表插入、修改效率。因为，在索引起作用的情况下，大量数据的DML操作会带来很多的索引更新和Redo Log的生成。这在批量数据加载的时候是不需要的。所以，可以暂时禁用索引。

 

SQL> alter index ind_t_owner_name unusable;

 

Index altered

 

查看索引状态：

 

SQL> select index_name, index_type, status, leaf_blocks from dba_indexes where index_name='IND_T_OWNER_NAME';

 

INDEX_NAME                    INDEX_TYPE                 STATUS  LEAF_BLOCKS

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

IND_T_OWNER_NAME              NORMAL                     UNUSABLE        287

 

Executed in 0.03 seconds

 

此时进行一系列的操作，是不会更新索引的。同时，一些操作，也不会走索引的执行计划。

 

SQL> select * from t where object_name='T' and wner='SYS';

 

已用时间: 00: 00: 00.26

 

执行计划

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

Plan hash value: 1601196873

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

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |

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

|  0 | SELECT STATEMENT |     |    1 |   92 |  156  (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T   |    1 |   92 |  156  (2)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

  1 - filter("OBJECT_NAME"='T' AND "OWNER"='SYS')

 

统计信息

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

       386 recursive calls

         0 db block gets

       758 consistent gets

       545 physical reads

       116 redo size

      1194 bytes sent via SQL*Net to client

       385 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         5 sorts (memory)

         0 sorts (disk)

         1 rows processed

 

如果我们强制使用hint，要求执行索引路径时，是会出现错误提示的。

 

SQL> select /*+ index(t ind_t_owner_name) */* from t where object_name='T' and wner='SYS';

 

select /*+ index(t ind_t_owner_name) */* from t where object_name='T' and wner='SYS'

 

ORA-01502: 索引 'SYS.IND_T_OWNER_NAME' 或这类索引的分区处于不可用状态

 

只有进行索引的重建rebuild，才可以实现索引状态的恢复和启用。

 

SQL> alter index ind_t_owner_name rebuild;

 

Index altered

 

Executed in 0.611 seconds

 

SQL> select index_name, index_type, status, leaf_blocks from dba_indexes where index_name='IND_T_OWNER_NAME';

 

INDEX_NAME                    INDEX_TYPE                 STATUS  LEAF_BLOCKS

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

IND_T_OWNER_NAME              NORMAL                     VALID           287

 

Executed in 0.06 seconds

 

SQL> select /*+ index(t ind_t_owner_name) */ object_name from t where object_name='T' and wner='SYS';

 

OBJECT_NAME

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

T

 

Executed in 0.02 seconds

 

默认也会将索引考虑入执行计划：

 

SQL> select * from t where object_name='T' and wner='SYS' ;

 

已用时间: 00: 00: 00.01

 

执行计划

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

Plan hash value: 1404465244

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

| Id | Operation                  | Name            | Rows | Bytes | Cost (%CPU)| Time    |

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

|  0 | SELECT STATEMENT           |                 |    1 |   92 |    2 (0)| 00:00:01 |

|  1 | TABLE ACCESS BY INDEX ROWID| T               |    1 |   92 |    2 (0)| 00:00:01 |

|* 2 |  INDEX RANGE SCAN         | IND_T_OWNER_NAME |    1 |      |    1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  2 - access("OWNER"='SYS' AND "OBJECT_NAME"='T')

 

统计信息

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

         1 recursive calls

         0 db block gets

         4 consistent gets

         0 physical reads

         0 redo size

      1198 bytes sent via SQL*Net to client

       385 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         0 sorts (memory)

         0 sorts (disk)

         1 rows processed

 

有一点额外的也要注意，如果使用的是唯一索引unique类型，在从unusable转变为valid的过程中，要进行数据列的一个检查。如果发现列的值已经不满足唯一性条件，则报错，并且索引状态不会发生变化。

 

除了手工进行索引的状态变化之外，一些管理操作，如move、分区表操作，也会影响到索引的状态。实际中可能需要进行rebuild工作，对一些比较大的数据表，rebuild工作的时间可能也会比较长。

 

 

4、 索引的监控

无论是投产之后还是开发测试中，我们都在数据表中加入了一些索引。通常我们是不能实时监视每条语句的执行计划，那么有没有一些手段可以监控索引的执行情况，发现一些不常用的索引，定位优化目标呢？

 

答案是肯定的。在oracle中，可以借助monitoring usage关键字和v$object_usage视图实现这个功能。

 

启用监控功能并且收集监控结果。

 

SQL> alter index ind_t_owner_name monitoring usage;

 

Index altered

 

 

SQL> select /*+ index(t ind_t_owner_name) */ object_name from t where object_name='T' and wner='SYS';

 

OBJECT_NAME

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

T

 

Executed in 0.01 seconds

 

SQL> select * from v$object_usage where index_name='IND_T_OWNER_NAME';

 

INDEX_NAME          TABLE_NAME MONITORING USED START_MONITORING   END_MONITORING

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

IND_T_OWNER_NAME    T         YES       YES 12/07/2010 23:33:36

 

Executed in 0.01 seconds

 

收集所有的相关视图信息，可以方便的找出哪个Index是一直没有使用过的，也就可以进一步定位到相应功能和SQL语句。

 

关闭监控功能：

 

SQL> alter index ind_t_owner_name nomonitoring usage;

 

Index altered

 

Executed in 0.01 seconds

 

 

5、 索引的管理

在最近看的一本书中，提出了一个比较新的索引管理思路，觉得值得借鉴。

 

我们建索引是一项有代价的工作，牺牲DML操作来实现索引的同步。那么，我们如果确定加什么索引，什么时候加索引。本质上还要看系统怎么使用数据表，更进一步是传入的SQL结构是一个什么样子，根据这些信息进行索引的管理。

 

同时，在DBA的工作中，要加入索引信息维护追踪的机制。那个索引对应那个模块的那个需求而建立，当这个需求变化或者弱化后，索引要随之发生变化。不能残留很多各种原因建立的索引。这样是给SQL执行计划带来很多问题。在CBO时代，积极的更新统计信息大部分时候要比强制用hint有效的多。

摘自：http://space.itpub.net/?uid-17203031-action-viewspace-itemid-681121