從開始接觸MySQL，我們就知道在設(shè)計(jì)主鍵時(shí)，要設(shè)置為自增主鍵，使用自增主鍵有以下幾個(gè)優(yōu)點(diǎn)：

• 效率高：使用自增主鍵可以避免頻繁生成主鍵值的操作，節(jié)省了數(shù)據(jù)庫(kù)的資源，提高了查詢效率。
• 索引優(yōu)化：自增主鍵一般是整數(shù)類型，可以方便地使用B-tree索引來加速數(shù)據(jù)查詢。
• 數(shù)據(jù)唯一性：自增主鍵可以保證數(shù)據(jù)的唯一性，防止重復(fù)插入數(shù)據(jù)。
• 方便性：使用自增主鍵可以方便地進(jìn)行更新、刪除和查詢操作，不需要復(fù)雜的聯(lián)合主鍵或其他索引操作。

我們?cè)谑褂米栽鲋麈I統(tǒng)計(jì)數(shù)據(jù)庫(kù)的數(shù)據(jù)量時(shí)，也會(huì)經(jīng)常使用id的最大值與最小值之間的差值作為數(shù)據(jù)庫(kù)當(dāng)前已有數(shù)據(jù)的條數(shù)，但是這種統(tǒng)計(jì)方式是否正確？是否存在誤差？

筆者先給出本文結(jié)論：自增主鍵可以保持主鍵遞增順序插入，避免頁(yè)分裂，索引更為緊湊，但是自增主鍵并不能保證連續(xù)遞增，即出現(xiàn)空洞。

但是問題再次出現(xiàn)，為什么明明是自增主鍵，為什么不能保證連續(xù)遞增？為什么會(huì)出現(xiàn)空洞？

在本文中，我們使用如下的數(shù)據(jù)庫(kù)配置：

1 自增值的存儲(chǔ)

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

表定義里面出現(xiàn)了一個(gè) AUTO_INCREMENT=2，表示下一次插入數(shù)據(jù)時(shí)，如果需要自動(dòng)生成自增值，會(huì)生成 id=2。

不同的引擎對(duì)于自增至的保存策略不同：

• MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。
• InnoDB 引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實(shí)現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為 MySQL 重啟前的值”，具體情況是：

• 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時(shí)候，都會(huì)去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個(gè)表當(dāng)前的自增值。舉例來說，如果一個(gè)表當(dāng)前數(shù)據(jù)行里最大的id 是10，AUTO_INCREMENT=11。這時(shí)候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實(shí)例，重啟后這個(gè)表的 AUTO_INCREMENT 就會(huì)變成 10。也就是說，MySQL 重啟可能會(huì)修改一個(gè)表的 AUTO_INCREMENT 的值。
• 在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時(shí)候依靠 redo log 恢復(fù)重啟之前的值。

2 自增值修改機(jī)制

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

• 如果插入數(shù)據(jù)時(shí) id 字段指定為 0、null 或未指定值，那么就把這個(gè)表當(dāng)前的 AUTO_INCREMENT 值填到自增字段；
• 如果插入數(shù)據(jù)時(shí) id 字段指定了具體的值，就直接使用語(yǔ)句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會(huì)有所不同。假設(shè)，某次要插入的值是 X，當(dāng)前的自增值是 Y。

• 如果 X<Y，那么這個(gè)表的自增值不變；
• 如果 X≥Y，就需要把當(dāng)前自增值修改為新的自增值。

新的自增值生成算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長(zhǎng)，持續(xù)疊加，直到找到第一個(gè)大于 X 的值，作為新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是兩個(gè)系統(tǒng)參數(shù)，分別用來表示自增的初始值和步長(zhǎng)，默認(rèn)值都是 1。

但是在一些場(chǎng)景下，使用的就不全是默認(rèn)值。比如，雙 M 的主備結(jié)構(gòu)里要求雙寫的時(shí)候，我們就可能會(huì)設(shè)置成 auto_increment_increment=2，讓一個(gè)庫(kù)的自增 id 都是奇數(shù)，另一個(gè)庫(kù)的自增 id 都是偶數(shù)，避免兩個(gè)庫(kù)生成的主鍵發(fā)生沖突。

當(dāng) auto_increment_offset 和 auto_increment_increment 都是 1 的時(shí)候，新的自增值生成邏輯很簡(jiǎn)單，就是：

• 如果準(zhǔn)備插入的值 >= 當(dāng)前自增值，新的自增值就是“準(zhǔn)備插入的值 +1”；
• 否則，自增值不變。

3 自增值修改時(shí)機(jī)

3.1 唯一鍵沖突

假設(shè)表t有了存在(1,1,1)這條記錄，再次執(zhí)行一次數(shù)據(jù)命令：

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

這個(gè)語(yǔ)句的執(zhí)行流程就是：

• 執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,1,1);
• InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值，獲取表 t 當(dāng)前的自增值 2；
• 將傳入的行的值改成 (2,1,1);
• 將表的自增值改成 3；
• 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在 c=1 的記錄，所以報(bào) Duplicate key error，語(yǔ)句返回。

可以看到，這個(gè)表的自增值修改為3之后也不會(huì)再回退，之后再插入拿到的自增id就是3，自增主鍵不再連續(xù)。

3.2 事務(wù)回滾

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)

如上語(yǔ)句就會(huì)出現(xiàn)不連續(xù)自增id的情況。MySQL不允許做回退，看如下的假設(shè)：假設(shè)有兩個(gè)并行執(zhí)行的事務(wù)，在申請(qǐng)自增值時(shí)，為了避免兩個(gè)事務(wù)申請(qǐng)到相同自增id，肯定加鎖，然后順序申請(qǐng)。

• 假設(shè)事務(wù)A申請(qǐng)到了id=2，事務(wù)B申請(qǐng)到id=3，那么表t的自增值是4，之后繼續(xù)執(zhí)行；
• 事務(wù)B正確提交后，事務(wù)A出現(xiàn)唯一鍵沖突；
• 如果允許事務(wù)A把自增id回退，也就是表t當(dāng)前自增值改回2；
• 接下來繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到id=2，然后再申請(qǐng)id=3，就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突"；

為了解決這個(gè)主鍵沖突，有兩種方法：

• 每次申請(qǐng) id 之前，先判斷表里面是否已經(jīng)存在這個(gè) id。如果存在，就跳過這個(gè) id。但是，這個(gè)方法的成本很高。因?yàn)椋緛砩暾?qǐng) id 是一個(gè)很快的操作，現(xiàn)在還要再去主鍵索引樹上判斷 id 是否存在。
• 把自增 id 的鎖范圍擴(kuò)大，必須等到一個(gè)事務(wù)執(zhí)行完成并提交，下一個(gè)事務(wù)才能再申請(qǐng)自增 id。這個(gè)方法的問題，就是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降。

出于性能考慮，如果設(shè)計(jì)為必須連續(xù)，那就需要每次都去檢查當(dāng)前申請(qǐng)的ID是否已存在，浪費(fèi)性能；或者提升鎖粒度，會(huì)導(dǎo)致申請(qǐng)ID退化為串行申請(qǐng) 

3.3 批量申請(qǐng)自增id策略

對(duì)于批量插入數(shù)據(jù)的語(yǔ)句，MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略：

• 語(yǔ)句執(zhí)行過程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)； 
• 1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)； 
• 2 個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)； 
• 依此類推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。 

4 自增鎖優(yōu)化

自增id鎖并不是一個(gè)事務(wù)鎖，而是每次申請(qǐng)完就馬上釋放，以便允許別的事務(wù)再申請(qǐng)。

在MySQL 5.0版本的時(shí)候，自增鎖的范圍是語(yǔ)句級(jí)別。也就是說，如果一個(gè)語(yǔ)句申請(qǐng)了一個(gè)表自增鎖，這個(gè)鎖會(huì)等語(yǔ)句執(zhí)行結(jié)束以后才釋放。顯然，這樣設(shè)計(jì)會(huì)影響并發(fā)度。

MySQL 5.1.22版本引入了一個(gè)新策略，新增參數(shù)innodb_autoinc_lock_mode，默認(rèn)值是1。

• 這個(gè)參數(shù)的值被設(shè)置為 0 時(shí)，表示采用之前 MySQL 5.0 版本的策略，即語(yǔ)句執(zhí)行結(jié)束后才釋放鎖；
• 這個(gè)參數(shù)的值被設(shè)置為 1 時(shí)：

普通 insert 語(yǔ)句，自增鎖在申請(qǐng)之后就馬上釋放；

類似 insert … select 這樣的批量插入數(shù)據(jù)的語(yǔ)句，自增鎖還是要等語(yǔ)句結(jié)束后才被釋放；

• 這個(gè)參數(shù)的值被設(shè)置為 2 時(shí)，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖。

你一定有兩個(gè)疑問：為什么默認(rèn)設(shè)置下，insert … select 要使用語(yǔ)句級(jí)的鎖？為什么這個(gè)參數(shù)的默認(rèn)值不是 2？原因就是為了保證數(shù)據(jù)的一致性。

在生產(chǎn)上，尤其是有 insert … select 這種批量插入數(shù)據(jù)的場(chǎng)景時(shí)，從并發(fā)插入數(shù)據(jù)性能的角度考慮，我建議你這樣設(shè)置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 這樣做，既能提升并發(fā)性，又不會(huì)出現(xiàn)數(shù)據(jù)一致性問題。

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

但是，在普通的 insert 語(yǔ)句里面包含多個(gè) value 值的情況下，即使 innodb_autoinc_lock_mode 設(shè)置為 1，也不會(huì)等語(yǔ)句執(zhí)行完成才釋放鎖。因?yàn)檫@類語(yǔ)句在申請(qǐng)自增 id 的時(shí)候，是可以精確計(jì)算出需要多少個(gè) id 的，然后一次性申請(qǐng)，申請(qǐng)完成后鎖就可以釋放了。

也就是說，批量插入數(shù)據(jù)的語(yǔ)句，之所以需要這么設(shè)置，是因?yàn)椤安恢酪A(yù)先申請(qǐng)多少個(gè) id”。

既然預(yù)先不知道要申請(qǐng)多少個(gè)自增 id，那么一種直接的想法就是需要一個(gè)時(shí)申請(qǐng)一個(gè)。但如果一個(gè) select … insert 語(yǔ)句要插入 10 萬(wàn)行數(shù)據(jù)，按照這個(gè)邏輯的話就要申請(qǐng) 10 萬(wàn)次。顯然，這種申請(qǐng)自增 id 的策略，在大批量插入數(shù)據(jù)的情況下，不但速度慢，還會(huì)影響并發(fā)插入的性能。

因此，對(duì)于批量插入數(shù)據(jù)的語(yǔ)句，MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略：

• 語(yǔ)句執(zhí)行過程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)；
• 1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)；
• 2 個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)；
• 依此類推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。