为什么mysql自增主键不是连续的
作者:新猿一马 发布时间:2024-01-19 06:59:53
一 前言
提出这个问题,是因为在工作中发现 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 表存储:
二 自增值存储说明
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,那么这个表的自增值不变;
如果 X≥Y,就需要把当前自增值修改为新的自增值。
新的自增值生成算法是:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。其中,auto_increment_offset 和 auto_increment_increment 是两个系统参数,分别用来表示自增的初始值和步长,默认值都是 1。
四 自增值修改时机
insert into user values(null, '张三');
1 当执行上述 SQL 时,执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,"张三");
2 InnoDB 发现 SQL 没有指定自增 id 的值,获取 user 表当前的自增值 2;
3 将传入的行的值改成 (2,"张三");
4 将表的自增值改成 3;
5 继续执行插入数据操作。
五 导致自增值不连续的原因
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 的策略:
1. 语句执行过程中,第一次申请自增 id,会分配 1 个;
2. 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
3. 2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
依此类推,同一个语句去申请自增 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开始。
六 参考文档
https://time.geekbang.org/column/intro/139
来源:https://blog.csdn.net/jack1liu/article/details/99699201
猜你喜欢
- 本文通过一个实际的散点图案例,展示了如何使用pyqt5嵌套一个pyecharts图层的方法,通过这个技巧,可以在pyqt5的框架中也实现精美
- 什么是 BokehBokeh 是 Python 中的交互式可视化库。Bokeh提供的最佳功能是针对现代 Web 浏览器进行演示的高度交互式图
- 这篇文章所说的视觉元素是指:在一个网站中除去内容(文本、图片、视频、音频等)之外的一些元素。比如图标,背景色,以及背景图案。视觉元素的设计是
- 在安装SQL Server 2005 时出现ASP.Net版本注册要求(警告),提示找不到ASP.Net 在 Microsoft Inter
- 写在前面的话:此篇还是asp相关的,相信玩ASP的都有这个感觉,当数据有5万多条时-------just like音乐网,要调用最新的10条
- 一、介绍在做YOLOv3项目时,会需要将文本文件中的某部分内容进行批量替换和修改,所以编写了python程序批量替换所有文本文件 * 定部分的
- Q. How can I restrict access to my SQL Server so that it only allows c
- 为了得到更加清晰的图像我们需要通过技术对图像进行处理,比如使用对比度增强的方法来处理图像,对比度增强就是对图像输出的灰度级放大到指定的程度,
- MySQL 读写分离在互联网项目中应该算是一个非常常见的需求了。受困于 Linux 和 MySQL 版本问题,很多人经常会搭建失败,今天松哥
- 本文实例讲述了Python中itertools模块用法,分享给大家供大家参考。具体分析如下:一般来说,itertools模块包含创建有效迭代
- 1, 创建pytorch 的Tensor张量:torch.rand((3,224,224)) #创建随机值的三维张量,大小为(3,224,2
- 在正文前,先简短介绍自己。我任职于广州的某个网站服务公司的系统开发员,主要任务是以.Net编写各种web系统,例如CMS.EIP。大家都知道
- 本文实例讲述了Python实现的ftp服务器功能。分享给大家供大家参考,具体如下:python 具备强大的网络编程功能,而且代码简介,用简单
- 本文实例为大家分享了python3连接MySQL数据库的具体代码,供大家参考,具体内容如下#python3连接MySQL实例import p
- pydantic-resolve 解决嵌套数据结构的生成和其他方案的比较pydantic-resolve和GraphQL相比GraphQL的
- 无意中在csdn上看到一帖有关绘制杨辉三角的sql表达式,感觉很有意思。后来自己想下不借助临时表,根据杨辉三角的组合数计算方法C(n,m)=
- 一、简介py2exe是一个将python脚本转换成windows上的可独立执行的可执行程序(*.exe)的工具,这样,你就可以不用装pyth
- 实验目的:用户输入网卡名称,通过函数返回对应的IPv4和IPv6地址。实验代码:步骤一: 由于window系统下网卡名称并不是真正的名字,而
- 思考:测试用例执行后,如何生成一个直观漂亮的测试报告呢?分析:1.unittest单元测试框架本身带有一个textTestRunner类,可
- 刚才显示数据的时候遇到一个日期里面带T的问题,就是天数跟小时数之间出现了一个T。 表字段里面也没有这个T,后来查询度娘,是因为json处理的