2.3.4    数据存储的影响

1.  聚簇因子较好的情况

接下来通过一个实例来验证一下聚簇因子对索引性能的影响吧。先考察聚簇因子较好的情况,此时CLUSTERING_FACTOR的数值将会比较小。下面例子中,我们按照前面提到的“111222333”的方式进行数据入库。

步骤1  清空表alex_t03的数据:

SQL> truncate table alex_t03;

步骤2  按照“11122233”的方式重新插入10万行数据, NUM_DISTINCT值控制为200:

SQL> declare
2    seq number := 1;
3  begin
4    for i in 1 .. 100000 loop
5      insert into alex_t03 values (i, seq, 'alex');
6      if mod(i, trunc(100000 / 200)) = 0 then
7        seq := seq + 1;
8      end if;
9    end loop;
10    commit;
11  end;
12  /

步骤3  重新收集一下表和索引的统计信息,这里不关注直方图信息:

SQL> exec dbms_stats.gather_table_stats('alex', 'alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'pk_alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'idx_alex_t03_col1')

主键列的数据入库是完全有序的,可以将主键索引视为一个理想的聚簇因子情况,将col1列的索引聚簇因子与之对比一下,可以发现CLUSTERING_FACTOR数值上是差不多的,索引IDX_ALEX_T03_COL1的聚簇因子比较好,远小于数据行数,接近数据块数。统计结果如下所示:

SQL> select i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  from user_tables t, user_indexes i
3  where t.table_name=i.table_name and i.table_name='ALEX_T03';

INDEX_NAME               BLOCKS    NUM_ROWS CLUSTERING_FACTOR
------------------ ---------- ---------- -----------------
PK_ALEX_T03                  370      102127                  254
IDX_ALEX_T03_COL1            370      102127                  324

当进行col1索引列的查询的时候,毋庸置疑的是进行索引扫描了,而且COST开销相对较小,也就是说索引利用比较高效。示例如下所示:

SQL> select * from alex_t03 where col1=1;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   655 |  8515 |     5   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T03          |   655 |  8515 |     5   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T03_COL1 |   655 |       |     2   (0)|
--------------------------------------------------------------------------------------

Statistics
-----------------------------------
0  db block gets
173  consistent gets
29  physical reads
16  sorts (memory)
0  sorts (disk)
500ows processed

2.  聚簇因子较差的情况

如果按照“123123123”的方式进行数据入库是不是会出现另一个极端的情况呢?同样通过一个例子来看一下吧,仍然使用alex_t03表,仅仅改变数据入库顺序。

步骤1  清空alex_t03的数据:

SQL> truncate table alex_t03;

步骤2  按照“123123123”的方式重新插入10万行数据, NUM_DISTINCT值控制为200:

SQL> declare
2    seq number := 1;
3  begin
4    for i in 1 .. 100000 loop
5      insert into alex_t03 values (i, seq, 'alex');
6      if mod(i, trunc(100000 / 200)) = 0 then
7        seq := seq + 1;
8      end if;
9    end loop;
10    commit;
11  end;
12  /

步骤3  重新收集一下表和索引的统计信息,这里不关注直方图信息:

SQL> exec dbms_stats.gather_table_stats('alex', 'alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'pk_alex_t03')
SQL> exec dbms_stats.gather_index_stats('alex', 'idx_alex_t03_col1')

如预料之中一样,CLUSTERING_FACTOR变得很大,远远大于数据块数,更趋于接近数据行数,显示这是一个比较差的聚簇因子的情况。统计结果如下所示:

SQL> select i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  from user_tables t, user_indexes i
3  where t.table_name=i.table_name and i.table_name='ALEX_T03';

INDEX_NAME               BLOCKS    NUM_ROWS CLUSTERING_FACTOR
------------------ ---------- ---------- -----------------
PK_ALEX_T03                  370      102127                  254
IDX_ALEX_T03_COL1           370      102127                50759

再进行一次同样查询操作,发现优化器放弃了索引扫描而进行了全表扫描,这意味着此时全表扫描的开销更小。从下例的统计信息看到,全表扫描的物理读和逻辑读都比较好聚簇因子的情况要大得多,甚至出现了16次额外的内存排序操作。

SQL> select * from alex_t03 where col1=1;
-------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   673 |  8749 |    85   (3)|
|*  1 |  TABLE ACCESS FULL| ALEX_T03 |   673 |  8749 |    85   (3)|
-------------------------------------------------------------------

Statistics
-----------------------------------
0  db block gets
433  consistent gets
302  physical reads
16  sorts (memory)
0  sorts (disk)
500  rows processed

即便如此,优化器仍认为全表扫描更优,那强制走一下索引扫描看看情况如何呢?如下例所示,比较索引扫描和全表扫描的统计信息似乎相差无几,但是COST开销、索引扫描大了很多。

SQL> select /*+index(alex_t03 idx_alex_t03_col1)*/ *
2  from alex_t03 where col1=1;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   673 |  8749 |   344   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| ALEX_T03          |   673 |  8749 |   344   (0)|
|*  2 |   INDEX RANGE SCAN          | IDX_ALEX_T03_COL1 |   673 |       |     2   (0)|
--------------------------------------------------------------------------------------
Statistics
-----------------------------------
0  db block gets
409  consistent gets
274  physical reads
16  sorts (memory)
0  sorts (disk)
500  rows processed

此时,如果再对比聚簇因子较好和较差两种情况下的索引扫描情况,很容易就验证了我们之前的说法,较好的聚簇因子会带来较大的I/O优势。

在实际应用中,表中数据的入库有一定的随机性,其顺序是我们无法控制的,这意味着索引的聚簇因子也很难得到控制。这对我们在索引设计上也是一个较大的挑战,在新建索引之前,应该尽可能的了解业务数据的存储特性,保证数据存储顺序有一定的规律,即拥有较小的聚簇因子,索引才能高效使用。如果不能保证较小聚簇因子,要想通过后期的维护来修正,那基本上是不可能实现。

综合以上内容,我们可以来总结一下了,一个设计优秀的索引应该具有以下特点:

q  具有较好的选择度,可能参考理想选择度来界定;

q  索引列的数据分布足够趋于均匀化;

q  具有较小的聚簇因子。

提示

当不知道如何把握的时候,可以参考一下唯一索引,其从各方面来思考都是最高效的,尽可能地使你的索引接近于它。

Trackback

no comment untill now

Add your comment now

切换到手机版