索引深入淺出(8/10)：覆蓋索引或列包含

在索引深入淺出：非聚集索引的B樹結構在聚集表里，在聚集表里，我們看到非聚集索引的葉子層只包含非聚集索引鍵和聚集索引鍵。從聚集表結構或堆表結構里拿到剩下列，SQL Server需要進行書簽/鍵查找操作。很多情況下書簽或鍵查找非常消耗資源。我們來看個例子。

在這里我們打開了IO統計信息，還有點擊工具欄的顯示包含實際的執行計劃。

USE IndexDB
GO
DROP TABLE dbo.SalesOrderDetail                               
GO                               
SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008r2.Sales.SalesOrderDetail
GO
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId)
GO 
SET STATISTICS IO ON
GO
SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice 
FROM SalesOrderDetail 
WHERE productid=707 AND SalesOrderID=43680

在IO統計信息里，SQL Server拿每條記錄需要進行5個IO操作。在執行計劃里，我們看到，查詢開銷的50%貢獻給了書簽查找（鍵查找）操作。

注意，這個表的非聚集索引有229個頁，非聚集索引的B樹結構深度為2。在這個非聚集索引里只要進行2個IO操作就可以完成查找操作，另外3個IO貢獻給了書簽查找。你可以用DBCC IND命令驗證下或者參考下索引深入淺出(4/10)：非聚集索引的B樹結構在聚集表。

假設這個查詢（用不同的參數）在應用程序里經常用到，現在你需要優化它。我們該怎么做？我們對此唯一能優化的話就是避免鍵查找（Key lookup）操作。因此我們修改下非聚集索引，把剩下2列（OrderQty， UnitPrice），不是聚集索引鍵，也不是非聚集索引鍵，也加入非聚集索引鍵。

DROP INDEX ix_Productid ON dbo.SalesOrderDetail
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId,OrderQty ,UnitPrice)
GO
SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail WHERE productid=707 AND SalesOrderID=43680 

從執行計劃里我們可以看到，我們已經回避了鍵查找操作，把IO操作從5個降到了3次。但是我們如果用DBCC IND看下非聚集索引，我們發現由于還這個改動，我們的非聚集索引深度增加了。因為索引層的增加，非聚集索引需要進行3次IO來完成這個操作。這將是最糟糕的，如果選擇列更多的話，我們就需要在非聚集索引里增加更多的列來避免鍵查找操作。

覆蓋索引（covering index?）就是用來解決這個問題的。覆蓋索引幫助我們在非聚集索引的葉子層增加非主鍵列，最小可能增加B樹結構的深度。可以用CREATE INDEX語句增加包含列完成。

當索引包含查詢列是，這個為稱為覆蓋索引。當我們創建非聚集索引去覆蓋一個查詢時，我們可以在索引里包含非主鍵列來覆蓋查詢列，這些覆蓋列在主查詢列里不會用到。這樣查詢性能會提升，因為查詢優化器在索引里就可以定位到需要列的數據，表或聚集索引不會被訪問。

DROP INDEX ix_Productid ON dbo.SalesOrderDetail
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId) 
include(OrderQty ,UnitPrice)
GO
SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail 
WHERE productid=707 AND SalesOrderID=43680 

可以發現，我們已經避免了鍵查找操作，并把IO操作降到了2次。IO操作清楚的告訴我們聚集索引的深度是2。我們用DBCC IND和DBCC PAGE命令驗證下。

SELECT index_id FROM sys.indexes WHERE name='ix_Productid' AND OBJECT_ID= OBJECT_ID('SalesOrderDetail')
GO
DBCC ind('IndexDB','SalesOrderDetail',2) 

TRUNCATE TABLE dbo.sp_table_pages
INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetail,2)')
GO

SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根節點/索引頁

DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,2800,3)--根頁

DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,2736,3)--葉子頁

include語句提到的列已經加到了葉子層的頁，沒有對非頁層頁造成影響。
列包含非常有用，我們可以把不能在索引鍵里加的列，在列包含里加入。另外避免超過當前索引大小的限制（最大鍵列數為 16，最大索引鍵大小為 900 字節）。我們可以包含除去text，ntext和image類型的其他列，列包含也同樣支持計算列（computed column）。

參考文章：?

http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/25/sql-server-part-8-explaining-the-covering-index-or-included-columns/