Mysql关于自增主键,自增主键优化总结

Mysql自增主键

自增主键如何创建

CREATE TABLE `blog`.`Idv_Inf_Tbl` (
	`Idv_Inf_No` INT(11) NOT NULL AUTO_INCREMENT,
	`Acct_No` VARCHAR(45) NOT NULL,
	`nickname`VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Idv_Inf_No`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

其中在建表时,在字段后面加上AUTO_INCREMENT,该字段即为自增字段。

如何使用自增主键

以上面创建的表格为例

Idv_Inf_NoAcct_Nonickname

即Idv_Inf_Tbl为空表,Idv_Inf_No为自增主键
现在,我们往里面添加数据

#第一种方式
insert into Idv_Inf_Tbl(`Idv_Inf_No`,`Acct_No`,`nickname`)
		values(1,"ddd","bbb");
#第二种方式
insert into Idv_Inf_Tbl(`Acct_No`,`nickname`)
		values("ddd","bbb");		

这两种方式的作用

Idv_Inf_NoAcct_Nonickname
1dddbbb

都会增加一条数据,并且自增主键默认从1开始

现在,我们再次添加一条数据

#那现在使用
insert into Idv_Inf_Tbl(`Acct_No`,`nickname`)
		values("aaa","ccc");		
Idv_Inf_NoAcct_Nonickname
1dddbbb
2aaaccc

执行流程

自增主键的执行流程:

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

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

3.将传入的行的值改成(2,“aaa”,“bbb”)

4.将表的自增值改成3

自增锁的优化

自增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个字节,上限就是 在这里插入图片描述
)为例,通过下面这个语句序列验证一下:

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

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页