面试官:MySQL自增主键为什么不是连续的?
共 2957字，需浏览 6分钟
·
2022-12-22 00:03
程序员的成长之路互联网/程序员/技术/资料共享
关注
阅读本文大概需要 3 分钟。
来自：blog.csdn.net/jack1liu/article/details/99699201一 前言
提出这个问题，是因为在工作中发现 mysql 中的 user 表的 id 默认是自增的，但是数据库存储的结果却不是连续的。user 表结构：
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id',
`name` varchar(20),
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),UNIQUE KEY `idx_name` (`name`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user表'
二 自增值存储说明
1.1.MyISAM 引擎的自增值保存在数据文件中。1.2.InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：-
在 MySQL 5.7 及之前的版本，自增值保存在内存里。每次重启后，第一次打开表的时候，都会去找自增值的最大值
max(id)，然后将max(id) + 1作为这个表当前的自增值。 -
在 MySQL 8.0 版本，将自增值的变更记录在了
redo log中，重启的时候依靠redo log恢复重启之前的值。
三 自增值修改机制
在 MySQL 里面，如果字段 id 被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：-
如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的
AUTO_INCREMENT值填到自增字段； -
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。
-
如果 X<Y，那么这个表的自增值不变；
-
如果 X≥Y，就需要把当前自增值修改为新的自增值。
auto_increment_offset 开始，以 auto_increment_increment为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。四 自增值修改时机
insert into user values(null, '张三');
-
当执行上述 SQL 时，执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是
(0,"张三")； - InnoDB 发现 SQL 没有指定自增 id 的值，获取 user 表当前的自增值 2；
-
将传入的行的值改成
(2,"张三"); - 将表的自增值改成 3；
- 继续执行插入数据操作。
五 导致自增值不连续的原因
5.1 唯一键冲突
假设执行 SQL 的时候 user 表id = 10，此时在内存中的自增 id 为11，此时发生唯一键冲突写库失败，则 user 表没有 id = 10 这条记录，之后 id 从11开始写入，因此 id 是不连续的。5.2 事务回滚
假设同时需要对 user、staff 表进行写库操作，执行 SQL 的时候 user 表id = 10，此时在内存中的自增 id 为11；staff 表 id = 20，此时内存中的自增 id 为21，一旦事务执行失败，事务回滚，写库失败，则 user 表没有 id = 10 这条记录，staff 表没有 id = 20 这条记录，user 表从11开始写入，staff 表从21开始写入，如此产生 id 不连续的现象。5.3 批量写库操作
对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：- 语句执行过程中，第一次申请自增 id，会分配 1 个；
- 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
- 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
id = 1，第二次分配到 id = 2、3 ，第三次分配到 id = 4、5、6、7，当批量写入四条记录之后，id = 1、2、3、4将会入库，但是 id = 5、6、7就被废弃了，下一个 id 从8开始。六 参考文档
<END>
- https://time.geekbang.org/column/intro/139
推荐阅读：
Java 自带的性能调优神器！！
互联网初中高级大厂面试题(9个G)
内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper......等技术栈！
⬇戳阅读原文领取！
朕已阅
