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

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

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 表一样，一行行似的。

user 表

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

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

B + 树结构

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

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

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

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

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

主键 id 自增的情况

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

主键 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） 。有没有很眼熟，这个在之前写的文章里出现过。

隐藏的 row_id 列

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

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

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

mysql 分库分表下的 id

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

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

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

根据 id 取模分表

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

根据 id 范围分表

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

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

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

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

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

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

雪花算法

首先第 0 位不用。

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

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

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

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

但是！

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

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

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

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

适合分库分表的 uuid 算法

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

适合分库分表的 uuid 算法

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

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

在接下来的 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 在 02kw，第二个分片表的 id 在 2kw4kw。这其实就是根据 id 范围进行数据库分表。

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

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

写热点问题

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

用户 id 不建议用自增 id

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

举个例子吧。

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

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

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

那反过来。

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

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

如果用户 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 作为主键的话，外部通过抓包，很容易可以知道新进用户量，成单量这些信息，所以需要谨慎考虑是否继续使用自增主键。

作者：CRMEB技术团队
链接：https://juejin.cn/post/7111616533042429959
来源：稀土掘金