在脈脈上有一個熱度很高的帖子「碩 1.5，被勸退后三月中旬到現(xiàn)在無 offer，不知道怎么破」。

圖片

有網(wǎng)友說：「如果能約到面試，說明簡歷不差，但是 50 場都沒有 offer，說明你的面試談判技巧出現(xiàn)了問題，應(yīng)該總結(jié)一下，及時調(diào)整，心態(tài)不要崩」。

圖片

由這個話題為引子，碼哥接下來給你分享一個知識點：「如何設(shè)計一個高性能MySQL 主鍵，實現(xiàn)海量數(shù)據(jù)下的高效查詢」。

為什么需要主鍵

三個點。

1. 數(shù)據(jù)記錄需具有唯一性(第一范式)
2. 數(shù)據(jù)需要關(guān)聯(lián) join。
3. 數(shù)據(jù)庫底層索引用于檢索數(shù)據(jù)所需數(shù)據(jù)。

為什么主鍵不宜過長

這個問題的點在長上。那短比長有什么優(yōu)勢？（嘿嘿嘿，內(nèi)涵）—— 短不占空間。

但這么點磁盤空間相對整個數(shù)據(jù)量來說微不足道。

那么原因應(yīng)該在快上，而且和原始數(shù)據(jù)關(guān)系不大。以此自然得出和索引相關(guān)，而且和索引讀取相關(guān)。那么為什么主鍵過大在索引中會影響性能？

圖片

圖中是 MySQL Innodb 引擎的索引數(shù)據(jù)結(jié)構(gòu)。

左邊是聚簇索引，通過主鍵定位數(shù)據(jù)記錄。

右邊是普通索引，對列數(shù)據(jù)做索引，通過列數(shù)據(jù)查找數(shù)據(jù)主鍵。

如果通過普通查詢數(shù)據(jù)，流程如圖所示，先從普通索引樹上搜索到主鍵，然后在聚簇索引上通過主鍵搜索到數(shù)據(jù)行。

其中普通索引的葉子節(jié)點是直接存儲的主鍵值，而不是主鍵指針。

所以如果主鍵太長，一個普通索引樹所能存儲的索引記錄就會變少，這樣在有限的索引緩沖中，需要讀取磁盤的次數(shù)就會變多，所以性能就會下降。

為什么建議使用遞增 ID

圖片

InnoDB 使用聚簇索引，如上圖所示，數(shù)據(jù)記錄本身被存于索引（一顆 B+Tree）的葉子節(jié)點上有序存儲。

這就要求同一個葉子節(jié)點內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放。

因此每當(dāng)有一條新的記錄插入時，MySQL 會根據(jù)其主鍵值將其插入適當(dāng)?shù)墓?jié)點和位置，如果頁面達到裝載因子（InnoDB 默認為 15/16），則開辟一個新的頁（節(jié)點）。

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當(dāng)前索引節(jié)點的后續(xù)位置，當(dāng)一頁寫滿，就會自動開辟一個新的頁。這樣就會形成一個緊湊的索引結(jié)構(gòu)，近似順序填滿。

由于每次插入時也不需要移動已有數(shù)據(jù)，因此效率很高，也不會增加很多開銷在維護索引上，如下圖所示。

否則由于每次插入主鍵的值近似于隨機，因此每次新記錄都要被插到現(xiàn)有索引頁的中間某個位置，MySQL 不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，如下圖右側(cè)所示。

圖片

ID 是否有需要具備業(yè)務(wù)含義

業(yè)務(wù) ID，即使用具有業(yè)務(wù)意義的 id，比如使用訂單流水號作為訂單表的主鍵 Key。

邏輯 ID，即無關(guān)業(yè)務(wù)的 id，按某種規(guī)則生成 id，如自增 ID。

業(yè)務(wù) ID 的優(yōu)點

• ID 具有業(yè)務(wù)意義，在查詢時可以直接作為搜索關(guān)鍵字使用。
• 不需要額外的列和索引空間。
• 可以減少一些 join 操作。

業(yè)務(wù) ID 的缺點

• 當(dāng)業(yè)務(wù)發(fā)生變化時，有時需要變更主鍵。
• 涉及多列 ID 時比較難操作。
• 業(yè)務(wù) ID 往往比較長，所占空間更大，導(dǎo)致更大的磁盤 I/O。
• 在 ID 確定前不能持久化數(shù)據(jù)，有時我們沒有在確定數(shù)據(jù) ID 。時，就想先添加一條記錄，之后再更新業(yè)務(wù) ID。
• 設(shè)計一個兼具易用和性能的 ID 生成方案比較難。

邏輯 ID 的優(yōu)點

• 不會因為業(yè)務(wù)的變動而需要修改 ID 邏輯。
• 操作簡單，且易于管理。
• 邏輯 ID 往往更小，性能更優(yōu)。
• 邏輯 ID 更容易保證唯一性。
• 更易于優(yōu)化。

邏輯 ID 的缺點

• 查詢主鍵列和主鍵索引需要額外的磁盤空間。
• 在插入數(shù)據(jù)和更新數(shù)據(jù)時需要額外的 I/O。
• 更多的 join 可能。
• 如果沒有唯一性策略限制，容易出現(xiàn)重復(fù)的 ID。
• 測試環(huán)境和正式環(huán)境 ID 不一致，不利于排查問題。
• ID 的值沒有和數(shù)據(jù)關(guān)聯(lián)，不符合三范式。
• 不能用于搜索關(guān)鍵字。
• 依賴不同數(shù)據(jù)庫系統(tǒng)的具體實現(xiàn)，不利于底層數(shù)據(jù)庫的替換。

主鍵 ID 生成方式有哪些？

一般情況下，我們都使用 MySQL 的自增 ID，來作為表的主鍵，這樣簡單，而且從上面講到的來看，性能也是最好的。

但是在分庫分表的情況情況下，自增 ID 則不能滿足需求。我們可以來看看不同數(shù)據(jù)庫生成 ID 的方式，也看一些分布式 ID 生成方案。

MySQL 自增

MySQL 在內(nèi)存中維護一個自增計數(shù)器，每次訪問 auto-increment 計數(shù)器的時候， InnoDB 都會加上一個名為AUTO-INC 鎖直到該語句結(jié)束(注意鎖只持有到語句結(jié)束,不是事務(wù)結(jié)束)。

AUTO-INC 鎖是一個特殊的表級別的鎖，用來提升包含 auto_increment 列的并發(fā)插入性。

在分布式的情況下，其實可以獨立一個服務(wù)和數(shù)據(jù)庫來做 ID 生成，依舊依賴 MySQL 的表 ID 自增能力來為第三方服務(wù)統(tǒng)一生成 id。

Mongodb ObjectId

Mongodb 為防止主鍵沖突，設(shè)計了一個 ObjectId 作為主鍵 id。它由一個 12 字節(jié)的十六進制數(shù)字組成，其中包含以下幾部分：

1. Time：時間戳。4 字節(jié)。秒級。
2. Machine：機器標(biāo)識。3 字節(jié)。一般是機器主機名的散列值，這樣就確保了不同主機生成不同的機器 hash 值，確保在分布式中不造成沖突，同一臺機器的值相同。
3. PID：進程 ID。2 字節(jié)。上面的 Machine 是為了確保在不同機器產(chǎn)生的 objectId 不沖突，而 pid 就是為了在同一臺機器不同的 mongodb 進程產(chǎn)生的 objectId 不沖突。
4. INC：自增計數(shù)器。3 字節(jié)。前面的九個字節(jié)保證了一秒內(nèi)不同機器不同進程生成的 objectId 不沖突，自增計數(shù)器，用來確保在同一秒內(nèi)產(chǎn)生的 objectId 也不會發(fā)現(xiàn)沖突，允許 256 的 3 次方等于 16777216 條記錄的唯一性。

開源框架實現(xiàn)

1. 百度 UidGenerator：基于snowflake算法。
2. 美團 Leaf：同時實現(xiàn)了基于 MySQL 自增（優(yōu)化）和 snowflake 算法的機制。

博主簡介

碼哥，9 年互聯(lián)網(wǎng)公司后端工作經(jīng)驗，InfoQ 簽約作者、51CTO Top 紅人，阿里云開發(fā)者社區(qū)專家博主，目前擔(dān)任后端架構(gòu)師主責(zé)，擅長 Redis、Spring、Kafka、MySQL 技術(shù)和云原生微服務(wù)。