MySQL主鍵設(shè)計(jì)方法

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

永泰ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！

一、為什么需要主鍵

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

數(shù)據(jù)需要關(guān)聯(lián) join

數(shù)據(jù)庫(kù)底層索引用于檢索數(shù)據(jù)所需

以下廢話連篇，可以直接跳過(guò)到下一節(jié)。

“信息是用來(lái)消除隨機(jī)不定性的東西”（香農(nóng)）。人通過(guò)獲得、識(shí)別自然界和社會(huì)的不同信息來(lái)區(qū)別不同事物，得以認(rèn)識(shí)和改造世界。數(shù)據(jù)是反映客觀事物屬性的記錄，是信息的具體表現(xiàn)形式。數(shù)據(jù)經(jīng)過(guò)加工處理之后，就成為信息；而信息需要經(jīng)過(guò)數(shù)字化轉(zhuǎn)變成數(shù)據(jù)才能存儲(chǔ)和傳輸。數(shù)據(jù)庫(kù)就是用于存儲(chǔ)數(shù)據(jù)記錄的。既已如此，記錄便是具有確定性(相對(duì))的信息，其確定性即唯一性。我們得出第一條原因：

1.數(shù)據(jù)記錄需具有唯一性

世界是由客觀存在及其關(guān)系組成的。數(shù)據(jù)是數(shù)字化和模型化的存在關(guān)系。數(shù)據(jù)除了本身的描述價(jià)值外，其價(jià)值還在于其相互關(guān)聯(lián)性。為實(shí)現(xiàn)關(guān)聯(lián)的準(zhǔn)確性，數(shù)據(jù)需要有對(duì)外相互關(guān)聯(lián)的標(biāo)識(shí)。所以體現(xiàn)在數(shù)據(jù)存儲(chǔ)上，主鍵的第二作用，也是存在的第二因素即：

2.數(shù)據(jù)需要關(guān)聯(lián)

數(shù)據(jù)用于描述客觀實(shí)在的，本身沒(méi)有意義。只有在根據(jù)主觀需求組織之后，通過(guò)一定方式滿足人認(rèn)識(shí)事物的過(guò)程才具有了意義。所以數(shù)據(jù)需要被檢索，被組織。則主鍵第三個(gè)作用：

3.數(shù)據(jù)庫(kù)底層索引用于檢索數(shù)據(jù)所需

二、為什么主鍵不宜過(guò)長(zhǎng)

這個(gè)問(wèn)題的點(diǎn)在長(zhǎng)上。那短比長(zhǎng)有什么優(yōu)勢(shì)？（嘿嘿嘿，內(nèi)涵）—— 短不占空間。但這么點(diǎn)磁盤空間相對(duì)整個(gè)數(shù)據(jù)量來(lái)說(shuō)微不足道，而且我們一般不怎么用到主鍵列。那么原因應(yīng)該在快上，而且和原始數(shù)據(jù)關(guān)系不大。以此自然得出和索引相關(guān)，而且和索引讀取相關(guān)。那么為什么長(zhǎng)主鍵在索引中會(huì)影響性能？

上面是 Innodb 的索引數(shù)據(jù)結(jié)構(gòu)。左邊是聚簇索引，通過(guò)主鍵定位數(shù)據(jù)記錄。右邊是二級(jí)索引，對(duì)列數(shù)據(jù)做索引，通過(guò)列數(shù)據(jù)查找數(shù)據(jù)主鍵。如果通過(guò)二級(jí)索引查詢數(shù)據(jù)，流程如圖上所示，先從二級(jí)索引樹上搜索到主鍵，然后在聚簇索引上通過(guò)主鍵搜索到數(shù)據(jù)行。其中二級(jí)索引的葉子節(jié)點(diǎn)是直接存儲(chǔ)的主鍵值，而不是主鍵指針。所以如果主鍵太長(zhǎng)，一個(gè)二級(jí)索引樹所能存儲(chǔ)的索引記錄就會(huì)變少，這樣在有限的索引緩沖中，需要讀取磁盤的次數(shù)就會(huì)變多，所以性能就會(huì)下降。

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

InnoDB 使用聚簇索引，如上圖所示，數(shù)據(jù)記錄本身被存于主索引（一顆 B+Tree）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時(shí)，MySQL 會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB 默認(rèn)為 15/16），則開(kāi)辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）。

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)。這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)，因此效率很高，也不會(huì)增加很多開(kāi)銷在維護(hù)索引上，如下圖左側(cè)所示。否則由于每次插入主鍵的值近似于隨機(jī)，因此每次新記錄都要被插到現(xiàn)有索引頁(yè)的中間某個(gè)位置，MySQL 不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，如下圖右側(cè)所示，這樣就造成了一定的開(kāi)銷。由于此，Mysql 為維護(hù)索引可能需要頻繁的刷新緩沖，增加了方法磁盤 IO 的次數(shù)，而且時(shí)常需要對(duì)索引結(jié)構(gòu)進(jìn)行重組織。

四、業(yè)務(wù) Key VS 邏輯 Key

業(yè)務(wù) Key，即使用具有業(yè)務(wù)意義的 id 作為 Key，比如使用訂單流水號(hào)作為訂單表的主鍵 Key。邏輯 Key，即無(wú)關(guān)業(yè)務(wù)的 Key，按某種規(guī)則生成 Key，如自增 Key。

業(yè)務(wù) Key 的優(yōu)點(diǎn)

Key 具有業(yè)務(wù)意義，在查詢時(shí)可以直接作為搜索關(guān)鍵字使用

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

可以減少一些 join 操作。

業(yè)務(wù) Key 的缺點(diǎn)

當(dāng)業(yè)務(wù)發(fā)生變化時(shí)，有時(shí)需要變更主鍵

涉及多列 Key 時(shí)比較難操作

業(yè)務(wù) Key 往往比較長(zhǎng)，所占空間更大，導(dǎo)致更大的磁盤 IO

在 Key 確定前不能持久化數(shù)據(jù)，有時(shí)我們沒(méi)有在確定數(shù)據(jù) Key 時(shí)，就想先添加一條記錄，之后再更新業(yè)務(wù) Key

設(shè)計(jì)一個(gè)兼具易用和性能的 Key 生成方案比較難

邏輯 Key 的優(yōu)點(diǎn)

不會(huì)因?yàn)闃I(yè)務(wù)的變動(dòng)而需要修改 Key 邏輯

操作簡(jiǎn)單，且易于管理

邏輯 Key 往往更小，性能更優(yōu)

邏輯 Key 更容易保證唯一性

更易于優(yōu)化

邏輯 Key 缺點(diǎn)

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

在插入數(shù)據(jù)和更新數(shù)據(jù)時(shí)需要額外的 IO

更多的 join 可能

如果沒(méi)有唯一性策略限制，容易出現(xiàn)重復(fù)的 Key

測(cè)試環(huán)境和正式環(huán)境 Key 不一致，不利于排查問(wèn)題

Key 的值沒(méi)有和數(shù)據(jù)關(guān)聯(lián)，不符合三范式

不能用于搜索關(guān)鍵字

依賴不同數(shù)據(jù)庫(kù)系統(tǒng)的具體實(shí)現(xiàn)，不利于底層數(shù)據(jù)庫(kù)的替換

五、主鍵生成

一般情況下，我們都使用 Mysql 的自增 ID，來(lái)作為表的主鍵，這樣簡(jiǎn)單，而且從上面講到的來(lái)看，性能也是最好的。但是在分庫(kù)分表的情況情況下，自增 ID 則不能滿足需求。我們可以來(lái)看看不同數(shù)據(jù)庫(kù)生成 ID 的方式，也看一些分布式 ID 生成方案。利于我們思考甚至實(shí)現(xiàn)自己的分布式 ID 生成服務(wù)。

數(shù)據(jù)庫(kù)的實(shí)現(xiàn)

Mysql 自增

Mysql 在內(nèi)存中維護(hù)一個(gè)自增計(jì)數(shù)器，每次訪問(wèn) auto-increment 計(jì)數(shù)器的時(shí)候， InnoDB 都會(huì)加上一個(gè)名為AUTO-INC 鎖直到該語(yǔ)句結(jié)束(注意鎖只持有到語(yǔ)句結(jié)束,不是事務(wù)結(jié)束)。AUTO-INC 鎖是一個(gè)特殊的表級(jí)別的鎖，用來(lái)提升包含 auto_increment 列的并發(fā)插入性。

在分布式的情況下，其實(shí)可以獨(dú)立一個(gè)服務(wù)和數(shù)據(jù)庫(kù)來(lái)做 id 生成，依舊依賴 Mysql 的表 id 自增能力來(lái)為第三方服務(wù)統(tǒng)一生成 id。為性能考慮可以不同業(yè)務(wù)使用不同的表。

MongoDB ObjectId

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

Time：時(shí)間戳。4 字節(jié)。秒級(jí)。

Machine：機(jī)器標(biāo)識(shí)。3 字節(jié)。一般是機(jī)器主機(jī)名的散列值，這樣就確保了不同主機(jī)生成不同的機(jī)器 hash 值，確保在分布式中不造成沖突，同一臺(tái)機(jī)器的值相同。

PID：進(jìn)程 ID。2 字節(jié)。上面的 Machine 是為了確保在不同機(jī)器產(chǎn)生的 objectId 不沖突，而 pid 就是為了在同一臺(tái)機(jī)器不同的 mongodb 進(jìn)程產(chǎn)生的 objectId 不沖突。

INC：自增計(jì)數(shù)器。3 字節(jié)。前面的九個(gè)字節(jié)保證了一秒內(nèi)不同機(jī)器不同進(jìn)程生成的 objectId 不沖突，自增計(jì)數(shù)器，用來(lái)確保在同一秒內(nèi)產(chǎn)生的 objectId 也不會(huì)發(fā)現(xiàn)沖突，允許 256 的 3 次方等于 16777216 條記錄的唯一性。

Cassandra TimeUUID

Cassandra 使用下面規(guī)則生成一個(gè)唯一的 id：time + MAC + sequence

方案

Zookeeper 自增：通過(guò) zk 的自增機(jī)制實(shí)現(xiàn)。

redis 自增：通過(guò) Redis 的自增機(jī)制實(shí)現(xiàn)。

UUID：使用 UUID 字符串作為 Key。

snowflake 算法：和 Mongodb 的實(shí)現(xiàn)類似，1位符號(hào)位 + 41位時(shí)間戳（毫秒級(jí)）+ 10位數(shù)據(jù)機(jī)器位 + 12位毫秒內(nèi)的序列。

開(kāi)源實(shí)現(xiàn)

百度 UidGenerator：基于snowflake算法。

美團(tuán) Leaf：同時(shí)實(shí)現(xiàn)了基于 Mysql 自增（優(yōu)化）和 snowflake 算法的機(jī)制。

對(duì)于以上MySQL主鍵設(shè)計(jì)方法相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。

文章題目：MySQL主鍵設(shè)計(jì)方法
網(wǎng)頁(yè)鏈接：http://bm7419.com/article18/jcsigp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、品牌網(wǎng)站設(shè)計(jì)、網(wǎng)頁(yè)設(shè)計(jì)公司、Google、自適應(yīng)網(wǎng)站、虛擬主機(jī)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容