性能優化的經濟學法則
- 生命周期三階段：設計（成本最低、收益最大）→ 開發 → 成品（成本最高、收益最小）。
- 優化維度：網絡、硬件、OS、數據庫參數、應用程序。
- 數據支撐：硬件/OS/參數優化合計僅貢獻 ~40% 提升，**應用程序優化（核心是 SQL）貢獻 60%~80%**。
數據庫層面優化：規范化與反規范化的平衡
規范化（正規化）
- 1NF~5NF + BCNF，工程實踐通常滿足 3NF：消除傳遞依賴，結構清晰、易維護。
- 副作用：多表連接增加查詢開銷。
反規范化策略（針對高併發/低延遲場景）
- 分割表
- 水平分割：按行劃分（如按時間範圍），提升單表掃描速度，但跨分區查詢/統計需應用層合併。
- 垂直分割：熱列/冷列分離，窄表增加每塊行數，減少 I/O；適用於熱冷訪問比例懸殊且聯查少的場景。
- 保留冗餘列：在頻繁連接的表間冗餘關鍵列，消除 JOIN。前提：冗餘列低頻變更，否則同步成本過高。
- 增加派生列：預計算聚合/統計列（如訂單金額小計），大幅縮短報表查詢時間。
應用層優化：SQL 語句質量決定系統上限
海量數據下，劣質 SQL 與優質 SQL 速度可差 **百倍以上**。優化重點在 SQL 而非源代碼（改動風險大、收益有限）。
WHERE 子句常見索引失效模式與改寫
| 模式 | 失效原因 | 改寫方案 |
|---|
IS NULL / IS NOT NULL | B*Tree 不存全空鍵值 | 改用位圖索引、函數索引或預設值+檢查約束 |
列連接 col1||col2 = 'val' | 表達式導致索引不可用 | 拆分為 col1='v1' AND col2='v2'；變量場景用 SUBSTR/INSTR 拆解 |
LIKE '%abc%' 通配符前置 | 無法利用索引順序 | 改為 LIKE 'abc%' 或 Oracle Text 全文索引 |
ORDER BY 非索引列/表達式 | 需額外排序操作 | 重寫用索引列排序；或為排序列建索引 |
NOT / <> / != | 否定條件難走索引 | salary<>3000 → salary<3000 OR salary>3000（可用索引範圍掃描） |
IN (subquery) | 先執行子查詢物化臨時表 | 改用 EXISTS (SELECT 'X' ...)：相關子查詢，僅需找到首匹配行 |
NOT IN | 同 IN 且含 NULL 邏輯陷阱 | 改用 NOT EXISTS，效率更高且語義正確 |
IN vs EXISTS 核心差異
IN：子查詢先行 → 物化結果集 → 主查詢掛起 → 連接臨時表。EXISTS：主查詢先行 → 對每行運行子查詢 → 命中即停止（半連接）。- 結論：優先用 EXISTS/NOT EXISTS，僅在子查詢極小、主查詢極大且無索引時考慮 IN。
落地執行清單
- 建立 SQL 審核機制：上線前必經 EXPLAIN PLAN 驗證。
- 定期收集統計信息：
DBMS_STATS.GATHER_SCHEMA_STATS 自動化作業。
- 建立基線庫：核心 SQL 計劃基線（SPM）防止回歸。
- 監控 Top SQL：AWR/ASH 定期分析，針對性優化高消耗語句。
- 索引治理：定期檢測未用/重複/碎片索引，重建或刪除。
