資料庫存取效能優化五大鐵律：從硬體特性到 SQL 重寫的系統化方法論

前言：為什麼優化總是頭痛？

多數文章只講單一技巧（如「加索引」），缺乏系統性框架。本文基於計算機硬體效能層級建立五層優化鐵律，提供從診斷到解決的完整方法論。

硬體效能層級決定優化優先序

從快到慢：CPU → Cache (L1-L3) → 記憶體 → SSD → 網路 → 機械硬碟

每層硬體有兩大指標：

• 延遲：突發處理能力（如單次 I/O 耗時）
• 頻寬：持續處理能力（如吞吐量 MB/s）

五大優化鐵律（按效能提升幅度遞減排序）

第一鐵律：減少資料存取（攻克最慢的機械硬碟 I/O）

核心手段：

1. 正確索引：B-Tree 索引原理、組合索引順序、覆蓋索引、索引選擇性評估公式（小表 <10%，大表依行長度計算）
2. 純索引存取：查詢欄位全在索引中，避免回表（Key Lookup）
3. 執行計畫優化：掌握 10-15 種常見演算法（Nested Loops、Hash Join、Merge Join、Stream Aggregate 等）

索引 DML 代價參考：Insert -56%、Update -47%、Delete -29% 效能下降，寫入密集型系統需謹慎評估。

第二鐵律：返回更少資料（降低網路傳輸與硬碟 I/O）

分頁處理三種模式對比：

Oracle 分頁優化：ROWNUM 直接分頁 vs ROWID 兩階段分頁（純索引定位 ROWID 再回表），後者 I/O 從 550 降至 60（1000筆資料取20筆案例）。

只選需要欄位：減少網路傳輸、伺服器處理、客戶端記憶體，且利於覆蓋索引。大欄位（備註、檔案內容）建議垂直分表隔離。

第三鐵律：減少交互次數（消除網路延遲累積）

1. Batch DML：1000 條一批提交，Insert 效能提升 8 倍，Update/Delete 提升 2-3 倍
2. IN List 合併查詢：單次查詢 IN (id1,id2...idn)，建議 20-100 個值為宜，超過 1000 觸發 Oracle 限制且易改變執行計畫
3. Fetch Size 調大：JDBC 預設 10，調至 100 可顯著降低往返，超過 1000 風險 OOM
4. 預存程序封裝邏輯：減少網路往返，但注意不可移植、維護成本、編譯衝擊等缺點
5. 業務邏輯優化：調整檢查順序（如先查 VIP 再查話費 vs 反向），根據資料分布特性動態決定
6. ResultSet 游標流式處理：TYPE_FORWARD_ONLY, CONCUR_READ_ONLY 替代分頁載入 List，效能提升 2 倍+，避免 JVM OOM

第四鐵律：減少 CPU 運算（優化解析、排序、比較）

1. 綁定變數：軟解析 0.02ms vs 硬解析 1ms，高併發下 CPU 瓶頸從 8000 提升至 16000 QPS（理論值）。例外：資料傾斜嚴重時（如 95% 同一值），需硬解析產生不同執行計畫
2. 合理排序：ORDER BY、GROUP BY、DISTINCT、UNION、MERGE JOIN 皆觸發排序。大結果集排序可能溢位至硬碟，效能崩潰。確認不需去重時用 UNION ALL
3. 減少比較運算：LIKE '%...%'、大型非索引 IN List 耗 CPU。解法：中間表 + Hash Join、str2varList 轉臨時表
4. 複雜運算下推客戶端：加密、三角函數、對數運算在 DB 層會嚴重佔用 CPU，移至應用層處理

第五鐵律：利用更多資源（擴展性手段，治標不治本）

1. 客戶端多執行緒並行：並行數取 min(CPU核心數, 資料磁碟數)，避免調度開銷
2. 資料庫並行查詢：/*+ PARALLEL(table, 4) */，僅適用全表/索引掃描，OLTP 慎用，會佔用大量資源、繞過快取、觸發髒頁寫入

優化成本效益參考表

結語

優化遵循「由上而下診斷，由下而上解決」原則。先看執行計畫確認 I/O 模式，再依序套用五大鐵律。沒有銀彈，只有系統化的工程思維。