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

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

1	int main(int argc, char* argv[])

2

{

3

int i;

4	for (i = 0; i < 0xAAAA; i++);

5

return 0;

6

}

根据所学的知识，应该会执行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
这个到底能不能用上索引呢？

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

首先构建测试环境：

01	create table test01

02

(

03	f01 int not null ,

04

f02 int ,

05	f03 int not null ,

06

f04 int

07

);

08	create index i_t01f01 on test01(f01);

09	create index i_t01f02 on test01(f02);

10

11	create table test02

12

(

13

f01 int ,

14	f02 int not null ,

15

f03 int ,

16

f04 int ,

17	constraint test02_pk primary key ( f01 )

18

);

19	create index i_t02f03 on test02(f03);

20

21	create table test03

22

(

23

f01 int ,

24

f02 int ,

25

f03 int ,

26

f04 int

27

);

28	create index i_t03f01 on test03( f01 );

29	create index i_t03f02 on test03( f02 );

30	create index i_t03f03 on test03( f03 );

31	create index i_t03f04 on test03( f04 );

然后是测试数据

01	-- SQL Server 2000/2005

02	declare @i int

03

set @i=0

04	while @i<3000

05

begin

06	set @i = @i+1 ;

07	insert into test01 values ( @i , @i , @i ,@i );

08	insert into test02 values ( @i , @i , @i ,@i );

09	if @i % 750 = 1 begin

10	insert into test03 values ( @i , @i , null ,null );

11

end

12	else begin

13	insert into test03 values ( @i , null , @i ,null );

14

end

15

end

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

01	select count(*)   from test01 ;

02	select count(f01) from test01 ;

03	select count(f02) from test01 ;

04	select count(f03) from test01 ;

05	select count(f04) from test01 ;

06

07	select count(*)   from test02 ;

08	select count(f01) from test02 ;

09	select count(f02) from test02 ;

10	select count(f03) from test02 ;

11	select count(f04) from test02 ;

12

13	select * from test03 where f01 is null;

14	select * from test03 where f02 is not null;

15	select * from test03 where f03 is null;

16	select * from test03 where f04 is not null;

 

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

 

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

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

01

select

02	count(*) count_all,

03	count(f01) count_f01,

04	count(f02) count_f02

05

from

06

(

07	select 1 f01 , 2 f02

08

union all

09	select null  , 2

10

union all

11	select null  , null

12

) a

13

14	count_all   count_f01   count_f02

15	----------- ----------- -----------

16	3           1           2

17	警告: 聚合或其他 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等信息。

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