自增主键不连续的几种情况---AsiaYe

//自增主键不连续的几种情况//

最近在极客时间上学习丁奇大佬的《MySQL 45讲》，这里结合自己的理解分享出来，喜欢的同学可以购买原版课程进行学习，里面的内容很丰富。

00

自增主键介绍

MySQL中的自增主键想必大家都不陌生，它是通过两个参数来控制的，分别是auto_increment_offset和auto_increment_increment，其中offset代表的是自增主键的开始值，而increment代表的是自增主键每次的增长值。常见的设置，是将这两个参数都设置为1。可以使用show create table语法来查看当前表的自增值。

自增值得生成规则如下：

如果即将插入的值>=当前自增值，那么新的自增值等于当前即将插入的值+1，否则自增值不变。

有的时候，我们会发现自增值

01

唯一键冲突导致的自增键修改

当我们给表里面插入数据的时候，如果没有指定主键的值，自增长的属性会自动为我们定义主键的值，如下示例:

mysql> 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; Query OK, 0 rows affected (0.01 sec) #创建一个表t，其中主键是id，还有唯一键c#先插入一条数据，此时表中的数据是1,1,1。mysql> insert into t values(null, 1, 1); Query OK, 1 row affected (0.01 sec) #再插入数据，发现唯一键冲突，自增值已经发生了改变mysql> insert into t values(null, 1, 1); ERROR 1062 (23000): Duplicate entry '1' for key 'c' #此时表的自增值已经变为3 mysql> show create table tG*************************** 1. row *************************** Table: t Create Table: 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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) #此时再重新插入null,2,2这条记录，发现结果跟我们想象的不一样 mysql> insert into t values(null, 2,2); Query OK, 1 row affected (0.00 sec) mysql> select *from t; +----+------+------+ | id | c | d | +----+------+------+ | 1 | 1 | 1 | | 3 | 2 | 2 | +----+------+------+ 2 rows in set (0.00 sec)

上述案例中，虽然第二次插入(null,1,1)失败，但是其实已经浪费了一个主键值了。再次插入(null,2,2)结果就不是我们想象的(2,2,2)了，而是(3,2,2)。

02

事务回滚导致的自增键不连续

当我们使用回滚事务的时候，如果该事务内部使用了自增值，那么同样会导致表自增主键出问题，示例如下：

mysql> insert into t values (null,1,1); Query OK, 1 row affected (0.01 sec) mysql> show create table tG *************************** 1. row *************************** Table: t Create Table: 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values (null,2,2); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> insert into t values (null,2,2); Query OK, 1 row affected (0.00 sec) mysql> select * from t; +----+------+------+ | id | c | d | +----+------+------+ | 1 | 1 | 1 | | 3 | 2 | 2 | +----+------+------+ 2 rows in set (0.00 sec)

那么为什么MySQL之所以在rollback模式下，不回滚自增主键呢？

其实这个问题的本质是为了避免其他并发事务报主键冲突的错误。假设会话A申请到的自增值是2，会话A提交前，会话B申请了自增值3，并提交了事务。如果会话A可以将自增属性回滚，那么其他事务就会接着从2开始申请自增值，而主键等于3的记录已经存在，那么必然会报错。

如果要解决这个问题，就只能在每次申请自增值之前，查询表里面的数据，然后分配自增值，这样会严重影响MySQL的性能。

03

MySQL自增锁优化带来的不连续

在MySQL5.7中，参数innodb_autoinc_lock_mode被用来控制自增锁的模式，该参数可以设置为三个值：0、1、2.

a、当该值为0的时候，是等insert语句执行完成之后才释放自增锁；

b、当该值为1的时候，普通的insert语句，自增锁在申请之后马上释放，insert into select语句，自增锁还是要等语句结束之后才释放

c、该值为2的时候，所有的申请自增主键的动作都是申请完成之后就释放锁

之所以对insert into select语句单独处理，是因为这种语句"预先不知道要申请多少个id"，如果我们要select的表有10w行记录，那么要做10w次的申请自增id的动作。MySQL认为这是欠妥当的，因此，对这种批量insert语句，包括load data等，它在内部做了一个自增值生成策略的优化：

1、批量执行的insert语句，第一次申请1个自增id

2、一个id用完了，第二次申请2个自增id

3、2个id用完了，第三次申请4个自增id

例如，我们看下面这个例子：

mysql> truncate table t; Query OK, 0 rows affected (0.01 sec) mysql> insert into t values(null, 1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into t values(null, 2,2); Query OK, 1 row affected (0.00 sec) mysql> insert into t values(null, 3,3); Query OK, 1 row affected (0.00 sec) mysql> insert into t values(null, 4,4); Query OK, 1 row affected (0.00 sec) mysql> create table t2 like t; Query OK, 0 rows affected (0.01 sec) mysql> insert into t2(c,d) select c,d from t; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t2 values(null, 5,5); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +----+------+------+ | id | c | d | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 4 | 4 | | 8 | 5 | 5 | +----+------+------+ 5 rows in set (0.00 sec)

这种模式下，即使我们认为自己插入了正确的自增值，但是结果还是出人意料。实际上，因为t2中要插入4条语句，所以自增id的分配策略是(1),(2,3),(4,5,6,7)，因为表t中只有4条记录，所以自增id 5、6、7就被浪费掉了，最后t2中的自增值就从8开始了。

为了避免自增id不连续而造成的主从数据不一致，线上环境，建议设置成innodb_autoinc_lock_mode=2 ，并且 binlog_format=row.这样做，既能提升并发性，又不会出现数据一致性问题.

---来自腾讯云社区的---AsiaYe