前言：效能源於設計，而非事後補救
經驗證明，SQL Server 最大的效能提升來自 **邏輯資料庫設計** 與 **索引策略**，而非單純參數調整。本文彙整十大查詢優化鐵律與四大系統優化支柱，助您從根源消除瓶頸。
十大查詢優化鐵律
1. 索引策略：精準而非堆疊
- 叢集索引首選欄位： 範圍查詢 (`BETWEEN`, `>`, `<`)、`ORDER BY`、`GROUP BY`、高重複值、常作連接鍵的欄位。一張表僅能有一個叢集索引，決定資料物理順序。
- 非叢集索引覆蓋查詢： 組合索引前導欄位必為最高選擇性/最常用欄位；包含 `INCLUDE` 欄位滿足 `SELECT` 清單，避免書籤查詢 (Key Lookup)。
- 拒絕過度索引： 每新增一索引，`INSERT/UPDATE/DELETE` 皆需維護 B 樹結構。定期檢視 `sys.dm_db_index_usage_stats` 移除未使用索引 (`user_seeks + user_scans + user_lookups = 0`)。
2. `NULL` 與索引的陷阱
- 含 `NULL` 的欄位**不會**被包含在索引中 (多欄位索引任一欄為 NULL 整列排除)。
- `WHERE col IS NULL/IS NOT NULL` **無法使用索引求值**，優化器將掃描表/索引。
- 對策：業務允許時設定 `NOT NULL DEFAULT ''` 或預設值；查詢改寫為 `WHERE col = ''` (需配合檢查約束/觸發器維護)。
3. `EXISTS` 完勝 `IN` 於子查詢
- `EXISTS` 採用 **半連接** 機制，找到首筆匹配即返回，不產生中間結果集。
- `IN` 子查詢常轉為 **全表掃描** 或 **哈希/合併連接** 處理完整結果集。
- 原則：**所有 `IN (SELECT ...)` 子查詢優先改寫為 `EXISTS (SELECT 1 FROM ... WHERE ...)`**。
4. 海量查詢避免隱含轉換
`WHERE varchar_col = 123` 導致隱含 `CONVERT`，索引失效。嚴格遵守 **資料型別一致**，參數化查詢自動處理型別對應。
5. `ORDER BY`/`GROUP BY` 與索引協同
索引天然有序，若索引欄位順序符合 `ORDER BY`/`GROUP BY` 序列，優化器可直接輸出有序結果，**省略 Sort 運算子**。注意：索引鍵含 `NULL` 時優化器可能放棄使用。
6. 運算下推：將計算移至等號右側
錯誤： `WHERE YEAR(OrderDate) = 2023` (函數作用於欄位 → 掃描)
正確： `WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'` (SARGable，可索引求值)
原則：**任何欄位運算 (函數、運算式、隱含轉換) 導致索引失效，改寫為範圍查詢或計算欄位/索引檢視表。**
7. `IN`/`OR` 子句謹慎使用
- 易產生工作表、索引失效、平行度降級。
- 低基數值場景拆分為 `UNION ALL` 多個 `WHERE col = val` (各走索引求值) 再合併。
- 高基數值或動態清單改用暫存表/表值參數 `JOIN`。
8. 執行計劃與完整性檢查
- `SET SHOWPLAN_ALL ON` / `SET STATISTICS IO, TIME ON` / 實際執行計劃 (Ctrl+M) 分析邏輯/物理運算子、預估/實際行數、I/O 成本。
- 定期 `DBCC CHECKDB` 驗證邏輯/物理完整性，損壞的頁面/索引會導致查詢中止或錯誤結果。
9. 游標：能不用盡量不用
- 集合操作 (單一 SQL) 效能遠優於逐列處理 (RBAR - Row By Agonizing Row)。
- 必須游標時：`FAST_FORWARD` (唯讀/順向) 最輕量；將符合條件資料先 `INSERT #Temp` 再游標處理，減少鎖定範圍與鎖定時間。
10. 參數嗅探與計劃快取陷阱
預存程序首次編譯依據參數值產生計劃並快取，後續不同參數值可能不適用。對策：`OPTION (RECOMPILE)`、`OPTIMIZE FOR (@param = typical_value)`、區域變數賦值、動態 SQL (謹慎)。
四大系統優化支柱
- 關鍵欄位建立索引 — 如前所述，精準覆蓋核心查詢路徑。
- 預存程序封裝邏輯 — 減少網路往返、啟用計劃重用、參數化防注入、權限管控 (僅授 `EXEC` 權限)。
- 備份策略與垃圾資料清理 — 完整/差異/記錄備份滿足 RPO/RTO；歸檔/刪除歷史資料控制表體積，縮短備份/檢查/統計更新窗口。
- SQL 語法優化 — 應用上述十大鐵律，善用 `sys.dm_exec_query_stats`、`sys.dm_db_missing_index_details` 等 DMV 發現高耗查詢與缺失索引建議。
總結
優化的本質：**WHERE 子句利用索引 (Index Seek) → 良好效能；發生 Table/Index Scan、Key Lookup、Sort、Hash Match (大量資料)、Spool → 需優化。** 沒有捷徑，唯有扎實的邏輯設計、索引規劃與查詢重寫能力，才能持續榨乾硬體效能。
