阿里一面，唯一索引和普通索引如何選擇？

因為明年也要準(zhǔn)備秋招了，最近準(zhǔn)備以面試為導(dǎo)向開始寫文章了，考慮到目前網(wǎng)絡(luò)上提供的面試題大部分都是短短的幾行字，也即背誦版，我覺得這無法滿足大部分同學(xué)的訴求，知其然而不知所以然，我希望的是以面試題為導(dǎo)向，建立完整的知識體系，而不是東一錘西一棒，所以后續(xù)準(zhǔn)備以?？蜕系拿娼?jīng)帖為導(dǎo)向，對每個面試題提供背誦版 + 詳解版，已經(jīng)會的同學(xué)呢可以直接看背誦版，還不太了解的同學(xué)呢可以結(jié)合詳解版一起看，目前已經(jīng)做了一部分匯總在 Notion 上，后續(xù)會放出來給大家的 ??

本題來源：

• https://www.nowcoder.com/discuss/613909?type=post&order=recall&pos=&page=1&ncTraceId=&channel=-1&source_id=search_post_nctrack

很多時候，我們在業(yè)務(wù)開發(fā)的時候，都是直接無腦上手唯一索引，Tell me，多少人考慮過這個問題，唯一索引和普通索引該怎么選擇？唯一索引是否會對系統(tǒng)性能造成影響？

事實上，確實是有影響，并且還不??！

今天這篇文章就來帶大家分析下什么時候該用普通索引，什么時候該用唯一索引（進(jìn)入本文之前建議各位先了解下 Buffer Pool、Insert Pool 和 Change Buffer 是個什么東西，可以參考上篇文章）。

學(xué)會了拿去裝杯（滑稽）

?? 背誦版可直接拉到文末查看

直接上個例子：

有一張用戶表 user，包含主鍵自增長的 id，身份證號 cid，年齡，性別等

我們的系統(tǒng)有大量的請求，需要根據(jù)身份證號來查詢用戶信息：

select?*?from?user?where?cid?=?'1111111111'

顯然，對于這條語句，對身份證號 cid 加上索引以此來提高查詢效率是在所難免的了。

由于身份證號每個人都是唯一的，所以我們有以下兩種方案：

1. 在業(yè)務(wù)邏輯中保證不會寫入重復(fù)的身份證，給身份證號加上普通索引
2. 給身份證號加上唯一索引

可能有小伙伴會問為啥不直接用身份證號做主鍵 id？

首先前文講過的，順序的聚集索引可以避免磁盤的隨機(jī)讀取，提高效率；另外，一般也不建議把業(yè)務(wù)上的字段作為主鍵

從邏輯上來說，上述兩種方案都是可行的。

但是，從性能上來說，對于這個情景，其實方案二會消耗更多的資源！

首先，不管建立在身份號上的是唯一索引還是普通索引，他們都屬于輔助索引，對吧。通過遍歷輔助索引 B+ 樹我們能夠知道要查找的數(shù)據(jù)的主鍵是多少，然后回表查詢。

唯一索引和普通索引的不同點(diǎn)就在于，普通索引查找到滿足條件 cid = 1111111111 的第一個記錄后，還會繼續(xù)去查找下一個記錄，直到碰到第一個不滿足 cid = 1111111111 條件的記錄；而對于唯一索引來說，一旦找到一個滿足條件的記錄后，就會立即停止繼續(xù)檢索。

看到這里有些小伙伴估計就笑了，啥玩意兒，就這么簡單？于是不屑地關(guān)閉了文章。

事實上，這點(diǎn)不同帶來的性能上的差距微乎其微

因為 InnoDB 存儲引擎是按頁進(jìn)行讀寫的，所以說，當(dāng)它找到 cid = 1111111111 的記錄的時候，這條記錄所在的數(shù)據(jù)頁就已經(jīng)都在內(nèi)存里了。對于普通索引來說，無非就是再移動一次指針罷了。

所以，顯然，這點(diǎn)性能差距不足以讓 “唯一索引會影響性能” 這個結(jié)論站住腳。

接下來才是重點(diǎn)！

在某些情況下，普通索引取代唯一索引的直接理由，就來自于 InnoDB 的 Inser Buffer / Change Buffer 這個振奮人心的新特性，這里還是簡單回顧下。

索引的本質(zhì)是什么？是 B+ 樹，是一個存在磁盤上的物理文件。

以插入操作為例：

我們在構(gòu)建輔助索引身份證號 cid 的這棵 B+ 樹的時候，由于身份證號不是連續(xù)自增的，也就是說輔助索引的節(jié)點(diǎn)插入是沒有順序的，當(dāng)要插入的索引頁不在 Buffer Pool 里的時候，就要去離散地訪問磁盤上的索引頁了。

正是由于隨機(jī)讀取的存在導(dǎo)致了插入操作性能下降。

那么 Insert Buffer 呢，簡單來說，當(dāng)要插入的索引頁不在 Buffer Pool 的時候，存儲引擎并不會每插入一個新數(shù)據(jù)就去離散地訪問一次磁盤頁，而是先將這個操作存儲到 Insert Buffer 中，在下次查詢需要訪問這個數(shù)據(jù)的時候，存儲引擎才會將其合并（Merge）到真正的輔助索引中。這時，就相當(dāng)于將多個葉子節(jié)點(diǎn)插入操作合并到一個操作中，這就大大提高了對于輔助索引的插入性能。

除了訪問這個數(shù)據(jù)會觸發(fā) Merge 外，系統(tǒng)有后臺線程會定期 Merge。在數(shù)據(jù)庫正常關(guān)閉的過程中，也會執(zhí)行 Merge 操作

不過，遺憾的是，Insert/Change Buffer 只適用于非唯一的輔助索引。

對于這樣一個用戶系統(tǒng)，每天都有大量的新用戶被插入進(jìn)來：

insert?into?user?values(id,?cid,?)

如果這個記錄要操作的索引頁不在 Buffer Pool 中：

• 對于唯一索引來說，需要將數(shù)據(jù)讀入 Buffer Pool，然后訪問磁盤上的索引頁來判斷到?jīng)]有沖突，隨后插入這個值，語句執(zhí)行結(jié)束；
• 對于普通索引來說，由于可以使用 Inser Buffer，所以只需要將操作記錄在 Insert Buffer 中，然后語句就可以結(jié)束了

很顯然了，隨機(jī) IO 磁盤是數(shù)據(jù)庫里面成本最高的操作之一，在這種場景下的唯一索引確實給性能帶來了很大的影響。

不過，對于非唯一的輔助索引來說，Insert/Change Buffer 總是能起到作用嗎？

其實不然，它主要適用于寫多讀少的業(yè)務(wù)，因為頁面在寫完以后馬上被訪問到的概率比較小，那么 Merge 操作就不會被頻繁的執(zhí)行。

所以，以后各位小伙伴要是遇到讀多寫少的業(yè)務(wù)，記得留個心眼，少用唯一索引~

最后放上這道題的背誦版：

?? 面試官：唯一索引和普通索引有什么區(qū)別，該如何選擇？

?? 小牛肉：唯一索引和普通索引的不同點(diǎn)就在于，普通索引查找到滿足條件的第一個記錄后，還會繼續(xù)去查找下一個記錄，直到碰到第一個不滿足該條件的記錄；而對于唯一索引來說，一旦找到一個滿足條件的記錄后，就會立即停止繼續(xù)檢索。

不過這一點(diǎn)性能差距幾乎是微乎其微，因為 InnoDB 存儲引擎是按頁進(jìn)行讀寫的，所以說，當(dāng)它找到符合某個條件的記錄的時候，這條記錄所在的數(shù)據(jù)頁就已經(jīng)都在內(nèi)存里了。對于普通索引來說，無非就是再移動一次指針罷了。

真正能夠區(qū)分唯一索引和普通索引差距的，在于 Insert Buffer / Change Buffer 的存在，因為它們只適用于非唯一的輔助索引。

以 Insert Buffer 為例，當(dāng)要插入的索引頁不在緩沖池的時候，存儲引擎并不會每插入一個新數(shù)據(jù)就去離散地訪問一次磁盤頁，而是先將這個操作存儲到 Insert Buffer 中，在下次查詢需要訪問這個數(shù)據(jù)的時候，存儲引擎才會將其合并（Merge）到真正的輔助索引中。這時，就相當(dāng)于將多個葉子節(jié)點(diǎn)插入操作合并到一個操作中，這就大大提高了對于輔助索引的插入性能。

所以，在平常使用中，對于寫多讀少的業(yè)務(wù)，因為頁面在寫完以后馬上被訪問到的概率比較小，那么?Merge?操作就不會被頻繁的執(zhí)行，所以這個時候?使用非唯一索引的性能就優(yōu)于唯一索引。