看完这一篇，别在说你学过MySQL了（三）

6. 主键自增ID

主键自增也是高频面试话题，今天我们就来介绍一下，使用MySQL自增ID可以节省我们多少成本，ID为什么会不连续，上限的一系列问题吧。

6.1 优缺点

• 使用主键自增ID比UUID节省一半磁盘空间
• 范围like查询，自增ID性能优于UUID。(不过有缓存的情况下，双方性能相差不大)
• 写入测试，自增ID是UUID的4倍

6.2 实现原理

存放位置

自增值是保存在表结构定义里的，实际上表结构定义是存放在后缀名为.frm 文件中，但不会保存自增值。

自增值的保存策略

• MyISAM 引擎的自增值保存在数据文件中。
• innodb5.7及之前的版本，自增值都是保存在内存中，没有持久化每次重启后，第一次打开表的时候都会去找自增值的最大值然后将最大值+1作为当前的自增值ID。
• innodb8.0版本，将自增值保存在了redolog中，重启的时候依靠redolog恢复重启之前的值

自增值修改机制

如果一个字段为自增字段，在插入一行数据的时

0. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
1. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

• 如果要插入的值小于自增值，那么这个表的自增值不变
• 如果要插入的值大于或等于自增值，就需要把当前自增值修改为新的自增值

自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。默认值都是 1

自增值的修改时机

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

假设表中存在一条（1，1，1）数据。如果在插入一条（null，1，1）。执行流程如下

0. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
1. InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2，然后把2代入 （2,1,1）
2. 然后再把自增值改成3
3. 执行插入操作，因为c是唯一索引。所以插入（2,1,1）会报错。

这个时候问题就出来了，2的值没插进去，自增值也没有被改回去，就形成了不连续的情况

还有一种情况就是事务问题。介绍完2个机制1个策略了。直接快刀斩乱麻不啰嗦了。

为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

• 事务A在执行一个插入语句的时候会申请一个ID值，此时如果申请一个3，那么这时自增后的值为4
• 事务B也在执行一个插入语句也申请了一个ID值，此时拿到了ID为4，同时主键自增为5

上面是两个事务在执行插入语句，如果其中一个事务执行失败或者进行了回滚。那么就缺失了一条产生了一条空隙。原本ID为4的数据没有了。而且自增值也是5，也没有改回4。

6.3 ID不连续的问题

0. 插入不成功自增值没有被改回去，导致不连续
1. 事务回滚问题

6.4 为什么MySQL没有把ID改回去

MySQL之所以没有把ID改回去是因为考虑性能问题！

我们可以举两个反例。如果要退回去的话，肯定要判断退回去的这个ID是否存在。那么如何判断，肯定是要查表的。

每次在申请ID之前，先判断表ID是否存在的话，性能是大打折扣。本来申请ID是一个很快的操作，现在还要去主键索引上判断ID是否存在。

性能： 敢削我性能我锤死你。

还要一种情况就是，完成一个事务提交确认无误之后，再释放锁。这样的话虽然可以保证安全性，但是锁的粒度太大，系统并发能力大大下降。

所以业务方面进行一个平衡，还是选择了性能，没有把ID改回去。

6.6 自增锁

这里可以跟面试官简单的介绍一些历史

5.0版本

系统采用的就是我们上面介绍的比较安全的，并发度偏低的方法。一个语句申请了自增锁，会等语句执行结束之后才释放。

5.1.22版本

对比5.0版本，这里引进了一个策略innodb_autoinc_lock_mode

• 如果设置为0，采用的自增锁策略就是等语句执行之后释放，属于5.0版本的策略
• 如果设置为1，insert语句申请之后会立马释放。如果是inser-select 还是要等语句执行完成之后再释放的。
• 如果设置为2，所有申请的自增锁，申请后立即释放，增加并发度

分析一下insert-select的问题

大家可以回想一下写入日志的事务。如果binlog格式为statement，那么他们如何记录？

如果insert-select采用申请后立即释放的方案。多个数据不过申请主键ID，会出现两个事务交叉的形式。也就是说一个事务的ID数据不是连续的。

一旦两个事务同时执行，要么先写事务A，要么先写事务B。无论哪一种binlog拿去从库执行的时候，insert-select恢复出来的时候ID是连续的，这个库的数据就出现了不一致的情况。

至今MySQL引用的是5.1.22的版本

6.7 ID上限后如何处理

这个知识点也是大厂比较常问的一个话题。因为大厂的数据量是比较大的，的确会遇到这样的场景。

主键ID自增上限后，就会出现覆盖掉原数据的情况。上线是4294967295，近43亿。

从这个角度看，我们还是应该在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。

毕竟覆盖数据，就意味着数据丢失，影响的是数据可靠性；报主键冲突，是插入失败，影响的是可用性。而一般情况下，可靠性优先于可用性。

一个表中没有主键的话，MySQL会默认建立一个隐藏字段，这个字段就是row_id。默认情况下建立的主键ID都是8个字节的，这个row_id是6个字节的。