[SQL] 具有內含資料行的索引

Nonclustered index 是由 Index Key、included column 和 Clustered Key 組成，利用 Nonclustered index 搜尋資料，會先利用 Index Key 來搜尋，假如利用 Index Key 就搜尋完全部資料，此情況稱為 Covering Index（覆蓋索引）；相反地，無法在 Index Key 上找到全部資料，就會進一步利用 Clustered Key 進入 Clustered Index 內去搜尋所需要資料，此行為稱為索引鍵查閱（Key Lookup），2005 SP2 之前稱為書籤查詢（Bookmark Lookup），會增加鎖定和執行時間

建立［具有內含資料行的索引］（文內簡稱包含索引）來滿足 Covering Index 進而消除 Key Lookup 來提高效能，就是此篇文章要說明的

建立包含索引的簡易語法

CREATE INDEX IndexName
ON SchemaName.TableName (Col1,Col2,Col3.......) 
INCLUDE (ColA,ColB,ColC.......)

利用 AdventureWork 的 Sales.SalesOrderHeader 來說明

-- Step 1：針對 OrderDate 建立 nonclustered index
CREATE INDEX IX_OrderDate ON [Sales].[SalesOrderHeader] (OrderDate)

-- Step 2：找出 OrderDate = '20040729' 的 SalesOrderID、OrderDate 和  CustomerID 這三個欄位資料
SELECT 
    SalesOrderID , 
    OrderDate ,
    CustomerID
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '20040729'

下圖為 Step 2 執行計畫，可以看出因為 IX_OrderDate 無法滿足 Covering Index 所以利用 Clustered Index 搭配 Nested Loop（巢狀迴圈）一筆一筆資料進入PK_SalesOrderHeader_SalesOrderID 抓取資料

把滑鼠移到［索引鍵查閱］上，會顯示下列資訊，可以觀察到是為了要找 CustomerID 資料（綠色框框），所以利用［索引鍵查閱］（紅色框框）進入PK_SalesOrderHeader_SalesOrderID （綠色框框）抓取資料

-- Step 3：刪除 IX_OrderDate 
DROP INDEX IX_OrderDate ON [Sales].[SalesOrderHeader]

-- Step 4：建立包含索引
CREATE INDEX IX_OrderDate 
ON [Sales].[SalesOrderHeader] (OrderDate)
INCLUDE (CustomerID) -- 包含 CustomerID 欄位

-- Step 5：再次執行查詢語法
SELECT 
    SalesOrderID , 
    OrderDate ,
    CustomerID
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate = '20040729'

下圖為 Step 5 執行計畫，可以觀察到因為 CustomerID 已經包含在 IX_OrderDate 內，不再需要［索引鍵查閱］去搜尋資料

Key Lookup 只會利用 Nested Loop 來抓取資料，是一個極耗成本的動作，是進行 Perfomance Tuning 時的觀察重點，而除了建立包含索引來避免，撰寫 T-SQL 語法時，也要避免抓出不必要的資料，SELECT * 的寫法更是大忌喔

• 2011 SQL Hero

• 參考資料
• 叢集與非叢集索引說明
• 具有內含資料行的索引
• [SQL SERVER]書籤查閱是什麼