資料庫設計經驗談：游標慎用、索引策略、數據一致性與事務陷阱

前言

資料庫模型設計優劣直接決定系統性能。本文基於多年 Sybase/SQL Server 實戰經驗，總結四大核心設計原則：游標優化、索引技巧、一致性與冗餘平衡、事務陷阱規避。

一、慎用游標

反模式：大集合逐行處理

某住房公積金系統，10萬賬戶日終計息，游標循環預估 48 小時（Alpha/4000 128MB RAM, Sybase 11.0）。

-- 反模式：游標逐行
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 替代游標
UPDATE COUNT SET 操作1 WHERE 條件1
UPDATE COUNT SET 操作2 WHERE 條件2
-- 耗時從 48h 降至 20 分鐘

必須用游標時：先篩選到臨時表

某電信收費系統，3萬行中僅 30 行符合條件，先導入臨時表再開游標，性能顯著提升（PII 266 64MB RAM, SQL Server 6.5）。

CREATE TABLE #tmp (字段1, 字段2, ...)
INSERT INTO #tmp SELECT * FROM TOTAL WHERE 條件
DECLARE Mycursor CURSOR FOR SELECT * FROM #tmp
-- 對臨時表操作，而非大表

二、索引使用技巧

兩大目的

1. 維護被索引列唯一性
2. 提供快速訪問策略

聚簇 vs 非聚簇

• 無聚簇索引 = 堆表，數據追加在尾部
• 有聚簇索引 = 數據物理順序按聚簇鍵存儲，**一表僅一聚簇索引**
• B 樹結構：任何索引提升查詢速度，但**同時降低增刪改性能**

填充因子策略

• 頻繁增刪改的表 → **設置較小填充因子**（如 70-80%）
• 預留頁面空間，減少頁分裂與重組開銷
• 唯讀/少寫表 → 填充因子 100% 最大化存儲密度

三、數據一致性與完整性：冗餘的辯譯

過度正規化的代價

• 大量表間關聯（FK） → 每次 DML 觸發 FK 檢查，消耗系統資源
• 查詢需多表 JOIN → 響應時間增加
• 避免用 IDENTITY 列作 PK 關聯子表（業務鍵更穩定）

合理冗餘的價值

• 為響應時間引入受控冗餘（如訂單表冗餘客戶名、商品名）
• 用 CHECK 約束替代 RULE（約束驗證更快）
• 根據**操作類型、頻度**在設計階段平衡：
  高頻查詢 → 適度冗餘
  高頻寫入 → 嚴格正規化

四、事務的陷阱

隱式 vs 顯式事務

• 單條 DML 自動有隱式事務（自動提交/回滾）
• 顯式事務：BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN
• 事務可嵌套，@@TRANCOUNT 返回嵌套層級

常見陷阱

1. 長事務鎖定資源： 用戶思考時間包含在事務中 → 阻塞、死鎖、日誌暴漲
   → 事務邊界縮到最小，業務邏輯判斷前置
2. 嵌套事務誤解： 內層 ROLLBACK 會回滾到最外層 BEGIN TRAN，非僅回滾內層
   → 僅在最外層控制提交/回滾，內層用 SAVE TRANSACTION 做部分回滾點
3. 自動提交模式下的陷阱： 客戶端驅動默認行為不一致（ODBC/OLE DB/ADO.NET）
   → 代碼中顯式控制事務邊界，不依賴驅動默認值

總結

• 游標 → 能用集合操作絕不用；必用時先縮小集合
• 索引 → 服務查詢模式，填充因子服務寫入模式
• 一致性 → 約束優於規則，冗餘服務性能，FK 服務核心完整性
• 事務 → 短、平、快；嵌套謹慎；邊界顯式

設計無銀彈，**基於真實負載特徵的權衡**才是良師益友。