先說結(jié)論：普通索引的效率比唯一索引要好，除非業(yè)務(wù)上有要求，不然建議使用普通索引

1、查詢過程

例：執(zhí)行的查詢語句是 select id from T where k=5；這個(gè)查詢語句在索引樹上查找的過程，先是通過B+樹從樹根開始，按層搜索到葉子節(jié)點(diǎn)，然后可以認(rèn)為數(shù)據(jù)頁(yè)內(nèi)部通過二分法來定位記錄。

• 普通索引，查到滿足條件的第一個(gè)記錄后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足條件的記錄。
• 唯一索引，由于定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。
• 但是，這個(gè)不同帶來的性能差距，微乎其微。

InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來讀寫的。也就是說，當(dāng)需要讀第一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤中讀取出來，而是以頁(yè)為單位，將其整體讀入內(nèi)存。在InnoDB中，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是16KB。

因此當(dāng)需要查找下一個(gè)記錄的時(shí)候，直接在內(nèi)存中判斷就好了，多一次指針尋找和計(jì)算。

對(duì)于當(dāng)前記錄為數(shù)據(jù)頁(yè)的最后一條記錄時(shí)，必須要取下一頁(yè)數(shù)據(jù)，這個(gè)操作會(huì)稍微復(fù)雜一點(diǎn)。

但是對(duì)于整形字段，一個(gè)數(shù)據(jù)頁(yè)可以放近千個(gè)key，因此出現(xiàn)這種情況的概率會(huì)很低。所以，在計(jì)算平均性能影響差異時(shí)，仍可以認(rèn)為這個(gè)操作成本對(duì)于現(xiàn)在的CPU來說可以忽略不計(jì)。

2、更新過程

為了說明普通索引和唯一索引對(duì)更新語句性能的影響這個(gè)問題，需要先介紹一下change buffer。

change buffer 的概念

• 當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新。
• 如果這個(gè)數(shù)據(jù)頁(yè)還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB會(huì)將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)頁(yè)了。
• 在下次查詢需要訪問這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行change buffer 中與這個(gè)頁(yè)有關(guān)的操作。

需要說明的是，雖然名字叫做 change buffer，實(shí)際上它是可以持久化的數(shù)據(jù)。也就是說，change buffer 在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上。

將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁(yè)，得到最新結(jié)果的過程稱為 merge。除了訪問這個(gè)數(shù)據(jù)頁(yè)會(huì)觸發(fā) merge外，系統(tǒng)有后臺(tái)線程會(huì)定期merge。在數(shù)據(jù)庫(kù)正常關(guān)閉的過程中，也會(huì)執(zhí)行merge操作。

顯然，如果能夠?qū)⒏虏僮飨扔涗浽?code>change buffer，減少讀磁盤，語句的執(zhí)行速度會(huì)得到明顯的提升。而且，雖然change buffer 也是需要占用內(nèi)存，但相比于數(shù)據(jù)頁(yè)來說（默認(rèn)16k），避免了占用內(nèi)存。

什么條件下可以使用change buffer？

• 唯一索引不能使用。因?yàn)樗械母虏僮鞫家扰袛噙@個(gè)操作是否違反唯一性約束。需要將數(shù)據(jù)頁(yè)讀入內(nèi)存才能判斷，既然已經(jīng)讀入內(nèi)存了，直接更新內(nèi)存會(huì)更快，就必要使用了。
• 普通索引可以使用。
• change buffer 用的是 buffer pool 中的內(nèi)存，可以通過 innodb_change_buffer_max_size 來動(dòng)態(tài)設(shè)置。這個(gè)參數(shù)值為50的時(shí)候，表示change_buffer的大小最多只能占用 buffer pool的50%。

3、插入過程

比如要在表中插入一個(gè)新數(shù)據(jù)，InnoDB是這樣處理的：

第一種情況是，這個(gè)記錄要插入的目標(biāo)頁(yè)在內(nèi)存中，流程如下：

• 唯一索引，找到記錄所在區(qū)間，判斷有沒有沖突，如果沒有，插入這個(gè)值，語句執(zhí)行結(jié)束
• 普通索引，找到記錄所在區(qū)間，插入這個(gè)值，語句執(zhí)行結(jié)束

這種情況，普通索引和唯一索引對(duì)插入語句性能影響的差別很小。

第二種情況是，這個(gè)記錄要插入的目標(biāo)頁(yè)不在內(nèi)存中，流程如下：

• 唯一索引，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷有沒有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束
• 普通索引，直接更新在change buffer 中，語句執(zhí)行結(jié)束

將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機(jī)IO的訪問，是數(shù)據(jù)庫(kù)里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問，所以對(duì)性能的提升是會(huì)很明顯的。

4、使用場(chǎng)景

根據(jù)以上分析，change buffer 只限于用在普通索引的場(chǎng)景下，不適用于唯一索引。但是并不是所有場(chǎng)景都適用。

• 對(duì)于寫多讀少的業(yè)務(wù)來說，使用change buffer 的效果最好。例如賬單類、日志類系統(tǒng)。
• 對(duì)于寫入后立馬做查詢的場(chǎng)景，即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪問這個(gè)數(shù)據(jù)頁(yè)，會(huì)立即觸發(fā)merge過程。這樣隨機(jī)訪問IO的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。所以對(duì)于這種模式來說，change buffer 反而起到了副作用。

5、索引選擇

綜合上述描述，這兩類索引在查詢能力上是沒差別的，主要考慮的是對(duì)更新性能的影響。所以，建議盡量選擇普通索引。當(dāng)然如果是業(yè)務(wù)需要，還是要以業(yè)務(wù)為主。

如果所有的更新后面，都馬上伴隨這對(duì)這個(gè)記錄的查詢，那么應(yīng)該關(guān)閉 change buffer。在其他情況下，change buffer 都能提升更新性能。

6、change buffer 和 redo log

這兩個(gè)都是通過減少隨機(jī)讀寫，提升性能，概念上容易混淆。下面是MySQL官網(wǎng)的InnoDB（5.7版本）架構(gòu)圖

MySQL架構(gòu)圖

可以發(fā)現(xiàn)，這兩個(gè)是處于不同的結(jié)構(gòu)中。我們以插入數(shù)據(jù)為例：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假設(shè)當(dāng)前k索引樹的狀態(tài)，查找到位置后，k1所在的數(shù)據(jù)頁(yè)在內(nèi)存（InnoDB buffer pool）中，k2所在的數(shù)據(jù)頁(yè)不在內(nèi)存中。如下圖：

更新示意圖

分析這條更新語句，它涉及到四個(gè)部分：內(nèi)存、redo log(ib_log_fileX)、數(shù)據(jù)表空間(t.ibd)、系統(tǒng)表空間(ibdata1)

更新步驟：

1. Page 1在內(nèi)存中，直接更新內(nèi)存
2. Page2 不在內(nèi)存中，就在內(nèi)存的change buffer 區(qū)域，記錄下“我要往Page2 插入一行”這個(gè)信息
3. 將上述兩個(gè)動(dòng)作計(jì)入 redo log 中（圖中 3 和 4）

做完上面這些，事務(wù)就可以完成了。執(zhí)行這條更新語句的成本很低，就是寫了兩處內(nèi)存（圖中1和2），然后寫了一處磁盤（圖中3和4一起寫入的），而且還是順序?qū)憽?/p>

同時(shí)，圖中的兩個(gè)虛線箭頭，是后臺(tái)操作，不影響更新的響應(yīng)時(shí)間。

如果此時(shí)，正好要查詢數(shù)據(jù)，比如select * from t where k in (k1,k2)，要怎么處理呢？

如果讀語句發(fā)生在更新語句后不久，內(nèi)存中的數(shù)據(jù)都還在。

• 讀Page1的時(shí)候，因?yàn)閿?shù)據(jù)在內(nèi)存中，就直接從內(nèi)存返回
• 讀Page2的時(shí)候，需要把 Page2從磁盤讀入內(nèi)存中，然后應(yīng)用 change buffer 里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果

可以直到，直到需要讀Page2 的時(shí)候，這個(gè)數(shù)據(jù)頁(yè)才會(huì)被讀入內(nèi)存。

所以，如果要簡(jiǎn)單地對(duì)比這兩個(gè)機(jī)制在提升更新性能上的收益的話，redo log 主要節(jié)省的是隨機(jī)讀寫磁盤 IO 消耗（轉(zhuǎn)成順序?qū)懀?，而change buffer 主要節(jié)省的則是隨機(jī)讀磁盤的IO消耗。