在Stackoverflow上有一个关于表扫描和索引扫描的关注度很高的问题

http://stackoverflow.com/questions/18764/whats-the-difference-between-a-table-scan-and-a-clustered-index-scan

 

提问:

既然表扫描和聚集索引扫描本质上都是扫描获取表中的所有记录,为什么聚集索引扫描会更好呢?

 

回答:

没有聚集索引的表(堆表),数据页并没有放在一起
聚集索引的表,数据页是以双向链表的形式链接在一起。在做顺序扫描时会快一些。但是在更新、插入和删除时,就需要对数据页多做处理了
总结:

  • 例子中的扫描所有记录,聚集索引表比堆表要快一些
  • 对于使用符合聚集索引的where条件进行的查询,会很快,因为已经排好序了,不需要扫描整个表
  • 对于使用不符合聚集索引的条件查询时,和扫描所有的记录一样。
  • 对于插入、更新和删除操作,堆表就更快了,因为它不需要对索引页排序做特殊的处理

以上情况是针对有索引和没有索引情况的一些对比,但是在大多数情况下,我们建表时会设置主键,这时候主键就默认为表的聚集索引。此外,还会对经常作为查询条件的列单独设置索引,即非聚集索引。

对于表查询,在执行计划当中会分表扫描、索引扫描、索引查找,查找效率为

聚集索引查找 > 非聚集索引查找 > 索引扫描 > 表扫描

下面我会用简单的例子比较一下在不同索引下查询的效率情况。

 

上代码先:

if exists
(select * from sys. objects
 where object_id = object_id( 'Users') and type in ('U')
)
drop table Users
go

create table Users
(
       Id int not null,
       UserCode nvarchar (50),
       UserName nvarchar (50)
)

alter table Users add constraint pk_users_id primary key (Id)

create nonclustered index idx_users_usercode on users (UserCode)

go

 

代码中对表Users设置了主键Id,和非聚集索引列UserCode。

测试一:

使用习惯性的SQL查询语句。

分为不使用查询条件、根据聚集索引查找、根据非聚集索引查找、根据非索引列进行查找

 

select * from Users
select * from Users where Id = 1
select * from Users where UserCode = '01'
select * from Users where UserName = ' 张三'

查看执行计划

全表查询如何走索引_聚集索引

 

可以看到,除了根据主键列查找为聚集索引查找,其它均为聚集索引扫描

尤其是非聚集索引列,已经设置了索引,为什么还是扫描呢?

 

我们先看下一个实验,设置查询结果所用到的列

同样的查询条件,不同的是只查询设置为索引的列Id和UserCode

select Id ,UserCode from Users
select Id ,UserCode from Users where Id = 1
select Id ,UserCode from Users where UserCode = '01'
select Id ,UserCode from Users where UserName = ' 张三 '

再看一下执行计划

全表查询如何走索引_非聚集索引_02

这时候非聚集索引的查找生效。由此可见,索引的设置与查询列也同样有关系。

当然,在实际项目中,经常需要查询很多列,但又不可能为每个列建立索引。就造成了实际的执行计划中,还会再进行索引扫描。

 

参考资料:

堆 http://msdn.microsoft.com/en-us/library/ms188270(SQL.105).aspx

聚集索引 http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

非聚集索引 http://msdn.microsoft.com/en-us/library/ms177484(v=sql.105).aspx