学习笔记 - MySQL自增主键值

1、有朋友给我留言

为什么他的库表里面,有些是连续的,有些不是连续的,而且他的表都是自增(`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id'),他的潜意识里这个id应该是连续的才对。因此准备梳理梳理mysql自增主键的值的变化情况,让朋友有个比较全面的了解。

 

2、自增值存储说明

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

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

a)在 MySQL 5.7 及之前的版本,自增值保存在内存里。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id) + 1 作为这个表当前的自增值。
b)在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。

 

3、自增值修改机制

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

如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。
     根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 i,当前的自增值是 j。

如果 i<j,那么这个表的自增值不变;
如果 i≥j,就需要把当前自增值修改为新的自增值。
     新的自增值生成算法是:从 auto_increment_offset 开始,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于i的值,作为新的自增值。其中,auto_increment_offset 和 auto_increment_increment 是两个系统参数,分别用来表示自增的初始值和步长,默认值都是 1。

 

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

4.1 唯一键冲突
       假设执行 SQL 的时候 user 表 id = 10,此时在内存中的自增 id 为11,此时发生唯一键冲突写库失败,则 user 表没有 id = 10 这条记录,之后 id 从11开始写入,因此 id 是不连续的。

4.2 事务回滚
       innodb设计的时候语句执行失败也不回退自增id,也正是因为这样,所有才只保证了自增id是递增的,但不保证是连续的。假设同时需要对 user、staff 表进行写库操作,执行 SQL 的时候 user 表 id = 10,此时在内存中的自增 id 为11;staff 表 id = 20,此时内存中的自增 id 为21,一旦事务执行失败,事务回滚,写库失败,则 user 表没有 id = 10 这条记录,staff 表没有 id = 20 这条记录,user 表从11开始写入,staff 表从21开始写入,如此产生 id 不连续的现象。

4.3 批量写库操作
       对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:

       a). 语句执行过程中,第一次申请自增 id,会分配 1 个;

       b). 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;

       c). 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开始。

 

4.4 大量delete操作,物理删除数据,delete from tbname where xxxx;这样硬删除会导致id不连续。

 

5、如何实现自增id值递增且连续

如果想让自己的bigint类型主键id字段值是连续的,要怎么实现呢,MySQL底层默认是实现不了的,需要我们自己做下特殊处理。

5.1、做成触发器的方式

insert:每次进行insert插入操作时,都先执行查询操作,得到该序列号,然后插入时再加1;当然这种办法是最笨的方法,而且需要多执行一次操作

update: update的时候,如果update主键值,要判断主键值是否是max(id)+1,如果不是设置成update(id)+1.

delete:不用做任何操作,甚至稳妥起见,不能物理delete,只能设置一个status=-1这样的逻辑删除。

 

5.2、业务层的处理

在每次插入数据之后,修正那个AUTO_INCREMENT值。就是   : 先 执行 ALTER TABLE `table` AUTO_INCREMENT =1

执行之后,在执行插入操作,这样就可以让自增类型连续自增;  (注:不一定再插入的id就是1;而是表中id最大值+1。),而且不允许硬删除delete操作。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页