一、前言

• 眾所周知，由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，大量的隨機(jī) IO，自增主鍵不連續(xù)。這應(yīng)該是大家已經(jīng)熟知的知識(shí)點(diǎn)，但是也應(yīng)該還有不少的朋友不知道為何自增主鍵不是嚴(yán)格遞增的？什么情況下自增主鍵會(huì)出現(xiàn) “斷層”？
• 為了更加形象，這里創(chuàng)建一個(gè)表 xl_tb，其中 id 是自增主鍵字段，a 是唯一索引，然后插入一條數(shù)據(jù)，查看它的表結(jié)構(gòu)：

CREATE TABLE `NewTable` ( `id` int(11) NOT NULL AUTO_INCREMENT , `a` int(11) NULL , `b` int(11) NULL , PRIMARY KEY (`id`), UNIQUE INDEX `a` (`a`) USING BTREE ); insert into xl_tb values(null, 1, 1) mysql> show create table xl_tb\G; *************************** 1. row ***************************Table: xl_tb Create Table: CREATE TABLE `xl_tb` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `a` (`a`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.04 sec)

• 可以看到，表定義里面出現(xiàn)了一個(gè)AUTO_INCREMENT=4，表示下一次插入數(shù)據(jù)時(shí)，如果需要自動(dòng)生成自增值，會(huì)生成 id=4。看到這里，可能有朋友會(huì)以為自增值存在表結(jié)構(gòu)里呢？要是你這樣想，那就又錯(cuò)啦。

二、自增主鍵為何不連續(xù)

① 自增主鍵存儲(chǔ)策略

• 其實(shí)，不同的存儲(chǔ)引擎，自增值保存策略不一樣的：
• • 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ù)重啟之前的值。

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

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

③ 自增值新增機(jī)制

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

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

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

insert into t values(null, 1, 1); (自增id,唯一鍵a,普通字段b)

• 這個(gè)語句的執(zhí)行流程就是：
• • 執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行，傳入的這一行的值是(0,1,1)；
• • InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值，獲取表 xl_tb 當(dāng)前的自增值 4；
• • 將傳入的行的值改成(4,1,1)；
• • 將表的自增值改成 5；
• • 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在 a=1 的記錄，所以報(bào) Duplicate key error，語句返回。
• 這個(gè)表的自增值改成 5，是在真正執(zhí)行插入數(shù)據(jù)的操作之前，這個(gè)語句真正執(zhí)行的時(shí)候，因?yàn)榕龅轿ㄒ绘I a 沖突，所以 id=2 這一行并沒有插入成功，但也沒有將自增值再改回去。
• 因此在這之后，再插入新的數(shù)據(jù)行時(shí)，拿到的自增 id 就是 5，也就是說，出現(xiàn)了自增主鍵不連續(xù)的情況。因此，唯一鍵沖突是導(dǎo)致自增主鍵 id 不連續(xù)的第一種原因。同樣地，事務(wù)回滾也會(huì)產(chǎn)生類似的現(xiàn)象，這就是第二種原因。
• 這時(shí)，你可能會(huì)想，為什么在出現(xiàn)唯一鍵沖突或者回滾的時(shí)候，MySQL 沒有把表 xl_tb 的自增值改回去呢？如果把表 xl_tb 的當(dāng)前自增值從 5 改回 4，再插入新數(shù)據(jù)的時(shí)候，不就可以生成 id=2 的一行數(shù)據(jù)了嗎？那么，接下來繼續(xù)來看看，為何不讓自增主鍵后退吧？
• 首先，假設(shè)有兩個(gè)并行執(zhí)行的事務(wù) A、B，在申請(qǐng)自增值的時(shí)候，為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增 id，肯定要加鎖，然后順序申請(qǐng)：

事務(wù)申請(qǐng)id當(dāng)前自增值操作假設(shè)現(xiàn)象允許自增值后退繼續(xù)申請(qǐng)id繼續(xù)申請(qǐng)id

A	2	3	插入	唯一鍵沖突，插入失效	變?yōu)?	2	3(主鍵沖突)
B	3	4	插入	成功插入	變?yōu)?	-	-

• 分析：
• • 首先，事務(wù)A申請(qǐng)到 id=2，此時(shí)當(dāng)前自增值為3，由于加鎖順序申請(qǐng)，事務(wù)B申請(qǐng)到 id=3（當(dāng)前自增值），此時(shí)，當(dāng)前自增值變?yōu)?3+1=4；
• • 然后，事務(wù) A、B 都插入，假設(shè)事務(wù) B 先插入然后成功插入，然后事務(wù) A 插入發(fā)生了唯一鍵沖突；
• • 如果假設(shè)允許自增值后退，自增值就變?yōu)?2 啦，假如事務(wù) A 繼續(xù)插入，申請(qǐng)到 id=2，成功插入，申請(qǐng)到 id=3，插入，由于之前事務(wù) B 已經(jīng)插入 id=3 的數(shù)據(jù)，此時(shí)發(fā)生主鍵沖突。
• 那怎樣解決呢？
• • 每次申請(qǐng) id 之前，先判斷表里面是否已經(jīng)存在這個(gè) id；
• • 擴(kuò)大鎖范圍，必須等事務(wù)執(zhí)行完，才能申請(qǐng)下一個(gè)；
• 雖然這兩種方法可以解決，但是無疑性能極低。于是，便讓自增值不能回退，而避免造成主鍵沖突等問題。

三、總結(jié)

• 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里，并沒有持久化；
• 事務(wù)回滾（自增值不能回退，因?yàn)椴l(fā)插入數(shù)據(jù)時(shí)，回退自增 id 可能造成主鍵沖突）；
• 唯一鍵沖突（由于表的自增值已變，但是主鍵發(fā)生沖突沒插進(jìn)去，下一次插入主鍵=現(xiàn)在變了的子增值 +1，所以不連續(xù)）。
• 這就是為什么自增主鍵不連續(xù)的原因所在。

總結(jié)

以上是生活随笔為你收集整理的MySQL之深入解析自增主键为何不连续的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。