SQL 語句效能調優完整指南：20 條核心原則與實戰技巧

為什麼要優化 SQL？

• SQL 是操作資料庫的唯一途徑，決定系統效能
• 70%-90% 資料庫資源消耗於 SQL 執行
• SQL 優化不影響程式邏輯，風險低
• 同樣結果，不同寫法效能差異可達數量級

SQL 編寫 10 大核心技巧

1. 計算筆數：COUNT(*) 優於 COUNT(1)

經測試，COUNT(*) 經優化器優化後略快於 COUNT(1)；若有索引欄位，COUNT(索引欄) 最快。

2. WHERE 子句避免對欄位做函數/運算

原則：讓欄位保持「乾淨」，利用索引範圍掃描。

3. 用 WHERE 替代 HAVING

HAVING 在分組後過濾，需排序聚合；WHERE 在分組前過濾，減少處理行數。

4. 減少子查詢對表的重複訪問

-- 低效：兩次掃描 lookup
WHERE sal = (SELECT MAX(sal) FROM lookup)
  AND comm = (SELECT MAX(comm) FROM lookup)

-- 高效：單次掃描
WHERE (sal, comm) = (SELECT MAX(sal), MAX(comm) FROM lookup)

5. 善用內部函數減少掃描

-- 兩次全表掃描
SELECT COUNT(*) FROM emp WHERE status='Y' AND ename LIKE 'SMITH%';
SELECT COUNT(*) FROM emp WHERE status='N' AND ename LIKE 'SMITH%';

-- 一次掃描完成
SELECT COUNT(DECODE(status,'Y','X')) Y_cnt,
       COUNT(DECODE(status,'N','X')) N_cnt
FROM emp WHERE ename LIKE 'SMITH%';

6. 使用表別名並加前綴

多表連接時加上別名（如 e.ename），減少解析時間與歧義錯誤。

7. 用 EXISTS 替代 IN

-- 低效
WHERE deptno IN (SELECT deptno FROM emp)

-- 高效：半連接，找到即停止
WHERE EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno)

8. 用 NOT EXISTS 替代 NOT IN

NOT IN 觸發全表掃描與排序合併；NOT EXISTS 或外連接 WHERE emp.rowid IS NULL 效能更佳。

9. 表連接優於 EXISTS/IN

一般情況下，直接 JOIN 效能優於子查詢寫法。

10. 用 EXISTS 替代 DISTINCT

一對多查詢時，DISTINCT 需排序去重；EXISTS 只需判斷存在性。

資料庫層面優化要點

選擇正確的優化器模式

• RULE：基於規則，適用簡單查詢
• COST (CBO)：基於成本，需定期 ANALYZE 收集統計資訊
• CHOOSE：預設模式，若表已分析則用 CBO，否則用 RULE，易產生非預期全表掃描

建議明確設定 OPTIMIZER_MODE=ALL_ROWS 或 FIRST_ROWS，避免 CHOOSE。

表訪問方式

• 全表掃描：多塊讀取，適合大比例資料
• ROWID 訪問：透過索引快速定位，適合小比例資料

共享 SQL 與綁定變數

使用綁定變數（:b1）而非字面值，提升語句共享率，減少硬解析。共享池大小需在 init.ora 適當設定。

其他實用技巧

• FROM 子句右至左處理（RBO）：將小表放最後作驅動表
• WHERE 條件順序：連接條件先寫，過濾性最強的條件寫最後
• 避免 SELECT *：需查詢資料字典轉換欄位名
• 減少資料庫往返：批次處理、陣列綁定
• TRUNCATE 替代 DELETE：不產生 UNDO，釋放空間，不可回滾
• 適時 COMMIT：釋放鎖、UNDO 空間、REDO LOG 緩衝