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
2.5 clustered index scan
如果這個表格上有聚集索引，事情會怎樣呢?還是以剛才那張表做例子，先給它在值是唯一的字段SalesOrderDetailID上建立一個聚集索引。這樣所有的數據都會按照聚集索引的順序存儲。
View Code
--為SalesOrderDetail_test創建聚集索引
create clustered index SalesOrderDetail_test_CL
on dbo.SalesOrderDetail_test (SalesOrderDetailID)
go
可惜的是，查詢條件UnitPrice上沒有索引，所以SQL Server還是要把所有記錄都掃描一遍。和剛才有區別的是，執行計劃裡的表掃描變成了聚集索引掃描(clustered index scan)。如下圖所示：
因為在有聚集索引的表格上，數據是直接存放在索引的最底層的，所以要掃描整個表格裡的數據，就要把整個聚集索引掃描一遍。在這裡，聚集索引掃描就相當於一個表掃描。所要用的時間和資源與表掃描沒有什麼差別。並不是說這裡有了“Index”這個字樣，就說明執行計劃比表掃描的有多大進步。當然反過來講，如果看到“Table Scan”的字樣，就說明這個表格上沒有聚集索引。
現在在UnitPrice上面建一個非聚集索引，看看情況會有什麼變化。
View Code
--在UnitPrice上創建非聚集索引
create index SalesOrderDetail_test_NCL_Price
on dbo.SalesOrderDetail_test (UnitPrice)
go
在非聚集索引裡，會為每條記錄存儲一份非聚集索引索引鍵的值和一份聚集索引索引鍵的值(在沒有聚集索引的表格裡，是RID值)。所以在這裡，每條記錄都會有一份UnitPrice和SalesOrderDetailID記錄，按照UnitPrice的順序存放。
再跑剛才那個查詢，
select SalesOrderDetailID, UnitPrice from dbo.SalesOrderDetail_test where UnitPrice > 200
你會看到這次SQL Server不用掃描整個表了，如下圖所示。這次查詢將根據索引直接找到UnitPrice > 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 <2的結果：
select SalesOrderID,SalesOrderDetailID,UnitPrice from dbo.SalesOrderDetail_test where UnitPrice < 2
我們發現查詢計劃就不再使用cluster index scan了，而是使用了index seek+clustered index seek，如下圖所示，返回記錄數只有1630條。相對來說記錄數目比較小，所以不需要clustered index scan。
2.6總結
總結一下，在SQL Server裡根據數據找尋目標的不同和方法不同，有下面幾種情況。
結 構ScanSeek
堆(沒有聚集索引的表格數據頁)Table Scan無
聚集索引Clustered Index ScanClustered Index Seek
非聚集索引Index ScanIndex Seek
如果在執行計劃裡看到這些動作，就應該能夠知道SQL Server正在對哪種對象在做什麼樣的操作。table scan(表掃描)表明正在處理的表格沒有聚集索引，SQL Server正在掃描整張表。clustered index scan(聚集索引掃描)表明SQL Server正在掃描一張有聚集索引的表格，但是也是整表掃描。Index Scan表明SQL Server正在掃描一個非聚集索引。由於非聚集索引上一般只會有一小部分字段，所以這裡雖然也是掃描，但是代價會比整表掃描要小很多。Clustered Index Seek和Index Seek說明SQL Server正在利用索引結果檢索目標數據。如果結果集只占表格總數據量的一小部分，Seek會比Scan便宜很多，索引就起到了提高性能的作用。如果查詢結果集很多，那麼可能會更傾向使用table scan。
3.Index Scan, Index Seek的比較
Index Seek就是SQL在查詢的時候利用建立的索引進行掃描，先掃描索引節點，即遍歷索引樹。在查找到索引的葉子節點後，如果是聚簇索引就直接取葉子節點值的值，如果是非聚簇索引，則根據葉子節點中的rowid去查找相應的行(聚集索引的葉子節點是數據頁，而非聚集索引的葉子節點是指向數據頁的索引頁，也就是數據頁的rowid，這是在表沒有聚集索引的情況下發生的;如果表本身含有聚集索引，那麼非聚集索引的葉子結點中保存的是非聚集索引鍵值和聚集索引鍵值，在得到聚集索引鍵值以後會再去聚集索引中查找。)。而對於Index Scan是從頭到位遍歷整個索引頁中的所有行，從頭到尾，因此在數據量很大時效率並不是很高，在聚集索引的情況下，clustered index scan就是table scan。
SQL有一個查詢優化分析器 Query Optimizer，其在執行查詢之前首先會進行分析，當查詢中有可以利用的索引時，那麼就優先分析使用Index Seek進行查詢的效率，假如得出使用Index Seek的查詢效率並不好，那麼就使用Index Scan進行查詢。那究竟是在什麼情況下會造成Index Seek效率比Index Scan還低呢?可以分一下集中情況：
1.在要查詢的表中數據並不是很多的情況下，使用Index Seek效率不一定高，因為使用Index seek還要先從索引樹開始，然後再利用葉子節點去查找相應的行。在行數比較少的情況下，還沒有直接進行Index scan快。因此，表中存儲的數據不能太少。
2.在返回的數據量很大的情況下，比如返回的數據量占總數據量的50%或者超過50%，使用Index Seek效率不一定好，在返回的數據量占10%-15%時，利用Index Seek能獲得最佳的性能。因此假如要使用index seek，返回的數據量既不能太多，也不能太少。
3.在建立索引的列的取值很多是一致的情況下，建立索引不一定能獲得很好的效率。比如不建議在“性別”列上建立索引。其實理由很簡單，當建立索引的列取值的變化少的情況下，建立的索引二叉樹應該是矮胖型的，樹層次不高，很多行的信息都包含在葉子上，這樣的查詢顯然是不能很好的利用到索引
MSDN原話：不要總是將索引的使用等同於良好的性能，或者將良好的性能等同於索引的高效使用。如果只要使用索引就能獲得最佳性能，那查詢優化器的工作就簡單了。但事實上，不正確的索引選擇並不能獲得最佳性能。因此，查詢優化器的任務是只在索引或索引組合能提高性能時才選擇它，而在索引檢索有礙性能時則避免使用它。
4.Sql server中的I/O
The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.
在sqlserver中I/O可以分為邏輯IO和物理IO，從緩存(buffer cache)中讀取一個頁(page)是邏輯讀，如果數據頁不在當前的緩存中，那麼必須從磁盤上讀取數據頁到緩存中，這樣算是物理讀。
