MySQL自增主鍵為何不是連續的呢？
大家好，我是小龍。之前阿里二面問過我這樣一個問題：
面試官："MySQL主鍵你一般怎樣用?"
心想："嘿嘿，面試官肯定想問InnoDB引擎索引特性相關知識!“
小龍：”平時主鍵我一般用自增主鍵!因為自增ID有序，會按順序往最后插入，而UUID無序，隨機生成，隨機插入，會造成頻繁頁分裂，內存碎片化，大量隨機IO，巴拉巴拉。。。。“
心想：“這波穩啦!“
然后，面試官又來一套組合拳
面試官：”en!好，那你知道自增主鍵是否嚴格遞增呢?“
心想：”這還不簡單，肯定遞增啊!不對，仔細一想，有一些情況下自增主鍵是斷開的，于是“
小龍：“‘肯定不是遞增的啊!"
以為到這里結束啦，只是考考我是否實際真正用過，沒想到還是天真啦!
面試官：”那你知道為啥不是嚴格遞增的嗎?換句話來說，為何不是連續的?“
小龍：”裂開!tm還真不知道，不會真要考那么底層吧!我去“
于是罵罵咧咧結束了這個話題!
但是由于其他問題回答的也可以，還是過啦!
不過，既然遇到啦這個問題，下來查閱了資料，還是想分享給大家!
1、前文
眾所周知，由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，大量的隨機IO。自增主鍵不連續。
這是大家已經熟知的知識點，但是，可能也有大部分朋友和之前的小龍一樣不知道為何自增主鍵不是嚴格遞增的?
今天這篇文章，我們就來說說這個問題，看看什么情況下自增主鍵會出現 “斷層”?
為了更加形象，這里創建一個表xl_tb，其中id是自增主鍵字段、a是唯一索引，然后插入一條數據，我們再查看它的表結構。
- 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)
可以看到，表定義里面出現了一個AUTO_INCREMENT=4，表示下一次插入數據時，如果需要自動生成自增值，會生成id=4。
大家也可以自己手動試試!
但是，看到這里，可能有朋友以為自增值存在表結構里吧!哈哈，你這樣想就有錯啦!
2、自增主鍵為何不連續
2.1、自增主鍵存儲策略接下來，我們一起看看自增值保存在哪里吧!
其實，不同的存儲引擎，自增值保存策略不一樣的。
- 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恢復重啟之前的值。
理解了MySQL對自增值的保存策略以后，我們再看看自增值修改機制。
2.2、自增值修改機制
如果插入數據時id字段指定為0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT值填到自增字段;
如果插入數據時id字段指定了具體的值，就直接使用語句里指定的值。
2.3、自增值新增機制
如果準備插入的值>=當前自增值，新的自增值就是“準備插入的值+1”;
否則，自增值不變。
2.4、自增值的修改時機
eg：
假設，表xl_tb里面已經有了(1,1,1)這條記錄，這時我再執行一條插入數據命令：
- insert into t values(null, 1, 1); (自增id,唯一鍵a,普通字段b)
這個語句的執行流程就是：
- 執行器調用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1);
- InnoDB發現用戶沒有指定自增id的值，獲取表xl_tb當前的自增值4;
- 將傳入的行的值改成(2,1,1);
- 將表的自增值改成5;
- 繼續執行插入數據操作，由于已經存在a=1的記錄，所以報Duplicate key error，語句返回。
這個表的自增值改成5，是在真正執行插入數據的操作之前。這個語句真正執行的時候，因為碰到唯一鍵a沖突，所以id=2這一行并沒有插入成功，但也沒有將自增值再改回去。
所以，在這之后，再插入新的數據行時，拿到的自增id就是5。也就是說，出現了自增主鍵不連續的情況。因此，唯一鍵沖突是導致自增主鍵id不連續的第一種原因。
同樣地，事務回滾也會產生類似的現象，這就是第二種原因。
這時，你可能會想，為什么在出現唯一鍵沖突或者回滾的時候，MySQL沒有把表xl_tb的自增值改回去呢?如果把表xl_tb的當前自增值從5改回4，再插入新數據的時候，不就可以生成id=2的一行數據了嗎?
那么，接下來跟著我的思路來看看，為何不讓自增主鍵后退吧!
首先，我們假設有兩個并行執行的事務A、B，在申請自增值的時候，為了避免兩個事務申請到相同的自增id，肯定要加鎖，然后順序申請。
- 首先，事務A申請到 id=2，此時當前自增值為3，由于加鎖順序申請，事務B申請到 id=3(當前自增值)，此時，當前自增值變為 3+1=4
- 然后，事務 A、B都插入，假設事務B先插入然后成功插入，然后事務A插入發生了唯一鍵沖突
- 如果假設允許自增值后退，自增值就變為2啦，假如事務A繼續插入，申請到 id=2，成功插入，申請到 id=3，插入，由于之前事務B已經插入 id=3的數據，此時發生主鍵沖突
那怎樣解決呢?
- 每次申請id之前，先判斷表里面是否已經存在這個id。
- 擴大鎖范圍，必須等事務執行完，才能申請下一個
雖然這兩種方法可以解決，但是無疑性能極低。
于是，便讓自增值不能回退，而避免造成主鍵沖突等問題。(也許還有其他問題我沒有想到)
3、總結
為什么自增主鍵不連續?
在MySQL 5.7及之前的版本，自增值保存在內存里，并沒有持久化
事務回滾(自增值不能回退，因為并發插入數據時，回退自增ID可能造成主鍵沖突)
唯一鍵沖突(由于表的自增值已變，但是主鍵發生沖突沒插進去，下一次插入主鍵=現在變了的子增值+1，所以不連續)
好啦，今天的分享到此結束啦!有疑問歡迎后臺留言，或者加入技術交流群大家一起討論學習!
本文轉載自微信公眾號「小龍coding」，可以通過以下二維碼關注。轉載本文請聯系小龍coding公眾號。
