SQL 查詢優化實戰：從索引設計到執行計畫分析的完整方法論

資料庫系統是資訊系統核心，查詢操作佔絕大多數負載。當資料量達百萬至千萬筆，全表掃描可能耗時數小時，而良好的查詢策略可將時間壓縮至分鐘級。本文結合理論與實戰，系統說明查詢優化關鍵技術。

查詢處理流程與優化器角色

1. 語法/詞法解析 → 關係代數樹
2. 代數優化：關聯式代數等價轉換（投影下推、選擇下推、聯結重排序）
3. 存取路徑優化：成本基礎最佳化 (CBO) 根據統計資料估算各執行計畫成本（I/O、CPU、記憶體），選擇最低成本計畫
4. 預編譯 → 執行計畫快取 → 執行

關鍵認知：優化器依賴使用者撰寫的 SQL 作為基礎。糟糕的 SQL 經優化器仍難產生高效計畫。開發人員必須掌握優化原則。

七大優化實戰策略

1. 合理使用索引

• 聯結鍵建索引：常用於 JOIN 的欄位（非 FK）優先建立索引。
• 排序/分組欄位：ORDER BY、GROUP BY 欄位建索引可避免 Sort 運算子。
• 高選擇性欄位：WHERE 條件中區分度高的欄位（如身分證號、訂單號）建索引；低選擇性（性別、狀態碼）勿建索引，反而拖慢寫入。
• 複合索引：多欄位排序/篩選遵循「最左前綴」原則，順序 = 等值條件 → 範圍條件 → 排序欄位。
• 索引維護：定期 DBCC SHOWCONTIG/sys.dm_db_index_physical_stats 檢查碎片，大量更新後重建索引。

2. 避免或簡化排序

• 利用索引順序輸出：ORDER BY 欄位順序與索引鍵順序一致時，最佳化器可省略 Sort。
• 避免跨表排序：ORDER BY t1.col, t2.col 無法利用單一索引。
• 必要時縮小排序範圍：先 WHERE 過濾再 ORDER BY，或使用 TOP N 限制輸出行數。

3. 消除大表順序掃描

• 巢狀迴圈聯結中，內層表若無索引會導致 外層行數 × 內層行數 的災難性 I/O。
• 對聯結鍵建索引 是核心對策（如 Student.StudentID、Enrollment.StudentID）。
• 利用 UNION ALL 拆解離散條件：
  WHERE (A=1 AND B>10) OR A=5 → WHERE A=1 AND B>10 UNION ALL WHERE A=5，各分支可獨立利用索引。

4. 避免相關子查詢

• 相關子查詢隨外層行數重複執行，效能隨資料量線性惡化。
• 改寫策略：JOIN、EXISTS、CROSS APPLY、窗口函數、暫存表 預先聚合。
• 必須使用時：在子查詢中盡早過濾、投影最少欄位、確保相關欄位有索引。

5. 避免困難的正規表示式

• LIKE '%keyword%'、LIKE '_pattern'（非開頭萬用字元）無法利用索引，強制掃描。
• 前綴比對 LIKE 'prefix%' 可利用索引求存。
• 字串函數包裹欄位同樣失效：SUBSTRING(col,2,3) = 'abc' → 改用計算欄位+索引或全文搜尋。
• 需模糊搜尋時：考慮 全文檢索、n-gram 索引、Elasticsearch 等專用引擎。

6. 善用暫存表加速複雜查詢

• 將中間結果集物質化至 #TempTable 或 @TableVar，建立索引後後續查詢可重複利用。
• 優勢：避免重複排序/聯結、簡化最佳化器搜尋空間、減少鎖持有時間。
• 注意：暫存表不反映原表即時變更，高併發修改場景需謹慎評估一致性需求。

7. 以排序取代非順序 I/O

• 隨機 I/O（磁頭移動）遠慢於循序 I/O。大量非叢集索引查閱（Key Lookup）產生隨機 I/O。
• 策略：ORDER BY 叢集索引鍵後再處理，或建立覆蓋索引（INCLUDE 欄位）消除 Key Lookup。
• 批次處理模式：INSERT ... ORDER BY ClusterKey 可產生循序寫入，減少頁分割。

現代工具鏈支援

• 查詢存放區 (QDS)：自動捕捉計畫變更、回歸，支援強制計畫。
• 自動調校：ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)。
• 擴展事件：輕量級取代 Profiler，持續監控長時間執行查詢。
• 動態管理檢視：sys.dm_exec_query_stats、sys.dm_exec_requests、sys.dm_os_wait_stats 即時診斷。

總結：優化檢查清單

1. 確認 WHERE/JOIN/ORDER BY 欄位有適當索引（含覆蓋索引）。
2. 檢視執行計畫：是否有 Table Scan、Key Lookup、Sort、Spool、Parallelism 異常。
3. 統計資料是否最新？DBCC SHOW_STATISTICS 驗證基數估計。
4. 是否存在隱含轉換、函數包裹欄位、非 SARGable 條件？
5. 能否將複雜邏輯拆解為暫存表/CTV 多階段處理？
6. 併發場景下鎖升級/死鎖風險？考慮 READ_COMMITTED_SNAPSHOT、SNAPSHOT 隔離等級。