SQL Server 選擇非選擇性索引

我正在測試 SQL Server 索引並發現非常奇怪的行為。這是我的程式碼：

DROP TABLE IF EXISTS  dbo._Test
DROP TABLE IF EXISTS  dbo._Newtest
GO
CREATE TABLE _Test(
ID INT NOT NULL, 
UserSystemID INT NOT NULL, 
Age INT
)
GO
INSERT INTO dbo._Test
       ( ID, UserSystemID, Age )

SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns
CROSS JOIN sys.all_objects a
CROSS JOIN sys.all_objects b
CROSS JOIN sys.all_objects c

; WITH cte AS (
SELECT ID, UserSystemID,  age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn
FROM dbo._Test
)

SELECT cte.ID ,
      cte.UserSystemID ,
      cte.Age
INTO _newTest
FROM cte
WHERE cte.rn = 1

CREATE UNIQUE NONCLUSTERED INDEX  IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age)
GO
ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID)
GO

此時，我在同一張表和同一列上有兩個索引。第一個是非集群的，第二個是集群的。該Id列更具選擇性（大約 5000000 個唯一值）而UserSystemID不是（兩個唯一值）。

然後我執行以下查詢來測試使用了哪個索引：

SELECT id,  UserSystemID, age   
FROM _NewTest
WHERE id = 1502945
AND UserSystemID = 1

它尋找聚集索引。你可以在這裡看到計劃。

問題是為什麼 SQL Server 更喜歡聚集索引而不是唯一的非聚集索引。

---

我的聚集索引的前導列比其他唯一非聚集索引的選擇性要低得多。所以我希望聚集索引的性能一定會更差，但實際上並非如此。

給定唯一索引，您的查詢將最多選擇一行。

優化器知道它只需要將索引 b-tree 下降一次，並且不需要從該點向前或向後掃描以找到更多匹配項。這被稱為單例搜尋（對唯一索引的平等測試）。

目前的索引匹配實現碰巧總是在可以使用單例查找時選擇聚集索引。

這裡聚集索引和非聚集索引的選擇一般不是很重要。導航 b 樹的上層（使用二分搜尋或線性插值）可能會產生很小的額外成本，但這甚至很難測量。請記住，非葉子索引頁面上只有ID和關鍵組件。UserSystemID

有人可能會爭辯說，平均而言，更廣泛的聚集索引葉頁不太可能在記憶體中。還有一些其他極端情況的後果，但我認為這種行為不會很快改變。

但是我的聚集索引的前導列比其他唯一非聚集索引的選擇性要低得多。所以我希望聚集索引的性能一定會更差，但實際上並非如此。

選擇性對於復合 b 樹索引上的相等搜尋無關緊要。

您的唯一聚集複合索引具有鍵 (UserSystemID, id)。

要查找 (UserSystemID = 1 和 id = 1502945) 的行，SQL Server 不會查找 UserSystemID = 1 的所有行，然後查找 id = 1502945 的行。那將非常低效。

您可以使用 來判斷您的測試查詢涉及多少頁SET STATISTICS IO ON。您的範例建構了一個具有兩個非葉級別的聚集索引。總而言之，找到您想要的行意味著要接觸三頁 - 索引的每一級都有一頁。

行在索引中按 UserSystemID 和 id 排序。我的展示表副本在聚集索引的根（頂級）頁面上具有以下佈局：

在此頁面上執行二進制搜尋很容易：

• 從中間行開始。

• 將 UserSystemID 與您要查找的使用者系統ID 進行比較。

  • 如果不相等，以通常的方式繼續二分查找（根據需要在前面或後面的行中選擇一個新的中點）。
  • 如果在 UserSystemID 上相等，將 id 與您要查找的 id 進行比較，然後繼續二進制搜尋

按照這個邏輯，我們將快速找到子（下一個較低級別）索引頁面，如果它們存在，則可以在其中找到搜尋到的鍵。在該頁面上重複二進制搜尋，依此類推，直到我們到達必須包含我們正在查找的行（如果存在）的單個葉級頁面。

引用自：https://dba.stackexchange.com/questions/254877