[SQL] 複合索引 - 排序

該筆記延續 [SQL] 複合索引 - 選擇性，不過因為方向不太一樣，所以就另外筆記一篇，主要是筆記下述 TSQL 語法適合的 Index

SELECT 
  CustomerID , 
  AVG(SubTotal)
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '日期區間'
GROUP BY CustomerID

結論

MS SQL 會比較 WHERE 條件和 GROUP BY 條件兩者成本，成本低者勝出就是

TSQL 中有 Order By 出現，會直覺有 Sort 操作子出現，但 GROUP BY 彙總資料前，資料假如沒有排序過，也會有 Sort 操作子出現，所以更明確定義方向為，WHERE 和 Sort 成本比較

以 AdventureWorks2017 [Sales].[SalesOrderHeader] 為資料，商業邏輯為查詢客戶在指定日期區間平均值

建立測試 Index

這兩個 Index 都 Include SubTotal 欄位，差異在於 OrderDate、CustimerID 誰是在第一欄位

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])

年度-2013

----- 年度-2013
SELECT 
  CustomerID , 
  AVG(SubTotal)
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20130101' AND '20131231'
GROUP BY CustomerID

[CustomerID 排序] 成本比 [WHERE 年度-2013] 成本還要低，所以跑 IX_CustomerID_OrderDate 的 Index Scan

[年度-2013] 語法分別指定 IX_CustomerID_OrderDate 和 IX_OrderDate_CustomerID 來觀察，可以發現跑 IX_CustomerID_OrderDate 的 Index Scan 成本比 IX_OrderDate_CustomerID 的 Index Seek 還要低

單日-20130701

----- 單日-20130701
SELECT 
  CustomerID , 
  AVG(SubTotal)
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20130701' AND '20130701'
GROUP BY CustomerID

[WHERE 單日-20130701] 成本比 [CustomerID 排序] 成本還要低，所以跑 IX_OrderDate_CustomerID 的 Index Seek

[WHERE 單日-20130701] 語法分別指定 IX_OrderDate_CustomerID 和 IX_CustomerID_OrderDate 來觀察，可以發現跑 IX_OrderDate_CustomerID  的 Index Seek 成本比 IX_CustomerID_OrderDate 的 Index Scan 還要低，這符合建立 Index 的目的，就是希望能跑 Index Seek

月份-201307

----- 月份-201307
SELECT 
  CustomerID , 
  AVG(SubTotal)
FROM [Sales].[SalesOrderHeader]
WHERE OrderDate BETWEEN '20130701' AND '20130731'
GROUP BY CustomerID

該月份範例重點在於執行計畫中有 Sort 出現，來證明 Group By 進行前會對資料進行排序後，才進會彙總

從下圖來觀察 IX_OrderDate_CustomerID 資料分布，CustomerID 欄位資料在日期天數大於 1 天時，就等於沒有排序，所以才必須有 Sort 來進行排序

[WHERE 月份-201307] 語法分別指定 IX_OrderDate_CustomerID 和 IX_CustomerID_OrderDate 來觀察，IX_OrderDate_CustomerID 為 [Index Seek + Sort]，[IX_CustomerID_OrderDate] 為Index Scan，先篩選資料再進行 Sort 成本會比較低

以上三種日期範圍會產生不同的 Index 選擇和使用方式

• 參考資料
• SQL Server 2014 執行計畫與資料分布統計 - 排序章節