SQL 語句優化 34 條鐵律：從寫法、索引、架構全方位提升資料庫效能

簡化原則

1. 單查詢 JOIN 表數 ≤ 5，超過拆分或用臨時表。
2. 中間結果集用 臨時表/表變數 暫存，避免超大子查詢。
3. 少用子查詢，改用 JOIN 或 APPLY。
4. 視圖嵌套 ≤ 2 層，深層視圖優化器難估算基數。

WHERE 子句寫法優化

SQL 文本一致性

• 同一邏輯 大小寫、空格、換行、擁有者前綴 完全一致，利用計畫快取重用，減少編譯與記憶體碎片。

條件順序與表順序

• WHERE 條件：選擇性最高（過濾率最低）的寫最前（舊版優化器自下而上解析），新版自動重排但好習慣仍有益。
• FROM 表順序：小表/篩選後小結果集寫前面（舊版依序連接），新版自動重排。

索引利用鐵律

1. 避免索引欄位上做函數/運算：SUBSTR(col,1,4)='5400' → col LIKE '5400%'；col+5=10 → col=5。
2. 隱式轉換殺索引：WHERE varchar_col = 123 → WHERE varchar_col = '123'。
3. 復合索引必須引用前導欄，否則全表掃描。
4. 定期 ALTER INDEX ... REBUILD 消除碎片，更新統計資訊。

進階重寫技巧

• DISTINCT → EXISTS（一對多去重）。
• HAVING → WHERE 前置過濾（分組前先過濾）。
• DELETE 全表 → TRUNCATE（最小日誌、重置種子，DDL 不可回滾）。
• 批次提交 COMMIT 釋放鎖、日誌空間。
• GROUP BY 前置 WHERE 過濾。
• 大量資料分頁用鍵值游標 (WHERE id > @lastId) 替代 OFFSET。

資源消耗操作識別

DISTINCT, UNION, MINUS, INTERSECT, ORDER BY 觸發 SORT，評估 SORT_AREA_SIZE 記憶體設定。

偵測低效 SQL 腳本

SELECT TOP 20
    EXECUTIONS, DISK_READS, BUFFER_GETS,
    ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) AS Hit_Ratio,
    ROUND(DISK_READS/EXECUTIONS,2) AS Reads_Per_Run,
    SQL_TEXT
FROM V$SQLAREA  -- Oracle 視圖，SQL Server 對應 sys.dm_exec_query_stats
WHERE EXECUTIONS > 0 AND BUFFER_GETS > 0
  AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY Hit_Ratio;

總結

優化核心：減少掃描、利用索引、避免排序、重用計畫。每條規則皆有適用場景，實測為準，切勿教條套用。