SQL 語句極致優化 34 條鐵律：從 Oracle 到通用關聯式資料庫的效能聖經

前言

優化 SQL 不僅是寫對語法，更是寫出「優化器友好」的語句。本文整理 34 條跨資料庫 (Oracle、SQL Server、MySQL、PostgreSQL) 通用的優化鐵律，附反模式對照與原理解析。

查詢結構與順序優化

1. 表連接順序：小表驅動大表 (RBO) / 讓優化器決定 (CBO)

舊版 Oracle RBO：FROM 子句最右表優先掃描 → 將結果集最小的表放最後 (作為驅動表)。現代 CBO 依統計資訊自動決定，重點在收集準確統計。

2. WHERE 條件順序：連接條件先行、高選擇性過濾後置 (RBO)

RBO 自下而上解析 WHERE：表連接條件寫在前面，過濾性最強 (過濾掉最多行) 的條件寫在最後。CBO 自動重排，但明確寫法利於維護。

3. 避免 SELECT * — 明確列出欄位

解析階段需查詢資料字典展開 *，增加解析時間；阻礙覆蓋索引；增加網路/IO/記憶體開銷。

4. 減少資料庫往返 — 批次操作、陣列綁定

單次往返開銷 (網路延遲、解析、綁定、執行) 遠大於單列處理。使用 Array Binding (JDBC addBatch、ODP.NET ArrayBindCount、PDO execute 迴圈) 批次 INSERT/UPDATE。

5. 調大陣列提取大小 — 減少 FETCH 次數

Oracle ARRAYSIZE、JDBC setFetchSize、ODP.NET FetchSize 建議 200-500，大幅減少網路往返。

函數與運算式優化

6. 善用 DECODE / CASE — 避免重複掃描/連接

單次掃描完成多條件分支，替代多次 UNION ALL 或自連接。

7. 合併無關簡單查詢 — 減少往返

多個獨立查詢可用 UNION ALL 合併 (即使無關)，單次往返回多結果集 (需驅動支援多結果集)。

8. 刪除重複資料：用 ROWID / CTE / 窗函數

-- Oracle/PostgreSQL/SQL Server 通用 (CTE + ROW_NUMBER)
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY rowid) rn
  FROM emp
)
DELETE FROM emp WHERE rowid IN (SELECT rowid FROM cte WHERE rn > 1);

9. TRUNCATE 替代 DELETE (全表清空)

TRUNCATE：DDL、不產生 REDO/UNDO、不觸發 Trigger、瞬間完成、釋放 High Water Mark。DELETE：DML、逐列、可回滾、產生大量 UNDO/REDO。

10. 適度 COMMIT — 釋放鎖與 UNDO 空間

長交易持有鎖阻塞並發、UNDO 表空間膨脹、恢復時間變長。批次處理每 N 千筆 COMMIT 一次。

11. WHERE 替代 HAVING — 早過濾

HAVING 在 GROUP BY 後過濾，需先聚合全量資料。WHERE 在聚合前過濾，大幅減少參與聚合的行數。非聚合條件必須寫 WHERE。

12. 減少子查詢表掃描 — 改寫為 JOIN 或半連接

-- 反模式
SELECT * FROM t1 WHERE (col1, col2) IN (SELECT col1, col2 FROM t2 WHERE ver=604);
-- 優化
SELECT t1.* FROM t1 JOIN t2 ON t1.col1=t2.col1 AND t1.col2=t2.col2 WHERE t2.ver=604;

13. 善用內建函數簡化邏輯 — 但避免在索引欄上使用

NVL/COALESCE、REGEXP、JSON_EXTRACT 等在投影列使用無礙；在 WHERE 欄位上使用會導致索引失效。

14. 使用表別名 — 減少解析歧義、提升可讀性

SELECT e.empno, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

15. EXISTS 替代 IN / NOT EXISTS 替代 NOT IN

IN 子查詢常物化為臨時表；EXISTS 採半連接，滿足即停止。NOT IN 若子查詢含 NULL 則永回傳空集 (三值邏輯陷阱)，NOT EXISTS 安全且高效。

16. 識別低效 SQL — 查詢 V$SQL / sys.dm_exec_query_stats / pg_stat_statements

關鍵指標：Buffer Gets / Executions (邏輯讀/次)、Disk Reads / Executions (物理讀/次)、Hit Ratio、Elapsed Time。

索引相關鐵律

17. 定期 REBUILD 索引 — 消除碎片、回收空間

ALTER INDEX idx_name REBUILD [ONLINE];

18. EXISTS 替代 DISTINCT (一對多去重)

-- 反模式
SELECT DISTINCT d.deptno, d.dname FROM dept d JOIN emp e ON d.deptno = e.deptno;
-- 優化
SELECT d.deptno, d.dname FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno);

19. SQL 關鍵字大寫 — 減少解析器大小寫轉換 (微優化)

20. Java 字串連接用 StringBuilder — 避免 + 產生大量臨時物件

21. 避免索引欄用 NOT / 函數 / 運算 — 導致全表掃描

-- 反模式                    -- 優化
WHERE sal * 12 > 25000       WHERE sal > 25000/12
WHERE UPPER(name) = 'JOHN'   WHERE name = 'JOHN' (配合函數索引或應用層統一大寫)
WHERE deptno != 10           WHERE deptno < 10 OR deptno > 10 (視分佈)

22. >= 替代 > (整數欄) — 直接定位首符合行

WHERE deptno >= 4  -- 直接跳到 4
WHERE deptno > 3   -- 先定位 3 再掃描下一行

23. UNION 替代 OR (索引欄) — OR 導致全表掃描

-- 反模式
SELECT * FROM loc WHERE loc_id = 10 OR region = 'MELBOURNE';
-- 優化 (兩欄皆有索引)
SELECT * FROM loc WHERE loc_id = 10
UNION
SELECT * FROM loc WHERE region = 'MELBOURNE';

24. IN 替代多重 OR (離散值)

WHERE loc_id IN (10, 20, 30);  -- 優化器可用 IN-list 迭代器

25. 避免索引欄 IS NULL / IS NOT NULL — NULL 不進 B+ 樹 (單列索引)

-- 反模式
WHERE dept_code IS NOT NULL;
-- 優化 (假設 dept_code >= 0)
WHERE dept_code >= 0;

26. 複合索引必須用到導欄 (最左前綴)

索引 (a,b,c)，查詢 WHERE b=1 無法使用；WHERE a=1、WHERE a=1 AND b=2 可用。

27. UNION ALL 替代 UNION (無需去重) — 免排序

UNION = UNION ALL + SORT DISTINCT。業務允許重複時必用 ALL。

28. WHERE 替代 ORDER BY (利用索引順序) — 避免 Filesort

索引 (dept_type)，WHERE dept_type > 0 自然按索引順序輸出，免 ORDER BY dept_type。

29. 避免隱式類型轉換 — 索引失效元兇

-- 隱式轉換發生在索引欄
WHERE emp_type = 123  -- emp_type 是 VARCHAR，Oracle 轉為 TO_NUMBER(emp_type)=123 → 索引失效
-- 顯式轉換
WHERE emp_type = '123';

30. 警惕導致索引失效的 WHERE 寫法

• col != val / col <> val
• col1 || col2 (字串連接)
• col + 0 (數學運算)
• col1 = col2 (同表索引欄互比)

31. 選擇率 > 30% 時全表掃描可能更快 — 不要強迫索引

優化器基於成本模型，若預估讀取 > 30% 區塊，全表掃描 (多區塊讀取) 往往優於索引範圍掃描 + 回表。

32. 避免高成本操作：DISTINCT/UNION/MINUS/INTERSECT/ORDER BY — 觸發 SORT

DISTINCT 1 次排序，其餘至少 2 次。可用 ROW_NUMBER() 窗函數替代 DISTINCT、應用層去重。

33. GROUP BY 前置過濾 — 縮小聚合輸入

-- 反模式
SELECT job, AVG(sal) FROM emp GROUP BY job HAVING job IN ('PRESIDENT','MANAGER');
-- 優化
SELECT job, AVG(sal) FROM emp WHERE job IN ('PRESIDENT','MANAGER') GROUP BY job;

34. 參數化查詢 / 綁定變數 — 計畫重用、防注入

字面值硬編碼導致硬解析、計畫庫膨脹、Cursor Sharing 壓力。永遠使用綁定變數。

總結：優化檢查清單 (每條 SQL 上線前自審)

1. 是否避免 SELECT *？
2. WHERE/JOIN/ORDER BY 欄位是否有索引？
3. 是否在索引欄上使用函數/運算/NOT/IS NULL？
4. 子查詢是否可改寫為 JOIN/EXISTS？
5. UNION 是否可改 UNION ALL？
6. HAVING 條件是否可下推至 WHERE？
7. 是否使用綁定變數？
8. EXPLAIN 執行計畫是否符合預期 (type、key、rows、Extra)？

優化是工程，不是玄學。建立基線 → 識別瓶頸 → 針對性重寫/加索引/調參 → 驗證 → 固化 的閉環，才是長久之計。