百萬級資料查詢優化 30 條鐵律：從索引設計到查詢重構

一、索引與存取路徑 (第 1-6 條)

1. 避免全表掃描：`WHERE`、`ORDER BY`、`JOIN ON` 涉及欄位**必須**有索引覆蓋。
2. 拒絕 `IS NULL` 判斷：`WHERE Col IS NULL` 導致索引失效 → 給欄位 `DEFAULT 0`/`''`，改查 `WHERE Col = 0`。
3. 拒絕 `!=` / `<>`：同樣導致掃描 → 改用 `IN`/`EXISTS`/範圍查詢。
4. 拒絕 `OR` 連接條件：`WHERE A=1 OR B=2` → 拆分 `UNION ALL` 兩查詢，各走各索引。
5. 慎用 `IN` / `NOT IN`：值清單較長時優化器傾向掃描 → 連續值用 `BETWEEN`，子查詢改 `EXISTS`。
6. 避免 `LIKE '%關鍵字%'` 開頭萬用字元：無法用索引 → 改用**全文檢索** 或 `LIKE '關鍵字%'` (前綴可用索引)。

二、參數與運算式陷阱 (第 7-10 條)

7. 區域變數導致計畫通用化：`WHERE Col = @var` 編譯時未知值 → 強制指定索引 `WITH (INDEX(idx_name))` 或 `OPTION (RECOMPILE)` (2008+)。
8. 欄位參與運算式/函數 = 索引失效：`WHERE Col/2 = 100` → 改 `WHERE Col = 200`；`WHERE SUBSTRING(Name,1,3)='ABC'` → `WHERE Name LIKE 'ABC%'`；`WHERE DATEDIFF(day, CreateDate, '2024-01-01')=0` → `WHERE CreateDate >= '2024-01-01' AND CreateDate < '2024-01-02'`。
9. 等號左邊不可有運算/函數：系統無法匹配索引搜尋引數 (SARGable 原則)。
10. 複合索引必須遵循「最左前綴」：索引 `(A,B,C)` → 條件必含 `A` 才會被使用；`WHERE B=1 AND C=2` 不走索引。

三、查詢重寫技巧 (第 11-14 條)

11. 空表結構複製：`SELECT * INTO #t FROM t WHERE 1=0` → 改 `CREATE TABLE #t (...)` 避免系統表鎖定。
12. `EXISTS` 替代 `IN` 子查詢：`WHERE Col IN (SELECT Col FROM B)` → `WHERE EXISTS (SELECT 1 FROM B WHERE B.Col = A.Col)`，語意相同但往往更優 (半連接)。
13. 索引非萬能：低選擇性欄位 (如性別、狀態碼幾乎均勻) 建索引無效，優化器仍選掃描。
14. 索引數量適度：單表 ≤ 6 個；過多拖慢 `INSERT/UPDATE/DELETE` (需維護索引)。定期 `sys.dm_db_index_usage_stats` 清理未用索引。

四、資料型別與結構優化 (第 15-18 條)

15. 避免更新叢集索引鍵：叢集索引決定物理順序，更新鍵值 = 移動資料頁 → 極高成本。頻繁更新欄位勿設為叢集鍵。
16. 優先數值型別：純數字業務鍵用 `INT/BIGINT`，字串比較逐字元、佔空間大、連接慢。
17. `VARCHAR` 取代 `CHAR`：變長節省空間、縮小掃描範圍、緩衝池效率高。
18. 禁止 `SELECT *`：明確列出欄位 → 減少網路/IO/記憶體、利用覆蓋索引、避免結構變更衝擊。

五、暫存表與游標策略 (第 19-27 條)

19. 表變數優於暫存表 (小資料量)：記憶體驻留、無鎖定系統表、無重編譯。大資料量 (>10萬列) 改暫存表 + 索引。
20. 避免頻繁建立/刪除暫存表：消耗 `tempdb` 系統表資源。
21. 大量資料一次性載入暫存表：用 `SELECT INTO #t` (最小記錄) 代替 `CREATE TABLE` + `INSERT`。
22. 程序結束顯式清理：`TRUNCATE TABLE #t; DROP TABLE #t;` 釋放系統表鎖定。
23. 能用集合操作別用游標：游標逐列處理極慢；優先 `JOIN`/`APPLY`/`窗口函數`/`MERGE`。
24. 小結果集可用 `FAST_FORWARD` 唯讀順向游標：較 `WHILE` 迴圈快，但仍次於集合方案。
25. 結果集含「合計/小計」：`GROUPING SETS`/`ROLLUP`/`CUBE` 或 `UNION ALL` 聚合，遠快於游標迴圈累加。

六、交易與網路效能 (第 28-30 條)

28. 程序/觸發器首尾 `SET NOCOUNT ON/OFF`：抑制 `DONE_IN_PROC` 網路封包，降低延遲。
29. 縮短交易範圍：大交易 = 長鎖定 = 阻塞 = 低併發。拆分邏輯、延遲鎖定、樂觀鎖 (RowVersion)。
30. 避免傳回超大結果集給客戶端：分頁、匯出檔案、非同步處理。若必須傳百萬列，檢視需求合理性。

附錄：SARGable 原則速查表