建立索引與主鍵時，索引欄位順序決定查詢生死

SQL Server 建立主鍵或索引時，預設所有欄位採用 升序 (ASC)，這是為了相容早期版本。但實際查詢若以 降序 (DESC) 為主，順序不匹配會導致掃描方向相反，嚴重拖垮效能。本文以百萬筆實測數據，量化順序不匹配的代價，並給出排序策略指導。

實測環境與腳本

• 表：test_indexorder，主鍵叢集索引 PK_testorder (Id DESC)
• 資料：1,000,000 筆，Id 為 Identity(1,1)
• 查詢：分頁取 50 筆 (ROW_NUMBER 排序)
• 環境：冷快取 (DBCC FREEPROCCACHE, DROPCLEANBUFFERS)

-- 降序查詢 (與索引順序一致)
WITH t_rn AS (
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY Id DESC) FROM test_indexorder
)
SELECT Id, Name, Content, Co1, Co2, Co3, Co4, Co5 
FROM t_rn WHERE rn BETWEEN 19007 AND 19057;  -- 393 ms

-- 升序查詢 (與索引順序相反)
WITH t_rn AS (
    SELECT *, rn = ROW_NUMBER() OVER (ORDER BY Id ASC) FROM test_indexorder
)
SELECT Id, Name, Content, Co1, Co2, Co3, Co4, Co5 
FROM t_rn WHERE rn BETWEEN 17007 AND 17057;  -- 606 ms

結果分析

結論： 僅排序方向不匹配，效能即下降 50%+。若涉及 Join、Group By、Window Function，放大效應更劇烈。

索引欄位順序設計原則

1. 以最高頻查詢的 ORDER BY / GROUP BY / JOIN 條件為準： 索引鍵順序 = 查詢排序順序。
2. 等值過濾欄位優先： WHERE 子句中 = 比較的欄位放最左 (最選擇性高的優先)，其次是範圍欄位，最後是 INCLUDE 覆蓋欄位。
3. 主鍵特別注意： 若主鍵同時是叢集索引，且業務常用 ORDER BY PK DESC (如最新記錄優先)，必須顯式指定 PRIMARY KEY CLUSTERED (Id DESC)。
4. 多欄索引順序 = 排序優先級遞減： 例如 ORDER BY CategoryId ASC, CreatedDate DESC → 索引 (CategoryId ASC, CreatedDate DESC)。
5. 驗證手段： 觀察執行計畫是否出現 Sort、Top N Sort、Index Scan (Ordered: False)，若有即代表順序不匹配。

進階：降序索引的儲存特性

• SQL Server 以 B+ Tree 儲存，葉層雙向鏈表。DESC 索引僅改變邏輯順序，頁面分裂、合併、填充因子運作機制不變。
• 統計資訊 (Histogram) 仍以升序建立，但優化器能正確推導降序密度。DBCC SHOW_STATISTICS 顯示仍為升序，屬正常。
• sys.index_columns 中 is_descending_key = 1 標識降序欄位。

避坑提醒

• 不要為了「可能」的降序查詢建立雙索引 (ASC + DESC)：維護成本雙倍，寫入效能減半。除非讀寫比 > 100:1 且兩方向查詢頻率相當。
• 非叢集索引若包含降序鍵，且為 INCLUDE 欄位，is_descending_key 無效 (INCLUDE 欄位不參與排序)。
• 資料分區表 的分區欄位順序必須與叢集索引順序一致，否則分區消除失效。

索引欄位順序不是預設值能決定的，而是由查詢工作負載決定的。每次建立索引前，先問三個問題：誰在查？怎麼排？多頻繁？ 答案裡藏著最佳順序。