MySQL之深入解析自增主键为何不连续

一、前言

• 众所周知，由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，大量的随机 IO，自增主键不连续。这应该是大家已经熟知的知识点，但是也应该还有不少的朋友不知道为何自增主键不是严格递增的？什么情况下自增主键会出现 “断层”？
• 为了更加形象，这里创建一个表 xl_tb，其中 id 是自增主键字段，a 是唯一索引，然后插入一条数据，查看它的表结构：

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`a`  int(11) NULL ,
`b`  int(11) NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX `a` (`a`) USING BTREE 
);

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
 

insert into xl_tb values(null, 1, 1)

  

 

  
1
 

mysql> show create table xl_tb\G;
*************************** 1. row ***************************
       Table: xl_tb
Create Table: CREATE TABLE `xl_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
 

• 可以看到，表定义里面出现了一个AUTO_INCREMENT=4，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=4。看到这里，可能有朋友会以为自增值存在表结构里呢？要是你这样想，那就又错啦。

二、自增主键为何不连续

① 自增主键存储策略

• 其实，不同的存储引擎，自增值保存策略不一样的：
• • MyISAM 引擎的自增值保存在数据文件中；
• • InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：
• • • 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化；每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值；
• • • 举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11，这时候，删除 id=10 的行，AUTO_INCREMENT 还是11；但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成10，也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
• • 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

② 自增值修改机制

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

③ 自增值新增机制

• 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；
• 否则，自增值不变。

④ 自增值的修改时机

• 假设，表 xl_tb 里面已经有了 (1,1,1) 这条记录，这时再执行一条插入数据命令：

insert into t values(null, 1, 1); (自增id,唯一键a,普通字段b)

  

 

  
1
 

• 这个语句的执行流程就是：
• • 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是(0,1,1)；
• • InnoDB 发现用户没有指定自增 id 的值，获取表 xl_tb 当前的自增值 4；
• • 将传入的行的值改成(4,1,1)；
• • 将表的自增值改成 5；
• • 继续执行插入数据操作，由于已经存在 a=1 的记录，所以报 Duplicate key error，语句返回。
• 这个表的自增值改成 5，是在真正执行插入数据的操作之前，这个语句真正执行的时候，因为碰到唯一键 a 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。
• 因此在这之后，再插入新的数据行时，拿到的自增 id 就是 5，也就是说，出现了自增主键不连续的情况。因此，唯一键冲突是导致自增主键 id 不连续的第一种原因。同样地，事务回滚也会产生类似的现象，这就是第二种原因。
• 这时，你可能会想，为什么在出现唯一键冲突或者回滚的时候，MySQL 没有把表 xl_tb 的自增值改回去呢？如果把表 xl_tb 的当前自增值从 5 改回 4，再插入新数据的时候，不就可以生成 id=2 的一行数据了吗？那么，接下来继续来看看，为何不让自增主键后退吧？
• 首先，假设有两个并行执行的事务 A、B，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请：

• 分析：
• • 首先，事务A申请到 id=2，此时当前自增值为3，由于加锁顺序申请，事务B申请到 id=3（当前自增值），此时，当前自增值变为 3+1=4；
• • 然后，事务 A、B 都插入，假设事务 B 先插入然后成功插入，然后事务 A 插入发生了唯一键冲突；
• • 如果假设允许自增值后退，自增值就变为 2 啦，假如事务 A 继续插入，申请到 id=2，成功插入，申请到 id=3，插入，由于之前事务 B 已经插入 id=3 的数据，此时发生主键冲突。
• 那怎样解决呢？
• • 每次申请 id 之前，先判断表里面是否已经存在这个 id；
• • 扩大锁范围，必须等事务执行完，才能申请下一个；
• 虽然这两种方法可以解决，但是无疑性能极低。于是，便让自增值不能回退，而避免造成主键冲突等问题。

三、总结

• 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化；
• 事务回滚（自增值不能回退，因为并发插入数据时，回退自增 id 可能造成主键冲突）；
• 唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值 +1，所以不连续）。
• 这就是为什么自增主键不连续的原因所在。

文章来源: blog.csdn.net，作者：Serendipity·y，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/Forever_wj/article/details/122385906