InnoDB可以通过配置AUTO_INCREMENT锁机制极大的提升向具有AUTO_INCREMENT列的表插入数据SQL语句的执行性能。因为InnoDB表使用等价于SELECT MAX(ai_col)的语句并且基于索引来查询AUTO_INCREMENT列的最大值，所以为了使用AUTO_INCREMENT机制，定义的AUTO_INCREMENT列必须位于某个索引中。
本文介绍不同AUTO_INCREMENT锁模式产生auto-increment值的不同行为以及不同的锁模式时如何影响复制（replication）的。Auto-increment 锁模式通过配置参数innodb_autoinc_lock_mode在服务启动时进行配置。
下面的定义将用于介绍innodb_autoinc_lock_mode设置：
-
INSERT-like语句
所有向表中添加新行的语句包括INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT，以及LOAD DATA，包括下面要介绍的simple-inserts，bulk-inserts，以及mixed-mode插入语句等。
Simple inserts
那些插入到表中的行数据可以提前决定的语句（提前指的是当语句被初始处理时），包括单行或者多行INSERT和不包括嵌套查询的REPLACE语句等。但是不包括REPLACE ... ON DUPLICATE KEY UPDATE语句。
Bulk inserts
那些插入到表中的行数据不可提前预知的语句（因此需要多少个auto-increment值也不可知）。包括INSERT ... SELECT，REPLACE ... SELECT,以及LOAD DATA语句等，但是不包括简单的INSERT语句。InnoDB通过对每行数据依次赋值的方式处理AUTO_INCREMENT列。
Mixed-mode inserts
那些为插入表中的部分行数据指定auto-increment值（但不是全部）的simple inserts语句。如下面的例子，假设c1是表t1中的一个AUTO_INCREMENT列：
INSERT INTO t1(c1, c2)
VALUES(1,'a'),(NULL, 'b'), (5, 'c'), (NULL, 'd');
另一种形式的mixed-mode insert语句是INSERT ... ON DUPLICATE KEY UPDATE，此语句在最坏情况下等价于执行INSERT语句后接着执行UPDATE语句，所以产生的auto-increment值可能也可能不被update阶段使用。
innodb_autoinc_lock_mode有三个可以设置的值：0,1和2，分别对应传统(traditional),连续(consecutive)以及间断(interleaved)锁模式。在MySQL 8.0中间断锁模式(innodb_autoinc_lock_mode=2)是默认的设置，之前版本的默认设置为连续模式(innodb_autoinc_lock_mode=1)。
MySQL 8.0默认使用间断锁模式也反应了默认的复制类型(replication type)从基于语句复制变成了基于行复制。基于语句复制需要使用连续锁模式来保证auto-increment值在执行同样一系列语句时产生的值可预知，并且也可以在slave上重复产生出来，而基于行的赋值则对语句执行的顺序不敏感。
-
innodb_autoinc_lock_mode = 0（传统锁模式）
传统锁模式提供和在MySQl 5.1版本引入innodb_autoinc_lock_mode参数之前一致的行为。传统锁模式主要为了向前兼容，性能测试以及处理mixed-mode inserts中存在的问题。
在这种锁模式下，在处理所有具有AUTO_INCREMENT列的INSERT-like语句时，都会获得一种特殊的表级别的AUTO-INC锁，AUTO-INC通常一致持有到语句执行结束（注意并不是事务结束），依次来保证同一顺序的一些列的语句在执行时分配的auto-increment值是可预测以及可重复的，并且也保证了auto-increment值的连续性。
在基于语句复制时，这意味着当sql语句在salve上执行时，slave上可以产生和master上一致的auto-increment值。执行多条INSERT语句的结果是确定的，并且slave可以产生和master上同样的数据。如果多条INSERT语句产生的auto-increment值是间断的，那么两条同时执行的INSERT语句的执行将不是确定的，通过基于语句复制到slave上也就不能保证其可靠性。
例如：
CRETE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY(c1)
) ENGINE=InnoDB;
假设现在有两个事务同时在运行，每个使用都向具有AUTO_INCREMENT列的表中插入数据。其中一个语句使用INSERT ... SELECT形式的的语句插入1000行数据，而另一条语句使用简单的INSERT语句插入一行数据：
Tx1: INSERT INTO t1(c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1(c2) VALUES('XXX');
InnoDB预先不能提前计算出Tx1中SELECT语句查询出的需要插入的行的数量，其通过每次为一行数据产生auto-increment值的方式处理此语句。在获取表级锁之后，并且一直持有到该语句结束，在同一时间，只有一个语句可以执行（即Tx1和Tx2不会同时执行），不同语句（指Tx1和Tx2）产生的auto-increment值不是间断的。Tx1语句产生的所有auto-increment值是连续的，而Tx2语句产生的auto-increment值要么大于，要么小于所有Tx1产生的auto-increment值，取决于Tx1和Tx2谁先执行。
在使用基于语句的二进制日志进行赋值（或者故障恢复时）时，只要SQL语句采用同样的顺序在slave上重放，那么就能保证产生的auto-increment值一致。因此，表级锁的采用并且一直持有该锁到语句执行结尾，使得基于语句复制时，在具有AUTO_INCREMENT列的表上执行INSERT语句是安全的，然而表级锁的使用影响了并发性以及可扩展性，降低了多个事务语句同时执行的性能。
在上面的例子中，如果不使用表级锁，Tx2中INSERT语句使用的auto-increment值则取决于该语句执行的时间。如果Tx2中INSERT语句在Tx1的INSERT语句执行过程中执行（即不是其开始之前，也不是其结束之后），那么Tx1和Tx2语句产生的auto-increment值将是不确定的，每次执行都可能会产生不同的auto-increment值。
在连续锁模式下，在插入到表中行数据可预知的simple insert语句执行时，InnoDB可以避免使用表级的AUTO-INC锁，并且也能保证基于语句复制的确定性。
如果你不准备使用二进制日志去做复制或者恢复工作，那么使用间断锁模式可以避免所有情况下的表级AUTO-INC锁的使用，这样可以获得最大的并发性以及最后的性能，代价就是允许auto-increment值的不连续性，以及并发同时执行的语句产生的auto-increment存在间断性。
-
innodb_autoinc_lock_mode = 1（连续锁模式）
在这种锁模式下，bulk inserts则会使用表级锁AUTO-INC并且一直持有到语句执行结束，这包括所有的INSERT ... SELECT，REPLACE ... SELECT，LOAD DATA等语句。同一时间只有持有AUTO-INC锁的语句可以执行，如果上述批量插入语句的数据源表和目的表不同，在数据源表上成功获取到共享锁之后才会到目的表获取AUTO-INC锁。如果数据源表和目的表相同，则对所有查询的数据行获取可共享锁之后才会获取该表的AUTO-INC锁。
Simple inserts(插入到表中的数据行数目可以提前知道)的语句，可以避免使用表级的AUTO-INC锁，通过使用轻量级的锁——互斥变量(mutex)来获取指定数量的auto-increment值即可，互斥变量仅仅在分配指定数量的auto-increment值时被持有，并不是持有到语句结束。除非AUTO-INC当前被其他的事务持有，其他情况下将不会有AUTO-INC锁的参与，如果其他事务当前持有了AUTO-INC锁，那么simple inserts语句就会和bulk insert语句一样，也会等待AUTO-INC锁。
这种锁模式保证了，如果INSERT语句向表中插入的数据行在事先不可预知（此时auto-increment值的数量也不可能预知）的情况下分配的所有auto-increment值都是连续的，且在基于语句复制的场景下也是安全的。
实现机制很简单，但是这种锁模式很大程度上提高了可扩展性，并且同时保证了复制的安全性。同样地，和传统锁模式一样，对于给定的一系列sql语句，按同样的顺序执行时，其产生的auto-increment值都是连续的。除了一种特殊情况外，其他的情景下，连续锁模式和传统锁模式的行为时完全相同的。
这种特殊情况就是mixed-mode inserts，在这种语句中，用户为插入到表中的某系数据提供了AUTO-INCREMENT值，但是并不是全部。此时，InnoDB会分配多于插入到表中行的个数的auto-increment值。然而语句执行过程中自动产生的所有auto-increment都是连续的，因此产生的这些auto-increment值也会大于所有最近执行的语句所产生的auto-increment值，多余的auto-increment值将会被丢弃不再使用。
-
innodb_autoinc_lock_mode = 2（间断锁模式）
在这种锁模式下，所有的INSERT-like语句都不会使用表级的AUTO-INC锁，多个语句可以同时执行。这也是最快以及最具有扩展性的锁模式，但是在基于语句的复制和恢复场景下，此种锁模式也是不安全的。
在这种锁模式下，可以保证auto-increment值是唯一的并且在所有并发执行的INSERT-like语句中时单调递增的。因为多个语句可以同时产生auto-increment值（也就是在一个语句执行时，分配的auto-increment值是间断的）,因此一个语句插入到表中的多行数据使用的auto-increment值可能并不是连续的。
除了mixed-mode inserts语句中的simple inserts语句，非mixed-mode inserts语句中的simple inserts语句，因为其插入的行数据可以提前预知，因此分配的auto-increment值都是连续的，没有间隙。但是当bulk inserts语句执行时，分配的auto-increment值可能会存在间隙。
MySQL 官方手册还介绍了设置锁模式的一些启示InnoDB AUTO_INCREMENT Lock Mode Usage Implications，也十分具有参考意义，感兴趣的读者可以点击链接进行阅读。
网友评论