数据库主键一定要自增吗？有哪些场景不建议自增？

淡淡的烟草味﹌ 2023-10-01 23:11 12阅读 0赞

我们平时建表的时候，一般会像下面这样。

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;

出于习惯，我们一般会加一列**id作为主键**，而这个主键一般边上都有个`AUTO_INCREMENT`, 意思是这个主键是自增的。自增就是i++，也就是每次都加1。

但问题来了。

**主键id不自增行不行？**

**为什么要用自增id做主键？**

**离谱点，没有主键可以吗？**

**什么情况下不应该自增？**

被这么一波追问，念头都不通达了？

这篇文章，我会尝试回答这几个问题。

#### 主键不自增行不行 ####

当然是可以的。比如我们可以把建表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;

然后执行

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

这时候会报错`Field 'id' doesn't have a default value`。也就是说如果你不让主键自增的话，那你在写数据的时候需要自己指定id的值是多少，想要主键id是多少就写多少进去，不写就报错。

改成下面这样就好了

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

#### 为什么要用自增主键 ####

我们在数据库里保存的数据就跟excel表一样，一行行似的。

![cb053067372886e981df09d7ec100128.png][]

user表

而在底层，这一行行数据，就是保存在一个个**16k大小的页**里。

每次都去遍历所有的行性能会不好，于是为了加速搜索，我们可以**根据主键id，从小到大排列这些行数据**，将这些数据页用**双向链表**的形式组织起来，再将这些页里的部分信息提取出来放到一个新的16kb的数据页里，再加入**层级的概念**。于是，一个个数据页就被组织起来了，成为了一棵**B+树索引**。

![bc92aa44cb3c1fbfbeb0bd96696c3381.png][]

B+树结构

而当我们在建表sql里声明了`PRIMARY KEY (id)`时，mysql的innodb引擎，就会为主键id生成一个**主键索引**，里面就是通过B+树的形式来维护这套索引。

到这里，我们有**两个点**是需要关注的：

*  数据页大小是**固定16k**
 *  数据页内，以及数据页之间，数据主键id都是从**小到大排序**的

由于数据页大小**固定了是16k**，当我们需要插入一条新的数据，数据页会被慢慢**放满**，当超过16k时，这个数据页就有可能会进行**分裂**。

针对B+树**叶子节点**，**如果主键是自增的**，那它产生的id每次都比前一次要大，所以每次都会将数据加在B+树**尾部**，B+树的叶子节点本质上是**双向链表**，查找它的首部和尾部，**时间复杂度O(1)**。而如果此时最末尾的数据页满了，那创建个新的页就好。

![1fad2f6fedf17092d8a44b4dc773d393.png][]

主键id自增的情况

**如果主键不是自增的**，比方说上次分配了id=7，这次分配了id=3，为了让新加入数据后**B+树的叶子节点还能保持有序**，它就需要往叶子结点的中间找，查找过程的**时间复杂度是O(lgn)**，如果这个页正好也满了，这时候就需要进行**页分裂**了。并且页分裂操作本身是需要加**悲观锁**的。总体看下来，自增的主键遇到页分裂的可能性更少，因此性能也会更高。

![3a631b8bdd5c709190b87011a35c86d3.png][]

主键id不自增的情况

#### 没有主键可以吗 ####

mysql表如果没有主键索引，查个数据都得全表扫描，那既然它这么重要，我今天就不当人了，**不声明主键，可以吗？**

嗯，你完全可以不声明主键。

你确实可以在建表sql里写成这样。

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

看起来确实是没有主键的样子。然而实际上，mysql的innodb引擎内部会帮你生成一个名为`ROW_ID`列，它是个6字节的隐藏列，你平时也看不到它，但实际上，它也是自增的。有了这层兜底机制保证，**数据表肯定会有主键和主键索引**。

跟ROW\_ID被隐藏的列还有`trx_id`字段，用于记录当前这一行数据行是被**哪个事务**修改的，和一个`roll_pointer`字段，这个字段是用来指向当前这个数据行的上一个版本，通过这个字段，可以为这行数据形成一条版本链，从而实现**多版本并发控制（MVCC）**。有没有很眼熟，这个在之前写的[文章][Link 1]里出现过。

![bc0babe3c0d7da854d40fe4949614bd0.png][]

隐藏的row\_id列

#### 有没有建议主键不自增的场景 ####

前面提到了主键自增可以带来很多好处，事实上**大部分场景下，我们都建议主键设为自增。**

那有没有不建议主键自增的场景呢？

mysql分库分表下的id

聊到分库分表，那我就需要说明下，**递增和自增的区别**了，**自增**就是每次都+1，而**递增**则是新的id比上一个id要大就行了，具体大多少，没关系。

之前写过一篇[文章][Link 2]提到过，mysql在水平分库分表时，一般有两种方式。

一种分表方式是通过**对id取模进行分表**，这种要求递增就好，不要求严格自增，因为取模后数据会被分散到多个分表中，就算id是严格自增的，在分散之后，都只能保证每个分表里id只能是递增的。

![1e2f98c7a5c5e0ff2829cce1641cd2b8.png][]

根据id取模分表

另一种分表方式是**根据id的范围进行分表（分片）**，它会划出一定的范围，比如以2kw为一个分表的大小，那0~2kw就放在这张分表中，2kw~4kw放在另一张分表中，数据不断增加，分表也可以不断增加，**非常适合动态扩容**，但它要求**id自增**，如果**id递增**，数据则会出现**大量空洞**。举个例子，比如第一次分配id=2，第二次分配id=2kw，这时候第一张表的范围就被打满了，后面再分配一个id，比如是3kw，就只能存到2kw~4kw（第二张）的分表中。那我在0~2kw这个范围的分表，也就存了**两条数据**，这太浪费了。

![598b62ea7a1bf5d1df20371663b29cb7.png][]

根据id范围分表

但不管哪种分表方式，一般是**不可能继续用原来表里的自增主键的**，原因也比较好理解，原来的每个表如果都从0开始自增的话，那好几个表就会出现好几次重复的id，根据id唯一的原则，这显然不合理。

所以我们在分库分表的场景下，插入的id都是专门的id服务生成的，如果是要严格自增的话，那一般会通过redis来获得，当然不会是一个id请求获取一次，一般会**按批次去获得，比如一次性获得100个。快用完了再去获取下一批100个。**

但这个方案有个问题，它严重依赖redis，如果redis挂了，那整个功能就傻了。

有没有不依赖于其他第三方组件的方法呢？

雪花算法

有，比如**Twitter开源的雪花算法。**

雪花算法通过64位有特殊含义的数字来组成id。

![2a037222dce044e0bfcff85491aa59eb.png][]

雪花算法

首先**第0位**不用。

接下来的**41位**是**时间戳**。精度是**毫秒**，这个大小大概能表示个`69年`左右，因为时间戳随着时间流逝肯定是越来越大的，所以这部分决定了生成的id肯定是越来越大的。

再接下来的**10位**是指产生这些雪花算法的**工作机器id**，这样就可以让每个机器产生的id都具有相应的标识。

再接下来的**12位**，**序列号**，就是指这个工作机器里生成的递增数字。

可以看出，只要处于同一毫秒内，所有的雪花算法id的前42位的值都是一样的，因此在这一毫秒内，能产生的id数量就是 `2的10次方✖️2的12次方`，大概`400w`，肯定是够用了，甚至有点多了。

**但是！**

细心的兄弟们肯定也发现了，雪花算法它算出的数字动不动就比上次的数字多个几百几万的，也就是它生成的id是**趋势递增**的，并不是严格**+1自增**的，也就是说它并不太适合于根据范围来分表的场景。这是个非常疼的问题。

还有个**小问题**是，那10位工作机器id，我每次扩容一个工作机器，这个机器怎么知道自己的id是多少呢？是不是得从某个地方读过来。

**那有没有一种生成id生成方案，既能让分库分表能做到很好的支持动态扩容，又能像雪花算法那样并不依赖redis这样的第三方服务。**

有。这就是这篇文章的重点了。

适合分库分表的uuid算法

我们可以参考雪花算法的实现，设计成下面这样。注意下面的每一位，**都是十进制**，而不是二进制。

![70ea47aa74016cfaf2a67e5ec8fa1cee.png][]

适合分库分表的uuid算法

开头的**12位**依然是时间，但并不是时间戳，雪花算法的时间戳精确到毫秒，我们用不上这么细，我们改为`yyMMddHHmmss`，注意开头的yy是两位，也就是这个方案能保证到2099年之前，id都不会重复，能用到重复，那也是真·百年企业。同样由于最前面是时间，随着时间流逝，也能保证id趋势递增。

接下来的**10位**，用**十进制**的方式表示工作机器的ip，就可以把12位的ip转为10位的数字，它可以保证全局唯一，只要服务起来了，也就知道自己的ip是多少了，不需要像雪花算法那样从别的地方去读取worker id了，又是一个小细节。

![086aa903b295a5b61cb8f43efd8720c2.jpeg][]

在接下来的**6位**，就用于生成序列号，它能支持每秒钟生成100w个id。

最后的**4位**，也是这个id算法最妙的部分。它**前2位**代表分库id，**后2位**代表分表id。也就是支持一共`100*100=1w`张分表。

举个例子，假设我只用了1个分库，当我一开始只有3张分表的情况下，那我可以通过配置，要求生成的uuid最后面的2位，取值只能是\[0,1,2\]，分别对应三个表。这样我生成出来的id，就能非常均匀的落到三个分表中，这还**顺带解决了单个分表热点写入的问题。**

如果随着业务不断发展，需要新加入两张新的表(3和4)，同时第0张表有点满了，不希望再被写了，那就将配置改为\[1,2,3,4\]，这样生成的id就不会再插入到对应的0表中。同时还可以加入生成id的**概率和权重**来调整哪个分表落更多数据。

有了这个新的uuid方案，我们**既可以保证生成的数据趋势递增，同时也能非常方便扩展分表**。非常nice。

数据库有那么多种，mysql只是其中一种，那其他数据库也是要求主键自增吗？

tidb的主键id不建议自增

tidb是一款分布式数据库，作为mysql分库分表场景下的替代产品，可以更好的对数据进行分片。

它通过引入**Range**的概念进行数据表分片，比如第一个分片表的id在0~2kw，第二个分片表的id在2kw~4kw。这其实就是**根据id范围进行数据库分表**。

它的语法几乎跟mysql一致，用起来大部分时候是无感的。

但跟mysql有一点很不一样的就是，mysql建议id自增，但**tidb却建议使用随机的uuid**。原因是如果id自增的话，根据范围分片的规则，一段时间内生成的id几乎都会落到同一个分片上，比如下图，从`3kw`开始的自增uuid，几乎都落到`range 1`这个分片中，而其他表却几乎不会有写入，性能没有被利用起来。出现**一表有难，多表围观**的场面，这种情况又叫**写热点**问题。

![c976c957b44d749fa6372360b343f5d2.png][]

写热点问题

所以为了充分的利用多个分表的写入能力，tidb建议我们写入时使用**随机id**，这样数据就能被均匀分散到多个分片中。

用户id不建议用自增id

前面提到的不建议使用自增id的场景，都是技术原因导致的，而下面介绍的这个，单纯是因为业务。

举个例子吧。

如果你能知道一个产品每个月，新增的用户数有多少，这个对你来说会是有用的信息吗？

对程序员来说，可能这个信息价值不大。

但如果你是做投资的呢，或者是分析竞争对手呢？

那反过来。

如果你发现你的竞争对手，总能非常清晰的知道你的产品每个月新进的注册用户是多少人，你会不会心里毛毛的？

如果真出现了这问题，先不要想是不是有内鬼，先检查下你的用户表主键是不是自增的。

![0d649d381cb266ceca8c9996be1d1698.jpeg][]

**如果用户id是自增的，那别人只要每个月都注册一个新用户，然后抓包得到这个用户的user\_id，然后跟上个月的值减一下，就知道这个月新进多少用户了。**

同样的场景有很多，有时候你去小店吃饭，发票上就写了你是今天的第几单，那大概就能估计今天店家做了多少单。你是店家，你心里也不舒服吧。

再比如说一些小app的商品订单id，如果也做成自增的，那就很容易可以知道这个月成了多少单。

类似的事情有很多，这些场景都建议使用趋势递增的uuid作为主键。

当然，**主键保持自增，但是不暴露给前端，那也行，那前面的话，你当我没说过**。

#### 总结 ####

*  建表sql里主键边上的`AUTO_INCREMENT`，可以让主键自增，去掉它是可以的，但这就需要你在insert的时候自己设置主键的值。
 *  建表sql里的 `PRIMARY KEY` 是用来声明主键的，如果去掉，那也能建表成功，但mysql内部会给你偷偷建一个 `ROW_ID`的隐藏列作为主键。
 *  由于mysql使用**B+树索引，叶子节点是从小到大排序的**，如果使用自增id做主键，这样每次数据都加在B+树的最后，比起每次加在B+树中间的方式，加在最后可以有效**减少页分裂的问题。**
 *  在分库分表的场景下，我们可以通过redis等第三方组件来获得严格自增的主键id。如果不想依赖redis，可以参考雪花算法进行**魔改**，**既能保证数据趋势递增，也能很好的满足分库分表的动态扩容。**
 *  并不是所有数据库都建议使用自增id作为主键，比如**tidb就推荐使用随机id**，这样可以有效避免**写热点**的问题。而对于一些敏感数据，比如用户id，订单id等，如果使用自增id作为主键的话，外部通过抓包，很容易可以知道新进用户量，成单量这些信息，所以需要**谨慎考虑**是否继续使用自增主键。

![ad68c20012f6474d8c0f529d959f3865.jpeg][]

[cb053067372886e981df09d7ec100128.png]: https://img-blog.csdnimg.cn/img_convert/cb053067372886e981df09d7ec100128.png
[bc92aa44cb3c1fbfbeb0bd96696c3381.png]: https://img-blog.csdnimg.cn/img_convert/bc92aa44cb3c1fbfbeb0bd96696c3381.png
[1fad2f6fedf17092d8a44b4dc773d393.png]: https://img-blog.csdnimg.cn/img_convert/1fad2f6fedf17092d8a44b4dc773d393.png
[3a631b8bdd5c709190b87011a35c86d3.png]: https://img-blog.csdnimg.cn/img_convert/3a631b8bdd5c709190b87011a35c86d3.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=Mzg5NDY2MDk4Mw==&mid=2247488340&idx=1&sn=6c5a0743918d582eb6ee76571d992897&scene=21#wechat_redirect
[bc0babe3c0d7da854d40fe4949614bd0.png]: https://img-blog.csdnimg.cn/img_convert/bc0babe3c0d7da854d40fe4949614bd0.png
[Link 2]: https://mp.weixin.qq.com/s?__biz=Mzg5NDY2MDk4Mw==&mid=2247488672&idx=1&sn=b2e0ba15933587bab8d9ee636c59a963&scene=21#wechat_redirect
[1e2f98c7a5c5e0ff2829cce1641cd2b8.png]: https://img-blog.csdnimg.cn/img_convert/1e2f98c7a5c5e0ff2829cce1641cd2b8.png
[598b62ea7a1bf5d1df20371663b29cb7.png]: https://img-blog.csdnimg.cn/img_convert/598b62ea7a1bf5d1df20371663b29cb7.png
[2a037222dce044e0bfcff85491aa59eb.png]: https://img-blog.csdnimg.cn/img_convert/2a037222dce044e0bfcff85491aa59eb.png
[70ea47aa74016cfaf2a67e5ec8fa1cee.png]: https://img-blog.csdnimg.cn/img_convert/70ea47aa74016cfaf2a67e5ec8fa1cee.png
[086aa903b295a5b61cb8f43efd8720c2.jpeg]: https://img-blog.csdnimg.cn/img_convert/086aa903b295a5b61cb8f43efd8720c2.jpeg
[c976c957b44d749fa6372360b343f5d2.png]: https://img-blog.csdnimg.cn/img_convert/c976c957b44d749fa6372360b343f5d2.png
[0d649d381cb266ceca8c9996be1d1698.jpeg]: https://img-blog.csdnimg.cn/img_convert/0d649d381cb266ceca8c9996be1d1698.jpeg
[ad68c20012f6474d8c0f529d959f3865.jpeg]: https://img-blog.csdnimg.cn/ad68c20012f6474d8c0f529d959f3865.jpeg