[原]关于数据库是否使用索引的讨论，我想说的

开篇之前，扯点题外话，大家认为以下这段C程序会进行多少次条件判断呢？

int main(int argc, char* argv[])
{
    int i;	
    for (i = 0; i < 0xAAAA; i++);	
    return 0;
}

根据所学的知识，应该会执行0xAAAA+1（43690+1）次 i < 0xAAAA的条件判断，但是，在Microsoft Visual C++ 6.0，程序编译成 release 版本后的汇编代码如下：

00401011 xor eax,eax
00401013 ret

通过观察这个程序的汇编码我们发现，编译器发现程序的执行结果不会影响任何寄存器变量，就将这个循环优化掉了，我们在汇编码里面没有看到任何和循环有关的部分。这两句汇编码仅仅相当于 return 0; （该例子出自囫囵C语言）

 

言归正传，在金色海洋的大作《为or、in 平反——or、in到底能不能利用索引？》中提到了大家比较关心的SQL语句写法将影响是否会索引的问题，很多朋友提出了宝贵中的意见和见解。其中有一位朋友提出了这样一个问题：

select count(*) from tableName
这个到底能不能用上索引呢？

就这个问题，我做了如下几个测试，同时也希望借助几个例子说明我的观点。

首先构建测试环境：

create table test01 
(
  f01 int not null ,
  f02 int ,
  f03 int not null ,
  f04 int 
);
create index i_t01f01 on test01(f01);
create index i_t01f02 on test01(f02);

create table test02 
(
  f01 int ,
  f02 int not null ,
  f03 int ,
  f04 int ,
  constraint test02_pk primary key ( f01 )
);
create index i_t02f03 on test02(f03);

create table test03
(
  f01 int ,
  f02 int ,
  f03 int ,
  f04 int 
);
create index i_t03f01 on test03( f01 ); 
create index i_t03f02 on test03( f02 );
create index i_t03f03 on test03( f03 ); 
create index i_t03f04 on test03( f04 );

然后是测试数据

-- SQL Server 2000/2005
declare @i int 
set @i=0 
while @i<3000 
begin 
    set @i = @i+1 ;
    insert into test01 values ( @i , @i , @i ,@i );
    insert into test02 values ( @i , @i , @i ,@i );
    if @i % 750 = 1 begin
        insert into test03 values ( @i , @i , null ,null );
    end 
    else begin
        insert into test03 values ( @i , null , @i ,null );
    end
end 

以下是测试的SQL语句，一共三组，很简单：

select count(*)   from test01 ;
select count(f01) from test01 ;
select count(f02) from test01 ;
select count(f03) from test01 ;
select count(f04) from test01 ;

select count(*)   from test02 ;
select count(f01) from test02 ;
select count(f02) from test02 ;
select count(f03) from test02 ;
select count(f04) from test02 ;

select * from test03 where f01 is null;
select * from test03 where f02 is not null;
select * from test03 where f03 is null;
select * from test03 where f04 is not null;

 

在往下看之前，建议先估计一下这些语句的执行计划。

 

我们先看一下SQL Server 2005 的第一组测试结果：

为什么 count(*)  和 count(f03) 都使用f01列上面的索引呢？大家先看看以下这个语句：

select 
  count(*) count_all,
  count(f01) count_f01,
  count(f02) count_f02
from 
( 
  select 1 f01 , 2 f02
  union all
  select null  , 2
  union all
  select null  , null
) a

count_all   count_f01   count_f02
----------- ----------- -----------
3           1           2
警告: 聚合或其他 SET 操作消除了空值。

上面的语句，说明count(字段) 是忽略null 的，count(*) 就是表中记录的数量，count(*) 和 count(字段)的语义是不同的，我记得以前园子里也讨论过这个问题。其实只要细心就会发现 not null 字段 f01 的 count(f01) 必然等于 count(*)，count(*)= count(f01)，通过快速扫描索引i_t01f01就能回答count(f01)=?这个问题，当然也能回答 count(*) = ? 这个问题了 ，所以优化器为我们“优化”出了这样一个貌似离奇的执行计划。

f03没有索引，但是count(f03)却使用 f01 上的索引就很容易理解了吧。 我称这种现象为“等价变换。

 

再看看第二组结果：

主键的数量肯定等于表中行的数量，为什么count(*) 不用主键的聚集索引而去使用f03的索引呢？聚集索引的叶子结点里面包含了行数据，而非聚集索引的叶子节点包含索引的数据还有行的ID（SQL Server中的RID，Oracle中的ROWID），索引越小，扫描起来需要访问的数据越少，因此，可以理解为什么不使用聚集索引了，优化器还是很会“避重就轻”的啊。

非聚集索引示意图

聚集索引结构示意图

 

看到这里，可能大家都觉得老是在not null 约束影响index行为上作文章，我们再看看网上流传着的一个传说——“is null 和 is not null 将会导致索引失效”，事实如此吗？

我们看看第三组测试结果：

 

清一色的索引查找，熟悉Oracle的朋友一定会对这个现象觉得非常不可思议。

事实上SQL Server的索引是包含了null 值，而Oracle的索引是不包含null值的，估计“is null 和 is not null 将会导致索引失效”这个传说是从Oracle开始发迹，后来被生搬硬套到SQL Server中了，然后讹传至今。

如果大家有兴趣，可以尝试修改 @i % 750 = 1 这一个条件从而改变一下test03中f02和f03这两列中null值的比率，再看看执行计划。

 

回到数据库是否使用索引这个问题上，优化器为我们进行了“等价变换”，“避重就轻”，还有可恶的null捣乱，导致平时认为很简单的select count(*/字段) from table 查询都出现了令人难以预料的执行计划，而实际上优化器做的事情比我们想象中的要多得多，Oracle中的物化视图还可改写查询，通过观察SQL语句而推断执行计划是很不现实的，引用冯大辉的《提问的智慧[Oracle版]》中的第五点：

5. SQL性能问题，列出当前SQL,以及执行计划。同时应提供表结构，index情况，Table的record数。CBO/RBO,table是否analyze等信息。

纠缠于“索引谓词白名单”和“索引谓词黑名单”毫无意义，把语句放在数据库中跑一下，出个执行计划，然后再拿出来讨论才是明智之举。