ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Clustering Factor——索引的成本指标

Clustering Factor——索引的成本指标

Linux操作系统 作者:kingsql 时间:2013-11-05 18:35:30 0 删除 编辑

使用索引是我们面对海量数据搜索是一种常用的手段。通过有效的索引访问,可以使我们更快的访问到需要的数据,减少物理、逻辑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%)的语句中,采用索引与全表扫描差别很大。

 

对比项目

全表扫描

强制索引

cpu cost

3092

19083

time

00:00:38

00:03:50

consistent get

15179

21586

physical reads

13567

244

redo size

1484

16892

 

结论:我们在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。两次试验的详细对比如下:

 

对比项目

未重构表

重构后

全表扫描

(范围)

强制索引

(范围)

准确查询

索引搜索

(范围)

准确查询

cpu cost

3092

19083

23

308

4

time

00:00:38

00:03:50

00:00:01

00:00:04

00:00:01

consistent get

15179

21586

25

2931

62

physical reads

13567

244

0

249

1

redo size

1484

16892

0

0

116

 

 

6、结论

通过上面的实验,我们可以看到:clustering factor是索引健康程度的一个重要指标。我们有的时候,虽然在数据表的指定列中加入了索引,但是因为索引的健康程度不高,可能效率很差。我们经常可以听说,如果查询比例在15%或者XXX%以下的时候,我们要加索引,但是在我们的例子中,数据只有2%,但是还是成本高于FTS(Full Table Scan),不会选择索引路径。所以,经常性的维护我们的索引是很重要的。

 

但是,本文书写的目的绝不是让将重构表成为一个常态任务,重构表只是一个手段。在实际中,是有一些问题的。

首先,重构表的行顺序是需要选择的。一个需要我们规划的表,其上常常不止一个索引,一个索引的clustering factor高效,可能就意味着另一个索引的恶化。所以一定要慎用。

其次,在一些物理性能不是很好的环境下做,重构数据表可能是一个比较繁重的工作。

最后,clustering factor总的趋势一般都是不断恶化(选择随机存取),合理的规划,使用其他如分区等技术也是解决我们最终问题的方案。这部分可能在海量数据库中应用比较多。

 

所以,在关注有没有索引的同时,也要关注我们索引的健康程度。

 

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

请登录后发表评论 登录
全部评论
OCM

注册时间:2013-02-28

  • 博文量
    231
  • 访问量
    834129