MySQL 8.0修复了一个MySQL十年之久的自增列Bug，在2003年由Percona的CEO（当时应该还没Percona吧）提出的bug#199。先重现一下这个BUG。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
insert into t1 values (1,2);
insert into t1 values (null, 2);
insert into t1 values (null, 2);
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+----+------+
3 rows in set (0.00 sec)
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# 删除一些记录
mysql> delete from t1 where id=2 or id=3;
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
1 row in set (0.00 sec)
|
这里我们关闭mysqld，再启动mysqld，然后再插入一条数据。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into t1 values (null,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
| 2 | 2 |
+----+------+
2 rows in set (0.00 sec)
|
我们看到AUTO_INCREMENT为2，同时插入了（2,2），而如果我没有重启，插入同样数据我们得到的应该是（4,2)。 上面的测试反映了mysqld重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况。
这在很多场景下可能导致问题，包括但不限于：主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里，可以看到大量的同行抱怨。如，假设t1有个历史表t1_history用来存t1表的历史数据，那么mysqld重启前，ti_history表中可能已经有了（2,2）这条数据，而重启后我们又插入了（2，2），当新插入的(2,2)迁移到历史表时，会违反主键约束。这类问题是否在数据迁移中会出现呢，我们也需要注意一下。比如我们使用mysqldump导出数据，然后导入到另外一个环境。mysqldump导出数据里面是指定了自增长值的方式，而非空。
建表时可以指定AUTO_INCREMENT值，不指定时默认为1，这个值表示当前自增列的起始值大小，如果新插入的数据没有指定自增列的值，那么自增列的值即为这个起始值。对于InnoDB表，这个值没有持久到文件中。而是存在内存中（dict_table_struct.autoinc）。那么又问，既然这个值没有持久下来，为什么我们每次插入新的值后，show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1是直接从dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。
知道了AUTO_INCREMENT是实时存储内存中的。那么，mysqld重启后，从哪里得到AUTO_INCREMENT呢？内存值肯定是丢失了，实际上MySQL采用执行类似select max(id)+1 from t1;方法来得到AUTO_INCREMENT，而这种方法就是造成自增id重复的原因。
另外，当MySQL开启一个事务后，有类INSERT操作，自增值就会增加；但是当事务回滚后，自增至并不会减小。也就是说自增值会有空洞。
我们再来看一个自增长问题，如下测试：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
# 第一种
mysql> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,2),(2,2),(3,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into t1 values (4,2),(null,2),(null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show create table t1G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# 第二种
mysql> create table t1(id int auto_increment primary key,name varchar(255));
Query OK, 0 rows affected (0.12 sec)
mysql> create table t2(name varchar(255))engine=innodb;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t2 values('aa'),('bb');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t1(name) select *from t2;
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> show create table t1;
| Table | Create Table
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)
|
我们可以看到第一种带NULL值的写法，自增长值会多增加一个值；第二种insert .. select，自增长也会多增加一个值。这个会带来什么问题呢？你会发现从库自增长值却是正常的（当复制格式为ROW时），这个时候其实也就是主从数据不一致了，但影响不大，除非出现记录ID大于自增长ID，那样插入数据重复会报错。
究其原因，和insert语句的定位也有关系，目前有这几类insert语句。
1、simple insert，如insert into t(name) values(‘test’)
2、bulk insert，如load data | insert into … select …. from ….
3、mixed insert，如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);
这个和参数innodb_autoinc_lock_mode有很大的关系，默认参数值为1。innodb_autoinc_lock_mode这个参数控制着在向有auto_increment列的表插入数据时，相关锁的行为，有三个取值：
- 0：这个表示tradition（传统）
它提供了一个向后兼容的能力，在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放这把锁。注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。
它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值（它保证了基于语句复制的安全）。由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。
- 1：这个表示consecutive（连续）
这一模式下对simple insert做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以MySQL可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的（它保证了基于语句复制的安全）。由于现在MySQL已经推荐把二进制的格式设置成ROW格式，所以没有复制安全问题了。
这一模式也是MySQL的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁。
- 2：这个表示interleaved（交错）
由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。
<延伸>
MySQL “replace into” 的坑
MySQL自增列主从不一致的测试
MySQL自增列导致主键重复问题分析
MySQL · 8.0.0新特性 · 持久化自增列值