Sql Server中的表访问方式Table Scan, Index Scan, Index Seek

nawawa001

0.参考文献
　　Table Scan, Index Scan, Index Seek
　　SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note
　　oracle表访问方式
　　Index Seek和Index Scan的区别以及适用情况

1.oracle中的表访问方式
　　在oracle中有表访问方式的说法，访问表中的数据主要通过三种方式进行访问：

• 全表扫描（full table scan），直接访问数据页，查找满足条件的数据
  
• 通过rowid扫描（table access by rowid），如果知道数据的rowid，那么直接通过rowid进行查找
  
• 索引扫描（index scan），如果一个表创建了索引，那么可以通过索引来找出我们想要的数据在表中的存放位置，也就是rowid，通过返回rowid然后用rowid来进行访问具体数据。
  
• 而索引扫描中又可分为索引全扫描（index full scan）、索引范围扫描（index range scan）和索引唯一扫描（index unique scan）等。
  

2.sql server中clustered index scan,table scan,index scan
　　在sqlserver中也有类似的内容，这里就要将的是table scan，index scan以及index seek.

• A table scan is where the table is processed row by row from beginning to end.
  
• An index scan is where the index is processed row by row from beginning to end.
  
• If the index is a clustered index then an index scan is really a table scan.
  
• 总结：在sql server中，对表中数据从头到尾一行一行的进行出来就是表扫描。这里的处理我们可以理解为sql中where子句的条件判断。我们需要遍历表中的每一行，判断是否满足where条件。最简单的table scan是select * from table。
  
• 索引扫描就是对索引中的每个节点从头到尾的访问。假设我们的索引是B树结构的，那么index scan就是访问B树中的每一个节点。
  
• 假如索引是聚集索引，那么B树索引的叶子节点保存的是数据页中的实际数据。假如索引是非聚集索引，那么B树叶子节点保存的是指向数据页的指针。
  

　　（ps：以下2.1-2.6于2012-9-4补充）

2.1实验数据准备
　　在介绍完clustered index scan，table scan和index scan以后，我们将通过实验来表述会在什么情况下使用这些表扫描方式。我们将使用AdventureWorks2008R2这个sample database进行实验，首先准备实验数据，TSQL如下所示：


View Code


--准备测试数据--------------------------------------------------
use adventureworks2008R2
go
--如果表已存在，删除
drop table dbo.SalesOrderHeader_test
go
drop table dbo.SalesOrderDetail_test
go
--创建表
select * into dbo.SalesOrderHeader_test
from Sales.SalesOrderHeader
go
select * into dbo.SalesOrderDetail_test
from Sales.SalesOrderDetail
go
--创建索引
create clustered index SalesOrderHeader_test_CL
on dbo.SalesOrderHeader_test (SalesOrderID)
go
create index SalesOrderDetail_test_NCL
on dbo.SalesOrderDetail_test (SalesOrderID)
go
--select * from dbo.SalesOrderDetail_test
--select * from dbo.SalesOrderHeader_test
declare @i int
set @i = 1
while @i 200
由于表格在UnitPrice上没有索引，所以SQL Server不得不对这个表格从头到尾扫描一遍，把所有UnitPrice的值大于200的记录一个一个挑出来，其过程如下图所示。

　　从执行计划里可以清楚地看出来SQL Server这里做了一个表扫描，如下图所示：


2.4 index scan 和 index seek
　　我们在SalesOrderID上创建了非聚集索引，加入查询条件是SalesOrderID，并且只SalesOrderID这一列的话，那么会以什么查询方式执行呢？首先我们查询SalesOrderID 200的记录。

　　根据新建的索引，它直接找到了符合记录的值，查询计划如下图所示。我们可以看到是直接在nonclustered index上进行index seek操作。


但是光用建立在UnitPrice上的索引不能告诉我们其他字段的值。如果在刚才那个查询里再增加几个字段返回，如下TSQL查询：


View Code


select SalesOrderID, SalesOrderDetailID, UnitPrice
from dbo.SalesOrderDetail_test with (index (SalesOrderDetail_test_NCL_Price))
where UnitPrice > 200
　　SQL Server就要先在非聚集索引上找到所有UnitPrice大于200的记录，然后再根据SalesOrderDetailID的值找到存储在聚集索引上的详细数据。这个过程可以称为“Bookmark Lookup”，如下图所示。

在SQL Server 2005以后，Bookmark Lookup的动作用一个嵌套循环来完成。所以在执行计划里，可以看到SQL Server先seek了非聚集索引SalesOrderDetail_test_NCL_Price，然后用Clustered Index Seek把需要的行找出来。这里的嵌套循环其实就是Bookmark Lookup，如下图所示：


　　上述Key Lookup就是Bookmark Lookup中的一种，这是因为我们的表中建有聚集索引，如果我们没有聚集索引，那么这里就是RID Lookup，如下图所示：

上述key lookup其所消耗的时间如下所示：



SQL Server Execution Times:
CPU time = 2995 ms, elapsed time = 10694 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
　　在上述查询中，之所以要使用with (index (SalesOrderDetail_test_NCL_Price))这个语句，是为了强制其使用SalesOrderDetail_test_NCL_Price这个非聚集索引，通过非聚集索引找到了聚集索引键值以后再去聚集索引中查询。如果不使用的话，sql server有可能会使用clustered index scan，也可能使用bookmark lookup，这取决于查询返回的数据量。
　　（1）比如还是查询UnitPrice > 200的结果：



select SalesOrderID,SalesOrderDetailID,UnitPrice from dbo.SalesOrderDetail_test where UnitPrice > 200
　　其查询计划如下，我们可以发现使用的是clustered index scan，返回的记录数有481590条，非常大。

　　更重要的是其cpu time，如下所示：



SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 10063 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
　　我们发现cpu time只有515ms，比我们之前看到的2995ms要小。这就表明：index seek 并不一定就比index scan要好。sql server会根据统计信息选择更有的方式执行操作。
　　（2）假如查询UnitPrice