解析SQL Server聚焦移除（Bookmark Lookup、RID Lookup、Key Lookup）

前言

前面幾節都是講的基礎內容，本節我們講講索引性能優化，當對大數據進行處理時首先想到的就是索引，一旦遇到這樣的問題則手忙腳亂，各種查資料，為何平常不扎實基本功呢，我們由淺入深，簡短的內容，深入的理解，而非一上來就把問題給框死，立馬給出解決方案，拋出問題，再到解決問題，你GET了沒有。

Bookmark Lookup、RID Lookup、Key Lookup定義

一說到這三者，如果對索引研究不深的童鞋估計是懵逼的，什麼玩意，我們姑且將上面三者翻譯為：標簽查找、行ID查找、鍵查找。標簽查找和鍵查找是一個意思，在SQL 2005之前叫Key Lookup。怎麼解釋，如何定義呢？首先我們不看定義，直接看下面一步一步解析，如果你實在忍不住，請看園友【永紅】的見解，解釋還是非常到位。我們簡短的說明下此三者概念。

在查詢中，我們對返回的列在查詢條件上若建立了非聚集索引，此時將可能嘗試使用非聚集索引查找，如果返回的列沒有創建非聚集索引，此時會返回到數據頁中去獲取這些列的數據，即使表中存在聚集索引或者沒有，都會返回到表中或者聚集索引中去獲取數據。對於以上場景描述，如果表沒有創建聚集索引則稱為Bookmar Lookup，如果表中沒有聚集索引但是存在非聚集索引我們稱為RID Lookup。看到這裡我們就會想法操作如此耗時，還要返回到基表中去獲取數據，所以才有了我們本節來移除以上三者來提高查詢性能。接下來我們一起來看看。

拋出Bookmark Lookup、RID Lookup、Key Lookup問題

我們首先創建如下表

USE TSQL2012 
GO
CREATE TABLE Sales.Orders 
(
[orderid] INT,
[shipaddress] VARCHAR(100),
[shipcity] VARCHAR(100),
[shipregion] VARCHAR(100))
GO

接著進行查詢

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders
WHERE shipcity = '深圳'

這個不用多講，沒添加任何索引，執行查詢計劃是全表掃描。接下來我們創建在orderid上創建聚集索引如下：

CREATE CLUSTERED INDEX idx_cls_orderid ON Sales.Orders(orderid)

我們再執行上述查詢

此時我們創建了聚集索引，所以此時查詢走聚集索引，到這裡我們看到情況由全表掃描轉換成了索引掃描。我們在查詢時一直是帶了查詢條件的，而對查詢條件我們未作任何操作，如果我們此時在查詢條件上創建了索引，此時查詢的性能又會得到一點改善。我們開始對查詢條件創建一個非聚集索引。

CREATE NONCLUSTERED INDEX idx_nc_shipcity ON Sales.Orders(shipcity)

我們再接著執行查詢

我們觀察到對查詢條件創建了非聚集索引，查詢計劃會使用非聚集索引查找返回結果，但是對於shipaddress, shipcity, shipregion並不是索引的一部分，此時查詢引擎會返回到基表中得到這些數據再返回。這種行為就叫做Bookmark Lookup或者Key Lookup。下面我們就如本文標題一樣問題出現來解決問題，移除Bookmark Lookup或者Key Lookup。我們嘗試用兩種不同的方法來解決。

解決Bookmark Lookup、RID Lookup、Key Lookup問題

創建非聚集索引覆蓋索引

我們對查詢條件以及檢索列創建非聚集索引。

CREATE NONCLUSTERED INDEX idx_all_cover ON Sales.Orders(shipaddress,orderid,shipcity,shipregion)

此時我們對檢索列創建了非聚集索引，此時將不會再到數據頁中獲取數據，而是從索引中直接返回，所以到這裡我們算是移除了Key Lookup。但是此時觸發另外一個問題，執行查詢計劃走的卻是索引掃描，索引到底是什麼呢？我們打個比方，一個索引相當於是數據庫中一個本書開始的索引，我們需要快速從書中查找到我們所需要的數據，這個時候書就是我們所說的表。索引掃描意味著要讀取表中的所有行，然後返回滿足條件的所有數據，當執行索引掃描時，所有行上葉子節點上的所有都會被掃描，這也就意味著索引上的所有行都會被檢索一遍而不是直接檢索表，和表掃描對比的話，表掃描是直接讀取表中數據，所以表掃描和索引掃描還是有一點點不同，而索引查找則是依賴於索引頁數據來定位滿足條件的所有行，索引查找僅僅只影響滿足條件以及頁上包含這些滿足條件的行，所以說索引查找更加高效。

上述我們稍微講解了下索引掃描和索引查找，而上述的問題是我們創建了非聚集索引，但是結果執行的查詢計劃是索引掃描，很是納悶，對於剛學索引小白的我來說，不知該如何是好，以為是緩存的緣故，清除各種緩存均不好使。於是開始胡思亂想是不是檢索列中數據有為NULL引起的，是不是檢索列數據重復引起的，嘗試了無數次，最終發現某一次居然好使。如下

CREATE NONCLUSTERED INDEX idx_cls_cover ON 
Sales.Orders(shipcity,orderid,shipaddress,shipregion)

此時若我們將查詢條件進行如下修改。

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders
WHERE shipaddress = '深圳' 
GO

到這裡我們應該發現了，唯一的區別在於我們創建非聚集索引時的順序和查詢條件不同就會導致索引掃描和索引查找的轉換，那麼到底什麼時候才會執行索引查找呢？我們可以進行如下一般性總結：

索引查找的一般性結論：如果條件中包含WHERE或者ON的話，查詢條件必須是位於索引集合列中首位，此時索引查找將會被使用。

此時我們穿插一點內容，上述我們創建了覆蓋索引，我們來比較下覆蓋索引和默認情況下聚集索引查找的性能開銷。

覆蓋索引與默認聚集索引性能開銷比較

FROM Sales.Orders WITH(INDEX([PK_Orders]))
WHERE orderid<11072
go
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_include_exceptorderid]))
WHERE orderid<11072
GO

從上可知，覆蓋索引的開銷要比默認主鍵聚集索引性能開銷要好一點，同時我們可以看看如下二者IO代價。

通過上述覆蓋索引與默認聚集索引的對比，我們能夠有效的減少IO，這一點也是非常明確的，當然下面的INCLUDE索引對比也是另外一種好的方案。

創建INCLUDE非聚集索引

USE TSQL2012
GO
CREATE NONCLUSTERED INDEX [ix_noncls_include] ON [TSQL2012].[Sales].[Orders] (
 shipcity
) INCLUDE (shipaddress, shipregion, orderid)

至此我們用兩種方式來移除了Bookmark Lookup、RID Lookup、Key Lookup，通過使用索引和覆蓋索引。

既然有如上兩種方式，我們應該有所取捨，二者誰的性能更好呢？我們接下來比較上述二者的開銷差異。

比較移除Bookmark Lookup等兩種方式差異

USE TSQL2012
GO
SELECT orderid, shipaddress, shipcity, shipregion
FROM Sales.Orders WITH(INDEX(idx_all_cover))
WHERE shipcity = '深圳'
GO
SELECT orderid, shipaddress, shipcity, shipregion
FROM Sales.Orders WITH(INDEX(ix_noncls_include))
WHERE shipcity = '深圳'
GO

我們從上所知，二者開銷一樣，並未有什麼區別，當然相信我們更傾向於的是將第二種方式作為解決方案。到這裡算是基本結束了，但是還有一個小問題，我們在之前已經創建了orderid的聚集索引，後面在解決方案中我們也添加了orderid的非聚集索引，難道非得添加嗎，我們去掉試試看。

CREATE NONCLUSTERED INDEX idx_noncls_cover_exceptorderid
ON Sales.Orders(shipcity,shipaddress,shipregion)
CREATE NONCLUSTERED INDEX idx_noncls_include_exceptorderid
ON Sales.Orders(shipcity) INCLUDE(shipaddress,shipregion)

去除orderid比較二者開銷差異：

USE TSQL2012
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_cover_exceptorderid]))
WHERE shipaddress = '深圳' 
GO
SELECT orderid, shipaddress, shipregion
FROM Sales.Orders WITH(INDEX([idx_noncls_include_exceptorderid]))
WHERE shipaddress = '深圳' 
GO

由上知，非聚集索引列不需要包含創建了聚集索引的列，那麼事實到底是怎樣的呢？

結論：其實對於任何非聚集索引列都不需要包含創建了聚集索引的列，因為創建聚集索引的列是非聚集索引集合列的一部分，也就是說只要一個表上的列創建了聚集索引，那麼非聚集索引集合列就包含了這個聚集索引。

總結

本節我們比較詳細就問題的拋出到問題的解決，從而來提高查詢性能，好了，到此結束，我們下節再會。簡短的內容，深入的理解

以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時也希望多多支持！