萬盛學電腦網

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

SQL Server 主鍵設計最佳實踐:從業務鍵到代理鍵的演進與序列實作

資料庫邏輯設計中,主鍵策略直接影響系統效能、擴展性與維護成本。本文深入剖析傳統「業務主鍵/聯合主鍵」在主從表架構下的弊端,提出採用「非業務代理鍵」搭配自訂序列機制的最佳實踐方案,並提供完整 T-SQL 實作碼。

一、 傳統業務主鍵/聯合主鍵的痛點

在傳統設計中,主鍵常直接源於業務邏輯(如銷售單號、商品編碼),隨著主從關係層級加深(主表 → 明細表 → 子明細表),會衍生出嚴重問題:

  • 資料冗餘嚴重:子表必須重複儲存父表所有主鍵欄位,資料量隨層級指數增長。
  • SQL 查詢複雜化:JOIN 條件包含多欄位,SQL 變長、難讀、易錯。
  • 程式開發負擔重:傳遞參數多、Entity 物件笨重、ORM 映射困難。
  • 效能損耗:複合索引體積大、比較成本高、統計資訊維護貴。
  • Web 分頁困境:複合鍵難以支援標準分頁邏輯(如 `OFFSET/FETCH` 或 `ROW_NUMBER`)。

二、 解決方案:引入非業務代理鍵

核心原則:每張表(含主表、從表)均新增一個無業務意義的數值型欄位(如 `U_ID BIGINT`)作為主鍵(PK)。子表透過儲存父表的 `U_ID` 作為外鍵(FK)建立關聯。

1. 為何不直接用 IDENTITY 自增欄?

雖然 SQL Server 原生 `IDENTITY` 簡單易用,但存在硬傷:

  • 使用者無法干預數值(難以手動指定、回填、合併資料)。
  • 發行/訂閱複寫、資料還原、跨資料庫合併時極度麻煩。
  • 插入後才能取得值(需 `SCOPE_IDENTITY()`),批次作業不便。

2. 參考 Oracle Sequence 實作自訂序列機制

建立序列管理表 `T_DB_SEQ`,模擬 Oracle Sequence 功能:

CREATE TABLE T_DB_SEQ (
    SEQ_NAME   VARCHAR(50) NOT NULL,        -- 序列名稱
    SEQ_OWNER  VARCHAR(50) NOT NULL DEFAULT 'DBO', -- 擁有者
    SEQ_CURRENT BIGINT NOT NULL DEFAULT 0,   -- 目前值
    SEQ_MIN    BIGINT NOT NULL DEFAULT 0,     -- 最小值
    SEQ_MAX    BIGINT NOT NULL DEFAULT 0,     -- 最大值 (原文有誤重複定義 SEQ_MIN,此處修正)
    SEQ_STEP   INT NOT NULL DEFAULT 1,        -- 遞增步長
    IF_CYCLE   INT NOT NULL DEFAULT 0,        -- 是否循環 (0=否, 1=是)
    CONSTRAINT PK_T_DB_SEQ PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);

配合三大核心函數(需自行實作邏輯,建議用預存程序 + 交易鎖定保證並發安全):

  • F_Create_SEQ(@SeqName):建立序列記錄。
  • F_Get_Next_Seq_Val(@SeqName):取得下一值(核心,需處理並發更新 `SEQ_CURRENT += SEQ_STEP`)。
  • F_Get_Seq_Cur_Val(@SeqName):取得目前值。

輔助函數:`F_Set_Seq_Val`、`F_Del_Seq`、`F_Seq_Exists`。

3. 表結構定義範例

主表

CREATE TABLE T_PK_DEMO (
    U_ID      BIGINT NOT NULL,          -- 代理主鍵 (由序列產生)
    COL_OTHER VARCHAR(20) NOT NULL,
    CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)
);

子表 (含外鍵)

CREATE TABLE T_PK_DEMO_C (
    U_ID      BIGINT NOT NULL,          -- 子表自身代理主鍵
    COL_OTHER VARCHAR(20) NOT NULL,
    P_ID      BIGINT NOT NULL,          -- 父表 U_ID (外鍵)
    CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY NONCLUSTERED (U_ID),
    CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID) 
        REFERENCES T_PK_DEMO(U_ID) ON DELETE CASCADE
);

三、 業務唯一性約束的替代方案

改用代理鍵後,原業務鍵(如帳號、身分證字號)失去 PK 唯一約束,必須額外處理:

  1. 資料庫層:建立 `UNIQUE CONSTRAINT` 或 `UNIQUE NONCLUSTERED INDEX`。
    ALTER TABLE T_PK_DEMO ADD CONSTRAINT UQ_Col_Other UNIQUE NONCLUSTERED (COL_OTHER);
  2. 應用層:寫入前檢查重複(不推薦作為唯一防線,易有競態條件)。

四、 索引策略:為何主鍵用 NONCLUSTERED?

代理鍵(`U_ID`)特性:唯一、極少更新、無業務查詢意義。
聚集索引(Clustered Index)資源稀缺(每表僅一個),應留給高頻範圍查詢、排序、JOIN 的業務欄位(如 `CreateDate`、`CategoryID`)。
因此,代理鍵主鍵預設建立 NONCLUSTERED(非叢集)索引,避免佔用寶貴的叢集索引位置。

五、 實戰效益:海量資料分頁查詢優化

採用單一整數代理鍵後,分頁查詢變得極其高效:

-- 範例:取得第 2 頁,每頁 50 筆,依建立時間降冪
SELECT * FROM T_PK_DEMO 
ORDER BY CreateDate DESC 
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

搭配存儲程序在 DB 端分頁,避免大量資料傳輸至 Web 伺服器,大幅降低網路頻寬與記憶體壓力。

結語

「代理鍵 + 自訂序列」模式雖增加初期建置成本(序列管理表、函數開發),但從長遠看,它解耦了業務邏輯與實體儲存,消除了聯合主鍵的連鎖副作用,是大型、高併發、長生命週期企業級 SQL Server 應用的架構標準配置。

copyright © 萬盛學電腦網 all rights reserved