关于索引的一点知识
时间:2014-12-08 14:22 来源:linux.it.net.cn 作者:IT
索引可能是我们对于数据库性能优化最常用的一个手段。这里简单说下里面的几个方面的问题。
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有效的多。
上述是几点拙见，忘路过之高人不吝指教。
(责任编辑：IT)
| 索引可能是我们对于数据库性能优化最常用的一个手段。这里简单说下里面的几个方面的问题。 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有效的多。 上述是几点拙见，忘路过之高人不吝指教。 (责任编辑：IT) |