前言
SQL 查詢效能優化是資料庫開發與維護的核心技能。本文彙整 30 條實戰驗證的優化原則，涵蓋查詢撰寫、索引策略、臨時表與游標使用、交易管理等面向，助你撰寫高效穩定的 SQL 程式碼。
一、查詢撰寫核心原則 (1-10)
- 避免全表掃描：優先在 WHERE、ORDER BY 涉及的欄位建立索引。
- 避免 NULL 判斷導致索引失效：將 NULL 替換為預設值（如 0），改查
WHERE num = 0。
- 慎用 != 或 <>：會導致全表掃描，嘗試改寫為
IN 或 UNION。
- 避免 OR 連接條件：改用
UNION ALL 分開查詢再合併。
- IN / NOT IN 慎用：連續數值改用
BETWEEN；大量離散值考慮 EXISTS。
- 避免 LIKE '%abc%'：前置萬用字元無法用索引，改用全文檢索或
LIKE 'abc%'。
- 變數參數導致編譯時無法用索引：必要時加
WITH (INDEX(...)) 強制指定。
- 避免 WHERE 欄位做運算：
num/2=100 → num=200，保持欄位「乾淨」。
- 避免 WHERE 欄位套用函數：
SUBSTRING(name,1,3)='abc' → name LIKE 'abc%'；日期範圍用 >= 和 <。
- 不在「=」左邊做運算：同原則 8，確保索引可被正確匹配。
二、索引設計黃金法則 (11-19)
- 複合索引須符合最左前綴：查詢條件必須包含索引第一欄，且順序一致。
- 避免無意義查詢：建空表用
CREATE TABLE，勿用 SELECT ... WHERE 1=0。
- 以 EXISTS 取代 IN：
EXISTS (SELECT 1 FROM b WHERE b.num = a.num) 通常更優。
- 索引非萬能：欄位重複度高（如性別）時索引效果差，優化器可能放棄使用。
- 索引不宜過多：單表建議 ≤ 6 個，權衡 SELECT 效能與 INSERT/UPDATE 成本。
- 慎更新叢集索引鍵：會導致資料物理重排，資源消耗大。
- 優先用數值型別：字串比較逐字元，數值一次比較，效能差異明顯。
- 用 VARCHAR/NVARCHAR 取代 CHAR/NCHAR：節省空間，縮小掃描範圍。
- 禁用 SELECT *：明確列出欄位，減少網路傳輸與 I/O。
三、臨時表與游標最佳實務 (20-27)
- 優先用表變數：輕量、自動清理；大量資料時注意僅支援主鍵索引。
- 避免頻繁建刪臨時表：減少系統表爭用。
- 適度使用臨時表：重複引用大型結果集時有效；一次性操作偏好導出表 (Derived Table)。
- 大量資料用 SELECT INTO：減少 Log 產生；小量資料用
CREATE TABLE + INSERT 緩解系統表壓力。
- 程序結束顯式清理：先
TRUNCATE TABLE 再 DROP TABLE，避免長時間鎖定系統表。
- 避免游標：超過 1 萬列必須改寫；集合導向解法通常更快。
- 先尋找集合解法：僅在小資料集、需逐列處理多表關聯時考慮
FAST_FORWARD 游標。
- 嘗試多種方案：開發時間允許下，基於集合與游標皆測試，取優者。
四、其他關鍵設定 (28-30)
- 設定 SET NOCOUNT ON/OFF：減少
DONE_IN_PROC 網路往返。
- 避免大交易：縮短鎖持有時間，提升並發度。
- 避免回傳大量資料給客戶端：評估需求合理性，必要時分頁或彙總。
總結
這 30 條原則並非鐵律，需依實際業務、資料分佈與硬體環境彈性套用。持續監控執行計畫、索引使用率與等待統計，才能真正實現查詢效能的持續優化。
