您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 語句效能優化完整指南：34 條核心準則與最佳實踐

編輯：MySQL 綜合教學

SQL 語句效能優化完整指南：34 條核心準則與最佳實踐

隨著資料量增長，SQL 效能優化成為系統穩定性的關鍵。優質 SQL 與劣質 SQL 執行速度可相差數百倍。本文整理 34 條經過生產環境驗證的優化準則，涵蓋運算子優化、索引利用、查詢重寫、架構設計等維度，助你寫出高效能 SQL。

一、運算子與條件優化

1. IN vs EXISTS

IN 缺點：Oracle 需轉換為多表連接或先執行子查詢，多一轉換過程
推薦：業務密集 SQL 改用 EXISTS 替代 IN
NOT IN 禁用：無法使用索引，必須改用 NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL

2. IS NULL / IS NOT NULL

索引不儲存 NULL 值，任何包含 NULL 的複合索引欄位都會從索引排除
替代方案：IS NOT NULL → > 0 或 > ''；設計階段設定 NOT NULL DEFAULT ''

3. 比較運算子優化

原寫法	優化寫法	原理
`A > 2`	`A >= 3`	直接定位到 3，避免先找 2 再比較
`LIKE '%5400%'`	`LIKE 'X5400%' OR LIKE 'B5400%'`	前導萬用字元無法用索引，指定前綴可用範圍索引
`col IS NOT NULL`	`col > 0` / `col > ''`	啟用索引掃描

4. UNION vs UNION ALL

UNION：合併後排序去重，大資料量可能用到磁碟排序
UNION ALL：直接合併返回，效能大幅提升
原則：業務允許重複或確定無重複時，優先用 UNION ALL

5. 列連接導致索引失效

-- 錯誤：連接列無法用索引
WHERE first_name || '' || last_name = 'Bill Clinton'

-- 正確：拆分條件
WHERE first_name = 'Bill' AND last_name = 'Clinton'

6. ORDER BY 優化

避免在 ORDER BY 中使用非索引欄位或運算表達式
為排序欄位建立索引，或重寫查詢使用索引順序

7. NOT 邏輯優化

-- 低效：無法用索引
WHERE status <> 'INVALID'
WHERE NOT (status = 'VALID')

-- 高效：拆分為 OR 條件（可用索引）
WHERE salary < 3000 OR salary > 3000

二、SQL 寫作習慣影響效能

8. 統一 SQL 寫法利用共享記憶體

Oracle SGA 依 SQL 字串完全匹配快取執行計畫
大小寫、空格、表擁有者前綴不一致會產生多份計畫，浪費記憶體
規範：團隊統一 SQL 格式化標準（大寫關鍵字、固定縮排、加表別名）

9. WHERE 條件順序影響 CPU 耗用

過濾率高（資料少）的條件優先寫在前面
範例：xh_bz=1 AND dy_dj='1KV以下' 優於反序（0.5% vs 99% 過濾率）

10. 表連接順序

無統計資訊時，Oracle 按 FROM 子句順序連接
小表優先連接大表，減少中間結果集
有統計資訊時優化器自動調整

三、索引利用核心原則

11. 函數/運算導致索引失效

失效寫法	優化寫法
`SUBSTR(hbs_bh,1,4)='5400'`	`hbs_bh LIKE '5400%'`
`TRUNC(sk_rq)=TRUNC(SYSDATE)`	`sk_rq >= TRUNC(SYSDATE) AND sk_rq < TRUNC(SYSDATE)+1`
`ss_df+20 > 50`	`ss_df > 30`
`'X' \|\| hbs_bh > 'X5400021452'`	`hbs_bh > '5400021542'`

12. 隱式型別轉換殺手

-- 字串欄位比較數字，觸發 TO_NUMBER 導致索引失效
WHERE hbs_bh = 5401002554

-- 修正：加上引號
WHERE hbs_bh = '5401002554'

13. 複合索引最左前綴原則

複合索引 (A,B,C) 僅當 WHERE 條件包含 A 時才會被使用
僅引用 B 或 C 會導致全表掃描

四、進階優化技巧（14-34）

#	優化準則	關鍵點
14	選擇最高效表順序（RBO）	FROM 右到左解析，記錄最少的表放最後（基礎表）
15	WHERE 連接條件優先於過濾條件	表連接寫在前，過濾率高的條件寫在後
16	避免 SELECT *	解析 * 需查資料字典，列出具體欄位
17	減少資料庫存取次數	合併查詢、使用陣列繫結、調大 ARRAYSIZE
18	使用 DECODE/CASE 減少重複掃描	單次掃描完成多條件聚合
19	整合無關聯簡單查詢	多個獨立查詢合併為一個
20	刪除重複記錄用 ROWID	`DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)`
21	TRUNCATE 替代 DELETE（全表清空）	不產生回滾段，極速執行，不可回滾
22	適度提交 COMMIT	釋放回滾段、鎖、redo log buffer
23	WHERE 替代 HAVING	分組前過濾優於分組後過濾
24	減少子查詢表存取	改用 JOIN 或合併條件
25	善用內部函數提升效率	如分析函數、窗口函數替代複雜子查詢
26	使用表別名	減少解析時間、避免欄位歧義
27	EXISTS 替代 IN / NOT EXISTS 替代 NOT IN	子查詢效能顯著提升
28	識別低效 SQL	查詢 V$SQLAREA，關注 BUFFER_GETS/EXECUTIONS、HIT_RATIO
29	索引維護與重建	定期 `ALTER INDEX REBUILD`，移除無用索引
30	EXISTS 替代 DISTINCT（一對多查詢）	滿足條件立即返回，避免排序去重
31	SQL 關鍵字大寫	減少解析器小寫轉大寫開銷
32	Java 代碼少用 + 連接字串	使用 StringBuilder 或 PreparedStatement
33	避免索引列用 NOT	導致全表掃描
34	避免索引列運算	`SAL * 12 > 25000` → `SAL > 25000/12`

五、特殊場景優化補充

35. >= 替代 >

-- 高效：直接跳到 4
WHERE deptno >= 4
-- 低效：先定位 3 再掃描
WHERE deptno > 3

36. UNION 替代 OR（索引欄位）

-- 高效：兩次索引掃描合併
SELECT ... WHERE loc_id = 10 UNION SELECT ... WHERE region = 'MELBOURNE'
-- 低效：全表掃描
SELECT ... WHERE loc_id = 10 OR region = 'MELBOURNE'

37. IN 替代多個 OR

WHERE loc_id IN (10,20,30)  -- 通常優於多個 OR

38. 避免索引列 IS NULL/IS NOT NULL

單列索引：NULL 不進索引
複合索引：全 NULL 不進索引，部分 NULL 進索引
唯一索引允許多筆全 NULL 記錄

39. WHERE 替代 ORDER BY

-- 低效：ORDER BY 非索引欄位
SELECT dept_code FROM dept ORDER BY dept_type
-- 高效：WHERE 條件啟用索引
SELECT dept_code FROM dept WHERE dept_type > 0

40. 避免隱式型別轉換

-- 字串索引列比較數字 → TO_NUMBER(索引列) → 索引失效
WHERE emp_type = 123
-- 修正：顯式轉換常數
WHERE emp_type = '123'

41. 30% 門檻法則

查詢資料超過表總筆數 30%，全表掃描可能優於索引掃描
但通常情況索引仍快數倍至數千倍

42. 避免耗資源操作

DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 觸發排序
DISTINCT 一次排序，其他至少兩次
可用 EXISTS、ROW_NUMBER() 重寫

43. 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