萬盛學電腦網

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

SQL Server 表設計核心:主鍵選型策略——業務鍵 vs 代理鍵深度解析

業務主鍵(自然鍵)的痛點

隨著主從關係層層延伸(主表單據號 → 明細表單據號+商品碼 → 子明細單據號+商品碼+價格類別),帶來四大問題:

  • 資料冗餘:多層表重複儲存上層鍵值,儲存空間膨脹。
  • SQL 複雜化:多欄位 JOIN、WHERE 條件冗長,執行計畫難以最佳化。
  • 程式複雜化:傳遞多參數、建構複雜鍵值物件,開發維護成本高。
  • 效能下降:複合索引體積大、比較成本高、分頁查詢極其困難。

解決方案:代理鍵

為每張表新增一個 無業務含義的數值型單欄主鍵(如 BIGINT IDENTITY 或自建序列)。

方案 A:IDENTITY 自增欄(簡單但有限制)

CREATE TABLE T_PK_DEMO (
    U_ID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    COL_OTHER VARCHAR(20) NOT NULL
);

限制:值由系統完全控制、無法手工指定/修改、複寫/還原/資料移轉時麻煩、插入後才能取得值(需 SCOPE_IDENTITY())。

方案 B:自建序列表(參考 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 9223372036854775807,
    SEQ_STEP INT NOT NULL DEFAULT 1,
    IF_CYCLE BIT NOT NULL DEFAULT 0,
    PRIMARY KEY CLUSTERED (SEQ_NAME, SEQ_OWNER)
);

-- 核心函數(概念)
-- F_CREATE_SEQ(@name)         -- 建立序列
-- F_GET_NEXT_SEQ_VAL(@name)   -- 取得下一值(含並發控制)
-- F_GET_SEQ_CUR_VAL(@name)    -- 取得當前值
-- F_SET_SEQ_VAL(@name, @val)  -- 設定值
-- F_DEL_SEQ(@name)            -- 刪除序列

應用層先呼叫 F_GET_NEXT_SEQ_VAL 取得主鍵值,再執行 INSERT,完全掌控時機與值。

主從表關聯重構

-- 父表
CREATE TABLE T_PK_DEMO (
    U_ID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    COL_OTHER VARCHAR(20) NOT NULL
);

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

業務唯一性保障(代理鍵不替代業務鍵約束)

代理鍵解決關聯效能,但業務規則(如使用者帳號唯一)仍需強制:

  • 資料庫層:建立 UNIQUE CONSTRAINT 或 UNIQUE NONCLUSTERED INDEX。
  • 應用層:寫入前檢查,作為纵深防禦。

叢集索引配置原則

代理鍵主鍵建議 NONCLUSTERED,將寶貴的叢集索引留給:

  1. 範圍查詢高頻欄位(如 OrderDate、CreateTime)
  2. 常用排序/分組欄位
  3. 窄且遞增的欄位(減少頁分裂)

實戰收益:Web 分頁查詢效能飛躍

單欄代理鍵使得分頁查詢(OFFSET/FETCH 或 TOP + 鍵值定位)極其簡單高效,避免複合鍵分頁的噩夢。配合覆蓋索引,大資料量分頁毫秒級回應。

copyright © 萬盛學電腦網 all rights reserved