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

　　0.参考文献

　　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<=9

　　begin

　　insert into dbo.SalesOrderHeader_test

　　(RevisionNumber, OrderDate, DueDate,

　　ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,

　　AccountNumber, CustomerID, SalesPersonID, TerritoryID,

　　BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,

　　CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,

　　Freight,TotalDue, Comment,rowguid,ModifiedDate)

　　select RevisionNumber, OrderDate, DueDate,

　　ShipDate,Status, OnlineOrderFlag, SalesOrderNumber,PurchaseOrderNumber,

　　AccountNumber, CustomerID,SalesPersonID, TerritoryID,

　　BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID,

　　CreditCardApprovalCode, CurrencyRateID, SubTotal,TaxAmt,

　　Freight,TotalDue, Comment,rowguid,ModifiedDate

　　from dbo.SalesOrderHeader_test

　　where SalesOrderID = 75123

　　insert into dbo.SalesOrderDetail_test

　　(SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID,

　　SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,

　　rowguid,ModifiedDate)

　　select 75123+@i, CarrierTrackingNumber, OrderQty, ProductID,

　　SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,

　　rowguid, getdate()

　　from Sales.SalesOrderDetail

　　set @i = @i +1

　　end

　　go

　　--数据准备完毕--------------------------------

　　2.2实验数据说明：

　　dbo.SalesOrderHeader_test里存放的是每一张订单的头信息，包括订单创建日期、客户编号、合同编号、销售员编号等，每个订单都有一个单独的订单号。在订单号这个字段上，有一个聚集索引。

　　dbo.SalesOrderDetail_test里存放的是订单的详细内容。一张订单可以销售多个产品给同一个客户，所以dbo.SalesOrderHeader_test和dbo.SalesOrderDetail_test是一对多的关系。每条详细内容包括它所属的订单编号，它自己在表格里的唯一编号(SalesOrderDetailID)、产品编号、单价，以及销售数量等。在这里，先只在SalesOrderID上建立一个非聚集索引。create index默认创建的就是非聚集索引。

　　按照AdventureWorks里原先的数据，dbo.SalesOrderHeader_test里有3万多条订单信息，dbo.SalesOrderDetail里有12万多条订单详细记录，基本上一条订单有3~5条详细记录。这是一个正常的分布。为了使数据分布不均匀，我们再在dbo.SalesOrderHeader_test里加入9条订单记录，它们的编号是从75124到75132。这是9张特殊的订单，每张有12万多条详细记录。也就是说，dbo.SalesOrderDetail_test里会有90%的数据属于这9张订单。主要是使用“select 75123+@i...”来搜索出Sales.SalesOrderDetail中的所有记录插入到dbo.SalesOrderDetail。一共执行9次。

　　2.3 table scan

　　sql server中表分为两种，一种是有聚集索引的聚集表，另外一种是没有聚集索引的对表。在聚集表中数据按照聚集索引有序存放，而对表则是无序存放在hash中的。以dbo.SalesOrderDetail_test为例，它的上面没有聚集索引，只有一个在SalesOrderID上的非聚集索引。所以表格的每一行记录，不会按照任何顺序，而是随意地存放在Hash里。此时我们找所有单价大于200的销售详细记录，要运行如下语句：

　　View Code

　　select SalesOrderDetailID, UnitPrice from dbo.SalesOrderDetail_test where UnitPrice > 200

　　由于表格在UnitPrice上没有索引，所以SQL Server不得不对这个表格从头到尾扫描一遍，把所有UnitPrice的值大于200的记录一个一个挑出来，其过程如下图所示。

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

　　2.4 index scan 和 index seek

　　我们在SalesOrderID上创建了非聚集索引，加入查询条件是SalesOrderID，并且只SalesOrderID这一列的话，那么会以什么查询方式执行呢?首先我们查询SalesOrderID<43664的记录，执行如下TSQL语句：

　　select SalesOrderID from SalesOrderDetail_test where SalesOrderID< 43664

　　其执行计划如下图所示，我们发现执行的是index seek

　　假如我们要查询所有SalesOrderID记录并且不加where条件，

　　select SalesOrderID from SalesOrderDetail_test

　　那么查询计划如下图所示，我们发现执行的是index scan。

　　那么假如我们要求查询所有SalesOrderID<80000的记录呢，是按照什么方式查询的。在执行查询之前晴空执行计划缓存

　　View Code

　　DBCC DROPCLEANBUFFERS--清空执行计划缓存

　　DBCC FREEPROCCACHE--清空数据缓存

　　select SalesOrderID from SalesOrderDetail_test where SalesOrderID< 80000

　　其查询计划如下图所示，我们发现使用的是index seek