自增主鍵為什么不是連續(xù)的

今天我們就來說說這個問題，看看什么情況下自增主鍵會出現(xiàn) “空洞”?

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

CREATE TABLE `t` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`c` int(11) DEFAULTNULL,
`d` int(11) DEFAULTNULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪兒?

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

圖1 自動生成的AUTO_INCREMENT值

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

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

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

• MyISAM引擎的自增值保存在數(shù)據(jù)文件中。
• InnoDB引擎的自增值，其實是保存在了內(nèi)存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才實現(xiàn)了“如果發(fā)生重啟，表的自增值可以恢復(fù)為MySQL重啟前的值”，具體情況是：在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值max(id)，然后將max(id)+1作為這個表當(dāng)前的自增值。

舉例來說，如果一個表當(dāng)前數(shù)據(jù)行里最大的id是10，AUTO_INCREMENT=11。這時候，我們刪除id=10的行，AUTO_INCREMENT還是11。但如果馬上重啟實例，重啟后這個表的AUTO_INCREMENT就會變成10。

也就是說，MySQL重啟可能會修改一個表的AUTO_INCREMENT的值。

在MySQL 8.0版本，將自增值的變更記錄在了redo log中，重啟的時候依靠redo log恢復(fù) 重啟之前的值。 理解了MySQL對自增值的保存策略以后，我們再看看自增值修改機(jī)制。

自增值修改機(jī)制

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

1. 如果插入數(shù)據(jù)時id字段指定為0、null 或未指定值，那么就把這個表當(dāng)前的AUTO_INCREMENT值填到自增字段;

2. 如果插入數(shù)據(jù)時id字段指定了具體的值，就直接使用語句里指定的值。根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會有所不同。假設(shè)，某次要插入的值是X，當(dāng)前的自增值是Y。

1. 如果X

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

自增值的修改時機(jī)

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

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

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

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

2. InnoDB發(fā)現(xiàn)用戶沒有指定自增id的值，獲取表t當(dāng)前的自增值2;

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

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

5. 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在c=1的記錄，所以報Duplicate keyerror，語句返回。

對應(yīng)的執(zhí)行流程圖如下：

圖2 insert(null, 1,1)唯一鍵沖突

可以看到，這個表的自增值改成3，是在真正執(zhí)行插入數(shù)據(jù)的操作之前。這個語句真正執(zhí)行的時候，因為碰到唯一鍵c沖突，所以id=2這一行并沒有插入成功，但也沒有將自增值再改回去。所以，在這之后，

唯一鍵沖突是導(dǎo)致自增主鍵id不連續(xù)的第一種原因。同樣地，事務(wù)回滾也會產(chǎn)生類似的現(xiàn)象，這就是第二種原因。

自增id用完怎么辦?

MySQL里有很多自增的id，每個自增id都是定義了初始值，然后不停地往上加步長。雖然自然數(shù)是沒有上限的，但是在計算機(jī)里，只要定義了表示這個數(shù)的字節(jié)長度，那它就有上限。比如，無符號整型(unsigned int)是4個字節(jié)，上限就是232 -1。

既然自增id有上限，就有可能被用完。但是，自增id用完了會怎么樣呢?

表定義自增值id

表定義的自增值達(dá)到上限后的邏輯是：再申請下一個id時，得到的值保持不變。 我們可以通過下面這個語句序列驗證一下：

create table t(id int unsigned auto_increment primary key) 
auto_increment=4294967295;
insert into t values(null);
//成功插入一行 4294967295
show create table t;
/* CREATE TABLE `t` (
`id` int(10) unsigned NOTNULLAUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/
insert into t values(null);
//Duplicate entry '4294967295' for key 'PRIMARY'

可以看到，第一個insert語句插入數(shù)據(jù)成功后，這個表的AUTO_INCREMENT沒有改變(還是4294967295)，就導(dǎo)致了第二個insert語句又拿到相同的自增id值，再試圖執(zhí)行插入語句，報主

鍵沖突錯誤。

4294967295不是一個特別大的數(shù)，對于一個頻繁插入刪除數(shù)據(jù)的表來說，是可能會被用完的。因此在建表的時候你需要考察你的表是否有可能達(dá)到這個上限，如果有可能，就應(yīng)該創(chuàng) 建成8個字節(jié)的bigint unsigned。

InnoDB系統(tǒng)自增row_id

如果你創(chuàng)建的InnoDB表沒有指定主鍵，那么InnoDB會給你創(chuàng)建一個不可見的，長度為6個字節(jié)的row_id。InnoDB維護(hù)了一個全局的dict_sys.row_id值，所有無主鍵的InnoDB表，每插入一行數(shù)據(jù)，都將當(dāng)前的dict_sys.row_id值作為要插入數(shù)據(jù)的row_id，然后把dict_sys.row_id的值加1。

實際上，在代碼實現(xiàn)時row_id是一個長度為8字節(jié)的無符號長整型(bigint unsigned)。但是，InnoDB在設(shè)計時，給row_id留的只是6個字節(jié)的長度，這樣寫到數(shù)據(jù)表中時只放了最后6個字節(jié)，所以row_id能寫到數(shù)據(jù)表中的值，就有兩個特征：

1. row_id寫入表中的值范圍，是從0到248 -1;

2. 當(dāng)dict_sys.row_id=248 時，如果再有插入數(shù)據(jù)的行為要來申請row_id，拿到以后再取最后6個

字節(jié)的話就是0。

也就是說，寫入表的row_id是從0開始到248 -1。達(dá)到上限后，下一個值就是0，然后繼續(xù)循環(huán)。當(dāng)然，這個值本身已經(jīng)很大了，但是如果一個MySQL實例跑得足夠久的話，還是可能達(dá)到這個上限的。在InnoDB邏輯里，申請到row_id=N后，就將這行數(shù)據(jù)寫入表中;如果表中已經(jīng)存在row_id=N的行，新寫入的行就會覆蓋原有的行。