DEE
Database Engineering Essentials

繁體中文

English

繁體中文

English

Appearance

[DEE-101] 主鍵與代理鍵 ​

INFO

每個資料表 MUST 有主鍵。大多數資料表偏好使用代理鍵（自動遞增或 UUID v7）；僅在領域值不可變且無隱私顧慮時使用自然鍵。

背景 ​

主鍵唯一識別資料表中的每一列，並作為外鍵引用的預設目標。在代理鍵（無業務意義的人工識別碼）和自然鍵（來自真實世界資料如 email 或 ISBN）之間的選擇會影響寫入效能、儲存大小、join 成本和長期 schema 彈性。

這個選擇也與儲存引擎互動。在 MySQL/InnoDB 中，主鍵就是叢集索引——列資料按 PK 物理排序。在 PostgreSQL 中，主鍵建立唯一的 B-tree 索引，但堆積預設不按此叢集。

原則 ​

  • 你 MUST 在每個資料表上定義明確的主鍵。
  • 你 SHOULD 使用代理鍵（BIGINT GENERATED ALWAYS AS IDENTITY 或 UUID v7）作為預設選擇。
  • 你 MAY 在以下情況使用自然鍵：(a) 值保證不可變，(b) 無隱私/PII 敏感性，(c) 領域保證唯一性（例如 ISO 國家代碼、IANA 語言標籤）。
  • 你 MUST NOT 使用可變的業務屬性（例如 email、使用者名稱、電話號碼）作為主鍵。
  • 你 SHOULD 在需要 UUID 時偏好 UUID v7 而非 UUID v4，尤其是在 InnoDB 為基礎的系統中。

代理鍵 vs 自然鍵 ​

標準代理鍵自然鍵
穩定性始終穩定——永不改變僅與其背後的業務規則一樣穩定
大小可預測（BIGINT 8 bytes、UUID 16 bytes）不定（email 可達 254 bytes）
Join 成本小型、固定寬度比較可能是寬字串比較
可讀性不透明；需要查找才能理解該列在查詢中對人類有意義
分散式生成UUID 可在任何地方無需協調地生成依賴外部唯一性保證
索引局部性順序（自動遞增）或時間排序（UUID v7）通常是隨機分佈

UUID v4 vs UUID v7 ​

UUID v4 產生 122 位元的隨機性。UUID v7（定義於 RFC 9562）在最高有效位元中嵌入 48 位元的 Unix 時間戳，後接 74 位元的隨機性。這個設計對資料庫索引效能有重大影響：

面向UUID v4UUID v7
排序隨機時間排序（單調遞增）
索引局部性差——新插入分散在 B-tree 各處好——新插入追加到末端附近
頁面分裂（InnoDB）頻繁——比順序 ID 多達 10 倍比 v4 減少約 90% 的頁面分裂
寫入吞吐量大型資料表因隨機 I/O 而降低接近順序插入的效能
時間戳洩漏無可提取毫秒精度的建立時間
快取效率低——工作集分散在許多頁面高——最近的插入共享頁面

在 MySQL/InnoDB 中，主鍵就是叢集索引，隨機的 UUID v4 值迫使持續的頁面重組。UUID v7 在插入方面的行為像自動遞增，同時保留了 UUID 的分散式生成優勢。

範例 ​

自動遞增（PostgreSQL IDENTITY） ​

sql
-- 單一資料庫系統的首選
CREATE TABLE customers (
    customer_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL
);

UUID v7（PostgreSQL 18+ / 應用程式生成） ​

sql
-- 當 ID 必須在資料庫外部或跨多個服務生成時的首選
CREATE TABLE orders (
    order_id    UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

對於 18 版之前的 PostgreSQL，在應用層使用函式庫生成 UUID v7，例如 uuid（Node.js）、uuid7（Python）或 java.util.UUID（Java 17+ 搭配函式庫）。

自然鍵（適當時使用） ​

sql
-- ISO 3166-1 alpha-2 代碼是不可變的、簡短的，且全球唯一的
CREATE TABLE countries (
    code  CHAR(2) PRIMARY KEY,  -- 例如 'US'、'TW'、'JP'
    name  TEXT NOT NULL
);

MySQL AUTO_INCREMENT ​

sql
-- MySQL 版本的 IDENTITY
CREATE TABLE products (
    product_id  BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    price       DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

常見錯誤 ​

錯誤為何有害修正
可變自然鍵作為 PK（例如 email、使用者名稱）當值變更時，每個 FK 引用都必須更新——級聯或中斷使用代理 PK；將自然值保留為 UNIQUE 約束
UUID v4 作為 InnoDB 的叢集 PK隨機插入在大型資料表上造成過多頁面分裂和寫入放大改用 UUID v7，或使用 BIGINT AUTO_INCREMENT 搭配獨立的 UUID 欄位對外曝露
高流量資料表上的複合 PK寬鍵增加索引大小和子表中的 FK 引用成本使用代理 BIGINT PK；以 UNIQUE 約束強制複合唯一性
完全沒有主鍵某些 ORM 和複製系統需要 PK；沒有的話，刪除和更新無法有效地定位特定列始終定義明確的 PK
在公開 API 中暴露順序 ID允許列舉攻擊（爬蟲、IDOR）對外使用 UUID；將順序 ID 保留在內部
在 PostgreSQL 中使用 SERIAL 而非 IDENTITYSERIAL 建立語義較鬆散的隱式序列；IDENTITY 符合 SQL 標準並避免意外的手動插入新的 schema 使用 GENERATED ALWAYS AS IDENTITY

相關 DEE ​

參考資料 ​