MySQL中自增主键的示例分析

70次阅读
没有评论

共计 3241 个字符,预计需要花费 9 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 中自增主键的示例分析,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。

一、自增值保存在哪儿?

不同的引擎对于自增值的保存策略不同

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

2.InnoDB 引擎的自增值,在 MySQL5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+ 步长作为这个表当前的自增值

select max(ai_col) from table_name for update;

在 MySQL8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值

二、自增值修改机制

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

1. 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段

2. 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值

假设,某次要插入的值是 X,当前的自增值是 Y

1. 如果 X Y,那么这个表的自增值不变

2. 如果 X =Y,就需要把当前自增值修改为新的自增值

新的自增值生成算法是:从 auto_increment_offset(初始值)开始,以 auto_increment_increment(步长)为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值

三、自增值的修改时机

创建一个表 t,其中 id 是自增主键字段、c 是唯一索引,建表语句如下:

CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `c` (`c`)) ENGINE=InnoDB;

假设,表 t 里面已经有了 (1,1,1) 这条记录,这时再执行一条插入数据命令:

insert into t values(null, 1, 1);

执行流程如下:

1. 执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是(0,1,1)

2.InnoDB 发现用于没有指定自增 id 的值,获取表 t 当前的自增值 2

3. 将传入的行的值改成(2,1,1)

4. 将表的自增值改成 3

5. 继续执行插入数据操作,由于已经存在 c = 1 的记录,所以报 Duplicate key error(唯一键冲突),语句返回

对应的执行流程图如下:

在这之后,再插入新的数据行时,拿到的自增 id 就是 3。出现了自增主键不连续的情况

唯一键冲突和事务回滚都会导致自增主键 id 不连续的情况

四、自增锁的优化

自增 id 锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请

但在 MySQL5.0 版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放

MySQL5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1

1. 这个参数设置为 0,表示采用之前 MySQL5.0 版本的策略,即语句执行结束后才释放锁

2. 这个参数设置为 1

普通 insert 语句,自增锁在申请之后就马上释放

类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放

3. 这个参数设置为 2,所有的申请自增主键的动作都是申请后就释放锁

为了数据的一致性,默认设置为 1

如果 sessionB 申请了自增值以后马上就释放自增锁,那么就可能出现这样的情况:

sessionB 先插入了两行数据(1,1,1)、(2,2,2)

sessionA 来申请自增 id 得到 id=3,插入了(3,5,5)

之后,sessionB 继续执行,插入两条记录(4,3,3)、(5,4,4)

当 binlog_format=statement 的时候,两个 session 是同时执行插入数据命令的,所以 binlog 里面对表 t2 的更新日志只有两种情况:要么先记 sessionA 的,要么先记录 sessionB 的。无论是哪一种,这个 binlog 拿到从库执行,或者用来恢复临时实例,备库和临时实例里面,sessionB 这个语句执行出来,生成的结果里面,id 都是连续的。这时,这个库就发生了数据不一致

解决这个问题的思路:

1)让原库的批量插入数据语句,固定生成连续的 id 值。所以,自增锁直到语句执行结束才释放,就是为了达到这个目的

2)在 binlog 里面把插入数据的操作都如实记录进来,到备库执行的时候,不再依赖于自增主键去生成。也就是把 innodb_autoinc_lock_mode 设置为 2,同时 binlog_format 设置为 row

如果有批量插入数据(insert … select、replace … select 和 load data)的场景时,从并发插入数据性能的角度考虑,建议把 innodb_autoinc_lock_mode 设置为 2,同时 binlog_format 设置为 row,这样做既能并发性,又不会出现数据一致性的问题

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

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

2.1 个用完以后,这个语句第二次申请自增 id,会分配 2 个

3.2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个

4. 依次类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert … select,实际上往表 t2 中插入了 4 行数据。但是,这四行数据是分三次申请的自增 id,第一次申请到了 id=1,第二次被分配了 id= 2 和 id=3,第三次被分配到 id= 4 到 id=7

由于这条语句实际上只用上了 4 个 id,所以 id= 5 到 id= 7 就被浪费掉了。之后,再执行 insert into t2 values(null, 5,5),实际上插入了的数据就是(8,5,5)

这是主键 id 出现自增 id 不连续的第三种原因

五、自增主键用完了

自增主键字段在达到定义类型上限后,再插入一行记录,则会报主键冲突的错误

以无符号整型(4 个字节,上限就是
   
     
     
       
        2
       
       
        32
       
     
     
       −
     
     
       1
     
     
     
      2^{32}-1      
    232−1)为例,通过下面这个语句序列验证一下:

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

第一个 insert 语句插入数据成功后,这个表的 AUTO_INCREMENT 没有改变(还是 4294967295),就导致了第二个 insert 语句又拿到相同的自增 id 值,再试图执行插入语句,报主键冲突错误

感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“MySQL 中自增主键的示例分析”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-01发表,共计3241字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)