Primary Key

Primary Key 的兩大流派

Natural Key

使用自然存在的 unique key 作 PK
例子: 如果用戶以 email 登入系統的，使用 email 作 PK 便好
例子：貨運站和香港海關都是以（航次日期＋航次編號）作 PK

Surrogate Key

人工合成的 Key ，在商業邏輯上一般上沒有任何意義
auto increment 或是 UUID

Natural Key 的優點

不用再額外建立 Secondary index
在以 email 作登入的系統，即使不用 email 作為 PK ，還是要為 email 建立 index 吧
一般來說，有足夠的隨機性來避免 Contention
對於支援 loose index 的 RDBMS ，Natural key 能減少 Secondary index

Natural Key 的缺點

一般來說，需要比較大的空間例子：用戶 email 總會比 UUID(4 bytes) 更大吧
影響到 PK index 還有 Foreign Key 的大小以今天 SSD 正在持續暴跌的價錢來看，其實問題不太大的
Natural Key 有機會失效某一天，老闆說：我想現在讓用戶也能用 facebook 戶口來登入系統，耶～萬一 Natural key 失效，後果會是災難性的
部份 ORM 對 composite key 的支援不好，開發時很麻煩

Surrogate Key 的優點

即使老闆~~又發瘋~~改動系統邏輯，Surrogate key 幾乎都不受影響
Surrogate Key 的 PK Index 還有 FK 空間都會比較小

Surrogate Key 的缺點

Auto increment 本身是一個潛在的 Contention 位置
單純地使用 Auto increment 會讓所有的 insert 集中在 B+ tree 的其中一邊造成 Contention，Delete 時造成 B+ tree uneven distribution
UUID 不能保證 100% 的 uniqueness ，只是相撞可能性很低

PK 與 Contention

一般商業系統（不考慮報表的部份），90% 的資料讀取／改動都是基於 PK 的
同一時期建立的 Record，它們相近時間被改動／刪除的可能性很高
所以為了避免發生 Contention ，不應該把同一時期建立的 Record 集中儲存在一起
但是，為了提升 Cache hit rate ，同一時間的 Record 不應該過度分散

Monotonic Increasing PK

例子：auto increment ，timestamp
MI PK 問題：同一時期的 record ，集中在 PK index 的最右邊
PK index 左邊的 Record 隨時間而被刪除，所以越左邊的 index leaf page 的 data 密度越低
這就是 index uneven distribution 問題啦
小結：MI PK 會造成 PK index 右邊不停 splitting ，左邊不停的 merging

避開 MI PK 的方法

決定用 Surrogate Key 時，使用 UUID，別用 auto-increment
如果需要使用 Natural Key，而那 Natural Key 卻是帶有 MI 特性
例子 1：航次管理系統中，航次的 PK 是＜出發日期，航次編號＞(＜ 15AUG2015, BR827 ＞)
例子２：PK 是由第三方系統提供的，像是 icq_id
這時候，可以使用簡單方法進行轉換：
方法 1：把 icq_id 倒過來儲存，像 123456 的，在系統中以 654321 作為 PK
方法 2：加上像是 MD5 這樣的 hashing，像是以＜ MD5(出發日期+航次編號)，出發日期，航次編號>作為 composite key
建議使用 RDBMS 內建的 reverse key index，把轉換過程交給 RDBMS 自動化進行