優化階段定位
「設計開發階段是優化的最佳時機，屬主動優化，以最小成本獲取最大效能增益。」
邏輯結構優化
1. 基本表設計：正規化與反正規化平衡
第三範式（3NF）保證一致性與完整性，但可能導致：
- 多表關聯查詢產生大量 JOIN
- 需部分資料時掃描整表
- 消耗磁碟 I/O 與 CPU
反正規化三大手法
| 手法 | 適用場景 | 優點 | 缺點 |
|---|
| 水平分割 | 按範圍/雜湊/清單分區大表 | 提升單分區查詢速度、支援分區裁剪 | 跨分區查詢複雜、統計需匯總 |
| 垂直分割 | 寬表中熱點欄位與冷門欄位分離 | 減少列寬度、增加每頁行數、I/O 效率提升 | 頻繁聯查熱/冷欄位時需 JOIN |
| 保留冗餘欄位 | 頻繁 JOIN 且資料變動少 | 避免 JOIN、查詢極快 | 資料一致性維護成本、儲存空間增加 |
| 增加衍生欄位 | 統計運算頻繁、源資料相對靜態 | 大幅縮短彙總運算時間 | 需定期重算/觸發器維護 |
設計原則
- 頻繁訪問、不頻繁修改 → 物理反正規化
- 頻繁修改、不頻繁訪問 → 物理正規化
- 邏輯設計遵循 3NF，物理實現按訪問模式優化
2. 索引與叢集優化
索引策略
- 建立合理索引可提升查詢效能數十倍
- 避免過度索引：頻繁 DML 的欄位索引導致 B*Tree 重組、效能下降
- 大表索引可能佔滿緩衝區，排擠資料塊 → 考慮分區索引
- 表與索引分離不同表空間/磁碟，避免 I/O 競爭
叢集應用
- 將常 JOIN 表按共同鍵值混合儲存於同一區塊
- 一次 I/O 同時獲得多表資料，減少區塊讀取
- 適用：主從表固定關聯、頻繁聯查、資料量中等
物理結構優化
1. 磁碟讀寫並行化
- 表與索引分離 → 索引掃描與表存取並行
- 不同表空間對應不同物理磁碟/磁碟陣列
- 利用作業系統/儲存層多路徑、條帶化（RAID 0/10）
2. 操作並行優化（基於統計分析）
- 統計各表訪問頻率、表間 JOIN 頻率
- 分配原則：
- 需 JOIN 的表 → 不同表空間/磁碟
- 高頻訪問表 → 分散不同磁碟
- 表結構與索引 → 分離存放
3. 減少動態擴展開銷
動態擴展影響
- 非動態期效能正常，動態期明顯下降
- 分配新區塊 → 可能觸發資料檔/表空間擴展 → 連鎖反應
- 線上系統回應變慢
預先規劃策略
- 建立物件時依應用預估充分計算大小
- 設定合理
INITIAL、NEXT、MINEXTENTS、PCTINCREASE
- 目標：在「不頻繁擴展」與「不過度佔用空間」間平衡
- 數值來源：實際運行一段時間後的統計分析
優化檢查清單（設計評審必查）
| 類別 | 檢查項目 | 驗收標準 |
|---|
| 表設計 | 是否識別熱/冷資料分離 | 垂直/水平分割方案已評估 |
| 表設計 | 冗餘/衍生欄位是否評估 | 維護成本與查詢收益量化比較 |
| 索引 | 高頻查詢欄位是否有索引 | 執行計劃無非預期 FULL SCAN |
| 索引 | 高頻 DML 表索引數量控制 | 單表索引 ≤ 5（視業務而定） |
| 儲存 | 表/索引表空間是否分離 | 不同表空間映射不同磁碟組 |
| 儲存 | 儲存參數是否預設值 | INITIAL/NEXT 已依預估調整 |
| 分區 | 大表是否採用分區策略 | 分區鍵支援分區裁剪 |
| 叢集 | 固定關聯高頻 JOIN 表 | 已評估叢集 vs 索引連接 |
從設計到運行的銜接
- 設計階段決策 → 建庫腳本參數化（INITIAL、NEXT、PCTINCREASE、TABLESPACE）
- 建立基線：上線前跑壓力測試，記錄效能指標
- 運行階段持續收集統計 → 驗證設計假設 → 微調參數
- 建立變更管理：結構變更走評審流程，避免隨意 ALTER
現代架構延伸
雲原生/分散式架構下：
- 分區表 → 分庫分表
- 叢集 → 共置/親和性調度
- 儲存參數 → 儲存類別/IOPS 保障
- 核心思想不變：根據訪問模式佈局資料，減少無效 I/O
