【翻譯】SQL Server索引進階：第二級，深入非叢集索引

原文地址：

Stairway to SQL Server Indexes: Level 2, Deeper into Nonclustered Indexes

本文是SQL Server索引進階系列Stairway to SQL Server Indexes）的一部分。

在第一級中介紹了SQL Server中的非叢集索引。而且在第一個學習的例子中，我們證明了在從表中擷取一行資料的情況下，索引帶來的潛在的好處。在這一級中，我們繼續介紹非叢集索引，看看他們在提升查詢效能中做出的貢獻。

我們先來介紹一些理論，瞭解一些索引的內部資訊，協助我們解釋理論，然後執行一些查詢。這些查詢會在包含和不包含索引的兩種情況被執行，開啟效能報告，我們可以看到索引產生的影響。

我們繼續使用AdventureWorks 資料庫的部分表，主要集中在Contact表。我們將只是用一個索引，在上一級中使用的FullName索引，來證明我們的觀點。為了確保我們很好的控制Contact表的索引，我們將做兩份拷貝，一份建立FullName索引，一份不建立索引。

 

 

  
IF EXISTS ( 
  
 
  
    SELECT * 
  
 
  
        FROM sys.tables 
  
 
  
        WHERE OBJECT_ID = OBJECT_ID('dbo.Contacts_index')) 
  
DROP TABLE dbo.Contacts_index; 
  
GO 
  
IF EXISTS ( 
  
 
  
    SELECT * 
  
 
  
        FROM sys.tables 
  
 
  
        WHERE OBJECT_ID = OBJECT_ID('dbo.Contacts_noindex')) 
  
 
  
    DROP TABLE dbo.Contacts_noindex; 
  
GO 
  
SELECT * INTO dbo.Contacts_index 
  
 
  
    FROM Person.Contact; 
  
SELECT * INTO dbo.Contacts_noindex 
  
 
  
    FROM Person.Contact; 
 

非叢集索引

在Contacts_index 表建立非叢集索引

  
 

 

  
CREATE INDEX FullName 
  
 
  
        ON Contacts_index 
  
 
  
( LastName, FirstName ); 
 

請記住，非叢集索引順序儲存索引鍵，通過標記來訪問表中真正的資料。你可以把標籤看做一種指標。將來的層級中會描述標籤的格式，標籤的用法，標籤的細節。

另外，SQL Server的非叢集索引的入口還有一些內部使用的頭資訊，還有一些可選的資料值。這些在後面的文章中都會有介紹，現在都不是重點內容。

到目前為止，我們只需要知道，鍵使得SQL Server找到合適的索引入口，入口的標籤使得SQL Server訪問表對應的行資料。

索引入口有序的好處

索引的入口是有序的，因此SQL Server可以快速的定位入口。掃描可以從頭部開始，可以從尾部開始，也可以從中間開始。

因此，如果一個查詢，請求所有LastName以S開頭的Contact使用者where lastname like 's%'）。SQL Server會快速定位到第一個S開頭的記錄，然後通過索引，使用標籤訪問資料行，直到第一個T開頭的記錄。

如果選擇的列都包含在索引中，上面的查詢會執行的更快。如果我們執行

 

 

  
SELECT FirstName, LastName 
  
 
  
    FROM Contact 
  
 
  
    WHERE LastName LIKE 'S%'; 
 

SQL Server快速的導航到S入口，然後通過索引，忽略標籤，直接從索引的入口返回資料，直到第一個T入口。在關聯式資料庫的名詞中，叫做查詢全覆蓋索引。

很多SQL的操作都可以從索引中受益，包括：ORDER BY, GROUP BY, DISTINCT, UNION( not UNION ALL ), JOIN ... ON 。

謹記從左至右的鍵順序的重要性。我們建立的索引對於lastname=“ashton”很管用，但是對於firstname=“ashton”作用會小很多，甚至沒有用。

測試一些簡單的查詢

如果你要執行下面的查詢，確保你執行了前面的指令碼，建立了contact_index和contact_noindex表，而且也在contact_index表建立了LastName, FirstName索引。

開啟統計

 

 

  
SET STATISTICS io ON 
  
 
  
SET STATISTICS time ON 
 

因為contact表中的資料只有19972行，很難得到有意義的統計時間。大部分的查詢都顯示CPU time： 0 毫秒，因此我們可以關閉time統計，只顯示io統計。如果你需要一張大表來統計真實的time資訊，可以用文章後面的指令碼構建一個百萬行資料的contact表。下面的測試都以19972行的表為測試對象。

測試一個完全覆蓋的查詢

第一個查詢是一個覆蓋索引的查詢，擷取contact表中lastname以S開頭的記錄的一部分列。下面是執行的資訊。

 

SQL語句	SELECT FirstName, LastName FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'S%'
沒有索引的情況下	(2130 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
有索引的情況	(2130 row(s) affected) Table 'Contacts_index'. Scan count 1, logical reads 14.
索引產生的影響	IO從568次減少到14次
注釋	覆蓋查詢的索引是個好東西。沒有索引，就會進行全表掃描。2130行，表明以S開頭的記錄佔到了10%的資料。

 

 

 

 

 

 

 

 

 

測試一個非完全覆蓋的查詢

我們修改一下查詢，還是相同的查詢，只是擷取的列包含了一些沒有建立索引的列，下面是執行的結果。

 

SQL語句	SELECT * FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'S%'
沒有索引的情況下	(2130 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
有索引的情況	(2130 row(s) affected) Table 'Contacts_index'. Scan count 1, logical reads 568.
索引產生的影響	IO沒有影響
注釋	在查詢的過程中沒有使用到索引。在這種情況下，SQL Server覺得使用索引尋找，比不適用索引直接掃描，還要做更多的工作。

 

 

 

 

 

 

 

 

 

測試一個非完全覆蓋的查詢，但是提供更多的條件

我們修改一下查詢，還是相同的查詢，只是縮減了查詢結果的範圍，增加使用索引的好處，下面是執行的結果。

 

SQL語句	SELECT * FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%'
沒有索引的情況下	(107 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
有索引的情況	(107 row(s) affected) Table 'Contact_index'. Scan count 1, logical reads 111.
索引產生的影響	IO從568次減少到111次。
注釋	SQL Server訪問了107條入口，都在索引的連續範圍內。每個入口的標籤都被用來擷取對應的行資料。這些行在表中不是連續的。 這些查詢用到了索引，但是不如第一次的覆蓋查詢效果好，尤其是在IO的讀取方面。 你希望讀取107次索引，然後擷取107條資料，產生107次讀取。 之前的查詢，請求了2130行資料，沒有用到索引。這次請求107行資料，使用了索引。你很像知道使用索引的臨界點在哪裡？在後面的層級中我們將會介紹這方面的內容。

 

 

 

 

 

 

 

 

 

 

 

 

 

測試一個完全覆蓋的彙總查詢

最後一個例子是一個彙總查詢，包含了count計算。

 

SQL語句	SELECT LastName, FirstName, COUNT(*) as 'Contacts' FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%' GROUP BY LastName, FirstName
沒有索引的情況下	(2130 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
有索引的情況	(104 row(s) affected) Table 'Contacts_index'. Scan count 1, logical reads 4.
索引產生的影響	IO從568次減少到4次
注釋	所有需要查詢的資訊都包含在索引中，很好的使用了索引。

 

 

 

 

 

 

 

 

 

測試一個非完全覆蓋的彙總查詢

我們修改一下查詢，還是相同的查詢，只是擷取的列包含了一些沒有建立索引的列，下面是執行的結果。

 

SQL語句	SELECT LastName, FirstName, MiddleName, COUNT(*) as 'Contacts' FROM dbo.Contacts  -- execute with both Contacts_noindex and -- Contacts_index WHERE LastName LIKE 'Ste%' GROUP BY LastName, FirstName, MiddleName
沒有索引的情況下	(2130 row(s) affected) Table 'Contacts_noindex'. Scan count 1, logical reads 568.
有索引的情況	(105 row(s) affected) Table 'ContactLarge'. Scan count 1, logical reads 111.
索引產生的影響	IO從568次減少到111次
注釋	。

 

 

 

 

 

 

 

 

 

 

結論

當目前位置，我們知道非叢集索引有下面的一些好處：

• 是一些有序的入口集合。
• 表中的每一行都有一個入口。
• 包含一個索引鍵和一個標籤。
• 使用者負責建立的。
• SQL Server來維護。
• SQL Server用來最小化查詢嘗試，來滿足客戶的請求。

通過一些例子，我們看到SQL Server通過索引可以滿足查詢的請求，也有一些查詢會完全忽略索引，還有一些會部分的使用索引。基於這個原因，我們來更新一下在第一級中的一個結論。

當一個請求到達資料庫的時候，SQL Server只有三種訪問資料的方式：

通常來說，第一種是理想的，第二種要比第三種好。在之前的介紹中，我們知道了如何增加索引的使用可能性，如何決定更高效的使用非叢集索引。但是我們需要知道更多的索引內部的細節。

在我們知道細節之前，我們還需要介紹其他類型的SQL Server索引：叢集索引。

代碼下載

• NonClustered.SQL
• Level 2 - NonClustered.sql 
• Level2_MillionRowContactTable.sql