我們平時(shí)建表的時(shí)候，一般會(huì)像下面這樣。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` char(10) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

出于習(xí)慣，我們一般會(huì)加一列id作為主鍵，而這個(gè)主鍵一般邊上都有個(gè)AUTO_INCREMENT, 意思是這個(gè)主鍵是自增的。自增就是i++，也就是每次都加1。

但問(wèn)題來(lái)了。

• 主鍵id不自增行不行？
• 為什么要用自增id做主鍵？
• 離譜點(diǎn)，沒(méi)有主鍵可以嗎？
• 什么情況下不應(yīng)該自增？
• 被這么一波追問(wèn)，念頭都不通達(dá)了？

這篇文章，我會(huì)嘗試回答這幾個(gè)問(wèn)題。

主鍵不自增行不行

當(dāng)然是可以的。比如我們可以把建表sql里的AUTO_INCREMENT去掉。

CREATE TABLE `user` (
  `id` int NOT NULL COMMENT '主鍵',
  `name` char(10) NOT NULL DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后執(zhí)行：

INSERT INTO `user` (`name`)  VALUES    ('debug');

這時(shí)候會(huì)報(bào)錯(cuò)Field 'id' doesn't have a default value。也就是說(shuō)如果你不讓主鍵自增的話，那你在寫(xiě)數(shù)據(jù)的時(shí)候需要自己指定id的值是多少，想要主鍵id是多少就寫(xiě)多少進(jìn)去，不寫(xiě)就報(bào)錯(cuò)。

改成下面這樣就好了。

INSERT INTO `user` (`id`,`name`)  VALUES    (10, 'debug');

為什么要用自增主鍵

我們?cè)跀?shù)據(jù)庫(kù)里保存的數(shù)據(jù)就跟excel表一樣，一行行似的。

user表

而在底層，這一行行數(shù)據(jù)，就是保存在一個(gè)個(gè)16k大小的頁(yè)里。

每次都去遍歷所有的行性能會(huì)不好，于是為了加速搜索，我們可以根據(jù)主鍵id，從小到大排列這些行數(shù)據(jù)，將這些數(shù)據(jù)頁(yè)用雙向鏈表的形式組織起來(lái)，再將這些頁(yè)里的部分信息提取出來(lái)放到一個(gè)新的16kb的數(shù)據(jù)頁(yè)里，再加入層級(jí)的概念。于是，一個(gè)個(gè)數(shù)據(jù)頁(yè)就被組織起來(lái)了，成為了一棵B+樹(shù)索引。

B+樹(shù)結(jié)構(gòu)

而當(dāng)我們?cè)诮ū韘ql里聲明了PRIMARY KEY (id)時(shí)，mysql的innodb引擎，就會(huì)為主鍵id生成一個(gè)主鍵索引，里面就是通過(guò)B+樹(shù)的形式來(lái)維護(hù)這套索引。

到這里，我們有兩個(gè)點(diǎn)是需要關(guān)注的：

• 數(shù)據(jù)頁(yè)大小是固定16k。
• 數(shù)據(jù)頁(yè)內(nèi)，以及數(shù)據(jù)頁(yè)之間，數(shù)據(jù)主鍵id都是從小到大排序的。

由于數(shù)據(jù)頁(yè)大小固定了是16k，當(dāng)我們需要插入一條新的數(shù)據(jù)，數(shù)據(jù)頁(yè)會(huì)被慢慢放滿，當(dāng)超過(guò)16k時(shí)，這個(gè)數(shù)據(jù)頁(yè)就有可能會(huì)進(jìn)行分裂。

針對(duì)B+樹(shù)葉子節(jié)點(diǎn)，如果主鍵是自增的，那它產(chǎn)生的id每次都比前一次要大，所以每次都會(huì)將數(shù)據(jù)加在B+樹(shù)尾部，B+樹(shù)的葉子節(jié)點(diǎn)本質(zhì)上是雙向鏈表，查找它的首部和尾部，時(shí)間復(fù)雜度O(1)。而如果此時(shí)最末尾的數(shù)據(jù)頁(yè)滿了，那創(chuàng)建個(gè)新的頁(yè)就好。

主鍵id自增的情況

如果主鍵不是自增的，比方說(shuō)上次分配了id=7，這次分配了id=3，為了讓新加入數(shù)據(jù)后B+樹(shù)的葉子節(jié)點(diǎn)還能保持有序，它就需要往葉子結(jié)點(diǎn)的中間找，查找過(guò)程的時(shí)間復(fù)雜度是O(lgn)，如果這個(gè)頁(yè)正好也滿了，這時(shí)候就需要進(jìn)行頁(yè)分裂了。并且頁(yè)分裂操作本身是需要加悲觀鎖的。總體看下來(lái)，自增的主鍵遇到頁(yè)分裂的可能性更少，因此性能也會(huì)更高。

主鍵id不自增的情況

沒(méi)有主鍵可以嗎

mysql表如果沒(méi)有主鍵索引，查個(gè)數(shù)據(jù)都得全表掃描，那既然它這么重要，我今天就不當(dāng)人了，不聲明主鍵，可以嗎？

嗯，你完全可以不聲明主鍵。

你確實(shí)可以在建表sql里寫(xiě)成這樣。

CREATE TABLE `user` (
  `name` char(10) NOT NULL DEFAULT '' COMMENT '名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

看起來(lái)確實(shí)是沒(méi)有主鍵的樣子。然而實(shí)際上，mysql的innodb引擎內(nèi)部會(huì)幫你生成一個(gè)名為ROW_ID列，它是個(gè)6字節(jié)的隱藏列，你平時(shí)也看不到它，但實(shí)際上，它也是自增的。有了這層兜底機(jī)制保證，數(shù)據(jù)表肯定會(huì)有主鍵和主鍵索引。

跟ROW_ID被隱藏的列還有trx_id字段，用于記錄當(dāng)前這一行數(shù)據(jù)行是被哪個(gè)事務(wù)修改的，和一個(gè)roll_pointer字段，這個(gè)字段是用來(lái)指向當(dāng)前這個(gè)數(shù)據(jù)行的上一個(gè)版本，通過(guò)這個(gè)字段，可以為這行數(shù)據(jù)形成一條版本鏈，從而實(shí)現(xiàn)多版本并發(fā)控制（MVCC）。有沒(méi)有很眼熟，這個(gè)在之前寫(xiě)的文章里出現(xiàn)過(guò)。

隱藏的row_id列

有沒(méi)有建議主鍵不自增的場(chǎng)景

前面提到了主鍵自增可以帶來(lái)很多好處，事實(shí)上大部分場(chǎng)景下，我們都建議主鍵設(shè)為自增。

那有沒(méi)有不建議主鍵自增的場(chǎng)景呢？

mysql分庫(kù)分表下的id

聊到分庫(kù)分表，那我就需要說(shuō)明下，遞增和自增的區(qū)別了，自增就是每次都+1，而遞增則是新的id比上一個(gè)id要大就行了，具體大多少，沒(méi)關(guān)系。

之前寫(xiě)過(guò)一篇文章提到過(guò)，mysql在水平分庫(kù)分表時(shí)，一般有兩種方式。

一種分表方式是通過(guò)對(duì)id取模進(jìn)行分表，這種要求遞增就好，不要求嚴(yán)格自增，因?yàn)槿∧：髷?shù)據(jù)會(huì)被分散到多個(gè)分表中，就算id是嚴(yán)格自增的，在分散之后，都只能保證每個(gè)分表里id只能是遞增的。

根據(jù)id取模分表

另一種分表方式是根據(jù)id的范圍進(jìn)行分表（分片），它會(huì)劃出一定的范圍，比如以2kw為一個(gè)分表的大小，那0~2kw就放在這張分表中，2kw~4kw放在另一張分表中，數(shù)據(jù)不斷增加，分表也可以不斷增加，非常適合動(dòng)態(tài)擴(kuò)容，但它要求id自增，如果id遞增，數(shù)據(jù)則會(huì)出現(xiàn)大量空洞。舉個(gè)例子，比如第一次分配id=2，第二次分配id=2kw，這時(shí)候第一張表的范圍就被打滿了，后面再分配一個(gè)id，比如是3kw，就只能存到2kw~4kw（第二張）的分表中。那我在0~2kw這個(gè)范圍的分表，也就存了兩條數(shù)據(jù)，這太浪費(fèi)了。

根據(jù)id范圍分表

但不管哪種分表方式，一般是不可能繼續(xù)用原來(lái)表里的自增主鍵的，原因也比較好理解，原來(lái)的每個(gè)表如果都從0開(kāi)始自增的話，那好幾個(gè)表就會(huì)出現(xiàn)好幾次重復(fù)的id，根據(jù)id唯一的原則，這顯然不合理。

所以我們?cè)诜謳?kù)分表的場(chǎng)景下，插入的id都是專門的id服務(wù)生成的，如果是要嚴(yán)格自增的話，那一般會(huì)通過(guò)redis來(lái)獲得，當(dāng)然不會(huì)是一個(gè)id請(qǐng)求獲取一次，一般會(huì)按批次去獲得，比如一次性獲得100個(gè)?？煊猛炅嗽偃カ@取下一批100個(gè)。

但這個(gè)方案有個(gè)問(wèn)題，它嚴(yán)重依賴redis，如果redis掛了，那整個(gè)功能就傻了。

有沒(méi)有不依賴于其他第三方組件的方法呢？

雪花算法

有，比如Twitter開(kāi)源的雪花算法。

雪花算法通過(guò)64位有特殊含義的數(shù)字來(lái)組成id。

雪花算法

首先第0位不用。

接下來(lái)的41位是時(shí)間戳。精度是毫秒，這個(gè)大小大概能表示個(gè)69年左右，因?yàn)闀r(shí)間戳隨著時(shí)間流逝肯定是越來(lái)越大的，所以這部分決定了生成的id肯定是越來(lái)越大的。

再接下來(lái)的10位是指產(chǎn)生這些雪花算法的工作機(jī)器id，這樣就可以讓每個(gè)機(jī)器產(chǎn)生的id都具有相應(yīng)的標(biāo)識(shí)。

再接下來(lái)的12位，序列號(hào)，就是指這個(gè)工作機(jī)器里生成的遞增數(shù)字。

可以看出，只要處于同一毫秒內(nèi)，所有的雪花算法id的前42位的值都是一樣的，因此在這一毫秒內(nèi)，能產(chǎn)生的id數(shù)量就是 2的10次方??2的12次方，大概400w，肯定是夠用了，甚至有點(diǎn)多了。

但是！

細(xì)心的兄弟們肯定也發(fā)現(xiàn)了，雪花算法它算出的數(shù)字動(dòng)不動(dòng)就比上次的數(shù)字多個(gè)幾百幾萬(wàn)的，也就是它生成的id是趨勢(shì)遞增的，并不是嚴(yán)格+1自增的，也就是說(shuō)它并不太適合于根據(jù)范圍來(lái)分表的場(chǎng)景。這是個(gè)非常疼的問(wèn)題。

還有個(gè)小問(wèn)題是，那10位工作機(jī)器id，我每次擴(kuò)容一個(gè)工作機(jī)器，這個(gè)機(jī)器怎么知道自己的id是多少呢？是不是得從某個(gè)地方讀過(guò)來(lái)。

那有沒(méi)有一種生成id生成方案，既能讓分庫(kù)分表能做到很好的支持動(dòng)態(tài)擴(kuò)容，又能像雪花算法那樣并不依賴redis這樣的第三方服務(wù)。

有。這就是這篇文章的重點(diǎn)了。

適合分庫(kù)分表的uuid算法

我們可以參考雪花算法的實(shí)現(xiàn)，設(shè)計(jì)成下面這樣。注意下面的每一位，都是十進(jìn)制，而不是二進(jìn)制。

適合分庫(kù)分表的uuid算法

開(kāi)頭的12位依然是時(shí)間，但并不是時(shí)間戳，雪花算法的時(shí)間戳精確到毫秒，我們用不上這么細(xì)，我們改為yyMMddHHmmss，注意開(kāi)頭的yy是兩位，也就是這個(gè)方案能保證到2099年之前，id都不會(huì)重復(fù)，能用到重復(fù)，那也是真·百年企業(yè)。同樣由于最前面是時(shí)間，隨著時(shí)間流逝，也能保證id趨勢(shì)遞增。

接下來(lái)的10位，用十進(jìn)制的方式表示工作機(jī)器的ip，就可以把12位的ip轉(zhuǎn)為10位的數(shù)字，它可以保證全局唯一，只要服務(wù)起來(lái)了，也就知道自己的ip是多少了，不需要像雪花算法那樣從別的地方去讀取worker id了，又是一個(gè)小細(xì)節(jié)。

在接下來(lái)的6位，就用于生成序列號(hào)，它能支持每秒鐘生成100w個(gè)id。

最后的4位，也是這個(gè)id算法最妙的部分。它前2位代表分庫(kù)id，后2位代表分表id。也就是支持一共100*100=1w張分表。

舉個(gè)例子，假設(shè)我只用了1個(gè)分庫(kù)，當(dāng)我一開(kāi)始只有3張分表的情況下，那我可以通過(guò)配置，要求生成的uuid最后面的2位，取值只能是[0,1,2]，分別對(duì)應(yīng)三個(gè)表。這樣我生成出來(lái)的id，就能非常均勻的落到三個(gè)分表中，這還順帶解決了單個(gè)分表熱點(diǎn)寫(xiě)入的問(wèn)題。

如果隨著業(yè)務(wù)不斷發(fā)展，需要新加入兩張新的表(3和4)，同時(shí)第0張表有點(diǎn)滿了，不希望再被寫(xiě)了，那就將配置改為[1,2,3,4]，這樣生成的id就不會(huì)再插入到對(duì)應(yīng)的0表中。同時(shí)還可以加入生成id的概率和權(quán)重來(lái)調(diào)整哪個(gè)分表落更多數(shù)據(jù)。

有了這個(gè)新的uuid方案，我們既可以保證生成的數(shù)據(jù)趨勢(shì)遞增，同時(shí)也能非常方便擴(kuò)展分表。非常nice。

數(shù)據(jù)庫(kù)有那么多種，mysql只是其中一種，那其他數(shù)據(jù)庫(kù)也是要求主鍵自增嗎？

tidb的主鍵id不建議自增

tidb是一款分布式數(shù)據(jù)庫(kù)，作為mysql分庫(kù)分表場(chǎng)景下的替代產(chǎn)品，可以更好的對(duì)數(shù)據(jù)進(jìn)行分片。

它通過(guò)引入Range的概念進(jìn)行數(shù)據(jù)表分片，比如第一個(gè)分片表的id在0~2kw，第二個(gè)分片表的id在2kw~4kw。這其實(shí)就是根據(jù)id范圍進(jìn)行數(shù)據(jù)庫(kù)分表。

它的語(yǔ)法幾乎跟mysql一致，用起來(lái)大部分時(shí)候是無(wú)感的。

但跟mysql有一點(diǎn)很不一樣的就是，mysql建議id自增，但tidb卻建議使用隨機(jī)的uuid。原因是如果id自增的話，根據(jù)范圍分片的規(guī)則，一段時(shí)間內(nèi)生成的id幾乎都會(huì)落到同一個(gè)分片上，比如下圖，從3kw開(kāi)始的自增uuid，幾乎都落到range 1這個(gè)分片中，而其他表卻幾乎不會(huì)有寫(xiě)入，性能沒(méi)有被利用起來(lái)。出現(xiàn)一表有難，多表圍觀的場(chǎng)面，這種情況又叫寫(xiě)熱點(diǎn)問(wèn)題。

寫(xiě)熱點(diǎn)問(wèn)題

所以為了充分的利用多個(gè)分表的寫(xiě)入能力，tidb建議我們寫(xiě)入時(shí)使用隨機(jī)id，這樣數(shù)據(jù)就能被均勻分散到多個(gè)分片中。

用戶id不建議用自增id

前面提到的不建議使用自增id的場(chǎng)景，都是技術(shù)原因?qū)е碌?，而下面介紹的這個(gè)，單純是因?yàn)闃I(yè)務(wù)。

舉個(gè)例子吧。

如果你能知道一個(gè)產(chǎn)品每個(gè)月，新增的用戶數(shù)有多少，這個(gè)對(duì)你來(lái)說(shuō)會(huì)是有用的信息嗎？

對(duì)程序員來(lái)說(shuō)，可能這個(gè)信息價(jià)值不大。

但如果你是做投資的呢，或者是分析競(jìng)爭(zhēng)對(duì)手呢？

那反過(guò)來(lái)。

如果你發(fā)現(xiàn)你的競(jìng)爭(zhēng)對(duì)手，總能非常清晰的知道你的產(chǎn)品每個(gè)月新進(jìn)的注冊(cè)用戶是多少人，你會(huì)不會(huì)心里毛毛的？

如果真出現(xiàn)了這問(wèn)題，先不要想是不是有內(nèi)鬼，先檢查下你的用戶表主鍵是不是自增的。

如果用戶id是自增的，那別人只要每個(gè)月都注冊(cè)一個(gè)新用戶，然后抓包得到這個(gè)用戶的user_id，然后跟上個(gè)月的值減一下，就知道這個(gè)月新進(jìn)多少用戶了。

同樣的場(chǎng)景有很多，有時(shí)候你去小店吃飯，發(fā)票上就寫(xiě)了你是今天的第幾單，那大概就能估計(jì)今天店家做了多少單。你是店家，你心里也不舒服吧。

再比如說(shuō)一些小app的商品訂單id，如果也做成自增的，那就很容易可以知道這個(gè)月成了多少單。

類似的事情有很多，這些場(chǎng)景都建議使用趨勢(shì)遞增的uuid作為主鍵。

當(dāng)然，主鍵保持自增，但是不暴露給前端，那也行，那前面的話，你當(dāng)我沒(méi)說(shuō)過(guò)。

總結(jié)

• 建表sql里主鍵邊上的AUTO_INCREMENT，可以讓主鍵自增，去掉它是可以的，但這就需要你在insert的時(shí)候自己設(shè)置主鍵的值。
• 建表sql里的 PRIMARY KEY 是用來(lái)聲明主鍵的，如果去掉，那也能建表成功，但mysql內(nèi)部會(huì)給你偷偷建一個(gè) ROW_ID的隱藏列作為主鍵。
• 由于mysql使用B+樹(shù)索引，葉子節(jié)點(diǎn)是從小到大排序的，如果使用自增id做主鍵，這樣每次數(shù)據(jù)都加在B+樹(shù)的最后，比起每次加在B+樹(shù)中間的方式，加在最后可以有效減少頁(yè)分裂的問(wèn)題。
• 在分庫(kù)分表的場(chǎng)景下，我們可以通過(guò)redis等第三方組件來(lái)獲得嚴(yán)格自增的主鍵id。如果不想依賴redis，可以參考雪花算法進(jìn)行魔改，既能保證數(shù)據(jù)趨勢(shì)遞增，也能很好的滿足分庫(kù)分表的動(dòng)態(tài)擴(kuò)容。
• 并不是所有數(shù)據(jù)庫(kù)都建議使用自增id作為主鍵，比如tidb就推薦使用隨機(jī)id，這樣可以有效避免寫(xiě)熱點(diǎn)的問(wèn)題。而對(duì)于一些敏感數(shù)據(jù)，比如用戶id，訂單id等，如果使用自增id作為主鍵的話，外部通過(guò)抓包，很容易可以知道新進(jìn)用戶量，成單量這些信息，所以需要謹(jǐn)慎考慮是否繼續(xù)使用自增主鍵。