InnoDB可以通過配置AUTO_INCREMENT鎖機(jī)制極大的提升向具有AUTO_INCREMENT列的表插入數(shù)據(jù)SQL語句的執(zhí)行性能。因?yàn)镮nnoDB表使用等價(jià)于SELECT MAX(ai_col)的語句并且基于索引來查詢AUTO_INCREMENT列的最大值，所以為了使用AUTO_INCREMENT機(jī)制，定義的AUTO_INCREMENT列必須位于某個(gè)索引中。
本文介紹不同AUTO_INCREMENT鎖模式產(chǎn)生auto-increment值的不同行為以及不同的鎖模式是如何影響復(fù)制（replication）的。Auto-increment 鎖模式通過配置參數(shù)innodb_autoinc_lock_mode在服務(wù)啟動(dòng)時(shí)進(jìn)行配置。
下面的定義將用于介紹innodb_autoinc_lock_mode設(shè)置：
-
INSERT-like語句
所有向表中添加新行的語句包括INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT，以及LOAD DATA，包括下面要介紹的simple-inserts，bulk-inserts，以及mixed-mode插入語句等。
Simple inserts
那些插入到表中的行數(shù)據(jù)可以提前決定的語句（提前指的是當(dāng)語句被初始處理時(shí)），包括單行或者多行INSERT和不包括嵌套查詢的REPLACE語句等。但是不包括REPLACE ... ON DUPLICATE KEY UPDATE語句。
Bulk inserts
那些插入到表中的行數(shù)據(jù)不可提前預(yù)知的語句（因此需要多少個(gè)auto-increment值也不可知）。包括INSERT ... SELECT，REPLACE ... SELECT,以及LOAD DATA語句等，但是不包括簡單的INSERT語句。InnoDB通過對(duì)每行數(shù)據(jù)依次賦值的方式處理AUTO_INCREMENT列。
Mixed-mode inserts
那些為插入表中的部分行數(shù)據(jù)指定auto-increment值（但不是全部）的simple inserts語句。如下面的例子，假設(shè)c1是表t1中的一個(gè)AUTO_INCREMENT列：
INSERT INTO t1(c1, c2)
VALUES(1,'a'),(NULL, 'b'), (5, 'c'), (NULL, 'd');
另一種形式的mixed-mode insert語句是INSERT ... ON DUPLICATE KEY UPDATE，此語句在最壞情況下等價(jià)于執(zhí)行INSERT語句后接著執(zhí)行UPDATE語句，所以產(chǎn)生的auto-increment值可能也可能不被update階段使用。
innodb_autoinc_lock_mode有三個(gè)可以設(shè)置的值：0,1和2，分別對(duì)應(yīng)傳統(tǒng)(traditional),連續(xù)(consecutive)以及間斷(interleaved)鎖模式。在MySQL 8.0中間斷鎖模式(innodb_autoinc_lock_mode=2)是默認(rèn)的設(shè)置，之前版本的默認(rèn)設(shè)置為連續(xù)模式(innodb_autoinc_lock_mode=1)。
MySQL 8.0默認(rèn)使用間斷鎖模式也反應(yīng)了默認(rèn)的復(fù)制類型(replication type)從基于語句復(fù)制變成了基于行復(fù)制?；谡Z句復(fù)制需要使用連續(xù)鎖模式來保證auto-increment值在執(zhí)行同樣一系列語句時(shí)產(chǎn)生的值可預(yù)知，并且也可以在slave上重復(fù)產(chǎn)生出來，而基于行的賦值則對(duì)語句執(zhí)行的順序不敏感。
-
innodb_autoinc_lock_mode = 0（傳統(tǒng)鎖模式）
傳統(tǒng)鎖模式提供和在MySQl 5.1版本引入innodb_autoinc_lock_mode參數(shù)之前一致的行為。傳統(tǒng)鎖模式主要為了向前兼容，性能測試以及處理mixed-mode inserts中存在的問題。
在這種鎖模式下，在處理所有具有AUTO_INCREMENT列的INSERT-like語句時(shí)，都會(huì)獲得一種特殊的表級(jí)別的AUTO-INC鎖，AUTO-INC通常一致持有到語句執(zhí)行結(jié)束（注意并不是事務(wù)結(jié)束），依次來保證同一順序的一些列的語句在執(zhí)行時(shí)分配的auto-increment值是可預(yù)測以及可重復(fù)的，并且也保證了auto-increment值的連續(xù)性。
在基于語句復(fù)制時(shí)，這意味著當(dāng)sql語句在salve上執(zhí)行時(shí)，slave上可以產(chǎn)生和master上一致的auto-increment值。執(zhí)行多條INSERT語句的結(jié)果是確定的，并且slave可以產(chǎn)生和master上同樣的數(shù)據(jù)。如果多條INSERT語句產(chǎn)生的auto-increment值是間斷的，那么兩條同時(shí)執(zhí)行的INSERT語句的執(zhí)行將不是確定的，通過基于語句復(fù)制到slave上也就不能保證其可靠性。
例如：
CRETE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY(c1)
) ENGINE=InnoDB;
假設(shè)現(xiàn)在有兩個(gè)事務(wù)同時(shí)在運(yùn)行，每個(gè)使用都向具有AUTO_INCREMENT列的表中插入數(shù)據(jù)。其中一個(gè)語句使用INSERT ... SELECT形式的的語句插入1000行數(shù)據(jù)，而另一條語句使用簡單的INSERT語句插入一行數(shù)據(jù)：
Tx1: INSERT INTO t1(c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1(c2) VALUES('XXX');
InnoDB預(yù)先不能提前計(jì)算出Tx1中SELECT語句查詢出的需要插入的行的數(shù)量，其通過每次為一行數(shù)據(jù)產(chǎn)生auto-increment值的方式處理此語句。在獲取表級(jí)鎖之后，并且一直持有到該語句結(jié)束，在同一時(shí)間，只有一個(gè)語句可以執(zhí)行（即Tx1和Tx2不會(huì)同時(shí)執(zhí)行），不同語句（指Tx1和Tx2）產(chǎn)生的auto-increment值不是間斷的。Tx1語句產(chǎn)生的所有auto-increment值是連續(xù)的，而Tx2語句產(chǎn)生的auto-increment值要么大于，要么小于所有Tx1產(chǎn)生的auto-increment值，取決于Tx1和Tx2誰先執(zhí)行。
在使用基于語句的二進(jìn)制日志進(jìn)行賦值（或者故障恢復(fù)時(shí)）時(shí)，只要SQL語句采用同樣的順序在slave上重放，那么就能保證產(chǎn)生的auto-increment值一致。因此，表級(jí)鎖的采用并且一直持有該鎖到語句執(zhí)行結(jié)尾，使得基于語句復(fù)制時(shí)，在具有AUTO_INCREMENT列的表上執(zhí)行INSERT語句是安全的，然而表級(jí)鎖的使用影響了并發(fā)性以及可擴(kuò)展性，降低了多個(gè)事務(wù)語句同時(shí)執(zhí)行的性能。
在上面的例子中，如果不使用表級(jí)鎖，Tx2中INSERT語句使用的auto-increment值則取決于該語句執(zhí)行的時(shí)間。如果Tx2中INSERT語句在Tx1的INSERT語句執(zhí)行過程中執(zhí)行（即不是其開始之前，也不是其結(jié)束之后），那么Tx1和Tx2語句產(chǎn)生的auto-increment值將是不確定的，每次執(zhí)行都可能會(huì)產(chǎn)生不同的auto-increment值。
在連續(xù)鎖模式下，在插入到表中行數(shù)據(jù)可預(yù)知的simple insert語句執(zhí)行時(shí)，InnoDB可以避免使用表級(jí)的AUTO-INC鎖，并且也能保證基于語句復(fù)制的確定性。
如果你不準(zhǔn)備使用二進(jìn)制日志去做復(fù)制或者恢復(fù)工作，那么使用間斷鎖模式可以避免所有情況下的表級(jí)AUTO-INC鎖的使用，這樣可以獲得最大的并發(fā)性以及最后的性能，代價(jià)就是允許auto-increment值的不連續(xù)性，以及并發(fā)同時(shí)執(zhí)行的語句產(chǎn)生的auto-increment存在間斷性。
-
innodb_autoinc_lock_mode = 1（連續(xù)鎖模式）
在這種鎖模式下，bulk inserts則會(huì)使用表級(jí)鎖AUTO-INC并且一直持有到語句執(zhí)行結(jié)束，這包括所有的INSERT ... SELECT，REPLACE ... SELECT，LOAD DATA等語句。同一時(shí)間只有持有AUTO-INC鎖的語句可以執(zhí)行，如果上述批量插入語句的數(shù)據(jù)源表和目的表不同，在數(shù)據(jù)源表上成功獲取到共享鎖之后才會(huì)到目的表獲取AUTO-INC鎖。如果數(shù)據(jù)源表和目的表相同，則對(duì)所有查詢的數(shù)據(jù)行獲取可共享鎖之后才會(huì)獲取該表的AUTO-INC鎖。
Simple inserts(插入到表中的數(shù)據(jù)行數(shù)目可以提前知道)的語句，可以避免使用表級(jí)的AUTO-INC鎖，通過使用輕量級(jí)的鎖——互斥變量(mutex)來獲取指定數(shù)量的auto-increment值即可，互斥變量僅僅在分配指定數(shù)量的auto-increment值時(shí)被持有，并不是持有到語句結(jié)束。除非AUTO-INC當(dāng)前被其他的事務(wù)持有，其他情況下將不會(huì)有AUTO-INC鎖的參與，如果其他事務(wù)當(dāng)前持有了AUTO-INC鎖，那么simple inserts語句就會(huì)和bulk insert語句一樣，也會(huì)等待AUTO-INC鎖。
這種鎖模式保證了，如果INSERT語句向表中插入的數(shù)據(jù)行在事先不可預(yù)知（此時(shí)auto-increment值的數(shù)量也不可能預(yù)知）的情況下分配的所有auto-increment值都是連續(xù)的，且在基于語句復(fù)制的場景下也是安全的。
實(shí)現(xiàn)機(jī)制很簡單，但是這種鎖模式很大程度上提高了可擴(kuò)展性，并且同時(shí)保證了復(fù)制的安全性。同樣地，和傳統(tǒng)鎖模式一樣，對(duì)于給定的一系列sql語句，按同樣的順序執(zhí)行時(shí)，其產(chǎn)生的auto-increment值都是連續(xù)的。除了一種特殊情況外，其他的情景下，連續(xù)鎖模式和傳統(tǒng)鎖模式的行為時(shí)完全相同的。
這種特殊情況就是mixed-mode inserts，在這種語句中，用戶為插入到表中的某系數(shù)據(jù)提供了AUTO-INCREMENT值，但是并不是全部。此時(shí)，InnoDB會(huì)分配多于插入到表中行的個(gè)數(shù)的auto-increment值。然而語句執(zhí)行過程中自動(dòng)產(chǎn)生的所有auto-increment都是連續(xù)的，因此產(chǎn)生的這些auto-increment值也會(huì)大于所有最近執(zhí)行的語句所產(chǎn)生的auto-increment值，多余的auto-increment值將會(huì)被丟棄不再使用。
-
innodb_autoinc_lock_mode = 2（間斷鎖模式）
在這種鎖模式下，所有的INSERT-like語句都不會(huì)使用表級(jí)的AUTO-INC鎖，多個(gè)語句可以同時(shí)執(zhí)行。這也是最快以及最具有擴(kuò)展性的鎖模式，但是在基于語句的復(fù)制和恢復(fù)場景下，此種鎖模式也是不安全的。
在這種鎖模式下，可以保證auto-increment值是唯一的并且在所有并發(fā)執(zhí)行的INSERT-like語句中時(shí)單調(diào)遞增的。因?yàn)槎鄠€(gè)語句可以同時(shí)產(chǎn)生auto-increment值（也就是在一個(gè)語句執(zhí)行時(shí)，分配的auto-increment值是間斷的）,因此一個(gè)語句插入到表中的多行數(shù)據(jù)使用的auto-increment值可能并不是連續(xù)的。
除了mixed-mode inserts語句中的simple inserts語句，非mixed-mode inserts語句中的simple inserts語句，因?yàn)槠洳迦氲男袛?shù)據(jù)可以提前預(yù)知，因此分配的auto-increment值都是連續(xù)的，沒有間隙。但是當(dāng)bulk inserts語句執(zhí)行時(shí)，分配的auto-increment值可能會(huì)存在間隙。
MySQL 官方手冊還介紹了設(shè)置鎖模式的一些啟示InnoDB AUTO_INCREMENT Lock Mode Usage Implications，也十分具有參考意義，感興趣的讀者可以點(diǎn)擊鏈接進(jìn)行閱讀。
