SQL SERVER 索引優化——移除鍵查找（書簽查找）或RID查找SQL SERVER 索引優化 ——移除鍵查找（書簽查找）或RID查找

SQL SERVER 索引優化

——移除鍵查找（書簽查找）或RID查找

今天，我将分享一個快速移除鍵查找或RID查找的技巧。讓我們首先弄明白什麼是鍵查找或者RID查找。請注意，從SQL Server 2005 SP1 及以前的版本，鍵查找被稱為書簽查找。

當查詢請求少量資料時，SQL Server 優化器将試圖使用在查詢結果列或包含在WHERE語句中列的非聚集索引，檢索資料。如果查詢請求的資料沒有呈現在非聚集索引中，SQL Server 必須傳回資料頁去擷取這些列中的資料。無論表是否有聚集索引，查詢仍将到表或聚集索引中檢索資料。

上面的場景中，如果表有聚集索引，其被稱為鍵查找 （key lookup，或書簽查找bookmark lookup）；如果表沒有聚集索引，但是有一個非聚集索引，其被稱為 RID 查找。這種操作消耗很大。為優化任何包含鍵查找或RID查找的查詢，其應該從查詢計劃中被移除。有兩種不同的移除鍵查找或RID查找的方法。

在弄清楚這兩種方法之前，我們将建立沒有聚集索引的測試表，激發RID查找。RID 查找是使用提供的行辨別符（row identifier ，RID）在堆上的書簽查找。

--建立有幾個列的表 OneIndex
CREATE TABLE OneIndex(ID INT,
       FirstName VARCHAR(100),
       LastName VARCHAR(100),
       City VARCHAR(100));
GO
--向表中插入10萬行記錄
INSERT INTO OneIndex (ID,FirstName,LastName,City)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
       'Bob',
       CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
       ELSE 'Brown' END,
       CASE
              WHEN ROW_NUMBER() OVER (ORDER BY a.name)%999 = 1 THEN 'Las Vegas'
              WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
              WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
              WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
       ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO
           

現在讓我們運作下面的查詢語句，并檢查查詢計劃

因為表上沒有索引，執行的是全表掃描（Table Scan）。我們将在表上建立一個聚集索引，然後再次檢查執行計劃。

-- 建立聚集索引
CREATE CLUSTERED INDEX [IX_OneIndex_ID] ON [dbo].[OneIndex] (
       [ID] ASC
);
           

現在再次運作如下查詢

SELECT ID, FirstName
FROM OneIndex
WHERE City = 'Las Vegas'
GO
           

執行計劃清晰的表明，當表上建立聚集索引後，Table Scan 現在轉變為 Clustered Index Scan。在這兩種情況下，基本表都是完全掃描的，表上沒有搜尋。

現在，讓我們看一下查詢的WHERE 語句。直覺上來看，如果我們在表上建立包含WHERE語句中字段列的索引，可能會獲得性能提升。讓我們在表上建立一個非聚集索引，然後再檢查執行計劃。

--建立非聚集索引
CREATE NONCLUSTERED INDEX IX_OneIndex_City
ON dbo.OneIndex(city ASC);
           

建立非聚集索引後，我們再次執行查詢，并檢查執行計劃

SELECT ID, FirstName
FROM OneIndex
WHERE City = 'Las Vegas'
GO
           

因為索引中包含WHERE語句中的字段，SQL Server 查詢執行引擎使用非聚集索引從表中檢索資料。然而，SELECT語句中的字段并不包含于索引中，為了擷取這些列，引擎必須再次傳回基礎表，檢索那些列。這種特定的行為被稱為鍵查找（或書簽查找）。

有兩種不同的方法可以解決這類問題。我将同時展示這兩種方法；然而，推薦你使用任何一種方法移除鍵查找。我選擇方法2.

方法1：建立非聚集覆寫索引

這種方法，我們将建立同時包含SELECT 語句中的字段和WHERE語句中的字段。

CREATE NONCLUSTERED INDEX IX_OneIndex_Cover
ON dbo.OneIndex(City,Firstname,ID);
           

一旦上面非聚集索引被建立，其覆寫查詢中的所有列，讓我們運作如下查詢語句，并檢查我們的執行計劃。

SELECT ID, FirstName
FROM OneIndex
WHERE City = 'Las Vegas'
GO
           

從執行計劃來看，我們可以确認，鍵查找已經被移除，僅僅發生索引查找（index seek）。因為沒有鍵查找，SQL Server 查詢引擎沒有必要再從資料頁中檢索資料，索引本身就包含了所有需要的資料。

方法2：建立包含列非聚集索引

這裡我們将用于SELECT 語句中的字段加入到包含列中，同時WHERE語句中的字段為索引字段。在這種方法中，我們将使用SQL Server 2005 開始介紹的新文法。一個包含非鍵列的索引，當查詢中所有列都包含在索引中，可以顯著提升性能。

CREATE NONCLUSTERED INDEX IX_OneIndex_Include
ON dbo.OneIndex(City)
INCLUDE(FirstName,ID);
           

建立上面的索引後，再次執行查詢，并檢視執行計劃

從執行計劃可以看到，這種方法也移除了鍵查找。

比較方法1、方法2的邏輯讀次數，發現，方法2的性能略優于方法1（方法2邏輯讀6次，方法1邏輯讀7次）。這也是我選擇方法2的原因之一，選擇方法2還有如下優點：

• 索引可以超出索引鍵900位元組限制
• 索引可以包含不允許作為鍵列的資料類型列，如varchar（max）、nvarchar（max）或者XML
• 主索引的大小被減小，其提升了索引操作的性能

總體來說，鍵查找、書簽查找或RID查找降低了查詢的性能，我們可以通過使用包含列索引或者覆寫索引來優化性能。

我将在另一篇文章中介紹一些與優化方法相關的概念。

如果喜歡，可以掃碼關注SQL Server 公衆号，将有更多精彩内容分享：