你好,面试官 | 你觉得MySQL自增主键连续吗?
面試現(xiàn)場
叮叮叮......
面試官:“你好,我是XX面試官,請問是小龍嗎?”
小龍:“您好,面試官,我是小龍”
面試官:“好的,現(xiàn)在有空嗎,我們開始面試吧”
小龍:“嗯嗯,準(zhǔn)備好啦”
.......
other questions
.......
面試官:“我看你簡歷上有提到你對 MySQL 掌握得挺好的對吧?。”
小龍:“哈哈,還算可以吧!”
面試官:“好的,那平時對于 MySQL 主鍵你一般怎樣用的呢?能簡單聊聊嗎?”
獨(dú)白:“嘿嘿,面試官肯定又想問 InnoDB 引擎索引特性相關(guān)知識,幸好早爛熟于心了!”
小龍:“平時主鍵我一般用自增主鍵!因為自增 ID 有序,會按順序往最后插入,而 UUID 無序,隨機(jī)生成,隨機(jī)插入,會造成頻繁頁分裂,內(nèi)存碎片化,大量隨機(jī) IO,巴拉巴拉。。。。。”
獨(dú)白:“這波穩(wěn)啦![手動狗頭]”
然后,面試官又來一套組合拳。
面試官:“en!好,那你知道自增主鍵是否嚴(yán)格遞增呢?”
獨(dú)白:“這還不簡單,肯定遞增啊!不對,仔細(xì)一想,有一些情況下自增主鍵是斷開的”
小龍:“肯定不是遞增的啊!”
以為到這里結(jié)束啦,只是考考我是否實際真正用過,沒想到還是天真啦。。。
面試官:“那你知道為啥不是嚴(yán)格遞增的嗎?換句話來說,為何不是連續(xù)的?”
小龍:“裂開!tm還真不知道,不會真要考那么底層吧!我去”
面試官:“回去等通知吧。。。”
.......
哈哈,開玩笑的,接下來正式開始表演~
.......
小龍:“我們都知道,由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入,避免了頁分裂,大量的隨機(jī) IO。”
面試官:“嗯嗯”
小龍:“至于為何自增主鍵不是嚴(yán)格遞增的?為何在某些時候會出現(xiàn) 斷層 ,我們可以創(chuàng)建一個表 xl_tb 來仔細(xì)剖析一下。”
小龍:“其中 id 是自增主鍵字段、a 是唯一索引,如下表:”
CREATE?TABLE?`xl_tb`?( `id`??int(11)?NOT?NULL?AUTO_INCREMENT?, `a`??int(11)?NULL?, `b`??int(11)?NULL?, PRIMARY?KEY?(`id`), UNIQUE?INDEX?`a`?(`a`)?USING?BTREE? );小龍:“然后插入一條數(shù)據(jù)。”
insert?into?xl_tb?values(null,?1,?1)小龍:“我們再查看它的表結(jié)構(gòu)”
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)了一個 AUTO_INCREMENT=4,表示下一次插入數(shù)據(jù)時,如果需要自動生成自增值,會生成 id=4 。”
小龍:“這是我親自實驗過的”
獨(dú)白:“凸顯自己動手實踐能力,不是簡單背八股(還真做過實驗哈~)”
小龍:“看到這里,你可能會問自增值存在表結(jié)構(gòu)里嗎?當(dāng)然不是哈”
面試官:“哈哈,那你說說看呢!”
小龍:“這個嘛,還得看是什么引擎。不同的存儲引擎,自增值保存策略不一樣的。”
小龍:“ MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。”
面試官:“嗯,那 InnoDB 呢?”
小龍:“ 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ù)重啟之前的值”
面試官:“好的,你能再給我講講關(guān)于自增值是怎樣修改的嗎?簡單來說,當(dāng)你插入一條數(shù)據(jù),自增值會做出怎樣變化?”
獨(dú)白:“哈哈,幸好在我【面試筆記】中總結(jié)過”
小龍:“如果插入數(shù)據(jù)時 id 字段指定為 0、null 或未指定值,那么就把這個表當(dāng)前的 AUTO_INCREMENT 值填到自增字段。”
面試官:“那如果 id 指定了具體值呢?”
小龍:“ 如果插入數(shù)據(jù)時 id 字段指定了具體的值,id 就直接使用語句里指定的值 ”
小龍:“如果準(zhǔn)備插入的值 >= 當(dāng)前自增值,新的自增值就是 準(zhǔn)備插入的值 +1,否則,自增值不變”
面試官:“你能舉個具體例子嗎?不太理解”
獨(dú)白:“這么簡單你都不知道,當(dāng) nm 面試官”
小龍:“假設(shè),表 xl_tb 里面已經(jīng)有了(1,1,1)這條記錄,這時我再執(zhí)行一條插入數(shù)據(jù)命令:”
insert?into?xl_tb?values(null,?1,?1);?(自增id,唯一鍵a,普通字段b)小龍:“這個語句的執(zhí)行流程就是:1、執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行,傳入的這一行的值是 (0,1,1);2、InnoDB發(fā)現(xiàn)用戶沒有指定自增 id 的值,獲取表 xl_tb 當(dāng)前的自增值 ;”
小龍:“3、將傳入的行的值改成 (4,1,1);4、將表的自增值改成5;繼續(xù)執(zhí)行插入數(shù)據(jù)操作,由于已經(jīng)存在a=1的記錄,所以報 Duplicate key error,語句返回。”
小龍:“這個表的自增值改成 5,是在真正執(zhí)行插入數(shù)據(jù)的操作之前。這個語句真正執(zhí)行的時候,因為 碰到唯一鍵 a 沖突,所以 id=2 這一行并沒有插入成功,但也沒有將自增值再改回去。”
小龍:“所以,在這之后,再插入新的數(shù)據(jù)行時,拿到的自增id就是5。也就是說,出現(xiàn)了自增主鍵不連續(xù)的情況。因此,唯一鍵沖突是導(dǎo)致自增主鍵id不連續(xù)的第一種原因。”
小龍:“同樣地,事務(wù)?回滾也會產(chǎn)生類似的現(xiàn)象,這就是第二種原因。”
面試官:“照你這樣說,那為什么在出現(xiàn)唯一鍵沖突或者回滾的時候,MySQL 沒有把表 xl_tb 的自增值改回去呢?
面試官:“如果把表 xl_tb 的當(dāng)前自增值從 5 改回 4,再插入新數(shù)據(jù)的時候,不就可以生成 id=2 的一行數(shù)據(jù)了嗎?”
小龍:“這個很好理解啊,我們接著分析”
小龍:“首先,我們假設(shè)有兩個并行執(zhí)行的事務(wù) A、B,在申請自增值的時候,為了避免兩個事務(wù)申請到相同的自增 id,肯定要加鎖,然后順序申請”
小龍:“結(jié)合上圖。首先,事務(wù)A申請到 id=2,此時當(dāng)前自增值為 3,由于加鎖順序申請,事務(wù) B 申請到 id=3(當(dāng)前自增值),此時,當(dāng)前自增值變?yōu)?3+1=4 ”
小龍:“然后,事務(wù) A、B都插入,假設(shè)事務(wù)B先插入然后成功插入,然后事務(wù)A插入發(fā)生了?唯一鍵沖突”
小龍:“如果假設(shè)允許自增值后退,自增值就變?yōu)?2 啦,假如事務(wù)A繼續(xù)插入,申請到 id=2,成功插入,申請到 id=3,插入,由于之前事務(wù) B 已經(jīng)插入 id=3 的數(shù)據(jù),此時發(fā)生?主鍵沖突”
小龍:“如果要解決這個問題”
小龍:“可以每次申請id之前,先判斷表里面是否已經(jīng)存在這個id 或則 擴(kuò)大鎖范圍,必須等事務(wù)執(zhí)行完,才能申請下一個”
小龍:“雖然這兩種方法可以解決,但是無疑性能極低。”
小龍:“于是,便讓自增值不能回退,而避免造成主鍵沖突等問題。(也許還有其他問題我沒有想到)。”
面試官:“好的,已經(jīng)可以啦。順帶問一句,這些你是哪里看到的?”
小龍:“我平時有看很多付費(fèi)專欄,書籍之類的,然后總結(jié)在了我的【面試筆記】里,每次面試前拿出來復(fù)習(xí),所以面試時可以很清晰的講出來,哈哈。”
面試官:“可以可以,繼續(xù)加油!”
獨(dú)白:“不愧是我,真男人是也!”
知識總結(jié)
本期我們通過面試模擬簡單介紹了 MySQL 關(guān)于自增主鍵的理解,通過例子重點(diǎn)剖析了 自增值修改機(jī)制 、自增值修改時機(jī)與 自增值新增機(jī)制 。
面試重點(diǎn)
為什么自增主鍵不連續(xù)?
在MySQL 5.7及之前的版本,自增值保存在內(nèi)存里,并沒有持久化
事務(wù)回滾(自增值不能回退,因為并發(fā)插入數(shù)據(jù)時,回退自增ID可能造成主鍵沖突)
唯一鍵沖突(由于表的自增值已變,但是主鍵發(fā)生沖突沒插進(jìn)去,下一次插入主鍵=現(xiàn)在變了的子增值+1,所以不連續(xù))
有道無術(shù),術(shù)可成;有術(shù)無道,止于術(shù)
歡迎大家關(guān)注Java之道公眾號
好文章,我在看??
總結(jié)
以上是生活随笔為你收集整理的你好,面试官 | 你觉得MySQL自增主键连续吗?的全部內(nèi)容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 为什么俄罗斯不怕芯片卡脖子?
- 下一篇: crystal ball 软件_推荐10