[原]从Clustering Factor说起

近日在看《Oracle 9i & 10g编程艺术：深入数据库体系结构》这本书的时候，发现一个叫做Clustering Factor的概念，该因子用于反映利用索引进行区间扫描的成本，说得可能有点抽象，我们做一个有趣的实验说明一下吧：

第一步，在Oracle中建立一个表Source，该表很简单，就两个字段，脚本如下：

create table Source 
(
 x int ,
 y int 
);

第二步，向Source里面插入100万条记录，其中x从1到1000000中按顺序取值，而y则是以随机数。

begin
    for i in 1 .. 1000000
    loop
         insert into source(x,y) values ( i , to_char(dbms_random.random,'99999999999999999') );
    end loop;
end;
commit;

第三步，基于Source表的数据，再创建两张表tab01、tab02，并为这两个表的x字段添加索引，最后分析统计一下信息。

建表语句如下：

create table tab01 as select * from source;
create table tab02 as select * from source order by y; 
create index idx01 on tab01(x); 
create index idx02 on tab02(x); 
begin 
  dbms_stats.gather_table_stats( user , 'tab01' , cascade=>true ); 
  dbms_stats.gather_table_stats( user , 'tab02' , cascade=>true ); 
end;

第四步，运行两个查询语句（将Autotrace打开）。

select avg(y/(x+1)) from tab01 where x between 10000 and 30000;
select avg(y/(x+1)) from tab02 where x between 10000 and 30000;

这两个查询查询非常简单，执行的速度也很快，我在普通PC机中的虚拟机（有点拗口）上面执行用了不到1秒就搞定了。

但是，这两个查询的执行计划，还有consistent gets的数字是不一样的。

第一个查询：

game@ora10g(oracle01) SQL> select avg(y/(x+1)) from tab01 where x between 10000 and 30000;

Elapsed: 00:00:00.02

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    12 |   143   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE              |       |     1 |    12 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB01 | 19971 |   234K|   143   (1)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IDX01 | 20005 |       |    48   (3)| 00:00:01 | **********
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets

          0  physical reads

第二个查询

game@ora10g(oracle01) SQL> select avg(y/(x+1)) from tab02 where x between 10000 and 30000;

Elapsed: 00:00:00.04

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    12 |   566   (5)| 00:00:07 |
|   1 |  SORT AGGREGATE    |       |     1 |    12 |            |          |
|*  2 |   TABLE ACCESS FULL| TAB02 | 20145 |   236K|   566   (5)| 00:00:07 |**********
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2417  consistent gets

          0  physical reads

大家应该发现，两个语句的执行计划不一样，已经查询成本也不一样了吧，我们再使用提示，强制使第二个查询使用索引 idx02 ，得到如下结果。

game@ora10g(oracle01) SQL> select /*+ index( tab02 idx02 ) */ avg(y/(x+1)) from tab02 where x between 10000 and 30000;

Elapsed: 00:00:00.05

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    12 | 20203   (1)| 00:04:03 |
|   1 |  SORT AGGREGATE              |       |     1 |    12 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB02 | 20145 |   236K| 20203   (1)| 00:04:03 |
|*  3 |    INDEX RANGE SCAN          | IDX02 | 20145 |       |    48   (3)| 00:00:01 |**********
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        136  recursive calls
          0  db block gets
      20058  consistent gets

          0  physical reads

从执行时间上来说，没看出多大的问题，但是从 consistent gets 来说，我们给Oracle发出了一个“错误”的提示。

为什么同样的数据同样的数据结构，却有如此大的差异呢？细心的你，可能已经发现我们的建表语句存在着一定的差异。

create table tab01 as select * from source;
create table tab02 as select * from source order by y; 

tab01中的行是按照Source表中行的顺序排列的，相邻的行几乎是在同一个数据块中，而tab02的行是按照y列排序的，也就是乱须的，简单的说tab01和tab02虽然数据是相同的，但是物理组织上是不相同的。

在物理组织上的差异造成了这么大的性能差异令我非常惊讶，我们再看看两个索引的填充因子是什么？

执行以下查询：

select 
    idx.index_name, 
    tab.table_name,
    tab.num_rows,
    tab.blocks,
    idx.clustering_factor
from 
    user_indexes idx inner join user_tables tab
on idx.table_name = tab.table_name
order by table_name;

我这里的结果是：

INDEX_NAME           TABLE_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
-------------------- ------------------------------ ---------- ---------- -----------------
IDX01                TAB01                              995426       2459              4723
IDX02                TAB02                             1003471       2459            999596

当Clustering Factor的值越高，进行索引区间扫描的成本越高，物理组织上更加“零散”。

这个例子可以从某种程度上解析：“为什么同一份数据在不同机器上跑，性能不一样？”。

 

如此类推，我觉得同样的实验，在SQL Server上也应该可以重演。于是类似地，我做了如下的一个实验：

第一步，建表，语句没变：

create table Source 
(
 x int ,
 y int 
);

第二步，向 Source 表里面插入实验数据，100万条。

begin tran;
declare @n int;
set @n = 0;
while @n < 1000000
begin 
 insert into source(x,y) values ( @n , checksum(newid()) );
 set @n=@n+1;
end;
commit;

第三步，建表，建索引

select * into tab01 from Source;
select * into tab02 from Source order by x;
create index idx01 on tab01(x); 
create index idx02 on tab02(x); 

第四步，运行两个查询语句（ set statistics time on; set statistics io on; ）。

select avg(y/(x+1)) from tab01 where x between 10000 and 11000;
select avg(y/(x+1)) from tab02 where x between 10000 and 11000;

由于 10000 到 30000 的取值范围SQL Server的查询优化器使用了表扫描，所以特意改了一下。

查询计划和统计信息却是一样的。

统计信息都是：

表 'tab01'。扫描计数 1，逻辑读取 1007 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'tab02'。扫描计数 1，逻辑读取 1007 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

看来Oracle的那一套理论不适合于SQL Server中啊。

引用Thomas Kyte在《Oracle 9i & 10g编程艺术：深入数据库体系结构》中的一句话：

首先，所有数据库本质上都不同。其次，为一个新的数据库平台设计应用时，对于数据库如何工作不能做任何假设。学习每一个新数据库时，应该假设自己从未使用过数据库。在一个数据库中能做的事情在另一个数据库中可能没有必要做，或者根本不能做。

 

我从04年开始写.net程序，刚开始的时候发现 C# 和 Java 多么相似（哥写的不是java，写的是C#），总觉得那堆Java 写的 Spring、Hibernate 框架 C# 都能做出来，不久NHibernate，IBatis.net 出来了。慢慢的随着代码写多了，对.net的感觉转变为“.net 好用，java不好用，所以搞.net的人，懒得搞框架”。前段时间看了大牛 Jeffrey Zhao 的我对NHibernate的感受（1）：对延迟加载方式的误解 和 我对NHibernate的感受（2）：何必到处都virtual 今天才大有感悟，学习一种语言不应被另一种语言禁锢思想，学习语言的最高境界应该是弄清什么事情在一种语言中能做，而在另一种语言中可能没有必要做或者根本不能做，而不是纠缠在哪种更有qian途的问题上，大牛 Jeffrey Zhao 即使没有达到这种境界也差不了多少啦。