自增主键不连续的几种情况
//自增主键不连续的几种情况//
最近在极客时间上学习丁奇大佬的《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 t\G*************************** 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 t\G
*************************** 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.这样做，既能提升并发性，又不会出现数据一致性问题.