MySQL45講讀書筆記 39講自增主鍵為什麼不是連續的

一 序

   本文屬于極客時間MySQL45講讀書筆記系列。本篇對于開發同學還是很有幫助的。

我們提到過自增主鍵，由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，是以索引更緊湊。

之前我見過有的業務設計依賴于自增主鍵的連續性，也就是說，這個設計假設自增主鍵是連續的。但實際上，這樣的假設是錯的，因為自增主鍵不能保證連續遞增。今天這篇文章，我們就來說說這個問題，看看什麼情況下自增主鍵會出現 “空洞”？

為了便于說明，我們建立一個表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，是在真正執行插入資料的操作之前。這個語句真正執行的時候，因為碰到唯一鍵c沖突，是以id=2這一行并沒有插入成功，但也沒有将自增值再改回去。

是以，在這之後，再插入新的資料行時，拿到的自增id就是3。也就是說，出現了自增主鍵不連續的情況。

如圖3所示就是完整的示範結果。

圖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的一行資料了嗎？

自增鎖的優化

可以看到，自增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，是很有必要的。今天的例子給這個結論多了一個理由。

***************

除了不重要的資料外，核心的主表資料，ID生成還是不要依賴資料庫自增的好。

裡面老師提到了 執行個體重新開機，主鍵沖突（可能會複用以前使用過的id值。如果你需要持續對這個表進行邏輯備份，那麼就可能會碰到主鍵沖突的問題。）