面试官:mysql自增主键用过吧?

小菜:用过,建表时指定自动递增就行,然后插入数据就可以不用指定主键的值了

面试官:不错,那你知道Mysql自增主键是严格递增的吗?

小菜:肯定是递增的呀,诶,好像不对,也有见过不是连续的

面试官:那你知道为什么会出现不连续的呢?

小菜:emmm......这个岗位可能不是很适合我

面试官:那今天就面到这里,还记得进来的路吧......

一、自增主键

自增主键采用数字型,占用空间小,自动编号,没有指定序号也不用担心主键冲突。可以让主键索引尽可能的保持递增顺序插入,避免了页分裂,大量的随机IO。

可以使用查看建表语句来查询下一次的自增序号

可以看到,表定义里面出现了一个AUTO_INCREMENT=6,表示下一次插入数据时,如果需要自动生成自增值,会生成id=6。

看到这,如果你觉得自增值是存在表里的话,那你就错了。

二、自增主键为何不连续

表里最大id=4,也可能出现上面这种AUTO_INCREMENT=6,下一次插入数据,自增主键=6的情况,那为什么呢?

2.1 自增值的存储策略

不同的存储引擎,自增值的存储策略也是不一样的。

  1. MyISAM引擎的自增值保存在数据文件中
  2. InnoDB引擎的自增值,保存在内存中,只有到了Mysql8.0版本,才有了自增值持久化的功能
    1. 在Mysql5.7及之前的版本,自增值保存在内存中。所以每次重启后,第一次打开表的时候,都会去表里找最大值max(id),使用max(id)+1当做这个表下一次的自增值。
      • eg.:当自增值=10,假设你删除了id=9的记录,重启了数据库实例,这时候,自增值就会变成9,不重启的话,就还是10。所以说,Mysql重启是可能修改一个表AUTO_INCREMENT的值
    2. 在Mysql8.0之后的版本,将自增值的变更记录在了redo log中,重启时依靠这个文件回复重启之前的值

2.2 自增值修改机制

  1. 如果插入时id字段指定的是0、null或未指定值,那么就使用这个表的AUTO_INCREMENT 的值当做自增值
  2. 如果插入时指定了具体的值,就使用sql语句里指定的值

2.3 自增值新增机制

  1. 如果插入的记录指定的 id >= 当前的自增值,那么新的自增值就是 这个 id 的值+1
  2. 否则,自增值不变

2.4 自增值的修改时机

假设有一张表,结构如下

自增主键id,唯一键a,普通字段b

表里已经存在一条记录,id=1,a=1,b=1

这时候,我再执行一条插入语句

insert into t values(0,1,1);

这条语句的执行顺序:

  1. 执行器调用InnoDB引擎接口写入一行,传入的值是 (0,1,1)
  2. InnoDB发现没有指定id,故获取表t的自增值3
  3. 将传入的值改为 (3,1,1)
  4. 将表的自增值改为4
  5. 继续执行插入操作,由于这时候已经存在a=1的记录,故触发唯一键冲突Duplicate key error,插入失败

自增值是在真正执行插入操作之前变更的,但是插入失败后,自增值也不会再被改回去。

所以,唯一索引冲突是导致自增主键id不连续的一种原因

同样,事务回滚也会产生类似的现象,事务回滚也是另外一种原因

而Mysql为什么设计成不让自增值回退呢?

因为多事务并发插入时,回退自增值可能会导致主键冲突,那么就要另外的方案来解决这个问题,无疑影响了性能。

文章目录