前言

在使用MySQL的過程中，隨著表數(shù)據(jù)的逐漸增多，為了更快的查詢我們需要的數(shù)據(jù)，我們會在表中建立不同類型的索引。

今天我們來聊一聊，普通索引和唯一索引的使用場景，以及為什么說推薦大家優(yōu)先使用普通索引，盡量避免使用唯一索引。

對于一個普通的二級索引，目的就是為了加速查詢，所以我們可能會為表中的某個字段或者某些字段，建立一個普通的二級索引。

而對于唯一索引來說，由于其唯一鍵約束的特性，有時我們會更多的賦予其業(yè)務(wù)含義。比如有一張存儲身份證號的表，為了保證身份證號的唯一性，我們會在身份證號字段上建立唯一索引。

那為什么說，不推薦大家使用唯一索引呢？

接下來，我們從查詢和更新兩方面分析一下唯一索引和普通索引的性能差距。

查詢性能

我們知道每個索引其實都是一棵二叉樹，所以我簡單畫了一個索引圖，不太好看，大家多多擔(dān)待。

給大家稍微解釋一下這張圖，不同顏色代表不同的數(shù)據(jù)頁，這里假設(shè)一個數(shù)據(jù)頁里面存放兩條數(shù)據(jù)。

我們知道MySQL磁盤與內(nèi)存交互是通過一個叫做數(shù)據(jù)頁的單位，每個數(shù)據(jù)頁默認(rèn)的大小是16K。

在一棵樹上，只有葉子節(jié)點才會真正的存放數(shù)據(jù)，非葉子節(jié)點存放的是每個下級數(shù)據(jù)頁中最小的索引字段以及指向下級數(shù)據(jù)頁的指針。

對于主鍵索引，葉子節(jié)點存放的是一行真正的數(shù)據(jù)，而對于二級索引來說，在葉子節(jié)點存儲的是索引字段以及對應(yīng)的主鍵id。

好了，下面我們分析一下，普通二級索引和唯一索引是如何查數(shù)據(jù)的？

以一個簡單的查詢sql為例：select id from t where m=103;

1，MySQL從根節(jié)點出發(fā)，通過二分法判斷m=103大于100小于104，所以會找到根節(jié)點中100對應(yīng)的數(shù)據(jù)頁100-102；

2，在100-102的數(shù)據(jù)頁上，由于103大于102，所以會找到102對應(yīng)的102-103的數(shù)據(jù)頁；

3，在這個數(shù)據(jù)頁上，找到了m=103的記錄，并獲取到了要查詢的id字段。

對于普通的二級索引來說，找到第一條m=103的記錄之后，會繼續(xù)向后查找，在104-105這個數(shù)據(jù)頁中判斷是否還有符合m=103條件的記錄，如果沒有則結(jié)束查詢。

而對于唯一索引來說，由于其唯一性約束，所以在查找到第一條記錄之后，就結(jié)束了查找。

可以看到，二者的差別就在于是否繼續(xù)查到下一條。

那這兩者有多大的性能差距呢？答案是幾乎沒有。

我們知道，MySQL的數(shù)據(jù)是以頁為單位存放的，以一個int類型的二級索引為例，一個int占4個字節(jié)，加上MySQL的頭信息6個字節(jié)，相當(dāng)于10個字節(jié)。

那么一個16k的頁上能存放多少記錄呢？

16*1024/10 = 1638。也就是說，一個數(shù)據(jù)頁就可能放下1600多條記錄。那么我們在查詢數(shù)據(jù)時，會把整個數(shù)據(jù)頁都加載進(jìn)內(nèi)存，此時對于普通二級索引判斷下一個記錄的操作所需的消耗是非常非常小的。

可以說，從查詢方面來看，普通二級索引和唯一索引的性能基本是相當(dāng)?shù)摹?/span>

更新性能

唯一索引和普通二級索引的性能差距主要體現(xiàn)在更新操作上。

對于MySQL來說，更新一條語句的邏輯是首先讀到要更新的記錄，如果這個記錄沒有在內(nèi)存里，就先加載到內(nèi)存。然后執(zhí)行更新的語句，之后再把變更的數(shù)據(jù)刷新到磁盤中。

但是，對于MySQL來說，把數(shù)據(jù)從磁盤讀到內(nèi)存涉及到隨機IO，是成本非常高的一種操作。

如果每次更新數(shù)據(jù)都要這么來一次的話，高性能這個指標(biāo)恐怕很難保證。

所以，設(shè)計MySQL的大神們引入了一個叫做change buffer的東西。

change buffer是一種可以持久化的緩存數(shù)據(jù)，當(dāng)我們要更新數(shù)據(jù)時，如果要更新的數(shù)據(jù)不存在于內(nèi)存，此時并不需要把數(shù)據(jù)從磁盤加載到內(nèi)存，而是將更新操作記錄在change buffer中，更新操作就算完成了。

當(dāng)下次要讀取這些數(shù)據(jù)時，會把讀到的數(shù)據(jù)和change buffer進(jìn)行合并，或者叫merge。

通過change buffer，更新操作就不需要去讀磁盤了，全程都是內(nèi)存操作，性能自然可以得到極大的提升。

但是！但是問題又來了！

change buffer只對普通二級索引有效，對于唯一索引是沒有效果的。

為什么呢？

因為在更新一條記錄時，我們需要檢查索引的唯一性約束。

如何檢查呢？自然首先要把數(shù)據(jù)從磁盤加載到內(nèi)存里面才能進(jìn)行判斷。

可是如果都已經(jīng)把數(shù)據(jù)加載到內(nèi)存里，再去使用change buffer不就顯得多此一舉了。

所以，唯一索引不能，也沒必要去使用change buffer來提升性能了。

由于對唯一索引的更新涉及到讀磁盤這個隨機IO操作，性能自然也是比不上普通二級索引了，這就是推薦大家優(yōu)先使用普通二級索引的原因了。

經(jīng)過對比，大家也可以看到，這兩種索引在查詢上性能基本是一致的，其性能差距主要體現(xiàn)在更新操作上。

其實即便是大家有一些特殊的業(yè)務(wù)需要，比如存放唯一的身份證號等，還是建議大家通過業(yè)務(wù)層去約束。

總的來說，普通的二級索引比唯一索引帶來的收益要更大。