MySQL中自增主键的示例分析 - 行业资讯 - 肥雀云

　　介绍

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

<强>一、自增值保存在哪儿吗?

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

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

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

select 马克斯(ai_col),得到table_name for 更新;

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

<强>二、自增值修改机制

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

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

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

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

1。如果X

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 空, 　　' d '才能,int (11), DEFAULT 空, 　　PRIMARY 才能;KEY (id), 　　UNIQUE 才能;KEY “c”, (“c”)),引擎=InnoDB;

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

insert into t 值(空,,1,1),

执行流程如下:

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

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

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

4。将表的自增值改成3

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

对应的执行流程图如下:
MySQL中自增主键的示例分析“> 在这之后,再插入新的数据行时,拿到的自增id就是3。出现了自增主键不连续的情况 <强>唯一键冲突和事务回滚都会导致自增主键id不连续的情况</强> <h3> <强>四、自增锁的优化</强> </h3> 自增id锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请 但在MySQL5.0版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放 MySQL5.1.22版本引入了一个新策略,新增参数innodb_autoinc_lock_mode,默认值是1 1。这个参数设置为0,表示采用之前MySQL5.0版本的策略,即语句执行结束后才释放锁 2。这个参数设置为1 <ul类= <李>

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

<李>

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

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

为了数据的一致性,默认设置为1
MySQL中自增主键的示例分析“> 如果sessionB申请了自增值以后马上就释放自增锁,那么就可能出现这样的情况: <ul类= <李>

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

<李>

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

<李>

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

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