[SQL]淺談索引(三)

[SQL]淺談索引(三) -非叢集索引

本篇文章會使用到SSMS的「圖形化執行計畫」,並對非叢集索引進行簡單得查詢測試。

非叢集索引是另外建立一個索引結構,該索引結構是包含非索引建的資料行的,除非在建立索引時特別將其它爛位INCLUDE進去。

非叢集索引的索引結構也是B-Tree結構。

 


接著先看看範例資料表

資料表MT共有6個欄位並建立了索引

叢集索引鍵值欄位(PK_MT): 1. BatchID  2. OrderNo 

非叢集索引鍵值欄位(IX_MT_Subject): Subject,CreateTime

測試一:當查詢的欄位含有「不是」非叢集索引鍵值之欄位則執行計畫會使用叢集索引掃描的方式(效能等同資料表掃描)來進行查詢。

SELECT Subject,MsgBody,CreateTime FROM MT WHERE Subject = N'驗證碼'

測試二:移除「測試一」查詢欄位中的MsgBody,則執行計畫會使用索引搜尋(Seek)的方式來進行查詢。

SELECT Subject,CreateTime FROM MT WHERE Subject = N'驗證碼'

測試三:使用「測試二」的T-SQL,並將WHERE 的條件欄位改成CreateTime,因為查詢條件只有CreateTime且CreateTime非IX_MT_Subject的第一個鍵值欄位,故執行計畫會使用索引掃描(Scan)的方式來進行查詢。

如果再WHERE 條件在加上Subject欄位進行查詢,則執行計畫將採用索引搜尋(Seek)的方式來進行查詢。

SELECT Subject,CreateTime FROM MT WHERE CreateTime <= '2017-07-24 18:10:11.000'

測試四:使用「測試一」的T-SQL,並將MsgBody改成BatchID,則執行計畫會使用索引搜尋(Seek)的方式來進行查詢。

奇怪!? BatchID並非IX_MT_Subject的鍵值欄位.相較於「測試一」卻是採用索引搜尋(Seek)的方式呢? 這是因為BatchID是「叢集索引」的鍵值之一,故原本的IX_MT_Subject除了 Subject,CreateTime以外可視為還包含了BatchID 與OrderNo欄位 。

SELECT Subject,BatchID,CreateTime FROM MT WHERE Subject = N'驗證碼'


※非叢集索引是另外建立一個索引結構,不會影響資料表實際資料的結構。

※非叢集索引是B-Tree結構。

※WHERE條件的欄位順序會影響SSMS執行計畫選擇用Seek或Scan,故設計非叢集索引時除評估鍵值欄位之外還須評估大部分查詢時WHERE條件怎麼下。

※除了非叢集索引本身的鍵值之外,即使T-SQL中含有叢集索引的鍵值欄位一樣能採用索引搜尋。

在淺談索引(二)、淺談索引(三)兩篇文章中一直看到叢集索引掃描、叢集索引搜尋、索引搜尋,下一篇則分享近來學習所整理的一些執行計畫運算子的比較。

 

egan2608@gmail.com