insert种类
在讲innodb_autoinc_lock_mode参数之前，我们先来了解下MySQL insert的种类
- simple insert：插入的记录行数是确定的。比如：insert into、replace
- bulk insert：插入的记录行数不能马上确认的。比如：insert…select、replace…select、load data
- mixed-mode insert：也是’simple insert’，但是语句插入中存在没指定自增值的行。比如：INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’)、INSERT … ON DUPLICATE KEY UPDATE
锁模式
在了解了以上insert种类后。我们来对innodb_autoinc_lock_mode参数设置不同值时的表现进行描述。
传统模式
innodb_autoinc_lock_mode=0
这种模式性能最差，它会对所有的insert模式操作都去获取一个特殊的table-level auto-inc。这种锁会持续到语句结束，以确保执行的insert语句以可预测且可重复的顺序分配自动递增值。这种模式更多是为了兼容老版本的MySQL运行。
举例：1
2
3
4
5
6
7
8
9CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
在上面的例子中，传统模式下，Tx1执行过程中会一直持有auto-inc锁，一直到Tx1执行完。所以Tx2必须等待锁释放。这会导致一个简单insert语句执行得非常慢。严重的影响了并发效率。
而在连续锁定模式下，Tx1执行前会预先计算需要插入的函数，在获取到auto-inc锁后立即释放。Tx2不会受到Tx1 auto-inc的影响。
连续锁定模式
innodb_autoinc_lock_mode=1
在MySQL8.0.3之前，innodb_autoinc_lock_mode参数的默认值是1。也就是连续锁定模式。
在这种模式下，普通insert语句，申请到自增锁后就马上释放。bulk insert这样的批量插入数据语句，自增锁还是要等语句结束后才被释放。
在这种模式下，可能会申请多一部分自增键。导致自增键空洞情况的发生。
例如，假设 c1 列是表 t1 的 AUTO_INCREMENT 列，且假设最新自动生成的序列号为 1001
2
3
4
5
6
7CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` char(1) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
在连续模式下执行插入操作后，innodb在处理时分配了4个自增值，但是只使用了两个。所以导致了103和104被丢失了。这就导致了自增键的空洞现象。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` char(1) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> select * from t1 order by c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 100 | b |
| 5 | c |
| 101 | d |
+-----+------+
4 rows in set (0.30 sec)
交叉锁定模式
innodb_autoinc_lock_mode=2
所有的申请自增锁操作都是申请后就立刻释放锁。在这个模式下，已经没有了AUTO-INC锁，所以这个模式下的性能是最好的。
这一模式下存在两个问题：
1、在这种模式下，如果binlog格式为statement，可能会引起数据不一致。
2、对于同一语句来说，它得到的auto_increment值可能不是连续的。
在SBR模式下，如果该值为2可能会引起主从数据不一致：
Master上的插入逻辑：
| 时间点 | session1 | session2 |
|---|---|---|
| 0 | 1,A | |
| 1 | 2,AA | |
| 2 | 3,B | |
| 3 | 4,C | |
| 4 | 5,CC | |
| 5 | 6,D |
Master结果记录：
| a | b |
|---|---|
| 1 | A |
| 2 | AA |
| 3 | B |
| 4 | C |
| 5 | CC |
| 6 | D |
由于session2先执行完，所以slave上的结果为：
| a | b |
|---|---|
| 1 | AA |
| 2 | CC |
| 3 | A |
| 4 | B |
| 5 | C |
| 6 | D |
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/innodb-auto-increment-handling.html
https://www.twle.cn/c/yufei/innodb/innodb-basic-auto_increment.html