萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 語句優化核心原則與 34 條實戰技巧:提升資料庫效能的關鍵指南

核心原則:簡單即高效

SQL 越簡單,執行計劃越穩定。複雜查詢拆解為臨時表、減少 JOIN、避免深層視圖嵌套,是優化首要策略。

  • 單查詢 JOIN 表 ≤ 5 張
  • 善用臨時表/表變量存放中間結果
  • 少用子查詢,改用 JOIN 或 WITH (CTE)
  • 視圖嵌套 ≤ 2 層

WHERE 子句寫法決定索引生死

1. 操作符陷阱

操作符問題優化方案
IN轉換開銷大,含統計則無法轉 JOIN改用 EXISTS
NOT IN完全不走索引改用 NOT EXISTS 或 LEFT JOIN ... IS NULL
IS NULL / IS NOT NULL索引不存空值,全表掃描改用預設值 (如 status DEFAULT 'active'),查 status > ''
> / <邊界值掃描差異A >= 3 優於 A > 2 (直接定位而非掃描再比較)
LIKE '%abc%'前導通配符失效索引改用全文索引、反轉存儲或 LIKE 'abc%'
UNION隱含排序去重無去重需求用 UNION ALL

2. 運算導致索引失效

-- 錯誤:函數/運算在索引列上
WHERE SUBSTR(col,1,4) = '5400'
WHERE TRUNC(date_col) = TRUNC(SYSDATE)
WHERE col + 20 > 50
WHERE 'X' || col > 'X5400'
WHERE date_col + 5 = SYSDATE

-- 正確:移項、改用範圍
WHERE col LIKE '5400%'
WHERE date_col >= TRUNC(SYSDATE) AND date_col < TRUNC(SYSDATE+1)
WHERE col > 30
WHERE col > '5400'
WHERE date_col = SYSDATE - 5

3. 多列運算 / 隱式轉換

-- 錯誤
WHERE col1 || col2 = '5400250000'
WHERE col1 > col2
WHERE num_col = '123'   -- 字串比數字,隱式 TO_NUMBER(num_col)

-- 正確
WHERE col1 = '5400' AND col2 = '250000'
-- 無法優化 col1 > col2,需業務層面解決
WHERE num_col = 123

SQL 寫法細節影響效能

  • 統一大小寫/格式:相同 SQL 文字才能共享游標,減少硬解析。
  • WHERE 條件順序:過濾性最高 (選擇率最低) 的條件寫在最後 (Oracle 自下而上解析)。
  • FROM 表順序:無統計信息時按順序 JOIN,小表放前;有統計信息優化器自動調整。
  • SELECT * 禁用:解析轉換列名、增加網路傳輸、阻礙覆蓋索引。
  • 減少資料庫往返:批量綁定、陣列處理、ARRAYSIZE 調大 (如 200)。
  • DECODE/CASE 替換多次掃描:單次掃描完成多條件統計。
  • 合併無關查詢:用 UNION ALL 合併結果集,減少連線開銷。

索引設計與維護

  • 大表建索引收益明顯;小表全表掃描可能更快。
  • 組合索引遵循「最左前綴」:WHERE 必須包含第一列。
  • 避免過度索引:每增一個索引,INSERT/UPDATE/DELETE 多 4-5 次 I/O。
  • 定期 ALTER INDEX idx REBUILD 消除碎片。
  • 位圖索引適合低基數列 (性別、狀態),OLTP 慎用。

經典改寫模式 (34 條精華)

  1. EXISTS 替代 IN / NOT EXISTS 替代 NOT IN
  2. UNION ALL 替代 UNION (無去重需求)
  3. WHERE 替代 HAVING (非聚合過濾)
  4. >= 替代 > (邊界值直接定位)
  5. IN 列表替代多個 OR
  6. 外連接 (+) / LEFT JOIN 替代 NOT IN
  7. 避免索引列上 NOT、函數、運算、隱式轉換
  8. 使用表別名 + 列前綴,減少解析歧義
  9. DISTINCT 改用 EXISTS (一對多去重)
  10. TRUNCATE 替代 DELETE (全表清空、無觸發器、不可回滾)
  11. 頻繁 COMMIT 釋放鎖/Undo/Redo 空間
  12. GROUP BY 前先 WHERE 過濾
  13. 識別低效 SQL:V$SQLAREA 按緩存命中率/磁盤讀取排序
  14. 綁定變量代替字面值,提升游標共用
  15. 分頁查詢用 ROW_NUMBER() OVER 或 OFFSET/FETCH (12c+)
  16. 大批量 DML 分批提交,避免 Undo 爆滿
  17. 統計信息定時收集 DBMS_STATS.GATHER_TABLE_STATS
  18. 分區表/分區索引針對大表時間序列查詢
  19. 物化視圖預聚合複雜報表查詢
  20. 結果快取 (Result Cache) 適用於靜態參考數據
  21. 避免隱式字符集轉換 (NLS_LANG 一致)
  22. 使用 /*+ HINT */ 僅作最後手段,並註明理由
  23. 監控執行計劃變更 (SQL Plan Baseline / SPM)
  24. 參數化視圖 / 內聯視圖減少物化開銷
  25. LOB 欄位避免在 WHERE 中直接比較
  26. 死鎖排查:SELECT * FROM V$SESSION_WAIT WHERE EVENT LIKE 'enq%'
  27. 長運行 SQL 終止:ALTER SYSTEM KILL SESSION 'sid,serial#'
  28. 備份窗口調整:ALTER DATABASE BEGIN BACKUP 影響效能
  29. 參數調優:SORT_AREA_SIZE、HASH_AREA_SIZE、PGA_AGGREGATE_TARGET
  30. 讀一致性機制理解:SCN、Undo、一致性讀
  31. 測試環境模擬生產數據量與分佈
  32. 持續整合管線納入 SQL 效能基準測試

總結

SQL 優化無銀彈,核心在於:理解執行計劃、善用索引、減少掃描、控制排序、批量處理、統計信息準確。將上述 34 條內化為編碼習慣,配合定期 EXPLAIN PLAN 複審,方能構建高效能資料庫應用。

copyright © 萬盛學電腦網 all rights reserved