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

雖然我們習慣於給主鍵 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執行的流程爲：

  1. 全表加 AUTO-INC鎖

    1.1 生成主鍵 ID：101

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

    1.3 生成主鍵 ID: 102

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

    ...

  2. 釋放 AUTO-INC鎖

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

MySql 5.1 版本帶來的優化

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

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

  1. 加輕量級互斥鎖

    1.1 分配自增數據

  2. 釋放鎖

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

  4. 將行 (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的值是不確定的，下一個自增值也是未知的。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/YpPbGzpfFYBDX1ScWAqjuw

猜你喜歡