代码改变世界

表扫描和索引扫描

2012-09-21 21:00  imyang  阅读(988)  评论(2)  编辑  收藏  举报

 

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

 
提问：
既然表扫描和聚集索引扫描本质上都是扫描获取表中的所有记录，为什么聚集索引扫描会更好呢？
 
回答：
没有聚集索引的表（堆表），数据页并没有放在一起
聚集索引的表，数据页是以双向链表的形式链接在一起。在做顺序扫描时会快一些。但是在更新、插入和删除时，就需要对数据页多做处理了
总结：
  • 例子中的扫描所有记录，聚集索引表比堆表要快一些
  • 对于使用符合聚集索引的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 = ' 张三 '

 再看一下执行计划

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

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