索引扫描与索引查找区别

转载：
http://space.itpub.net/355374/viewspace-495808

扫描与查找操作均是SQL Server从表或索引中读取数据采用的迭代器，这些也是SQL Server支持的最基本的运算．几乎在每一个查询计划中都可以找到，因此理解它们的不同是很重要的，扫描是在整张表上进行处理，而索引是在整个页级上进行处理，而查找则返回特定谓词上一个或多个范围内的数据行．
      下面让我们看一个扫描的例子（这里使用Northwind数据库）
       

 SELECT [OrderId] FROM [Orders] WHERE [RequiredDate] = '1998-03-26'

　　在Orders表中，并不存在对RequiredDate列的索引，因此，SQL Server必须读取Orders表的每一行来估计每一行的RequiredDate谓词，如果满足该谓词条件（即找到包含’1998-03-26’的记录），则返回该行数据．

        为了最大化提升性能，SQL Server尽可能地使用扫描迭代器来估计该谓词，然而，如果该谓词过于复杂或开销过大，SQL Server或许使用别的筛选迭代器来估计．以下是WHERE关键字中的文本计划的过程：

　--Clustered Index Scan(OBJECT:([Orders].[PK_Orders]),WHERE:([Orders].[RequiredDate]='1998-03-26'))

由于扫描表的每一行数据，不论满足与否，因此，其查询开销对表中的总记录数是均衡的，当表中的数据很少或满足谓词的行比较多时，采用扫描操作有效，如果表中数据量比较大或满足谓词的行较少时，使用扫描将读取更多的页面或执行更多的I/O操作来获取数据，这显而不是最有效的方法．

        下面让我们看一个关于索引查找的例子，下面的例子在OrderdDate列上创建了索引：
   

　　SELECT [OrderId] FROM [Orders] WHERE [OrderDate] = '1998-02-26'

 

         这次SQL Server能够使用索引查找来直接找到满足谓词的那些记录行，这里称该谓词为＂查找＂谓词．大多数情况下，SQL Server并不显式地估计＂查找＂谓词，而索引确保了＂查找＂操作仅返回满足的数据行，以下是＂查找＂谓词的文本计划：

　　--Index Seek(OBJECT:([Orders].[OrderDate]),SEEK:([Orders].[OrderDate]=CONVERT_IMPLICIT(datetime,[@1],0)) ORDERED FORWARD)

注意：SQL Server自动使用@1参数替换查询文本中的参数

 

由此看来，查找仅扫描满足该谓词的数据页，其查询开销显然要比表中总记录数的开销低，因此，对于高选择度的查询谓词操作，查找通常是最有效的策略．也就是说，对于估计大表中的数据时，使用查找谓词是比较有效率的．

SQL Server将扫描与查找进行区分，如同将在堆（无聚集索引的对象）上扫描，聚集索引上的扫描，非聚集索引上的扫描进行分区．下表说明了这些出现在的查询计划中的扫描与查找运算．

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

可查找的谓词与覆盖列

        SQL Server在执行索引查找之前，它需要确定索引的键是否满足查询中的谓词，我们称该谓词为＂可查找的谓词＂，SQL Server必须确定该索引是否包含或＂覆盖＂查询中引用的列集合．下面描述了如何确定哪个谓词是可查找的，哪个谓词不是可查找的，哪些列需要索引覆盖．

单列索引

        在单列索引上判断谓词是否是可查找的是很容易的，SQL Server使用单列索引来响应多数简单的比较（包括相等和不等（大于，小于等））或者更复杂的表达式，如在列上运算的函数和LIKE %谓词，这些运算符将阻止SQL Server使用索引查找．

例如，假设我们在Col1列上创建了单列索引，可以在以下谓词上进行索引查找：

Ø [Col1] = 3.14

Ø [Col1] > 100

Ø [Col1] BETWEEN 0 AND 99

Ø [Col1] LIKE 'abc%'

Ø [Col1] IN (2, 3, 5, 7)

然页，在以下谓词上将不能使用索引查找：

Ø ABS([Col1]) = 1

Ø [Col1] + 1 = 9

Ø [Col1] LIKE '%abc'

 

下面我通过一些例子来介绍单列索引：

首先创建一些架构对象：

create table person(id int, last_name varchar(30), first_name varchar(30))

create unique clustered index person_id on person (id)
create index person_name on person (last_name, first_name)

　 

以下是三个查询及其各自的文本查询计划，第一个查询在person_name索引上进行查找，第二个查询首先在第一个键列上进行索引查找，然后使用residual谓词来估计first_name,第三个查询不能使用索引查找，而是使用了索引扫描来处理residual谓词．

 

select id from person where last_name = 'Doe' and first_name = 'John'
--Index Seek(OBJECT:([person].[person_name]), SEEK:([person].[last_name]='Doe' AND [person].[first_name]='John'))

select id from person where last_name > 'Doe' and first_name = 'John'
--Index Seek(OBJECT:([person].[person_name]), SEEK:([person].[last_name] > 'Doe'), WHERE:([person].[first_name]='John'))

select id from person where last_name like '%oe' and first_name = 'John'
--Index Scan(OBJECT:([person].[person_name]), WHERE:([person].[first_name]='John' AND [person].[last_name] like '%oe'))