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

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

我們在使用自增主鍵統(tǒng)計(jì)數(shù)據(jù)庫的數(shù)據(jù)量時(shí)，也會經(jīng)常使用id的最大值與最小值之間的差值作為數(shù)據(jù)庫當(dāng)前已有數(shù)據(jù)的條數(shù)，但是這種統(tǒng)計(jì)方式是否正確？是否存在誤差？

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

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

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

1 自增值的存儲

在如上的空表 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í)，如果需要自動生成自增值，會生成 id=2。

不同的引擎對于自增至的保存策略不同：

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

• 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時(shí)候，都會去找自增值的最大值 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 就會變成 10。也就是說，MySQL 重啟可能會修改一個(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 字段指定了具體的值，就直接使用語句里指定的值。

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

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

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

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

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

• 如果準(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è)語句的執(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，語句返回。

可以看到，這個(gè)表的自增值修改為3之后也不會再回退，之后再插入拿到的自增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)

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

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

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

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

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

3.3 批量申請自增id策略

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

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

4 自增鎖優(yōu)化

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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