Sql Server中的表訪問方式Table Scan, Index Scan, Index Seek

 　　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