MySQL主鍵設計方法

下面講講關于MySQL主鍵設計方法，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL主鍵設計方法這篇文章你一定會有所受益。                                                           

一、為什么需要主鍵

數據記錄需具有唯一性(第一范式)

數據需要關聯 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主鍵設計方法相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。