MySQL自增主键为什么不是连续的

2023-07-05  本文已影响0人  AC编程

一、前言

提出这个问题,是因为在工作中发现 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表'
user

二、自增值存储说明

MyISAM 引擎的自增值保存在数据文件中。

InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了自增值持久化的能力,也就是才实现了如果发生重启,表的自增值可以恢复为 MySQL 重启前的值。具体情况是:

三、自增值修改机制

在 MySQL 里面,如果字段 id 被定义为AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:

根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 X,当前的自增值是 Y。

新的自增值生成算法是:从auto_increment_offset开始,以 auto_increment_increment为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。其中,auto_increment_offsetauto_increment_increment是两个系统参数,分别用来表示自增的初始值和步长,默认值都是 1。

四、自增值修改时机

insert into user values(null, '张三'); 

五、导致自增值不连续的原因

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,每次申请到的自增 id 个数都是上一次的两倍。假设批量往 user 表中写入四条记录,则这四条记录将分为三次申请id,第一次分配到 id = 1,第二次分配到 id = 2、3 ,第三次分配到 id = 4、5、6、7,当批量写入四条记录之后,id = 1、2、3、4将会入库,但是 id = 5、6、7就被废弃了,下一个 id 从8开始。

转载自:面试官:MySQL自增主键为什么不是连续的?

上一篇 下一篇

猜你喜欢

热点阅读