精通數據庫系列入門技巧篇 4：核心設計原則與效能優化實戰（經典重讀）

前言：好模型勝過好產品

優秀的數據庫產品 ≠ 優秀的應用系統。缺乏合理模型設計，不僅增加雙端開發維護難度，更會在系統上線後隨數據增長引發效能雪崩。測試階段數據量小，易掩蓋隱患；上線後補救則成「打補丁」工程。本文凝聚多年實戰經驗，提煉核心設計準則。

一、命名規範：跨平台的通用語言

異構數據庫對大小寫敏感度、保留字、長度限制各異。統一採用：

• 大小寫敏感 命名（含後台代碼）
• 長度 ≤ 30 字符
• 避開所有主流資料庫保留字

此舉確保應用系統在 Oracle、SQL Server、Sybase、DB2 間平滑遷移。

二、游標慎用：集合思維的勝利

游標提供逐行掃描能力，但對大表/多表極易導致漫長等待甚至死鎖。實戰案例：

• 住房公積金系統：10 萬賬戶游標計息，預估 48 小時（Alpha/4000, 128M, Sybase 11.0）
• 重構方案：拆分為多條條件式 UPDATE，20 分鐘完成

-- 游標寫法（反模式）
DECLARE MyCursor CURSOR FOR SELECT count_no FROM COUNT
OPEN MyCursor
FETCH MyCursor INTO @vcount_no
WHILE (@@sqlstatus = 0)
BEGIN
  IF @vcount_no = '條件1' 執行操作1
  IF @vcount_no = '條件2' 執行操作2
  FETCH MyCursor INTO @vcount_no
END

-- 集合寫法（正統）
UPDATE COUNT SET 操作1 WHERE 條件1
UPDATE COUNT SET 操作2 WHERE 條件2

萬不得已用游標：先將符合條件的少量行導入臨時表（#tmp），再對臨時表開游標。電信計費系統實測：3 萬行中僅 30 行符合條件，臨時表游標效能顯著提升。

三、索引策略：B 樹藝術的平衡術

索引雙重目的：維護唯一性、加速檢索。SQL Server 兩大索引類型：

• 叢集索引：決定數據物理存儲順序，每表僅一個
• 非叢集索引：邏輯順序與物理順序分離

無叢集索引表採用堆結構（數據追加尾部）。B 樹機制下，任何索引皆加速查詢但拖慢寫入（插入/更新/刪除），填充因子（Fill Factor）越大、頁分裂越頻繁。高寫入表應降低填充因子，預留頁空間減少重組開銷。

四、一致性與完整性：約束與冗余的博弈

• 過度關聯（Relation）雖降低冗余，但每次 DML 皆產生系統開銷
• 避免 Identity 欄位作主鍵關聯子表（遷移時極易斷裂）
• 合理冗余換查詢速度：適度冗余可大幅減少多表連接
• 規則 vs 約束：CHECK 約束驗證效能優於 RULE，但濫用同樣拖累系統
• 設計階段須依業務操作類型、頻次權衡

五、事務陷阱：@@trancount 的隱藏邏輯

顯式事務（BEGIN/COMMIT/ROLLBACK TRANSACTION）支援嵌套，@@trancount 記錄層級。核心機制：

• 每個事務開始（顯式/隱式）@@trancount +1
• 每次 COMMIT @@trancount -1
• 任何 ROLLBACK 直接歸零
• 只有 @@trancount 歸零時的最後一次 COMMIT，才真正寫入磁盤

此機制極易誤導開發者，導致數據未真正持久化或鎖定過久。

六、效能調優：邏輯與物理的折中藝術

硬件網絡固定前提下，效能取決於資料庫設計與客戶端代碼。標準兩步法：

1. 邏輯設計：消除冗余、提升吞吐、保證完整性、清晰表達實體關係
2. 物理設計：針對大表高頻連接查詢，依業務規則引入適度冗余，降低客戶端編程複雜度

七、數據類型選擇：隱形效能殺手

• Identity 禁作主鍵關聯：阻礙數據遷移
• Text/Image 慎用：指針型 BLOB 操作極慢
• 日期型雙面刃：日期運算便利，但 WHERE DATE_COL = '1998-01-01' 或函數包裹（YEAR(DATE_COL)=1998）導致掃表，無法利用索引優化

最佳實踐：日期範圍查詢改用 WHERE DATE_COL >= '1998-01-01' AND DATE_COL < '1999-01-01'，保留 SARGable 特性。

編輯註：本文內容與第 5 篇（ID 86520）高度重合，疑為系列課程重複發布。建議讀者參考第 5 篇作為主版本，本文作為備份存檔。