您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 主鍵設計爭議：業務鍵 vs 代理鍵，深度解析與最佳實務

編輯：關於SqlServer

主鍵設計是資料模型的基石，直接影響效能、擴充性、維護成本。本文針對「業務天然鍵」與「無意義代理鍵」的長期爭論，從儲存冗餘、連接複雜度、分頁效能、複寫衝突等維度深度剖析，並給出落地實施方案。

業務鍵（自然鍵）的痛點

傳統設計常直接使用業務欄位（單號、身分證、商品編碼）或其組合（聯集鍵）作為主鍵：

儲存膨脹： 子表需完整複製父表所有主鍵欄位，層層遞歸導致儲存量指數增長。
連接複雜： 多欄位 JOIN 條件冗長，執行計畫難以優化，統計資訊維護成本高。
程式耦合： 應用層需傳遞多參數，DTO/Entity 膨脹，API 簽名不穩定。
效能劣化： 複合索引鍵值大、比較慢、分頁查詢 ORDER BY col1, col2, col3 無法有效利用覆蓋索引。
Web 分頁困境： 複合鍵無法簡單對應 OFFSET/FETCH 或 ROW_NUMBER()，游標式分頁邏輯極其複雜。
異動風險： 業務鍵變更（如單號重編）需級聯更新所有子表，鎖定範圍大、阻塞久。

代理鍵方案：單一整數身分欄

引入無業務含義的 BIGINT IDENTITY 或自建序列作為主鍵（Surrogate Key）：


CREATE TABLE Parent (
    ID       BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,  -- 代理鍵
    BizCode  VARCHAR(30) NOT NULL UNIQUE,               -- 業務鍵加唯一約束
    ...
)

CREATE TABLE Child (
    ID       BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
    ParentID BIGINT NOT NULL REFERENCES Parent(ID),      -- 單欄位 FK
    ...
)

IDENTITY vs 自建序列

特性	IDENTITY	自建序列表
控制權	完全由引擎控制，不可手動指定	應用層可控，支援跨庫、跨表、預取、重置
發布/訂閱	需管理身分範圍，易衝突	天然支援多主節點分配不重疊區間
資料恢復/合併	身分值衝突風險高	可手動調整序列值，彈性大
取值時機	INSERT 後才能取得	INSERT 前即可預取，利於批次邏輯
實作複雜度	零成本	需建立序列表、函數、並發控制

建議： 中小型系統直接用 IDENTITY(1,1)；大型分散式、需跨庫合併、多主寫入場景，投資自建序列（參考 Oracle Sequence）長期受益。

唯一性約束：業務鍵的歸宿

改用代理鍵後，原業務鍵必須加上 UNIQUE CONSTRAINT 或 UNIQUE INDEX 保證語義唯一：


ALTER TABLE Parent ADD CONSTRAINT UQ_Parent_BizCode UNIQUE NONCLUSTERED (BizCode);
-- 或
CREATE UNIQUE INDEX IX_Parent_BizCode ON Parent(BizCode);

應用層亦需保留重複檢查邏輯（先查後插或捕獲 2627 錯誤），雙重保險。

叢集索引策略：別讓代理鍵佔用寶貴資源

代理鍵通常為遞增、不更新、高選擇性，不適合做叢集索引。叢集索引應留給：

範圍查詢高頻欄位（如 OrderDate、CreatedAt）
常用排序/分組欄位
父子表連接鍵（若子表常按父表順序讀取）

代理鍵主鍵建立為 NONCLUSTERED，叢集索引另行設計，實現「主鍵 ≠ 叢集索引」的進階模式。

分頁查詢實戰：代理鍵的殺手級優勢


-- 利用代理鍵單調遞增特性，鍵集分頁，無需 OFFSET 掃描
DECLARE @PageSize INT = 50, @LastSeenID BIGINT = 0;

SELECT TOP (@PageSize) ID, BizCode, Col1, Col2
FROM Parent
WHERE ID > @LastSeenID
ORDER BY ID;  -- 極速，索引覆蓋，無排序運算子

此模式在千萬列表下仍能亞毫秒響應，是高併發 Web API 的標準解法。