MySQL auto_increment間隙問題，mysqlautoincrement

MySQL auto_increment間隙問題，mysqlautoincrement

本文簡書連結（http://www.jianshu.com/p/cca59b515e20）

MySQL中auto_increment欄位估計大家都經常用到，特別是innodb引擎。我也經常用，只知道mysql可以保證這個欄位在多進程操作時的原子性，具體原理不甚了了，一次心血來潮，遂去查閱了MySQL手冊以及相關資料，瞭解了個大概。本文只探究了mysql5.5中innodb引擎auto_increment的問題，myisam引擎未測試，後續如果有時間我會補上。

1.傳統auto_increment原理

傳統的auto_increment實現機制：mysql innodb引擎的表中的auto_increment欄位是通過在記憶體中維護一個auto-increment計數器，來實現該欄位的賦值，注意自增欄位必須是索引,而且是索引的第一列,不一定要是主鍵。例如我現在在我的資料庫test中建立一個表t，語句如下:

CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;

則欄位a為auto_increment類型，在mysql伺服器啟動後，第一次插入資料到表t時，InnoDB引擎會執行等價於下面的語句:

SELECT MAX(a) FROM t FOR UPDATE;

Innodb擷取到當前表中a欄位的最大值並將增加1(預設是增加1，如果要調整為增加其他數目，可以設定auto_increment_increment這個配置的設定)然後賦值給該列以及記憶體中該表對應的計數器。

如果表t為空白，則InnoDB用來設定的值為為1.當然這個預設值夜可以通過 auto_increment_offset這個配置項來修改。

auto-increment計數器初始化以後，如果插入資料沒有指定auto_increment列的值，則Innodb直接增加auto-increment計數器的值並將增加後的值賦給新的列。如果插入資料指定了auto_increment列的值且這個值大於該表當前計數器的值，則該表計數器的值會被設定為該值。

插入資料時如果指定auto_increment列的值為NULL或者0,則和你沒有指定這個列的值一樣,mysql會從計數器中分配一個值給該列.而如果指定auto_increment列的值為負數或者超過該列所能儲存的最大數值,則該行為在mysql中沒有定義,可能會出現問題.根據我的測試來看,插入負值會有警告,不過最終儲存的資料還是正確的.如果是超過了比如上面定義的表t的bigint類型的最大值,同樣會有警告,而且插入的數值是bigint類型所能儲存的最大值18446744073709551615.

在傳統的auto_increment設定中,每次訪問auto-increment計數器的時候, INNODB都會加上一個名為AUTO-INC鎖直到該語句結束(注意鎖只持有到語句結束,不是事務結束).AUTO-INC鎖是一個特殊的表層級的鎖,用來提升包含auto_increment列的並發插入效能.因此,兩個事務不能同時擷取同一個表上面的AUTO-INC鎖,如果持有AUTO-INC鎖太長時間可能會影響到資料庫效能(比如INSERT INTO t1… SELECT … FROM t2這類語句).

2.改進的auto_increment

鑒於傳統auto_increment機制要加AUTO-INC這種特殊的表級鎖,效能還是太差,於是在mysql5.1開始,新增加了一個配置項innodb_autoinc_lock_mode來設定auto_increment方式.可以設定的值為0,1,2.其中0就是第一節中描述的傳統auto_increment機制,而1和2則是新增加的模式,預設該值為1,可以中mysql設定檔中修改該值.這裡主要來看看這兩種新的方式的差別，在描述差別前需要先明確幾個插入類型：

• 1）simple inserts

  simple inserts指的是那種能夠事先確定插入行數的語句，比如INSERT/REPLACE INTO 等插入單行或者多行的語句，語句中不包括嵌套子查詢。此外，INSERT INTO … ON DUPLICATE KEY UPDATE這類語句也要除外。

• 2）bulk inserts

  bulk inserts指的是事先無法確定插入行數的語句，比如INSERT/REPLACE INTO … SELECT, LOAD DATA等。

• 3）mixed-mode inserts

  指的是simple inserts類型中有些行指定了auto_increment列的值有些沒有指定，比如：
  INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);
  另外一種mixed-mode inserts是 INSERT … ON DUPLICATE KEY UPDATE這種語句，可能導致分配的auto_increment值沒有被使用。

下面看看設定innodb_autoinc_lock_mode為不同值時的情況：

• innodb_autoinc_lock_mode=0（traditional lock mode）

  傳統的auto_increment機制，詳見1.這種模式下所有針對auto_increment列的插入操作都會加AUTO-INC鎖，分配的值也是一個個分配，是連續的，正常情況下也不會有間隙（當然如果事務rollback了這個auto_increment值就會浪費掉，從而造成間隙）。

• innodb_autoinc_lock_mode=1（consecutive lock mode）

  這種情況下，針對bulk inserts才會採用AUTO-INC鎖這種方式，而針對simple inserts，則採用了一種新的輕量級的互斥鎖來分配auto_increment列的值。當然，如果其他事務已經持有了AUTO-INC鎖，則simple inserts需要等待.

  需要注意的是，在innodb_autoinc_lock_mode=1時，語句之間是可能出現auto_increment值的間隔的。比如mixed-mode inserts以及bulk inserts中都有可能導致一些分配的auto_increment值被浪費掉從而導致間隙。後面會有例子。

• innodb_autoinc_lock_mode=2（interleaved lock mode）

  這種模式下任何類型的inserts都不會採用AUTO-INC鎖，效能最好，但是在同一條語句內部產生auto_increment值間隙。此外，這種模式對statement-based replication也不安全。

3.可能產生間隙原因總結

經過上面的文件剖析，下面總結下針對auto_increment欄位的各種類型的inserts語句可能出現間隙問題的原因：

• simple inserts

  針對innodb_autoinc_lock_mode=0,1,2，只有在一個有auto_increment列操作的事務出現復原時，分配的auto_increment的值會丟棄不再使用，從而造成間隙。

• bulk inserts（這裡就不考慮交易回復的情況了，交易回復是會造成間隙的）

  innodb_autoinc_lock_mode=0,由於一直會持有AUTO-INC鎖直到語句結束，產生的值都是連續的，不會產生間隙。
  innodb_autoinc_lock_mode=1，這時候一條語句內不會產生間隙，但是語句之間可能會產生間隙。後面會有例子說明。
  innodb_autoinc_lock_mode=2，如果有並發的insert操作，那麼同一條語句內都可能產生間隙。

• mixed-mode inserts

  這種模式下針對innodb_autoinc_lock_mode的值配置不同，結果也會不同，當然innodb_autoinc_lock_mode=0時時不會產生間隙的，而innodb_autoinc_lock_mode=1以及innodb_autoinc_lock_mode=2是會產生間隙的。後面例子說明。

另外注意的一點是，在master-slave這種架構中，複製如果採用statement-based replication這種方式，則innodb_autoinc_lock_mode=0或1才是安全的。而如果是採用row-based replication或者mixed-based replication，則innodb_autoinc_lock_mode=0,1,2都是安全的。

4.執行個體

測試的兩個表分別為t和t1，定義分別如下:

CREATE TABLE `t` (  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`a`)) ENGINE=InnoDB;CREATE TABLE `t1` (  `c1` int(11) NOT NULL AUTO_INCREMENT,  `c2` varchar(10) DEFAULT NULL,  PRIMARY KEY (`c1`)) ENGINE=InnoDB

首先在表t插入1-10000000共1千萬條資料，為了後面測試方便。開啟session1，執行下面語句：

insert into t1(c2) select * from t;

然後開啟session2，在t1中插入資料：

insert into t1(c2) values(400);

針對innodb_autoinc_lock_mode不同的情況，新插入的資料的c1的值也不同。

innodb_autoinc_lock_mode=0時，因為session1的語句都是加AUTO-INC鎖，因此，session1先開始的話，c1列的值都是1-10000000連續的值，由於在傳統機制下，auto_increment值都是一個個分配，因此session2插入的資料c1的值則是10000001。最終看到的就是有兩條這樣的資料(400,400),(10000001,400)。

innodb_autoinc_lock_mode=1時，同樣session1也會加AUTO-INC鎖，但是由於該模式下會預先分配auto_increment的值，所以可以看到在session2中插入的資料的c1值不會是10000001，但是不會是1-10000000這其中的數字，因為session1有加AUTO-INC鎖。最終的資料會是這樣兩條:（400，400）, （10026856，400）。

innodb_autoinc_lock_mode=2時，session1不會加AUTO-INC鎖，因此雖然session2是後執行，但是並不影響auto_increment值分配，最終的值跟我們執行session2的時間有關，最終的值可能是這樣的：(400,400)，（1235603，400）這樣的，會佔用1-10000000之間的值。

5.另外幾點

1）關於innodb_autoinc_lock_mode=1時，auto_increment預先分配策略可以參照參考資料2，假定表t中已經初始有一條記錄1，然後在表t中我們用`insert into t select NULL from t執行四次，可以看到表t中最終的記錄會是1，2，3，4，6，7，8，9，13，14，15，16，17，18，19，20，其中5，10，11，12都浪費掉了。參考資料1後面部分也有講到預分配問題。

2）INSERT INTO t1…SELECT … FROM t這類語句會對錶t1加record lock，如果隔離等級是read committed，或者設定了innodb_locks_unsafe_for_binlog且隔離等級不是serialize，則不會對t加鎖，否則對t加shared next-key lock。

6.參考資料

• innodb-auto-increment-handling
• auto_increment gaps
• innodb-locks-set