共计 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 行业资讯频道,更多相关知识等着你来学习!