SQL 效能優化五大黃金法則：從寫法到架構的系統提速指南

資深 DBA 親測：解決 SQL 慢查詢的五大核心策略

SQL 效能優化無銀彈，但遵循核心原則可解決 80% 以上效能問題。本文提煉五大黃金法則，附反面教材與正確範例，涵蓋查詢重寫、介面選擇、批次處理、索引策略、欄位設計。

法則一：集合思維 — 以單一複雜查詢替代多次簡單查詢 (N+1 問題)

-- ❌ 反模式：迴圈查詢 (N+1)
Set rsBooks = conn.Execute("SELECT * FROM Books")
Do While Not rsBooks.EOF
    Set rsAuthors = conn.Execute("SELECT Name FROM Authors WHERE AuthorID=" & rsBooks("AuthorID"))
    Response.Write rsBooks("Title") & " - " & rsAuthors("Name") & "
"
    rsBooks.MoveNext
Loop

-- ✅ 正解：單一 JOIN 查詢
Set rs = conn.Execute("
    SELECT B.Title, A.Name 
    FROM Books B 
    INNER JOIN Authors A ON A.AuthorID = B.AuthorID")
Do While Not rs.EOF
    Response.Write rs("Title") & " - " & rs("Name") & "
"
    rs.MoveNext
Loop

原理：減少網路往返、編譯開銷、鎖爭用；優化器可全域規劃最佳連接順序與演算法。

法則二：避免可更新游標 — 直接執行 DML 敘述

-- ❌ 反模式：Recordset 更新 (鎖持久、游標開銷)
rs.Open "SELECT * FROM Authors WHERE AuthorID=17", conn, adOpenKeyset, adLockOptimistic
rs("Name") = "Karl Karlsson"
rs.Update

-- ✅ 正解：直發 UPDATE (自動交易、最短鎖持有)
conn.Execute "UPDATE Authors SET Name='Karl Karlsson' WHERE AuthorID=17"

法則三：批次處理 — 多筆 DML 合併單一往返 + 顯式交易

-- JScript 範例
var sql = "";
sql += "SET XACT_ABORT ON; BEGIN TRAN; ";
sql += "INSERT INTO Orders(OrdID,CustID,OrdDat) VALUES('9999','1234',GETDATE()); ";
sql += "INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','01','G4385',5); ";
sql += "INSERT INTO OrderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','02','G4726',1); ";
sql += "COMMIT TRAN; SET XACT_ABORT OFF; ";
conn.Execute(sql);

關鍵：SET XACT_ABORT ON 確保任一陳述式失敗整批回滾，維持一致性。

法則四：索引策略 — 讓查詢「求取」而非「掃描」

• 叢集索引：依範圍查詢、排序、分組欄位建立 (如 OrderDate)
• 非叢集覆蓋索引：INCLUDE 查詢所需所有欄位，避免書籤查找
• 維護：定期 REBUILD/REORGANIZE + UPDATE STATISTICS
• 審查：sys.dm_db_index_usage_stats 找出未使用索引果斷刪除

法則五：欄位型別精準 — 拒絕過度大型與固定長度浪費

進階補充：查詢計劃閱讀三步曲

1. 最貴操作：最粗箭頭 (成本 %) → 通常是 Clustered Index Scan / Table Scan / Key Lookup
2. 缺失索引提示：綠色提示文字 → 建議建立索引欄位與 INCLUDE
3. 估算 vs 實際行數：懸停工具提示對比 → 差距 > 10 倍 = 統計資料過期/參數嗅探

總結：優化清單

1. ☐ 是否有 N+1 查詢？合併為 JOIN/批次
2. ☐ 是否使用可更新游標？改為直發 DML
3. ☐ 批次作業是否打包交易？
4. ☐ 核心查詢是否有覆蓋索引？
5. ☐ 欄位型別是否精準？
6. ☐ 統計資料是否最新？

效能優化是持續工程，而非一次專案。建立基準、監控趨勢、針對性修正，方能長治久安。