您现在的位置是:群英 > 数据库 > MySQL数据库
SQL中自增主键你了解多少?自增值修改机制是什么?
Admin发表于 2021-12-09 17:47:26868 次浏览

    这篇文章给大家分享的是SQL中自增主键的相关内容,介绍了自增值修改机制、自增值的修改时机、自增锁的优化方法等等,文中介绍的非常详细,对大家深入理解SQL中自增主键有一定的帮助,感兴趣的朋友接下来一起跟随小编了解看看吧。

一、自增值保存在哪儿?

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

    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 2321)为例,通过下面这个语句序列验证一下:

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值,再试图执行插入语句,报主键冲突错误

    以上就是关于MySQL中的自增主键的介绍,上述SQL语句具有一定的参考价值,有需要的朋友可以了解看看,希望对大家学习和深入了解MySQL中的自增主键有帮助,想要了解更多可以继续浏览群英网络其他相关的文章。

文本转载自PHP中文网

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。

标签: mysql自增主键
相关信息推荐
2022-05-18 17:25:00 
摘要:在oracle中,可以在where子句中利用like关键字来实现模糊查询效果,字符匹配操作可以使用通配符“%”和“_”,语法为“SELECT * FROM user WHERE 列名 LIKE '模糊查询字段'”。
2021-11-08 17:46:24 
摘要:Oracle中RAC是什么?RAC是Oracle新版数据库中采用的一项新技术,一些朋友可能对于RAC不是很了解,对此本文就给大家来简单的介绍一下RAC以及RAC的优缺点。感兴趣的朋友接下来跟随小编一起了解看看吧。
2022-10-18 17:21:21 
摘要:本篇文章给大家带来了关于mysql的相关知识,其中主要整理了约束的相关问题,约束为了保证数据的完整性,数据完整性是指数据的精确性和可靠性,它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的,下面一起来看一下,希望对大家有帮助。
群英网络助力开启安全的云计算之旅
立即注册,领取新人大礼包
  • 联系我们
  • 24小时售后:4006784567
  • 24小时TEL :0668-2555666
  • 售前咨询TEL:400-678-4567

  • 官方微信

    官方微信
Copyright  ©  QY  Network  Company  Ltd. All  Rights  Reserved. 2003-2019  群英网络  版权所有   茂名市群英网络有限公司
增值电信经营许可证 : B1.B2-20140078   粤ICP备09006778号
免费拨打  400-678-4567
免费拨打  400-678-4567 免费拨打 400-678-4567 或 0668-2555555
微信公众号
返回顶部
返回顶部 返回顶部