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

2022-05-15 0 193
目录
  • 一 前言
  • 二 自增值存储说明
  • 三 自增值修改机制
  • 四 自增值修改时机
  • 五 导致自增值不连续的原因
    • 5.1 唯一键冲突
    • 5.2 事务回滚
    • 5.3 批量写库操作
  • 六 参考文档 

    一 前言

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

    为什么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<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 

    到此这篇关于为什么mysql自增主键不是连续的 的文章就介绍到这了,更多相关mysql自增主键连续内容请搜索NICE源码以前的文章或继续浏览下面的相关文章希望大家以后多多支持NICE源码!

    免责声明:
    1、本网站所有发布的源码、软件和资料均为收集各大资源网站整理而来;仅限用于学习和研究目的,您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 不得使用于非法商业用途,不得违反国家法律。否则后果自负!

    2、本站信息来自网络,版权争议与本站无关。一切关于该资源商业行为与www.niceym.com无关。
    如果您喜欢该程序,请支持正版源码、软件,购买注册,得到更好的正版服务。
    如有侵犯你版权的,请邮件与我们联系处理(邮箱:skknet@qq.com),本站将立即改正。

    NICE源码网 MySql 为什么mysql自增主键不是连续的 https://www.niceym.com/38875.html