資料庫調優筆記系列（一）：索引設計鐵律與實戰技巧

1. 複合索引威力：1+1 > 2

查詢 `WHERE A=@a AND B=@b`：

• 無索引：全表掃描，耗時 a。
• 單列索引 A 或 B：索引尋找 + 書簽查找，耗時 b ≈ c。
• **複合索引 (A, B)**：單次索引尋找直達葉層，耗時 d。
• **鐵律：a > b = c > d**。實測 7000 次插入驗證：8 秒 vs 1 分 50 秒，差距 14 倍。

⚠️ 升序/降序定義影響範圍查詢方向，`ORDER BY A DESC, B ASC` 需索引 `(A DESC, B ASC)` 避免排序運算符。

2. 高選擇性閾值：5% 黃金線

非叢集索引選擇性 > 5% (即回傳 < 5% 總列) 時極具價值。優化器成本模型在此閾值傾向尋找而非掃描。低選擇性欄位 (性別、狀態碼) 單獨建索引通常無效，需納入複合索引前綴或作為 `INCLUDE` 欄位。

3. 複合索引欄位順序：高選擇性優先 (但要看查詢模式)

原則：等值查詢高選擇性欄位放最左。例：`WHERE IP=@ip AND UrlId=@url`。測試環境 IP 重複度高 (選擇性低)，但生產環境 UrlId 重複度高。決策：依**生產實際分佈**決定順序，或建立兩個索引 `(IP, UrlId)` + `(UrlId, IP)` 覆蓋不同查詢模式 (SQL 2008+ 合併索引交集可單索引)。

4. 覆蓋索引：短網址跳轉的極速查詢

場景：`SELECT TargetUrl FROM ShortUrl WHERE ShortCode=@code`。
建立索引 `(ShortCode) INCLUDE (TargetUrl)`。查詢僅掃描索引葉層，**零書簽查找、零基表存取**，極致快速。覆蓋索引是點查詢 (Key Lookup) 的終極解藥。

5. 叢集鍵自動包含在非叢集索引中

叢集表上，非叢集索引葉層天然包含叢集鍵值。查詢 `SELECT ClusterKey, NonClusterCol FROM Table WHERE NonClusterCol=@val` 若有非叢集索引 `(NonClusterCol)`，**即為覆蓋查詢**，無需額外 `INCLUDE` 叢集鍵。

6. 窄索引、限制回傳、避免掃描

• `SELECT` 明確列清單，`WHERE` 限制行數 → 減少 I/O、網路、鎖範圍。
• 無用掃描 = 浪費資源 + 鎖升級風險。

7. 長字串索引：雜湊索引 (CHECKSUM) 替代全文檢索

長 `VARCHAR(MAX)`/`NVARCHAR(MAX)` 不宜直接建索引 (體積大、維護重)。方案：

ALTER TABLE Docs ADD ContentHash AS CHECKSUM(Content); -- 計算欄位
CREATE INDEX IX_Docs_ContentHash ON Docs(ContentHash);
-- 查詢
SELECT * FROM Docs WHERE ContentHash = CHECKSUM(@searchText) AND Content = @searchText;

優點：索引小 (INT)、精確匹配快。缺點：雜湊碰撞需二次驗證、不支援模糊/語意搜索。全文檢索 (FTS) 適合語意/模糊，但空間/I/O 成本極高。按需選擇。

總結

索引設計鐵律：**複合 > 單列、高選擇性左序、覆蓋消除 Lookup、叢集鍵天然包含、長字串用雜湊**。每個索引都要有明確服務的查詢模式，無查詢支撐的索引純屬負擔。定期審視 `sys.dm_db_index_usage_stats` 清理未使用索引。