MySQL中自增主键的示例分析

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