MySQL主鍵自增在什么情況下會出現空洞

本篇內容主要講解“MySQL主鍵自增在什么情況下會出現空洞”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL主鍵自增在什么情況下會出現空洞”吧!

為了便于說明，我們創建一個表t，其中id是自增主鍵字段、c是唯一索引。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪兒？

在這個空表t里面執行insert into t values(null, 1, 1);插入一行數據，再執行show create table命令，就可以看到如下圖所示的結果：

圖1 自動生成的AUTO_INCREMENT值

可以看到，表定義里面出現了一個AUTO_INCREMENT=2，表示下一次插入數據時，如果需要自動生成自增值，會生成id=2。

其實，這個輸出結果容易引起這樣的誤解：自增值是保存在表結構定義里的。實際上，表的結構定義存放在后綴名為.frm的文件中，但是并不會保存自增值。

不同的引擎對于自增值的保存策略不同。

• MyISAM引擎的自增值保存在數據文件中。

• InnoDB引擎的自增值，其實是保存在了內存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才實現了“如果發生重啟，表的自增值可以恢復為MySQL重啟前的值”，具體情況是：

• 在MySQL 5.7及之前的版本，自增值保存在內存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值max(id)，然后將max(id)+1作為這個表當前的自增值。?
  舉例來說，如果一個表當前數據行里最大的id是10，AUTO_INCREMENT=11。這時候，我們刪除id=10的行，AUTO_INCREMENT還是11。但如果馬上重啟實例，重啟后這個表的AUTO_INCREMENT就會變成10。?
  也就是說，MySQL重啟可能會修改一個表的AUTO_INCREMENT的值。

• 在MySQL 8.0版本，將自增值的變更記錄在了redo log中，重啟的時候依靠redo log恢復重啟之前的值。

理解了MySQL對自增值的保存策略以后，我們再看看自增值修改機制。

自增值修改機制

在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：

1. 如果插入數據時id字段指定為0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT值填到自增字段；

2. 如果插入數據時id字段指定了具體的值，就直接使用語句里指定的值。

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設，某次要插入的值是X，當前的自增值是Y。

1. 如果X<Y，那么這個表的自增值不變；

2. 如果X≥Y，就需要把當前自增值修改為新的自增值。

新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續疊加，直到找到第一個大于X的值，作為新的自增值。

其中，auto_increment_offset 和 auto_increment_increment是兩個系統參數，分別用來表示自增的初始值和步長，默認值都是1。

備注：在一些場景下，使用的就不全是默認值。比如，雙M的主備結構里要求雙寫的時候，我們就可能會設置成auto_increment_increment=2，讓一個庫的自增id都是奇數，另一個庫的自增id都是偶數，避免兩個庫生成的主鍵發生沖突。

當auto_increment_offset和auto_increment_increment都是1的時候，新的自增值生成邏輯很簡單，就是：

1. 如果準備插入的值>=當前自增值，新的自增值就是“準備插入的值+1”；

2. 否則，自增值不變。

這就引入了我們文章開頭提到的問題，在這兩個參數都設置為1的時候，自增主鍵id卻不能保證是連續的，這是什么原因呢？

自增值的修改時機

要回答這個問題，我們就要看一下自增值的修改時機。

假設，表t里面已經有了(1,1,1)這條記錄，這時我再執行一條插入數據命令：

insert into t values(null, 1, 1);

這個語句的執行流程就是：

1. 執行器調用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1);

2. InnoDB發現用戶沒有指定自增id的值，獲取表t當前的自增值2；

3. 將傳入的行的值改成(2,1,1);

4. 將表的自增值改成3；

5. 繼續執行插入數據操作，由于已經存在c=1的記錄，所以報Duplicate key error，語句返回。

對應的執行流程圖如下：

                                                       圖3 一個自增主鍵id不連續的復現步驟

可以看到，這個操作序列復現了一個自增主鍵id不連續的現場(沒有id=2的行）。可見，唯一鍵沖突是導致自增主鍵id不連續的第一種原因。

同樣地，事務回滾也會產生類似的現象，這就是第二種原因。

下面這個語句序列就可以構造不連續的自增id，你可以自己驗證一下。

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

你可能會問，為什么在出現唯一鍵沖突或者回滾的時候，MySQL沒有把表t的自增值改回去呢？如果把表t的當前自增值從3改回2，再插入新數據的時候，不就可以生成id=2的一行數據了嗎？

其實，MySQL這么設計是為了提升性能。接下來，我就跟你分析一下這個設計思路，看看自增值為什么不能回退。

假設有兩個并行執行的事務，在申請自增值的時候，為了避免兩個事務申請到相同的自增id，肯定要加鎖，然后順序申請。

1. 假設事務A申請到了id=2， 事務B申請到id=3，那么這時候表t的自增值是4，之后繼續執行。

2. 事務B正確提交了，但事務A出現了唯一鍵沖突。

3. 如果允許事務A把自增id回退，也就是把表t的當前自增值改回2，那么就會出現這樣的情況：表里面已經有id=3的行，而當前的自增id值是2。

4. 接下來，繼續執行的其他事務就會申請到id=2，然后再申請到id=3。這時，就會出現插入語句報錯“主鍵沖突”。

而為了解決這個主鍵沖突，有兩種方法：

1. 每次申請id之前，先判斷表里面是否已經存在這個id。如果存在，就跳過這個id。但是，這個方法的成本很高。因為，本來申請id是一個很快的操作，現在還要再去主鍵索引樹上判斷id是否存在。

2. 把自增id的鎖范圍擴大，必須等到一個事務執行完成并提交，下一個事務才能再申請自增id。這個方法的問題，就是鎖的粒度太大，系統并發能力大大下降。

可見，這兩個方法都會導致性能問題。造成這些麻煩的罪魁禍首，就是我們假設的這個“允許自增id回退”的前提導致的。

因此，InnoDB放棄了這個設計，語句執行失敗也不回退自增id。也正是因為這樣，所以才只保證了自增id是遞增的，但不保證是連續的。

自增鎖的優化

可以看到，自增id鎖并不是一個事務鎖，而是每次申請完就馬上釋放，以便允許別的事務再申請。其實，在MySQL 5.1版本之前，并不是這樣的。

接下來，我會先給你介紹下自增鎖設計的歷史，這樣有助于你分析接下來的一個問題。

在MySQL 5.0版本的時候，自增鎖的范圍是語句級別。也就是說，如果一個語句申請了一個表自增鎖，這個鎖會等語句執行結束以后才釋放。顯然，這樣設計會影響并發度。

MySQL 5.1.22版本引入了一個新策略，新增參數innodb_autoinc_lock_mode，默認值是1。

1. 這個參數的值被設置為0時，表示采用之前MySQL 5.0版本的策略，即語句執行結束后才釋放鎖；

2. 這個參數的值被設置為1時：

• 普通insert語句，自增鎖在申請之后就馬上釋放；

• 類似insert … select這樣的批量插入數據的語句，自增鎖還是要等語句結束后才被釋放；

3. 這個參數的值被設置為2時，所有的申請自增主鍵的動作都是申請后就釋放鎖。

你一定有兩個疑問：為什么默認設置下，insert … select 要使用語句級的鎖？為什么這個參數的默認值不是2？

答案是，這么設計還是為了數據的一致性。

我們一起來看一下這個場景：

                                                            圖4 批量插入數據的自增鎖

在這個例子里，我往表t1中插入了4行數據，然后創建了一個相同結構的表t2，然后兩個session同時執行向表t2中插入數據的操作。

你可以設想一下，如果session B是申請了自增值以后馬上就釋放自增鎖，那么就可能出現這樣的情況：

• session B先插入了兩個記錄，(1,1,1)、(2,2,2)；

• 然后，session A來申請自增id得到id=3，插入了（3,5,5)；

• 之后，session B繼續執行，插入兩條記錄(4,3,3)、 (5,4,4)。

你可能會說，這也沒關系吧，畢竟session B的語義本身就沒有要求表t2的所有行的數據都跟session A相同。

是的，從數據邏輯上看是對的。但是，如果我們現在的binlog_format=statement，你可以設想下，binlog會怎么記錄呢？

由于兩個session是同時執行插入數據命令的，所以binlog里面對表t2的更新日志只有兩種情況：要么先記session A的，要么先記session B的。

但不論是哪一種，這個binlog拿去從庫執行，或者用來恢復臨時實例，備庫和臨時實例里面，session B這個語句執行出來，生成的結果里面，id都是連續的。這時，這個庫就發生了數據不一致。

你可以分析一下，出現這個問題的原因是什么？

其實，這是因為原庫session B的insert語句，生成的id不連續。這個不連續的id，用statement格式的binlog來串行執行，是執行不出來的。

而要解決這個問題，有兩種思路：

1. 一種思路是，讓原庫的批量插入數據語句，固定生成連續的id值。所以，自增鎖直到語句執行結束才釋放，就是為了達到這個目的。

2. 另一種思路是，在binlog里面把插入數據的操作都如實記錄進來，到備庫執行的時候，不再依賴于自增主鍵去生成。這種情況，其實就是innodb_autoinc_lock_mode設置為2，同時binlog_format設置為row。

因此，在生產上，尤其是有insert … select這種批量插入數據的場景時，從并發插入數據性能的角度考慮，我建議你這樣設置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row.這樣做，既能提升并發性，又不會出現數據一致性問題。

需要注意的是，我這里說的批量插入數據，包含的語句類型是insert … select、replace … select和load data語句。

但是，在普通的insert語句里面包含多個value值的情況下，即使innodb_autoinc_lock_mode設置為1，也不會等語句執行完成才釋放鎖。因為這類語句在申請自增id的時候，是可以精確計算出需要多少個id的，然后一次性申請，申請完成后鎖就可以釋放了。

也就是說，批量插入數據的語句，之所以需要這么設置，是因為“不知道要預先申請多少個id”。

既然預先不知道要申請多少個自增id，那么一種直接的想法就是需要一個時申請一個。但如果一個select … insert語句要插入10萬行數據，按照這個邏輯的話就要申請10萬次。顯然，這種申請自增id的策略，在大批量插入數據的情況下，不但速度慢，還會影響并發插入的性能。

因此，對于批量插入數據的語句，MySQL有一個批量申請自增id的策略：

1. 語句執行過程中，第一次申請自增id，會分配1個；

2. 1個用完以后，這個語句第二次申請自增id，會分配2個；

3. 2個用完以后，還是這個語句，第三次申請自增id，會分配4個；

4. 依此類推，同一個語句去申請自增id，每次申請到的自增id個數都是上一次的兩倍。

舉個例子，我們一起看看下面的這個語句序列：

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert…select，實際上往表t2中插入了4行數據。但是，這四行數據是分三次申請的自增id，第一次申請到了id=1，第二次被分配了id=2和id=3， 第三次被分配到id=4到id=7。

由于這條語句實際只用上了4個id，所以id=5到id=7就被浪費掉了。之后，再執行insert into t2 values(null, 5,5)，實際上插入的數據就是（8,5,5)。

這是主鍵id出現自增id不連續的第三種原因。

小結

今天，我們從“自增主鍵為什么會出現不連續的值”這個問題開始，首先討論了自增值的存儲。

在MyISAM引擎里面，自增值是被寫在數據文件上的。而在InnoDB中，自增值是被記錄在內存的。MySQL直到8.0版本，才給InnoDB表的自增值加上了持久化的能力，確保重啟前后一個表的自增值不變。

然后，我和你分享了在一個語句執行過程中，自增值改變的時機，分析了為什么MySQL在事務回滾的時候不能回收自增id。

MySQL 5.1.22版本開始引入的參數innodb_autoinc_lock_mode，控制了自增值申請時的鎖范圍。從并發性能的角度考慮，我建議你將其設置為2，同時將binlog_format設置為row。我在前面的文章中其實多次提到，binlog_format設置為row，是很有必要的。今天的例子給這個結論多了一個理由。

insert語句的鎖為什么這么多

盡量在申請到自增id以后，就釋放自增鎖。

因此，insert語句是一個很輕量的操作。不過，這個結論對于“普通的insert語句”才有效。也就是說，還有些insert語句是屬于“特殊情況”的，在執行過程中需要給其他資源加鎖，或者無法在申請到自增id以后就立馬釋放自增鎖。

那么，今天這篇文章，我們就一起來聊聊這個話題。

insert … select 語句

我們先從昨天的問題說起吧。表t和t2的表結構、初始化數據語句如下，今天的例子我們還是針對這兩個表展開。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

現在，我們一起來看看為什么在可重復讀隔離級別下，binlog_format=statement時執行：

insert into t2(c,d) select c,d from t;

這個語句時，需要對表t的所有行和間隙加鎖呢？

其實，這個問題我們需要考慮的還是日志和數據的一致性。我們看下這個執行序列：

                                                                               圖1 并發insert場景

實際的執行效果是，如果session B先執行，由于這個語句對表t主鍵索引加了(-∞,1]這個next-key lock，會在語句執行完成后，才允許session A的insert語句執行。

但如果沒有鎖的話，就可能出現session B的insert語句先執行，但是后寫入binlog的情況。于是，在binlog_format=statement的情況下，binlog里面就記錄了這樣的語句序列：

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

這個語句到了備庫執行，就會把id=-1這一行也寫到表t2中，出現主備不一致。

insert 循環寫入

當然了，執行insert … select 的時候，對目標表也不是鎖全表，而是只鎖住需要訪問的資源。

如果現在有這么一個需求：要往表t2中插入一行數據，這一行的c值是表t中c值的最大值加1。

此時，我們可以這么寫這條SQL語句 ：

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

這個語句的加鎖范圍，就是表t索引c上的(4,supremum]這個next-key lock和主鍵索引上id=4這一行。

它的執行流程也比較簡單，從表t中按照索引c倒序，掃描第一行，拿到結果寫入到表t2中。

因此整條語句的掃描行數是1。

這個語句執行的慢查詢日志（slow log），如下圖所示：

                                                                                圖3 慢查詢日志--將數據插入表t

可以看到，這時候的Rows_examined的值是5。

我在前面的文章中提到過，希望你都能夠學會用explain的結果來“腦補”整條語句的執行過程。今天，我們就來一起試試。

如圖4所示就是這條語句的explain結果。

                                                                      圖5 查看 Innodb_rows_read變化

可以看到，這個語句執行前后，Innodb_rows_read的值增加了4。因為默認臨時表是使用Memory引擎的，所以這4行查的都是表t，也就是說對表t做了全表掃描。

這樣，我們就把整個執行過程理清楚了：

1. 創建臨時表，表里有兩個字段c和d。

2. 按照索引c掃描表t，依次取c=4、3、2、1，然后回表，讀到c和d的值寫入臨時表。這時，Rows_examined=4。

3. 由于語義里面有limit 1，所以只取了臨時表的第一行，再插入到表t中。這時，Rows_examined的值加1，變成了5。

也就是說，這個語句會導致在表t上做全表掃描，并且會給索引c上的所有間隙都加上共享的next-key lock。所以，這個語句執行期間，其他事務不能在這個表上插入數據。

至于這個語句的執行為什么需要臨時表，原因是這類一邊遍歷數據，一邊更新數據的情況，如果讀出來的數據直接寫回原表，就可能在遍歷過程中，讀到剛剛插入的記錄，新插入的記錄如果參與計算邏輯，就跟語義不符。

由于實現上這個語句沒有在子查詢中就直接使用limit 1，從而導致了這個語句的執行需要遍歷整個表t。它的優化方法也比較簡單，就是用前面介紹的方法，先insert into到臨時表temp_t，這樣就只需要掃描一行；然后再從表temp_t里面取出這行數據插入表t1。

當然，由于這個語句涉及的數據量很小，你可以考慮使用內存臨時表來做這個優化。使用內存臨時表優化時，語句序列的寫法如下：

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一鍵沖突

前面的兩個例子是使用insert … select的情況，接下來我要介紹的這個例子就是最常見的insert語句出現唯一鍵沖突的情況。

對于有唯一鍵的表，插入數據時出現唯一鍵沖突也是常見的情況了。我先給你舉一個簡單的唯一鍵沖突的例子。

                                                                                圖7 唯一鍵沖突--死鎖

在session A執行rollback語句回滾的時候，session C幾乎同時發現死鎖并返回。

這個死鎖產生的邏輯是這樣的：

1. 在T1時刻，啟動session A，并執行insert語句，此時在索引c的c=5上加了記錄鎖。注意，這個索引是唯一索引，因此退化為記錄鎖（如果你的印象模糊了，可以回顧下第21篇文章介紹的加鎖規則）。

2. 在T2時刻，session B要執行相同的insert語句，發現了唯一鍵沖突，加上讀鎖；同樣地，session C也在索引c上，c=5這一個記錄上，加了讀鎖。

3. T3時刻，session A回滾。這時候，session B和session C都試圖繼續執行插入操作，都要加上寫鎖。兩個session都要等待對方的行鎖，所以就出現了死鎖。

這個流程的狀態變化圖如下所示。

                                                                         圖9 兩個唯一鍵同時沖突

可以看到，主鍵id是先判斷的，MySQL認為這個語句跟id=2這一行沖突，所以修改的是id=2的行。

需要注意的是，執行這條語句的affected rows返回的是2，很容易造成誤解。實際上，真正更新的只有一行，只是在代碼實現上，insert和update都認為自己成功了，update計數加了1， insert計數也加了1。

到此，相信大家對“MySQL主鍵自增在什么情況下會出現空洞”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！