SQL 資料庫效能調優大全:百萬級資料優化 34 條實戰準則
網路上 SQL 優化教程雜亂無章,本文整理 34 條經實戰驗證的優化準則,涵蓋表順序、WHERE 條件順序、索引使用、函數陷阱、統計資訊、緩存、鎖定、分頁等核心主題,適合百萬級以上資料量的 OLTP/OLAP 場景。
一、查詢結構優化
- FROM 表順序(RBO):Oracle 由右至左解析,將記錄最少的表放在最後(基礎表)。多表聯結時選「交叉表」作為基礎表。
- WHERE 條件順序(RBO):自下而上解析,表聯結條件寫在最前,過濾性最強的條件寫在最後。
- 避免 SELECT *:會查詢數據字典轉換欄位名,增加解析開銷。
- 減少資料庫往返:批次處理、Array Binding、Stored Procedure 減少網路延遲。
- 調大 ARRAYSIZE:SQL*Plus/Forms/Pro*C 中設 200,減少 Fetch 次數。
- 用 DECODE/CASE 避免重複掃描:單次掃描完成多條件分支。
- 整合無關查詢:多個簡單查詢合併為一條(即使無關聯)。
二、DML 優化
- 刪除重複資料用 ROWID:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
- 全表清空用 TRUNCATE 代替 DELETE:不產生 UNDO、不觸發 Trigger、重置 HWM、極快。(注意:TRUNCATE 是 DDL,不可回滾)
- 多用 COMMIT:釋放鎖、UNDO 空間、Redo Log Buffer、內部管理開銷。
- 用 WHERE 代替 HAVING:HAVING 在彙總後過濾,WHERE 在彙總前過濾,減少中間結果集。
- 減少子查詢對表的重複存取:改寫為 JOIN 或使用分析函數。
三、索引與存取路徑
- 善用內部函數提升效率:掌握分析函數、KEEP/DENSE_RANK 等進階語法。
- 使用表別名並加前綴:減少解析時間、避免欄位歧義。
- EXISTS 代替 IN / NOT EXISTS 代替 NOT IN:NOT IN 會觸發全表掃描與排序合併;EXISTS 遇滿足即返回。
- 識別低效 SQL:查詢
V$SQLAREA 找出緩存命中率低、磁碟讀取高的語句。
- 索引策略:大表建索引效果明顯;小表全掃描可能更快。索引需維護(INSERT/DELETE/UPDATE 額外 4-5 次 I/O),定期
ALTER INDEX REBUILD。
- EXISTS 代替 DISTINCT:一對多查詢去重時,EXISTS 只需掃描一次即停止。
- SQL 關鍵字大寫:Oracle 解析時會將小寫轉大寫,大寫省去轉換。
- Java 中少用 + 串聯 SQL:用 PreparedStatement + 參數綁定。
- 避免索引列用 NOT:導致全表掃描。
- 避免索引列參與運算/函數:
SAL*12>25000 → SAL>25000/12。
- >= 代替 >:
DEPTNO>=4 直接定位;>3 需先定位 3 再掃描。
- UNION 代替 OR(索引列):OR 導致全掃;UNION 可分別走索引再合併。若有未索引列則視情況。
- IN 代替 OR:
LOC_ID IN (10,20,30) 通常優於多個 OR。
- 避免索引列 IS NULL/IS NOT NULL:NULL 不存入索引(單列/全空複合索引),改用
>=0 等等價條件。
- 複合索引必須引用第一欄:否則優化器忽略索引。
- UNION ALL 代替 UNION:免去排序去重,但會保留重複行,需業務允許。
- WHERE 代替 ORDER BY:ORDER BY 僅在嚴格條件下用索引;WHERE 可直接走索引篩選。
- 避免隱式類型轉換:
EMP_TYPE=123 導致 TO_NUMBER(EMP_TYPE) 索引失效,顯式轉換或參數化。
- 警惕 WHERE 陷阱:
!=、||、+、同欄位自我比較皆導致全掃。
- 大表小表判斷:超過 30% 資料量用索引優勢不明顯;但通常索引仍比全掃快數倍。
- 避免昂貴操作:DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 觸發 SORT,需足夠 SORT_AREA_SIZE。
- 優化 GROUP BY:先 WHERE 過濾再 GROUP BY,如
WHERE JOB IN ('PRESIDENT','MANAGER') GROUP BY JOB。
小結
優化無銀彈,需結合執行計劃、統計資訊、業務模式綜合判斷。建立「先測量、再優化、再驗證」的閉環習慣,才能在百萬級資料下穩定產出高效能 SQL。