[[426440]]

大家好，我是小龍。之前阿里二面問過我這樣一個(gè)問題：

面試官："MySQL主鍵你一般怎樣用?"

心想："嘿嘿，面試官肯定想問InnoDB引擎索引特性相關(guān)知識!“

小龍：”平時(shí)主鍵我一般用自增主鍵!因?yàn)樽栽鯥D有序，會(huì)按順序往最后插入，而UUID無序，隨機(jī)生成，隨機(jī)插入，會(huì)造成頻繁頁分裂，內(nèi)存碎片化，大量隨機(jī)IO，巴拉巴拉。。。。“

心想：“這波穩(wěn)啦!“

然后，面試官又來一套組合拳

面試官：”en!好，那你知道自增主鍵是否嚴(yán)格遞增呢?“

心想：”這還不簡單，肯定遞增啊!不對，仔細(xì)一想，有一些情況下自增主鍵是斷開的，于是“

小龍：“‘肯定不是遞增的啊!"

以為到這里結(jié)束啦，只是考考我是否實(shí)際真正用過，沒想到還是天真啦!

面試官：”那你知道為啥不是嚴(yán)格遞增的嗎?換句話來說，為何不是連續(xù)的?“

小龍：”裂開!tm還真不知道，不會(huì)真要考那么底層吧!我去“

于是罵罵咧咧結(jié)束了這個(gè)話題!

但是由于其他問題回答的也可以，還是過啦!

不過，既然遇到啦這個(gè)問題，下來查閱了資料，還是想分享給大家!

1、前文

眾所周知，由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，大量的隨機(jī)IO。自增主鍵不連續(xù)。

這是大家已經(jīng)熟知的知識點(diǎn)，但是，可能也有大部分朋友和之前的小龍一樣不知道為何自增主鍵不是嚴(yán)格遞增的?

今天這篇文章，我們就來說說這個(gè)問題，看看什么情況下自增主鍵會(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。

大家也可以自己手動(dòng)試試!

但是，看到這里，可能有朋友以為自增值存在表結(jié)構(gòu)里吧!哈哈，你這樣想就有錯(cuò)啦!

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

2.1、自增主鍵存儲(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ù)重啟之前的值。

理解了MySQL對自增值的保存策略以后，我們再看看自增值修改機(jī)制。

2.2、自增值修改機(jī)制

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

如果插入數(shù)據(jù)時(shí)id字段指定了具體的值，就直接使用語句里指定的值。

2.3、自增值新增機(jī)制

如果準(zhǔn)備插入的值>=當(dāng)前自增值，新的自增值就是“準(zhǔn)備插入的值+1”;

否則，自增值不變。

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

eg：

假設(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;
• 將傳入的行的值改成(2,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)榕龅轿ㄒ绘Ia沖突，所以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ù)了嗎?

那么，接下來跟著我的思路來看看，為何不讓自增主鍵后退吧!

首先，我們假設(shè)有兩個(gè)并行執(zhí)行的事務(wù)A、B，在申請自增值的時(shí)候，為了避免兩個(gè)事務(wù)申請到相同的自增id，肯定要加鎖，然后順序申請。

• 首先，事務(wù)A申請到 id=2，此時(shí)當(dāng)前自增值為3，由于加鎖順序申請，事務(wù)B申請到 id=3(當(dāng)前自增值)，此時(shí)，當(dāng)前自增值變?yōu)?3+1=4
• 然后，事務(wù) A、B都插入，假設(shè)事務(wù)B先插入然后成功插入，然后事務(wù)A插入發(fā)生了唯一鍵沖突
• 如果假設(shè)允許自增值后退，自增值就變?yōu)?啦，假如事務(wù)A繼續(xù)插入，申請到 id=2，成功插入，申請到 id=3，插入，由于之前事務(wù)B已經(jīng)插入 id=3的數(shù)據(jù)，此時(shí)發(fā)生主鍵沖突

那怎樣解決呢?

• 每次申請id之前，先判斷表里面是否已經(jīng)存在這個(gè)id。
• 擴(kuò)大鎖范圍，必須等事務(wù)執(zhí)行完，才能申請下一個(gè)

雖然這兩種方法可以解決，但是無疑性能極低。

于是，便讓自增值不能回退，而避免造成主鍵沖突等問題。(也許還有其他問題我沒有想到)

3、總結(jié)

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

在MySQL 5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化

事務(wù)回滾(自增值不能回退，因?yàn)椴l(fā)插入數(shù)據(jù)時(shí)，回退自增ID可能造成主鍵沖突)

唯一鍵沖突(由于表的自增值已變，但是主鍵發(fā)生沖突沒插進(jìn)去，下一次插入主鍵=現(xiàn)在變了的子增值+1，所以不連續(xù)) 

好啦，今天的分享到此結(jié)束啦!有疑問歡迎后臺留言，或者加入技術(shù)交流群大家一起討論學(xué)習(xí)!

本文轉(zhuǎn)載自微信公眾號「小龍coding」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系小龍coding公眾號。