InnoDB自增原理都搞不清楚，還怎么CRUD？

以下文章來源于苦味代碼，作者L

雖然我們習慣于給主鍵ID指定AUTO_INCREMENT屬性，但是AUTO_INCREMENT也是可以指定到非主鍵字段的，唯一的約束就是這個字段上面得加索引，有了索引，就可以通過類似SELECT MAX(*ai_col*)的語句快速讀到這列數據的最大值。

本文要探討的話題是MySQL的InnoDB引擎處理自增數據列的原理

MySql 5.1之前的實現

在這個版本之前，用AUTO_INCREMENT修飾的數據列確實是嚴格連續自增的。MySql的實現是會針對每個插入語句加一個全表維度的鎖，這個鎖可以保證每次只有一條插入語句在執行，每插入一行數據，就會生成一個自增數據。

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=100;

假如我們在數據庫中新建上面的這張表，接著我們執行插入語句。

mysql> INSERT INTO t1 (c1,c2) VALUES (NULL,'a'), (NULL,'b'), (NULL,'c'), (NULL,'d');

針對這條MySql執行的流程為：

全表加 AUTO-INC鎖

1.1 生成主鍵ID：101

1.2 將行(101, 'a')插入表中

1.3 生成主鍵ID: 102

1.4 將行(102, 'b')插入表中

...

釋放 AUTO-INC鎖

MySql5.1之前的這種實現方式可以保證AUTO_INCREMENT嚴格自增，但是并發程度也最差，因為AUTO_INCREMENT鎖是全表加鎖直到這條語句結束

MySql 5.1版本帶來的優化

前文中的insert語句是比較簡單的，所謂簡單的insert語句指的是插入的的數據行數是可以提前確定的，與之相對的是Bulk insert比如INSERT ... SELECT這類語句，這類插入語句的插入行數不能提前確定。

在這個版本以及之后，對于簡單語句的插入，不再加全表的AUTO-INC鎖，只會在產生自增列數據的時候加一個輕量級的互斥鎖，等自增數據分配好，鎖就釋放了，因此像上面的例子，在MySql5.1之后的執行流程如下

加輕量級互斥鎖

1.1 分配自增數據

釋放鎖

將行(101, 'a')插入表中

將行(102, 'b')插入表中

...

可以看到，對于簡單的插入語句，并發情況下的臨界區變小了，且不再持有全表的鎖，提升了并發性能。當然，如果在嘗試加鎖的過程中遇到有其他事務持有全表的AUTO-INC鎖，還是要等待全表的AUTO-INC鎖釋放再執行本次插入操作

對于Bulk insert的插入語句，仍然避免不了全局的AUTO-INC鎖，這類語句，他們的執行流程仍然保持和5.1之前版本一致，比如以下表為例

CREATE 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時，InnoDB無法知道要插入的具體行數，因此會獲取一個全表的鎖，每執行一條插入語句就會給自增列賦新的值。因為有全表的鎖，所以Tx1這條語句插入的所有行數都是連續自增的，Tx2自增列的值要么小于Tx1自增列的最小值，要么大于Tx1自增列中的最大值，這取決于這兩條語句的執行順序

InnoDB采取這樣的決策一個重要的原因是主從復制，在MySql8.0之前，MySql的主從是基于語句復制的。在剛才的例子中，如果Tx1執行的時候沒有全表的鎖，那有可能在Tx1執行的過程中Tx2也在執行，這就會導致Tx1和Tx2自增列的數據每次執行結果都不相同，也就無法在從庫中通過語句回放復制。

MySql 8.0版本之后的優化

雖然MySql5.1版本對簡單的插入語句做了優化，避免了全表加鎖，但對于INSERT ... SELECT這樣的復雜插入語句，仍然避免不了全表的AUTO-INC鎖，主要是基于執行語句的主從復制要能在從庫完全回放復制主庫，所有的語句執行結果就不能和執行順序有關。

在MySql 8.0以及之后默認的主從復制策略變成了基于數據行實現，在這樣的背景下INSERT ... SELECT這樣的復雜插入語句也不需要全表加鎖來生成自增列數據了，所有的插入語句只有在生成自增列數據的時候要求持有一個輕量級的互斥鎖，等到自增數據生成好之后釋放鎖。在這種實現下，所有插入語句的自增列都不能保證連續自增，但是并發性能確實最好的。

總結

需要說明的是，如果插入語句所處的事務回滾了，生成的自增列數據是不會回滾的，這種情況下會造成自增列數據非連續增長。

以上所述都是各個MySql版本的默認實現，MySql 5.1引入了一個新的參數 innodb_autoinc_lock_mode 通過修改這個字段的值，可以改變InnoDB生成自增列的策略，其值總結如下：

不推薦顯式指定自增列數據，因為在5.7以及之前的版本，如果通過update語句顯式指定一個比SELECT MAX(*ai_col*)還大的自增列值，后續insert語句可能會拋"Duplicate entry"錯誤，這一點在8.0版本之后也有了改變，如果通過顯式的update語句顯式指定一個比SELECT MAX(*ai_col*)還大的自增列值，那該值就會被持久化，后續的自增列值都從該值開始生成。

假如有下面這張表

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB AUTO_INCREMENT=100;

試想，在我們執行完下面這條語句之后表的內容變成了什么？

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

MySql 5.1之前，或者innodb_autoinc_lock_mode設置為0

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

在這種模式下，每插入一行數據就會生成一個自增值賦到c1這一行，因此c1的下一個自增值是103

MySql 8.0之前，或者innodb_autoinc_lock_mode設置為1

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

當前表的數據與前一個場景一致，但是下一個自增值卻是105，因為在這個場景下，自增數據是在插入語句執行的最開始一次性生成的

MySql 8.0之后，或者innodb_autoinc_lock_mode設置為2

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

在這種場景下，因為同時可能有其他的插入語句執行，因此x和y的值是不確定的，下一個自增值也是未知的。