[SQL] 複合索引 - 選擇性

論壇問題

兩個欄位複合索引，在應用的商業邏輯上都適合當第一欄位，那要選哪一個好

從統計資訊密度和選擇性來了解，結論為 [低密度高選擇]，白話點就是看哪一個 WHERE 條件能找出最少資料，該條件就適合當第一欄位

以 AdventureWorks2017 [Sales].[SalesOrderHeader] 為資料，以查詢單一客戶本月訂單記錄為範例紀錄

------ 建立 IX_CustomerID_OrderDate 和 IX_OrderDate_CustomerID 兩個複合索引

DROP INDEX IF EXISTS IX_CustomerID_OrderDate ON [Sales].[SalesOrderHeader]
DROP INDEX IF EXISTS IX_OrderDate_CustomerID ON [Sales].[SalesOrderHeader]

CREATE NONCLUSTERED INDEX IX_CustomerID_OrderDate
ON [Sales].[SalesOrderHeader] (CustomerID , OrderDate)
INCLUDE ([SubTotal])

CREATE NONCLUSTERED INDEX IX_OrderDate_CustomerID
ON [Sales].[SalesOrderHeader] (OrderDate , CustomerID)
INCLUDE ([SubTotal])

------ 查詢客戶編號 11176 在 201307 的訂單紀錄
SELECT 
  CustomerID , 
  OrderDate , 
  SubTotal
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20130701' AND '20130730'
  AND CustomerID = '11176'

執行計畫

從執行計畫可以看出是使用 IX_CustomerID_OrderDate 來搜尋資料，原因在於 CustomerID = '11176' 篩選後資料筆數比較少

從上圖就可以發現

• OrderDate BETWEEN '20130701' AND '20130730' 條件，有 1528 筆資料
• CustomerID = '11176' 條件，有 28 筆資料

所以 CustomerID 在該情境下，較適合當第一欄位

最後竟然兩者都適合當第一個欄位，那就指定 Index 來跑看看

----- 指定跑 IX_CustomerID_OrderDate
SELECT 
  CustomerID , 
  OrderDate , 
  SubTotal
FROM [Sales].[SalesOrderHeader] WITH (INDEX(IX_CustomerID_OrderDate))
WHERE OrderDate BETWEEN '20130701' AND '20130730'
  AND CustomerID = '11176'

----- 指定跑 IX_OrderDate_CustomerID
SELECT 
  CustomerID , 
  OrderDate , 
  SubTotal
FROM [Sales].[SalesOrderHeader] WITH (INDEX(IX_OrderDate_CustomerID))
WHERE OrderDate BETWEEN '20130701' AND '20130730'
  AND CustomerID = '11176'

從執行計畫可以看出都是 Index Seek，但在執行計畫成本就分出差異

• 參考資料
• SQL Server Performance Tuning 效能調校 - [8-2-5 是否值得建索引]
• [SQL SERVER][Performance]密度和選擇性
• [查詢優化]影響執行計畫的因素2-Density(密度)
• [查詢優化]影響執行計畫的因素4-Selectivity(選擇性)