在普通的 insert 語句里面包含多個 value 值的情況下，即使 innodb_autoinc_lock_mode 設置為 1，也不會等語句執行完成才釋放鎖。因為這類語句在申請自增 id 的時候，是可以精確計算出需要多少個 id 的，然后一次性申請，申請完成后鎖就可以釋放了。
從開始接觸MySQL，我們就知道在設計主鍵時，要設置為自增主鍵，使用自增主鍵有以下幾個優點：
- 效率高：使用自增主鍵可以避免頻繁生成主鍵值的操作，節省了數據庫的資源，提高了查詢效率。
- 索引優化：自增主鍵一般是整數類型，可以方便地使用B-tree索引來加速數據查詢。
- 數據唯一性：自增主鍵可以保證數據的唯一性，防止重復插入數據。
- 方便性：使用自增主鍵可以方便地進行更新、刪除和查詢操作，不需要復雜的聯合主鍵或其他索引操作。
我們在使用自增主鍵統計數據庫的數據量時，也會經常使用id的最大值與最小值之間的差值作為數據庫當前已有數據的條數，但是這種統計方式是否正確？是否存在誤差？
筆者先給出本文結論：自增主鍵可以保持主鍵遞增順序插入，避免頁分裂，索引更為緊湊，但是自增主鍵并不能保證連續遞增，即出現空洞。
但是問題再次出現，為什么明明是自增主鍵，為什么不能保證連續遞增？為什么會出現空洞？
在本文中，我們使用如下的數據庫配置：
1 自增值的存儲
在如上的空表 t 里面執行 insert into t values(null, 1, 1); 插入一行數據，再執行 show create table 命令，就可以看到如下圖所示的結果：
表定義里面出現了一個 AUTO_INCREMENT=2，表示下一次插入數據時，如果需要自動生成自增值，會生成 id=2。
不同的引擎對于自增至的保存策略不同：
- 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 恢復重啟之前的值。
2 自增值修改機制
在 MySQL 里面，如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：
- 如果插入數據時 id 字段指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段；
- 如果插入數據時 id 字段指定了具體的值，就直接使用語句里指定的值。
根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設，某次要插入的值是 X，當前的自增值是 Y。
- 如果 X<Y，那么這個表的自增值不變；
- 如果 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”；
- 否則，自增值不變。
3 自增值修改時機
3.1 唯一鍵沖突
假設表t有了存在(1,1,1)這條記錄，再次執行一次數據命令：
insert into t values(null, 1, 1);
這個語句的執行流程就是：
- 執行器調用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,1,1);
- InnoDB 發現用戶沒有指定自增 id 的值，獲取表 t 當前的自增值 2；
- 將傳入的行的值改成 (2,1,1);
- 將表的自增值改成 3；
- 繼續執行插入數據操作，由于已經存在 c=1 的記錄，所以報 Duplicate key error，語句返回。
可以看到，這個表的自增值修改為3之后也不會再回退，之后再插入拿到的自增id就是3，自增主鍵不再連續。
3.2 事務回滾
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)
如上語句就會出現不連續自增id的情況。MySQL不允許做回退，看如下的假設：假設有兩個并行執行的事務，在申請自增值時，為了避免兩個事務申請到相同自增id，肯定加鎖，然后順序申請。
- 假設事務A申請到了id=2，事務B申請到id=3，那么表t的自增值是4，之后繼續執行；
- 事務B正確提交后，事務A出現唯一鍵沖突；
- 如果允許事務A把自增id回退，也就是表t當前自增值改回2；
- 接下來繼續執行的其他事務就會申請到id=2，然后再申請id=3，就會出現插入語句報錯“主鍵沖突"；
為了解決這個主鍵沖突，有兩種方法：
- 每次申請 id 之前，先判斷表里面是否已經存在這個 id。如果存在，就跳過這個 id。但是，這個方法的成本很高。因為，本來申請 id 是一個很快的操作，現在還要再去主鍵索引樹上判斷 id 是否存在。
- 把自增 id 的鎖范圍擴大，必須等到一個事務執行完成并提交，下一個事務才能再申請自增 id。這個方法的問題，就是鎖的粒度太大，系統并發能力大大下降。
出于性能考慮，如果設計為必須連續，那就需要每次都去檢查當前申請的ID是否已存在，浪費性能；或者提升鎖粒度，會導致申請ID退化為串行申請
3.3 批量申請自增id策略
對于批量插入數據的語句，MySQL 有一個批量申請自增 id 的策略：
- 語句執行過程中，第一次申請自增 id，會分配 1 個；
- 1 個用完以后，這個語句第二次申請自增 id，會分配 2 個；
- 2 個用完以后，還是這個語句，第三次申請自增 id，會分配 4 個；
- 依此類推，同一個語句去申請自增 id，每次申請到的自增 id 個數都是上一次的兩倍。
4 自增鎖優化
自增id鎖并不是一個事務鎖，而是每次申請完就馬上釋放，以便允許別的事務再申請。
在MySQL 5.0版本的時候，自增鎖的范圍是語句級別。也就是說，如果一個語句申請了一個表自增鎖，這個鎖會等語句執行結束以后才釋放。顯然，這樣設計會影響并發度。
MySQL 5.1.22版本引入了一個新策略，新增參數innodb_autoinc_lock_mode，默認值是1。
- 這個參數的值被設置為 0 時，表示采用之前 MySQL 5.0 版本的策略，即語句執行結束后才釋放鎖；
- 這個參數的值被設置為 1 時：
普通 insert 語句，自增鎖在申請之后就馬上釋放；
類似 insert … select 這樣的批量插入數據的語句，自增鎖還是要等語句結束后才被釋放；
- 這個參數的值被設置為 2 時，所有的申請自增主鍵的動作都是申請后就釋放鎖。
你一定有兩個疑問：為什么默認設置下，insert … select 要使用語句級的鎖？為什么這個參數的默認值不是 2？原因就是為了保證數據的一致性。
在生產上，尤其是有 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 的策略：
- 語句執行過程中，第一次申請自增 id，會分配 1 個；
- 1 個用完以后，這個語句第二次申請自增 id，會分配 2 個；
- 2 個用完以后，還是這個語句，第三次申請自增 id，會分配 4 個；
- 依此類推，同一個語句去申請自增 id，每次申請到的自增 id 個數都是上一次的兩倍。
