MySQL 如何設計主鍵

本篇說明 Mysql 的主鍵問題，從為什麼的角度來了解 Mysql 主鍵相關的知識，並拓展到主鍵的生成方案問題。再也不怕被問到 Mysql 時只知道 CRUD 了。

一、為什麼需要主鍵

資料記錄需具有唯一性(第一範式)

#資料需要關聯join

資料庫底層索引用於檢索資料所需

以下廢話連篇，可以直接跳過到下一節。

「資訊是用來消除隨機不定性的東西」（香農）。人們透過獲得、辨識自然界和社會的不同資訊來區分不同事物，得以認識和改造世界。數據是反映客觀事物屬性的記錄，是資訊的具體表現。資料經過加工處理之後，就變成資訊；而資訊需要經過數位化轉變成資料才能儲存和傳輸。資料庫就是用來儲存資料記錄的。既已如此，記錄便是具有確定性(相對)的信息，其確定性即唯一性。我們得到第一條原因：

1.資料記錄需具有唯一性

世界是由客觀存在及其關係所組成的。數據是數位化和模型化的存在關係。數據除了本身的描述價值外，其價值還在於其相互關聯性。為實現關聯的準確性，資料需要有對外相互關聯的標識。所以體現在資料儲存上，主鍵的第二作用，也是存在的第二因素即：

2.資料需要關聯

資料用來描述客觀實在的，本身沒有意義。只有在根據主觀需求組織之後，透過某種方式滿足人認識事物的過程才具有了意義。所以資料需要被檢索，被組織。則主鍵第三個作用：

3.資料庫底層索引用來擷取資料所需

二、為什麼主鍵不宜過長

這個問題的點在長上。那短比長有什麼優勢？ （嘿嘿嘿，內涵）—— 短不佔空間。但這麼點磁碟空間相對整個資料量來說微不足道，而且我們通常不怎麼用到主鍵列。那麼原因應該在快上，而且和原始資料關係不大。以此自然得出和索引相關，而且和索引讀取相關。那為什麼長主鍵在索引中會影響效能呢？

上面是 Innodb 的索引資料結構。左邊是聚集索引，透過主鍵定位資料記錄。右邊是二級索引，對列資料做索引，透過列資料查找資料主鍵。如果透過二級索引查詢數據，流程如圖上所示，先從二級索引樹上搜尋到主鍵，然後在叢集索引上透過主鍵搜尋到資料行。其中二級索引的葉子節點是直接儲存的主鍵值，而不是主鍵指標。所以如果主鍵太長，一個二級索引樹所能儲存的索引記錄就會變少，這樣在有限的索引緩衝中，需要讀取磁碟的次數就會變多，所以效能就會下降。

三、為什麼建議使用自增ID

InnoDB 使用叢集索引，如上圖所示，資料記錄本身就存於主索引（一顆B Tree ）的葉子節點上。這就要求同一個葉子節點內（大小為一個內存頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL 會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB 預設為15/16），則開闢一個新的頁面（節點）。

如果表格使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到目前索引節點的後續位置，當一頁寫滿，就會自動開啟一個新的頁面。這樣就會形成一個緊湊的索引結構，近似順序填滿。由於每次插入時也不需要移動已有數據，因此效率很高，也不會增加很多開銷在維護索引上，如下圖左側所示。否則由於每次插入主鍵的值近似於隨機，因此每次新記錄都要被插到現有索引頁的中間某個位置，MySQL 不得不為了將新記錄插到合適位置而移動數據，如下圖右側所示，這樣就造成了一定的開銷。由於此，Mysql 為維護索引可能需要頻繁的刷新緩衝，增加了方法磁碟 IO 的次數，而且時常需要對索引結構進行重組織。

四、業務 Key VS 邏輯 Key

業務 Key，即使用具有業務意義的 id 作為 Key，例如使用訂單流水號作為訂單表的主鍵 Key。邏輯 Key，即無關業務的 Key，以某種規則產生 Key，如自增 Key。

業務Key 的優點

Key 具有業務意義，在查詢時可以直接作為搜尋關鍵字使用

不需要額外的列和索引空間

可以減少一些join 操作。

業務Key 的缺點

當業務發生變化時，有時需要變更主鍵

#涉及多列Key 時比較難操作

業務Key 往往比較長，所佔空間更大，導致更大的磁碟IO

在Key 確定前無法持久化數據，有時我們沒有在確定數據Key 時，就想先添加一條記錄，之後再更新業務Key

設計一個兼具易用和性能的Key 生成方案比較難

邏輯Key 的優點

不會因為業務的變動而需要修改Key 邏輯

操作簡單，且易於管理

邏輯Key 往往更小，效能更優

邏輯Key 更容易保證唯一性

更容易最佳化

##邏輯Key 缺點

查詢主鍵列和主鍵索引需要額外的磁碟空間

在插入資料和更新資料時需要額外的IO

#更多的join 可能

如果沒有唯一性策略限制，容易出現重複的Key

測試環境和正式環境Key 不一致，不利於排查問題

Key 的值沒有和資料關聯，不符合三範式

不能用來搜尋關鍵字

依賴不同資料庫系統的具體實現，不利於底層資料庫的替換

五、主鍵產生

#一般情況下，我們都使用Mysql 的自增ID，來作為表格的主鍵，這樣簡單，而且從上面講到的來看，效能也是最好的。但在分庫分錶的情況下，自增 ID 則無法滿足需求。我們可以來看看不同資料庫產生 ID 的方式，也看一些分散式 ID 生成方案。利於我們思考甚至實現自己的分散式 ID 生成服務。

資料庫的實作

Mysql 自增

Mysql 在記憶體中維護一個自增計數器，每次存取auto -increment 計數器的時候， InnoDB 都會加上一個名為AUTO-INC 鎖定直到該語句結束(注意鎖定只持有到語句結束,不是事務結束)。 AUTO-INC 鎖是一個特殊的表格層級的鎖，用來提升包含 auto_increment 欄位的並發插入性。

在分散式的情況下，其實可以獨立一個服務和資料庫來做 id 生成，依舊依賴 Mysql 的表 id 自增能力來為第三方服務統一生成 id。為效能考量可以不同業務使用不同的表。

Mongodb ObjectId

Mongodb 為防止主鍵衝突，設計了一個 ObjectId 作為主鍵 id。它由一個 12 個位元組的十六進位數字組成，其中包含以下幾個部分：

Time：時間戳記。 4 位元組。秒級。

Machine：機器標識。 3 位元組。一般是機器主機名稱的雜湊值，這樣就確保了不同主機產生不同的機器 hash 值，確保在分散式中不會造成衝突，而同一台機器的值相同。

PID：進程 ID。 2 位元組。上面的 Machine 是為了確保在不同機器產生的 objectId 不衝突，而 pid 就是為了在同一台機器不同的 mongodb 程序產生的 objectId 不衝突。 INC：自增計數器。 3 位元組。前面的九個位元組保證了一秒內不同機器不同進程產生的objectId 不衝突，自增計數器，用來確保在同一秒內產生的objectId 也不會發現衝突，允許256 的3 次方等於16777216 條記錄的唯一性。

Cassandra TimeUUID

Cassandra 使用下列規則產生一個唯一的id：time MAC sequence

方案

Zookeeper 自增：透過zk 的自增機制實現。 Redis 自增：透過 Redis 的自增機制實現。

UUID：使用 UUID 字串作為 Key。

snowflake 演算法：和 Mongodb 的實作類似，1位元符號位元 41位元時間戳（毫秒） 10位元資料機器位元 12位元毫秒內的序列。

######開源實作#########百度 UidGenerator：基於snowflake演算法。 ######美團 Leaf：同時實作了基於 Mysql 自增（最佳化）和 snowflake 演算法的機制。 ###

以上是MySQL 如何設計主鍵的詳細內容。更多資訊請關注PHP中文網其他相關文章！