萬盛學電腦網

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

資料庫訪問效能優化五大法則與實戰案例

硬體效能階層與優化法則

依硬體速度由快到慢:CPU/Cache → 記憶體 → SSD → 網路 → 傳統硬碟。優化應針對最慢環節,遵循五大法則(由高到低效能投報比):

  1. 減少資料存取(降低硬碟 I/O)
  2. 返回更少資料(降低網路/硬碟傳輸)
  3. 減少交互次數(降低網路延遲)
  4. 減少 CPU 開銷(降低運算/排序成本)
  5. 增加資源(擴充硬體,成本最高)

優化法則金字塔

Oracle 核心概念補充

  • Data Block:磁碟 I/O 最小單位(2K~32K),含多筆記錄。
  • ROWID:記錄唯一定位器(檔案號、物件號、區塊號、槽號),直接定位 Block。

Oracle 物理結構 ROWID 結構

法則一:減少資料存取 — 索引策略

B-Tree 索引結構

根節點 → 分支節點 → 葉節點(索引鍵值 + ROWID)。如字典目錄。

B-Tree 結構 字典目錄比喻

會使用索引的條件

  • col = :val, col > :val, col BETWEEN ..., col IN (...), col LIKE 'abc%'(後導模糊)。
  • 連接條件 t1.col = t2.col(雙方均有索引最佳)。

不會使用索引的條件

不使用索引的條件

  • 函數/運算在索引欄上:UPPER(col) = 'X', col + 1 > 10。
  • 前導萬用字元:col LIKE '%abc'。
  • !=, NOT IN, NOT EXISTS(視計畫)。
  • 隱含轉換導致索引失效。

索引建立決策

  1. 出現在高頻查詢的 WHERE/JOIN/ORDER BY。
  2. 過濾比例低(< 5%-10%,視表大小)。
  3. 避免過度索引:每新增一個索引,DML 開銷增加。
  4. 複合索引遵循「最左前綴」與「高選擇性在前」。

法則二:返回更少資料

  • 禁止 SELECT *,明確列出業務所需欄位。
  • 分頁查詢只取當前頁欄位,避免大字段(LOB)傳輸。
  • 使用覆蓋索引(INCLUDE 欄位)避免 Key Lookup。

法則三:減少交互次數

  • 批次處理:INSERT ... VALUES (...), (...), ...。
  • Table-Valued Parameters (TVP) 替代迴圈呼叫。
  • 預存程序封裝多步驟邏輯,單次往返完成事務。

法則四:減少 CPU 開銷

  • 避免不必要的排序(ORDER BY 僅在需要時加)。
  • 簡化複雜純量函數,改用內聯表值函數或直接展開邏輯。
  • 參數化查詢重用執行計畫,減少編譯 CPU。

法則五:增加資源(最後手段)

  • 讀寫分離、分庫分表、升級 SSD、增加記憶體、Scale-out。
  • 前四大法則未解決瓶頸時再考慮。
copyright © 萬盛學電腦網 all rights reserved