眾所周知,自增主鍵可以讓聚集索引盡量地保持遞增順序插入,避免了隨機(jī)查詢(xún),從而提高了查詢(xún)效率
但實(shí)際上,MySQL 的自增主鍵并不能保證一定是連續(xù)遞增的。
下面舉個(gè)例子來(lái)看下,如下所示創(chuàng)建一張表:
自增值保存在哪里?
使用 insert into test_pk values(null, 1, 1) 插入一行數(shù)據(jù),再執(zhí)行 show create table 命令來(lái)看一下表的結(jié)構(gòu)定義:
上述表的結(jié)構(gòu)定義存放在后綴名為 .frm 的本地文件中,在 MySQL 安裝目錄下的 data 文件夾下可以找到這個(gè) .frm 文件:
img
從上述表結(jié)構(gòu)可以看到,表定義里面出現(xiàn)了一個(gè) AUTO_INCREMENT=2,表示下一次插入數(shù)據(jù)時(shí),如果需要自動(dòng)生成自增值,會(huì)生成 id = 2。
但需要注意的是,自增值并不會(huì)保存在這個(gè)表結(jié)構(gòu)也就是 .frm 文件中,不同的引擎對(duì)于自增值的保存策略不同:
1)MyISAM 引擎的自增值保存在數(shù)據(jù)文件中
2)InnoDB 引擎的自增值,其實(shí)是保存在了內(nèi)存里,并沒(méi)有持久化。第一次打開(kāi)表的時(shí)候,都會(huì)去找自增值的最大值 max(id),然后將 max(id)+1 作為這個(gè)表當(dāng)前的自增值。
舉個(gè)例子:我們現(xiàn)在表里當(dāng)前數(shù)據(jù)行里最大的 id 是 1,AUTO_INCREMENT=2,對(duì)吧。這時(shí)候,我們刪除 id=1 的行,AUTO_INCREMENT 還是 2。
但如果馬上重啟 MySQL 實(shí)例,重啟后這個(gè)表的 AUTO_INCREMENT 就會(huì)變成 1。?也就是說(shuō),MySQL 重啟可能會(huì)修改一個(gè)表的 AUTO_INCREMENT 的值。
以上,是在我本地 MySQL 5.x 版本的實(shí)驗(yàn),實(shí)際上,到了 MySQL 8.0 版本后,自增值的變更記錄被放在了 redo log 中,提供了自增值持久化的能力,也就是實(shí)現(xiàn)了“如果發(fā)生重啟,表的自增值可以根據(jù) redo log 恢復(fù)為 MySQL 重啟前的值”
也就是說(shuō)對(duì)于上面這個(gè)例子來(lái)說(shuō),重啟實(shí)例后這個(gè)表的 AUTO_INCREMENT 仍然是 2。
理解了 MySQL 自增值到底保存在哪里以后,我們?cè)賮?lái)看看自增值的修改機(jī)制,并以此引出第一種自增值不連續(xù)的場(chǎng)景。
自增值不連續(xù)場(chǎng)景 1
在 MySQL 里面,如果字段 id 被定義為 AUTO_INCREMENT,在插入一行數(shù)據(jù)的時(shí)候,自增值的行為如下:
如果插入數(shù)據(jù)時(shí) id 字段指定為 0、null 或未指定值,那么就把這個(gè)表當(dāng)前的 AUTO_INCREMENT 值填到自增字段;
如果插入數(shù)據(jù)時(shí) id 字段指定了具體的值,就直接使用語(yǔ)句里指定的值。
根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系,自增值的變更結(jié)果也會(huì)有所不同。假設(shè)某次要插入的值是 insert_num,當(dāng)前的自增值是 autoIncrement_num:
如果 insert_num < autoIncrement_num,那么這個(gè)表的自增值不變
如果 insert_num >= autoIncrement_num,就需要把當(dāng)前自增值修改為新的自增值
也就是說(shuō),如果插入的 id 是 100,當(dāng)前的自增值是 90,insert_num >= autoIncrement_num,那么自增值就會(huì)被修改為新的自增值即 101
一定是這樣嗎?
非也~
了解過(guò)分布式 id 的小伙伴一定知道,為了避免兩個(gè)庫(kù)生成的主鍵發(fā)生沖突,我們可以讓一個(gè)庫(kù)的自增 id 都是奇數(shù),另一個(gè)庫(kù)的自增 id 都是偶數(shù)
這個(gè)奇數(shù)偶數(shù)其實(shí)是通過(guò) auto_increment_offset 和 auto_increment_increment 這兩個(gè)參數(shù)來(lái)決定的,這倆分別用來(lái)表示自增的初始值和步長(zhǎng),默認(rèn)值都是 1。
所以,上面的例子中生成新的自增值的步驟實(shí)際是這樣的:從 auto_increment_offset 開(kāi)始,以 auto_increment_increment 為步長(zhǎng),持續(xù)疊加,直到找到第一個(gè)大于 100 的值,作為新的自增值。
所以,這種情況下,自增值可能會(huì)是 102,103 等等之類(lèi)的,就會(huì)導(dǎo)致不連續(xù)的主鍵 id。
更遺憾的是,即使在自增初始值和步長(zhǎng)這兩個(gè)參數(shù)都設(shè)置為 1 的時(shí)候,自增主鍵 id 也不一定能保證主鍵是連續(xù)的
自增值不連續(xù)場(chǎng)景 2
舉個(gè)例子,我們現(xiàn)在往表里插入一條 (null,1,1) 的記錄,生成的主鍵是 1,AUTO_INCREMENT= 2,對(duì)吧
img
這時(shí)我再執(zhí)行一條插入 (null,1,1) 的命令,很顯然會(huì)報(bào)錯(cuò) Duplicate entry,因?yàn)槲覀冊(cè)O(shè)置了一個(gè)唯一索引字段 a:
img
但是,你會(huì)驚奇的發(fā)現(xiàn),雖然插入失敗了,但自增值仍然從 2 增加到了 3!
這是為啥?
我們來(lái)分析下這個(gè) insert 語(yǔ)句的執(zhí)行流程:
執(zhí)行器調(diào)用 InnoDB 引擎接口準(zhǔn)備插入一行記錄 (null,1,1);
InnoDB 發(fā)現(xiàn)用戶(hù)沒(méi)有指定自增 id 的值,則獲取表 test_pk 當(dāng)前的自增值 2;
將傳入的記錄改成 (2,1,1);
將表的自增值改成 3;
繼續(xù)執(zhí)行插入數(shù)據(jù)操作,由于已經(jīng)存在 a=1 的記錄,所以報(bào) Duplicate key error,語(yǔ)句返回
可以看到,自增值修改的這個(gè)操作,是在真正執(zhí)行插入數(shù)據(jù)的操作之前。
這個(gè)語(yǔ)句真正執(zhí)行的時(shí)候,因?yàn)榕龅轿ㄒ绘I a 沖突,所以 id = 2 這一行并沒(méi)有插入成功,但也沒(méi)有將自增值再改回去。所以,在這之后,再插入新的數(shù)據(jù)行時(shí),拿到的自增 id 就是 3。也就是說(shuō),出現(xiàn)了自增主鍵不連續(xù)的情況。
至此,我們已經(jīng)羅列了兩種自增主鍵不連續(xù)的情況:
自增初始值和自增步長(zhǎng)設(shè)置不為 1
唯一鍵沖突
除此之外,事務(wù)回滾也會(huì)導(dǎo)致這種情況
自增值不連續(xù)場(chǎng)景 3
我們現(xiàn)在表里有一行 (1,1,1) 的記錄,AUTO_INCREMENT = 3:
img
我們先插入一行數(shù)據(jù) (null, 2, 2),也就是 (3, 2, 2) 嘛,并且 AUTO_INCREMENT 變?yōu)?4:
img
再去執(zhí)行這樣一段 SQL:
img
雖然我們插入了一條 (null, 3, 3) 記錄,但是使用 rollback 進(jìn)行回滾了,所以數(shù)據(jù)庫(kù)中是沒(méi)有這條記錄的:
在這種事務(wù)回滾的情況下,自增值并沒(méi)有同樣發(fā)生回滾!如下圖所示,自增值仍然固執(zhí)地從 4 增加到了 5:
所以這時(shí)候我們?cè)偃ゲ迦胍粭l數(shù)據(jù)(null, 3, 3)的時(shí)候,主鍵 id 就會(huì)被自動(dòng)賦為 5 了:
那么,為什么在出現(xiàn)唯一鍵沖突或者回滾的時(shí)候,MySQL 沒(méi)有把表的自增值改回去呢?回退回去的話(huà)不就不會(huì)發(fā)生自增 id 不連續(xù)了嗎?
事實(shí)上,這么做的主要原因是為了提高性能。
我們直接用反證法來(lái)驗(yàn)證:假設(shè) MySQL 在事務(wù)回滾的時(shí)候會(huì)把自增值改回去,會(huì)發(fā)生什么?
現(xiàn)在有兩個(gè)并行執(zhí)行的事務(wù) A 和 B,在申請(qǐng)自增值的時(shí)候,為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增 id,肯定要加鎖,然后順序申請(qǐng),對(duì)吧。
假設(shè)事務(wù) A 申請(qǐng)到了 id = 1, 事務(wù) B 申請(qǐng)到 id=2,那么這時(shí)候表 t 的自增值是3,之后繼續(xù)執(zhí)行。
事務(wù) B 正確提交了,但事務(wù) A 出現(xiàn)了唯一鍵沖突,也就是 id = 1 的那行記錄插入失敗了,那如果允許事務(wù) A 把自增 id 回退,也就是把表的當(dāng)前自增值改回 1,那么就會(huì)出現(xiàn)這樣的情況:表里面已經(jīng)有 id = 2 的行,而當(dāng)前的自增 id 值是 1。
接下來(lái),繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到 id=2。這時(shí),就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。
而為了解決這個(gè)主鍵沖突,有兩種方法:
每次申請(qǐng) id 之前,先判斷表里面是否已經(jīng)存在這個(gè) id,如果存在,就跳過(guò)這個(gè) id
把自增 id 的鎖范圍擴(kuò)大,必須等到一個(gè)事務(wù)執(zhí)行完成并提交,下一個(gè)事務(wù)才能再申請(qǐng)自增 id
很顯然,上述兩個(gè)方法的成本都比較高,會(huì)導(dǎo)致性能問(wèn)題。而究其原因呢,是我們假設(shè)的這個(gè) “允許自增 id 回退”。
因此,InnoDB 放棄了這個(gè)設(shè)計(jì),語(yǔ)句執(zhí)行失敗也不回退自增 id。也正是因?yàn)檫@樣,所以才只保證了自增 id 是遞增的,但不保證是連續(xù)的。
綜上,已經(jīng)分析了三種自增值不連續(xù)的場(chǎng)景,還有第四種場(chǎng)景:批量插入數(shù)據(jù)。
自增值不連續(xù)場(chǎng)景 4
對(duì)于批量插入數(shù)據(jù)的語(yǔ)句,MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略:
語(yǔ)句執(zhí)行過(guò)程中,第一次申請(qǐng)自增 id,會(huì)分配 1 個(gè);
1 個(gè)用完以后,這個(gè)語(yǔ)句第二次申請(qǐng)自增 id,會(huì)分配 2 個(gè);
2 個(gè)用完以后,還是這個(gè)語(yǔ)句,第三次申請(qǐng)自增 id,會(huì)分配 4 個(gè);
依此類(lèi)推,同一個(gè)語(yǔ)句去申請(qǐng)自增 id,每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。
注意,這里說(shuō)的批量插入數(shù)據(jù),不是在普通的 insert 語(yǔ)句里面包含多個(gè) value 值!??!,因?yàn)檫@類(lèi)語(yǔ)句在申請(qǐng)自增 id 的時(shí)候,是可以精確計(jì)算出需要多少個(gè) id 的,然后一次性申請(qǐng),申請(qǐng)完成后鎖就可以釋放了。
而對(duì)于 insert … select、replace … select 和 load data 這種類(lèi)型的語(yǔ)句來(lái)說(shuō),MySQL 并不知道到底需要申請(qǐng)多少 id,所以就采用了這種批量申請(qǐng)的策略,畢竟一個(gè)一個(gè)申請(qǐng)的話(huà)實(shí)在太慢了。
舉個(gè)例子,假設(shè)我們現(xiàn)在這個(gè)表有下面這些數(shù)據(jù):
我們創(chuàng)建一個(gè)和當(dāng)前表 test_pk 有相同結(jié)構(gòu)定義的表 test_pk2:
img
然后使用 insert...select 往 teset_pk2 表中批量插入數(shù)據(jù):
可以看到,成功導(dǎo)入了數(shù)據(jù)。
再來(lái)看下 test_pk2 的自增值是多少:
如上分析,是 8 而不是 6
具體來(lái)說(shuō),insert…select 實(shí)際上往表中插入了 5 行數(shù)據(jù) (1 1)(2 2)(3 3)(4 4)(5 5)。但是,這五行數(shù)據(jù)是分三次申請(qǐng)的自增 id,結(jié)合批量申請(qǐng)策略,每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍,所以:
第一次申請(qǐng)到了一個(gè) id:id=1
第二次被分配了兩個(gè) id:id=2 和 id=3
第三次被分配到了 4 個(gè) id:id=4、id = 5、id = 6、id=7
由于這條語(yǔ)句實(shí)際只用上了 5 個(gè) id,所以 id=6 和 id=7 就被浪費(fèi)掉了。之后,再執(zhí)行 insert into test_pk2 values(null,6,6),實(shí)際上插入的數(shù)據(jù)就是(8,6,6):
小結(jié)
總結(jié)下自增值不連續(xù)的四個(gè)場(chǎng)景:
自增初始值和自增步長(zhǎng)設(shè)置不為 1
唯一鍵沖突
事務(wù)回滾
批量插入(如 insert...select 語(yǔ)句)
審核編輯:劉清
-
Auto
+關(guān)注
關(guān)注
0文章
42瀏覽量
15246 -
MySQL
+關(guān)注
關(guān)注
1文章
794瀏覽量
26353 -
MYSQL數(shù)據(jù)庫(kù)
+關(guān)注
關(guān)注
0文章
95瀏覽量
9372 -
mysql觸發(fā)器
+關(guān)注
關(guān)注
0文章
6瀏覽量
1105
原文標(biāo)題:美團(tuán)面試:MySQL 自增主鍵一定是連續(xù)的嗎?
文章出處:【微信號(hào):小林coding,微信公眾號(hào):小林coding】歡迎添加關(guān)注!文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
評(píng)論