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

標籤：

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中有表訪問方式的說法，訪問表中的資料主要通過三種方式進行訪問：

1. 全表掃描（full table scan），直接存取資料頁，尋找滿足條件的資料
2. 通過rowid掃描（table access by rowid），如果知道資料的rowid，那麼直接通過rowid進行尋找
3. 索引掃描（index scan），如果一個表建立了索引，那麼可以通過索引來找出我們想要的資料在表中的存放位置，也就是rowid，通過返回rowid然後用rowid來進行訪問具體資料。
4. 而索引掃描中又可分為索引全掃描（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.

1. A table scan is where the table is processed row by row from beginning to end.
2. An index scan is where the index is processed row by row from beginning to end.
3. If the index is a clustered index then an index scan is really a table scan.
4. 總結：在sql server中，對錶中資料從頭到尾一行一行的進行出來就是表掃描。這裡的處理我們可以理解為sql中where子句的條件判斷。我們需要遍曆表中的每一行，判斷是否滿足where條件。最簡單的table scan是select * from table。
5. 索引掃描就是對索引中的每個節點從頭到尾的訪問。假設我們的索引是B樹結構的，那麼index scan就是訪問B樹中的每一個節點。
6. 假如索引是叢集索引，那麼B樹索引的葉子節點儲存的是資料頁中的實際資料。假如索引是非叢集索引，那麼B樹葉子節點儲存的是指向資料頁的指標。

（ps：以下2.1-2.6於2012-9-4補充）

2.1實驗資料準備

在介紹完clustered index scan，table scan和index scan以後，我們將通過實驗來表述會在什麼情況下使用這些表掃描方式。我們將使用AdventureWorks2008R2這個sample database進行實驗，首先準備實驗資料，TSQL如下所示：

--準備測試資料--------------------------------------------------use adventureworks2008R2go--如果表已存在，刪除drop table dbo.SalesOrderHeader_testgodrop table dbo.SalesOrderDetail_testgo--建立表select * into dbo.SalesOrderHeader_testfrom Sales.SalesOrderHeadergoselect * into dbo.SalesOrderDetail_testfrom Sales.SalesOrderDetailgo--建立索引create clustered index SalesOrderHeader_test_CL on dbo.SalesOrderHeader_test (SalesOrderID)gocreate index SalesOrderDetail_test_NCLon dbo.SalesOrderDetail_test (SalesOrderID)go--select * from dbo.SalesOrderDetail_test--select * from dbo.SalesOrderHeader_test declare @i intset @i = 1while @i<=9begin    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 [email protected], CarrierTrackingNumber, OrderQty, ProductID,    SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,    rowguid, getdate()    from Sales.SalesOrderDetail    set @i = @i +1endgo--資料準備完畢--------------------------------

2.2實驗資料說明：

1. dbo.SalesOrderHeader_test裡存放的是每一張訂單的頭資訊，包括訂單建立日期、客戶編碼、合約編號、銷售人員編號等，每個訂單都有一個單獨的訂單號。在訂單號這個欄位上，有一個叢集索引。
2. dbo.SalesOrderDetail_test裡存放的是訂單的詳細內容。一張訂單可以銷售多個產品給同一個客戶，所以dbo.SalesOrderHeader_test和dbo.SalesOrderDetail_test是一對多的關係。每條詳細內容包括它所屬的訂單編號，它自己在表格裡的唯一編號（SalesOrderDetailID）、產品編號、單價，以及銷售數量等。在這裡，先只在SalesOrderID上建立一個非叢集索引。create index預設建立的就是非叢集索引。
3. 按照AdventureWorks裡原先的資料，dbo.SalesOrderHeader_test裡有3萬多條訂單資訊，dbo.SalesOrderDetail裡有12萬多條訂單詳細記錄，基本上一條訂單有3~５條詳細記錄。這是一個正常的分布。為了使資料分布不均勻，我們再在dbo.SalesOrderHeader_test裡加入9條訂單記錄，它們的編號是從75124到75132。這是9張特殊的訂單，每張有12萬多條詳細記錄。也就是說，dbo.SalesOrderDetail_test裡會有90%的資料屬於這9張訂單。主要是使用“select [email protected]”來搜尋出Sales.SalesOrderDetail中的所有記錄插入到dbo.SalesOrderDetail。一共執行9次。

 2.3 table scan

sql server中表分為兩種，一種是有叢集索引的聚集表，另外一種是沒有叢集索引的對錶。在聚集表中資料按照叢集索引有序存放，而對錶則是無序存放在hash中的。以dbo.SalesOrderDetail_test為例，它的上面沒有叢集索引，只有一個在SalesOrderID上的非叢集索引。所以表格的每一行記錄，不會按照任何順序，而是隨意地存放在Hash裡。此時我們找所有單價大於200的銷售詳細記錄，要運行如下語句：

View Code

由於表格在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的記錄呢，是按照什麼方式查詢的。在執行查詢之前晴空執行計畫緩衝

DBCC DROPCLEANBUFFERS--清空執行計畫緩衝DBCC FREEPROCCACHE--清空資料緩衝select SalesOrderID from SalesOrderDetail_test where SalesOrderID< 80000

其查詢計劃如所示，我們發現使用的是index seek

2.5 clustered index scan

如果這個表格上有叢集索引，事情會怎樣呢？還是以剛才那張表做例子，先給它在值是唯一的欄位SalesOrderDetailID上建立一個叢集索引。這樣所有的資料都會按照叢集索引的順序儲存。

View Code

可惜的是，查詢條件UnitPrice上沒有索引，所以SQL Server還是要把所有記錄都掃描一遍。和剛才有區別的是，執行計畫裡的表掃描變成了叢集索引掃描（clustered index scan）。如所示：

因為在有叢集索引的表格上，資料是直接存放在索引的最底層的，所以要掃描整個表格裡的資料，就要把整個叢集索引掃描一遍。在這裡，叢集索引掃描就相當於一個表掃描。所要用的時間和資源與表掃描沒有什麼差別。並不是說這裡有了“Index”這個字樣，就說明執行計畫比表掃描的有多大進步。當然反過來講，如果看到“Table Scan”的字樣，就說明這個表格上沒有叢集索引。

現在在UnitPrice上面建一個非叢集索引，看看情況會有什麼變化。

--在UnitPrice上建立非叢集索引create index SalesOrderDetail_test_NCL_Priceon 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

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雷根據資料找尋目標的不同和方法不同，有下面幾種情況。

結　　構	Scan	Seek
堆（沒有叢集索引的表格式資料頁）	Table Scan	無
叢集索引	Clustered Index Scan	Clustered Index Seek
非叢集索引	Index Scan	Index 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）是邏輯讀，如果資料頁不在當前的緩衝中，那麼必須從磁碟上讀取資料頁到緩衝中，這樣算是物理讀。

轉：http://www.cnblogs.com/xwdreamer/archive/2012/07/06/2579504.html

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