|
使用索引是我们面对海量数据搜索是一种常用的手段。通过有效的索引访问，可以使我们更快的访问到需要的数据，减少物理、逻辑IO，从而提高系统性能。在CBO时代，Oracle对于提交SQL的执行路径是有所选择的。一个select是走Index还是走Full Table Scan，或者别的路径，要根据Oracle对表列的统计信息收集结果加以计算出的执行计划成本而确定。在计算索引成本的公式中，索引的clustering factor是一个重要参考信息。 简单的说，clustering factor就是反映数据表行存储有序程度与索引有序程度对照的指标。如果这个值越大，说明在进行索引搜索是，需要获取的数据块数量越多，从而进行逻辑物理读的次数也就越多，相应消耗的成本Cost越高。反之，对应的成本也就越低。 原理上，可以这么理解。当我们使用索引进行查询的时候，获取到叶节点最后是符合条件的一系列ROWID，代表对应数据结果所在的物理地址。在根据ROWID所对应的位置，读取对应的数据块。如果指定的数据块（Date Block）已经存在于SGA中的Buffer Cache，就直接读取（进行逻辑读）。如果不存在于Buffer Cache，就需要先从物理存储上把数据块读取到内存SGA里，之后再进行读取。（物理读+逻辑读）。在这个过程中，我们通常倾向于减少物理读和逻辑读的次数。如果我们要获取的数据都是在同样块或者尽可能少的数据块里，那么我们索引的执行效率较高。如果有一个查询，虽然执行路径中包括了索引，但是将全表所有的数据块都读取在SGA里，其成本也是值得商榷的。 Clustering Factor就是衡量索引执行效率成本的一个重要指标。我们通常会希望数据表中排列的顺序与索引列排序的顺序一致。但是，在使用一些存储结构（随机存储结构），索引和数据分开存储，数据行一般为随机存储。这样，Clustering Factor是一个不断退化的过程。下面，我们通过实验，观察clustering factor对于数据索引的影响，以及优化的方法。 1、 构建实验环境 我们选择Oracle版本为10gR2。 //获取当前版本 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 – Production Executed in 0.04 seconds dba_object构建一张百万级数据表，构造脚本如下： //插入脚本 declare i number; begin for i in 1..10 loop insert /*+ append */ into t select * from dba_objects order by i; commit; end loop; end; 插入大约一百万条数据。 SQL> select count(*) from t; COUNT(*) ---------- 1008000 Executed in 4.987 seconds 2、加入数据索引，收集统计信息 我们构建的数据表基本是无序的，数据字典分析情况如下： SQL> select owner, segment_name, blocks, extents, bytes from dba_segments where wner='SYS' and segment_name='T'; OWNER SEGMENT_NA BLOCKS EXTENTS BYTES ------- ---------- ---------- ---------- ---------- SYS T 14208 85 116391936 Executed in 0.221 seconds 构建t上的object_id索引列。 SQL> create index ind_t_id on t(object_id); Index created Executed in 17.515 seconds SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); PL/SQL procedure successfully completed Executed in 21.29 seconds 其索引的字典信息。 SQL> select owner, segment_name, segment_type,blocks, extents, bytes from dba_segments where wner='SYS' and segment_name=upper('ind_t_id'); OWNER SEGMENT_NA SEGMENT_TYPE BLOCKS EXTENTS BYTES ------- ---------- ------------------ ---------- ---------- ---------- SYS IND_T_ID INDEX 2304 33 18874368 Executed in 0.17 seconds 发现，数据表T本身占据空间超过116兆，对应的索引为18.9兆左右。 3、未进行数据表重构前，执行效率分析 在未进行重构之前，数据表T对应索引ind_t_id的clustering_factor信息如下： SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID'; OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS ------- --------------- ----------------- -------------------- SYS IND_T_ID 1008000 1008000 Executed in 0.14 seconds 对索引列查询，分析精确查询和范围查询两种分析。 精确查询： SQL> select * from t where object_id=1500; 已选择20行。 已用时间: 00: 00: 00.04 执行计划 ---------------------------------------------------------- Plan hash value: 4182247035 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1860 | 23 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 23 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1500) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 3126 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed 范围查询： 当选取范围略大的时候，Oracle执行优化器，会主动拒绝执行索引。 SQL> select * from t where object_id>1000 and object_id<2000 已选择19980行。 已用时间: 00: 00: 04.85
执行计划
Plan hash value: 1601196873-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18947 | 1720K| 3092 (3)| 00:00:38 | |* 1 | TABLE ACCESS FULL| T | 18947 | 1720K| 3092 (3)| 00:00:38 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 15179 consistent gets 13567 physical reads 1484 redo size 993274 bytes sent via SQL*Net to client 15026 bytes received via SQL*Net from client 1333 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 19980 rows processed 虽然在数据列object_id上加了索引，而且查询返回的数据量不到2万行，远远少于数据表数据总量（100万）。Oracle优化器在选取路径的时候，放弃了按照索引进行搜索的方案。 同样的条件，使用Hint提示方法，强制走索引也可以查看效果。 SQL> select /*+ index(t ind_t_id) */ * from t where object_id>1000 and object_id<2000; 已选择19980行。 已用时间: 00: 00: 01.65 执行计划 ---------------------------------------------------------- Plan hash value: 4182247035 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 18947 | 1720K| 19089 (1)| 00:03:50 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 18947 | 1720K| 19089 (1)| 00:03:50 | |* 2 | INDEX RANGE SCAN | IND_T_ID | 19020 | | 45 (3)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 21586 consistent gets 244 physical reads 16892 redo size 1938406 bytes sent via SQL*Net to client 15026 bytes received via SQL*Net from client 1333 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 19980 rows processed 对比两个结果，可以看出在这个返回结果比较小（约2%）的语句中，采用索引与全表扫描差别很大。
结论：我们在object_id上加入的索引ind_t_id，其clustering factor取值为100万，与数据行相同。索引健康程度比较差。在唯一查询object_id的时候，走索引。但是进行范围查询的时候，即使结果数据量仅为2%，oracle还是放弃了索引的执行计划，选择了full table scan，说明索引健康程度有时是比加索引的技巧左右大。 4、重构数据表，优化索引质量 从索引、Clustering Factor的原理看，解决clustering Factor的核心在于数据行重排。让数据行按照索引列排序的顺序存储，效果比较好。 //重构表 SQL> create table tt as select * from t where 1=0; Table created Executed in 0.14 seconds SQL> insert /*+ append */ into tt select * from t order by object_id; 1008000 rows inserted Executed in 80.225 seconds SQL> commit; Commit complete Executed in 0 seconds SQL> truncate table t; Table truncated Executed in 0.421 seconds SQL> insert /*+ append */ into t select * from tt; 1008000 rows inserted Executed in 29.182 seconds SQL> commit; Commit complete Executed in 0.03 seconds SQL> alter index ind_t_id rebuild; Index altered Executed in 10.665 seconds 获取统计数据，并且查看索引的健康程度。 //统计信息 SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); PL/SQL procedure successfully completed Executed in 19.157 seconds SQL> select owner, index_name, clustering_factor, num_rows from dba_indexes where wner='SYS' and index_name='IND_T_ID'; OWNER INDEX_NAME CLUSTERING_FACTOR NUM_ROWS ------- --------------- ----------------- -------------------- SYS IND_T_ID 13831 1008000 Executed in 0.09 seconds 可以看到，我们按照object_id进行表重构之后，索引的clustering factor缩小为13831，只有num rows的约2%。健康程度优化。 5、重新执行搜索，查看执行计划 进行精确查询： SQL> select * from t where object_id=1500; 已选择20行。 已用时间: 00: 00: 00.08 执行计划 ---------------------------------------------------------- Plan hash value: 4182247035 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 1860 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1860 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T_ID | 20 | | 3 (0)| 00:00:01 | -------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=1500) 统计信息 ---------------------------------------------------------- 381 recursive calls 0 db block gets 62 consistent gets 1 physical reads 116 redo size 3126 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 20 rows processed 对比重构前后情况，发现CPU成本上有很明显的变化，其中Index Range Scan的成本没有发生变化，一直为3。而Table Access By Index ROWID有较大的变化，从原有的23下降到4。这一现象说明搜索索引的成本没有变化，但是根据索引返回ROWID，查找数据表块的成本有所小，和预计情况相同。 范围查询： SQL> select * from t where object_id>1000 and object_id<2000; 已选择19980行。 已用时间: 00: 00: 01.22 执行计划 ---------------------------------------------------------- Plan hash value: 4182247035 ------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18980 | 1723K| 308 (1)| 00:00:04 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 18980 | 1723K| 308 (1)| 00:00:04 | |* 2 | INDEX RANGE SCAN | IND_T_ID | 19031 | | 45 (3)| 00:00:01 | ----------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 2931 consistent gets 249 physical reads 0 redo size 1938406 bytes sent via SQL*Net to client 15026 bytes received via SQL*Net from client 1333 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 19980 rows processed 从返回的结果看，进行数据表重构后，我们的查询效率大大提升，原有Oracle选择全表扫描的情况已经不再出现，直接选择了Index搜索。而Index搜索的效果也有相当提升，CPU成本只有308，消耗时间00:00:04。数据逻辑读块只有2931，物理读只有249。两次试验的详细对比如下：
6、结论 通过上面的实验，我们可以看到：clustering factor是索引健康程度的一个重要指标。我们有的时候，虽然在数据表的指定列中加入了索引，但是因为索引的健康程度不高，可能效率很差。我们经常可以听说，如果查询比例在15%或者XXX%以下的时候，我们要加索引，但是在我们的例子中，数据只有2%，但是还是成本高于FTS（Full Table Scan），不会选择索引路径。所以，经常性的维护我们的索引是很重要的。 但是，本文书写的目的绝不是让将重构表成为一个常态任务，重构表只是一个手段。在实际中，是有一些问题的。 首先，重构表的行顺序是需要选择的。一个需要我们规划的表，其上常常不止一个索引，一个索引的clustering factor高效，可能就意味着另一个索引的恶化。所以一定要慎用。 其次，在一些物理性能不是很好的环境下做，重构数据表可能是一个比较繁重的工作。 最后，clustering factor总的趋势一般都是不断恶化（选择随机存取），合理的规划，使用其他如分区等技术也是解决我们最终问题的方案。这部分可能在海量数据库中应用比较多。 所以，在关注有没有索引的同时，也要关注我们索引的健康程度。 (责任编辑：IT) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||