MySQL——11、自增主键

1.1 自增主键

1.1.1 自增值保存在哪儿？

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

\1) MyISAM引擎的自增值保存在数据文件中，索引是hash表结构

\2) InnoDB引擎的自增值：

​ i. 在MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。

每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+步长，作为这个表当前的自增值，sql如下：

select max(ai_col) from table_name for update;

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

1.1.2 自增值修改机制

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

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

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

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

i. 如果X<Y，那么这个表的自增值不变

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

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

1.1.3 自增值的修改时机：mysql自增主键是在哪一步生成的(众安)

创建一个表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。出现了自增主键不连续的情况

1.1.4 自增主键不连续的情况

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

1.1.5 自增锁的优化

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

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

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

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

b) 这个参数设置为1：

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

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

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

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