全表查询如何走索引

转载

doscommand 2024-10-22 14:06:50

文章标签 全表查询如何走索引聚集索引非聚集索引主键 文章分类 数据仓库大数据

在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