请选择 进入手机版 | 继续访问电脑版

[MySql] 为什么mysql自增主键不是连续 的

[复制链接]
查看94 | 回复13 | 2021-9-12 22:41:36 | 显示全部楼层 |阅读模式
目次

一 前言

       提出这个标题 ,是由于 在工作中发现 mysql 中的 user 表的 id 默认是自增的,但是数据库存储的结果 却不是连续 的。

       user 表布局 :

  1. CREATE TABLE `user` (
  2. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id',
  3. `name` varchar(20),
  4. `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  5. `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  6. PRIMARY KEY (`id`),UNIQUE KEY `idx_name` (`name`))
  7. ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user表'
复制代码

       user 表存储:

为什么mysql自增主键不是连续

的

二 自增值存储阐明

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
  • 假如 X≥Y,就必要 把当前自增值修改为新的自增值。

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

四 自增值修改机遇

  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 

到此这篇关于为什么mysql自增主键不是连续 的 的文章就先容 到这了,更多相干 mysql自增主键连续 内容请搜刮 脚本之家从前 的文章或继续欣赏 下面的相干 文章盼望 大家以后多多支持脚本之家!


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

avatar 我的承诺只给你 | 2021-9-29 21:04:39 | 显示全部楼层
admin楼主是在找骂么?
回复

使用道具 举报

avatar 卡庙寺 | 2021-9-30 03:39:16 | 显示全部楼层
小弟默默的路过贵宝地~~~
回复

使用道具 举报

avatar 摸金狂人 | 2021-10-9 10:46:26 | 显示全部楼层
学习雷锋,好好回帖!
回复

使用道具 举报

avatar 哦也X5 | 2021-10-11 16:37:09 | 显示全部楼层
论坛的人气越来越旺了!
回复

使用道具 举报

avatar 深渊金哥哥j | 2021-10-11 17:17:32 | 显示全部楼层
admin楼主,我告诉你一个你不知道的的秘密,有一个牛逼的网站,运动刷步数还是免费刷的,QQ和微信都可以刷,特别好用。访问地址:http://yd.mxswl.com 猫先森网络
回复

使用道具 举报

avatar 阿甘cx1982 | 2021-10-13 11:51:26 | 显示全部楼层
东方不败外加灭绝师太啊!
回复

使用道具 举报

avatar 和你相伴1 | 2021-10-15 12:59:31 | 显示全部楼层
态度决定一切,不错!
回复

使用道具 举报

avatar 涨吧涨吧章 | 2021-10-15 22:48:03 | 显示全部楼层
刚分手,心情不好!
回复

使用道具 举报

avatar 极品小处男处v | 2021-10-17 14:27:42 | 显示全部楼层
收藏了,很不错的内容!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则