貓肥熊老爸的窩

淺談 Index Seek 和 Scan

在這邊我們來稍微談一下，什麼是Index Seek 、Index Scan以及Table Scan、Index Scan。真是感嘆世態炎涼阿，有好就有壞，有壞不一定就有好，大家可以看出我標題為何只寫「Index Seek 和Scan」嗎?，原因事是Seek 只有在建Index後才會有可能的發生的事，並且建了Index後，系統可不一定就會用Seek的方式找資料唷，但是若沒建Index 那就一定沒有Seek可以玩啦。

所謂的Scan ，是指查詢後以整個資料表或整個索引的方式讀取資料，而Seek是指在已經排序過的實體資料中，以取一筆或一個區間資料的方式讀取連續性的資料，這聽起來像是再繞口令，大家還是可以從字面上的意思，瞭解其固中的道理。不過，在此我還是以圖形的方式來表達一下好了，請參考下圖。

(圖4)

如上圖，若KEY未經過排序時，系統讀取資料是以逐筆的方式進行，將KEY=2的資料讀取出來，若索引的排序件值與Where 條件不一致，也會發生這樣的情形喔，所以索引的鍵值與查詢的條件可真是密不可分阿。

接下來談談Seek，Seek只有在當實體資料有經過排序後才有的讀取方式，因為已經建立過索引，所以可以透過索引內的指標直接指到對應的資料列，並且讀取連續的區間資料，中間不會花任何時間讀取其他資料。承上例，假設我們已經以KEY欄位鍵過Clustered Index，請參考下圖資料讀取的方式。

如上圖，應該可以看得出讀取資料的箭頭變短了，系統只讀取三筆資料就結束了，因為之後的可以確定是不符合資料的記錄，系統就不多作讀取的動作，可節省不少時間喔。

接下來我們來介紹幾種資料搜尋的方式：

一、Table Scan(資料表掃描)：

  這種掃描的方式，是在當一個資料表沒有建立任何Clustered Index 時所採取的資料搜尋方式，也就是實體資料以Heap架構存放時的處理方式。

二、Clustered Index Scan(叢集索引掃描)：

  這種搜尋方式，是當資料表有建立叢集索引鍵，但是在找詢資料時並非有效的利用到實體資料排序。

假設我們有個資料表EOM_LOG的架構如下：

我們以MC_YM+CRE_DATE建立了一個Clustered Index，指令如下：

接著我們來看看下列SQL，在無輸入任何條件時的執行計畫：

dbcc freeproccache
GO

select   *  from eom_log
 

如上圖，因為沒有下任何條件，所以是採用Clustered Index Scan，以整個索引檔掃描的方式搜尋資料。

接下來我們在SQL中加入了以非Clustered Index Key的欄位進行查詢

dbcc freeproccache
GO

select   *  from eom_log
where  EOM_DATE > 20040706

如上圖，大家有沒有發現，資料的搜尋還是以Clustered Index Scan的方式進行，這與沒有下任何條件時的方式是一樣的，所以若查詢的條件中沒有用到Clustered Index Key 的欄位作為條件是無法運用到叢集索引。

三、Clustered Index Seek(叢集索引搜尋)：

  這種搜尋方式，是最有效率的方式，資料完全利用叢集索引內所建立的實體資料排序，我們繼續以上面的例子將SQL加入Clustered Index Key 欄位並且重新執行下列SQL指令：

dbcc freeproccache
GO

select * from eom_log
where MC_YM >= '200410'

從上圖中可以看出，資料的搜尋方式變成Clustered Index Seek

接下來我們作幾個實驗來探討查詢Where條件中的欄位與搜尋方式之間的關係。

首先，查詢時我們單獨用叢集索引中的第二個欄位鍵值當成Where條件，並且進行以下的測試

dbcc freeproccache
GO

select * from eom_log
where CRE_DATE >= '20040101'

咦? 是使用Clustered Index Scan?? 如果大家有看過先前我寫的Clustered Index架構，應該就不難想到其中的道理。

接下來我們再次針對SQL做了一些調整，我們將Clustered Index Key 中的兩個欄位修都列到Where 條件中，重新進行以下的測試

dbcc freeproccache
GO

select * from eom_log
where   MC_YM >='200410'  and  CRE_DATE >= '20040101' 

如上圖，我即使將MC_YM與CRE_DATE兩者條件位置互調，結果都是一樣以Clustered Index Seek的方式搜尋資料，由此可知在Where 條件中起碼要含叢集索引鍵中的第一個欄位才可以有效的運用到實體排序。

接下來我們試試，如過建立三個欄位當叢集索引的鍵值 是否Where 也一定要含A+B+C呢?所以我又重建了EOM_LOG 的叢集索引，這次我以MC_YM+CRE_DATE+EOM_DATE當叢集索引鍵

CREATE CLUSTERED INDEX IX_EOM_LOG ON dbo.EOM_LOG
    (
    MC_YM,
    CRE_DATE,
    EOM_DATE
    ) WITH FILLFACTOR = 60 ON [PRIMARY]
GO

在此我們直接以第二個欄位(CRE_DATE)+第三個欄位(EOM_DATE)的狀況來測試好了，執行下列調整過後的SQL

select * from eom_log
where     CRE_DATE >= '20040101'   and EOM_DATE >= '20040706 '

答案是Clustered Index  Scan，所以還是要有第一個欄位才可以，這次我用A+C看看好了，重新執行下列SQL

select * from eom_log
where    MC_YM >='200410'   and  EOM_DATE >= '20040706 '

如上圖，依結果看來，似乎跳過欄位二也可以用Seek的方式，證明當索引鍵為A+B+C時，只要Where 條件中有A欄位，系統就會進行SEEK的方式，但是遇到A+C時，雖然看起來也是用Clustered Index Seek但實際上只有A有用到索引，而執行計畫仍是判斷為Seek方式。