一、為什么用自增列作為主鍵

1、如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會(huì)選擇主鍵作為聚集索引。

如果沒(méi)有顯式定義主鍵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引。

如果也沒(méi)有這樣的唯一索引，則InnoDB會(huì)選擇內(nèi)置6字節(jié)長(zhǎng)的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫(xiě)入而主鍵遞增，這個(gè)ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

2、數(shù)據(jù)記錄本身被存于主索引(一顆B+Tree)的葉子節(jié)點(diǎn)上，這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)(大小為一個(gè)內(nèi)存頁(yè)或磁盤(pán)頁(yè))的各條數(shù)據(jù)記錄按主鍵順序存放

因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子(InnoDB默認(rèn)為15/16)，則開(kāi)辟一個(gè)新的頁(yè)(節(jié)點(diǎn))

3、如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫(xiě)滿(mǎn)，就會(huì)自動(dòng)開(kāi)辟一個(gè)新的頁(yè)

4、如果使用非自增主鍵(如果身份證號(hào)或?qū)W號(hào)等)，由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置

此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫(xiě)到磁盤(pán)上而從緩存中清掉，此時(shí)又要從磁盤(pán)上讀回來(lái)，這增加了很多開(kāi)銷(xiāo)

同時(shí)頻繁的移動(dòng)、分頁(yè)操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面。

二、為什么使用數(shù)據(jù)索引能提高效率

數(shù)據(jù)索引的存儲(chǔ)是有序的

在有序的情況下，通過(guò)索引查詢(xún)一個(gè)數(shù)據(jù)是無(wú)需遍歷索引記錄的

極端情況下，數(shù)據(jù)索引的查詢(xún)效率為二分法查詢(xún)效率，趨近于 log2(N)

三、B+樹(shù)索引和哈希索引的區(qū)別

B+樹(shù)是一個(gè)平衡的多叉樹(shù)，從根節(jié)點(diǎn)到每個(gè)葉子節(jié)點(diǎn)的高度差值不超過(guò)1，而且同層級(jí)的節(jié)點(diǎn)間有指針相互鏈接，是有序的，如下圖：

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時(shí)不需要類(lèi)似B+樹(shù)那樣從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)逐級(jí)查找，只需一次哈希算法即可,是無(wú)序的，如下圖所示：

四、哈希索引的優(yōu)勢(shì)：

等值查詢(xún)，哈希索引具有絕對(duì)優(yōu)勢(shì)(前提是：沒(méi)有大量重復(fù)鍵值，如果大量重復(fù)鍵值時(shí)，哈希索引的效率很低，因?yàn)榇嬖谒^的哈希碰撞問(wèn)題。)

五、哈希索引不適用的場(chǎng)景：

不支持范圍查詢(xún)

不支持索引完成排序

不支持聯(lián)合索引的最左前綴匹配規(guī)則

通常，B+樹(shù)索引結(jié)構(gòu)適用于絕大多數(shù)場(chǎng)景，像下面這種場(chǎng)景用哈希索引才更有優(yōu)勢(shì)：

在HEAP表中，如果存儲(chǔ)的數(shù)據(jù)重復(fù)度很低(也就是說(shuō)基數(shù)很大)，對(duì)該列數(shù)據(jù)以等值查詢(xún)?yōu)橹鳎瑳](méi)有范圍查詢(xún)、沒(méi)有排序的時(shí)候，特別適合采用哈希索引，例如這種SQL：

#?僅等值查詢(xún)

select?id,?name?from?table?where?name='李明';?

而常用的 InnoDB 引擎中默認(rèn)使用的是B+樹(shù)索引，它會(huì)實(shí)時(shí)監(jiān)控表上索引的使用情況。

如果認(rèn)為建立哈希索引可以提高查詢(xún)效率，則自動(dòng)在內(nèi)存中的“自適應(yīng)哈希索引緩沖區(qū)”建立哈希索引(在InnoDB中默認(rèn)開(kāi)啟自適應(yīng)哈希索引)。

通過(guò)觀察搜索模式，MySQL會(huì)利用index key的前綴建立哈希索引，如果一個(gè)表幾乎大部分都在緩沖池中，那么建立一個(gè)哈希索引能夠加快等值查詢(xún)。

注意：在某些工作負(fù)載下，通過(guò)哈希索引查找?guī)?lái)的性能提升遠(yuǎn)大于額外的監(jiān)控索引搜索情況和保持這個(gè)哈希表結(jié)構(gòu)所帶來(lái)的開(kāi)銷(xiāo)。

但某些時(shí)候，在負(fù)載高的情況下，自適應(yīng)哈希索引中添加的read/write鎖也會(huì)帶來(lái)競(jìng)爭(zhēng)，比如高并發(fā)的join操作。like操作和%的通配符操作也不適用于自適應(yīng)哈希索引，可能要關(guān)閉自適應(yīng)哈希索引。

以上就是“數(shù)據(jù)庫(kù)面試題目及答案的詳細(xì)內(nèi)容”，你能回答上來(lái)嗎?如果想要了解更多的Java面試題相關(guān)內(nèi)容，可以關(guān)注動(dòng)力節(jié)點(diǎn)Java官網(wǎng)。