為什麼 SQL Server 使用非聚集索引而不使用聚集索引？

我有一張有 1.45 億行的表

CREATE TABLE [dbo].[RFTest](
   [SnapshotKey] [int] NOT NULL,
   [SnapshotDt] [datetime] NOT NULL,
   [LoanNum] [int] NOT NULL,
   [GLSourceSystem] [varchar](10) NOT NULL,
   [FlowDescription] [varchar](30) NULL,
   [Account] [varchar](30) NULL,

   --- plus 20 more column
)

該表在 SnapshotDt 上進行分區。

我在我的表上添加了以下索引：

create clustered index ci on RFTest (SnapshotDt, SnapshotKey, LoanNum)

create nonclustered index nci on RFTest (SnapshotDt, SnapshotKey, LoanNum)
   include ([GLSourceSystem],[Account],[FlowDescription])

我執行了以下查詢：（我使用前 100 個進行測試，因為如果我想執行整個表將需要很長時間）

select top 100 *
from RFTest with (index(ci)) -- force index
where LoanNum = 2712

select top 100 *
from RFTest 
where LoanNum = 2712

LoanNum列存在於兩個索引中，在集群中是鍵的一部分，在非集群中包含。

執行計劃顯示引擎選擇非集群“nci”索引，而不是集群索引。

我想知道為什麼。

澄清：

對我來說，在這兩種情況下，SQL 都讀取了相同數量的數據。並且LoanNum在索引和 BTW 中，LoanNum是鍵的一部分，所以在我看來，如果它使用聚集索引更有意義。

索引與我發布的完全相同。當我擷取計劃時，查詢中有一些評論。您在文章中看到的查詢是正確的。我不想同時保留這兩個索引，我想看看哪一個表現更好，問題就來了。

優化器可以在兩種主要策略之間進行選擇：

1. 掃描表（聚集索引）檢查每一行，看看 LoanNum = 2712。
2. 掃描和查找

• 掃描非聚集索引以查找 LoanNum = 2712 的行
• 查找非聚集索引未覆蓋的匹配行的列數據。

關鍵是非聚集索引更小，所以掃描它預計會更便宜。這可能看起來違反直覺，因為聚集索引定義具有相同的鍵，並且非聚集索引包含列，但關鍵是聚集索引包括儲存在行中的所有列 - 聚集索引的葉（最低）級別字面意思是行內數據。

對於少量的預期匹配，掃描較小索引所節省的成本足以補償鍵查找。

順便說一句，您可能會發現WHERE 1 = 1從查詢中刪除 會導致優化器選擇聚集索引掃描。（冗餘）常量與常量比較可防止 SQL Server 參數化查詢，因此估計值基於有關LoanNum 2712的統計資訊。如果查詢是參數化的，SQL Server 將使用LoanNum值的平均分佈，這可能會導致更多的預期行數和計劃選擇的變化。

也可以看看：

• Michelle Ufford 的有效聚集索引
• TOP 如何（以及為什麼）影響執行計劃？

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