對(duì)于身份證來(lái)說(shuō)一般都是唯一的，那么針對(duì)該字段應(yīng)該是創(chuàng)建唯一索引，還是創(chuàng)建一個(gè)普通索引。如果業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫(xiě)入重復(fù)的身份證號(hào)，那么這兩個(gè)選擇邏輯上都是正確的。 那么從性能的角度考慮，是選擇唯一索引還是普通索引呢？選擇的依據(jù)是什么呢？針對(duì)查詢和更新分別有什么影響呢？

以這個(gè)表為例

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

查詢過(guò)程

假設(shè)執(zhí)行查詢的語(yǔ)句是 select id from T where k=5

• 這個(gè)查詢語(yǔ)句在索引樹(shù)上查找的過(guò)程，先是通過(guò) B+ 樹(shù)從樹(shù)根開(kāi)始，按層搜索到葉子節(jié)點(diǎn)，也就是圖中右下角的這個(gè)數(shù)據(jù)頁(yè)，然后可以認(rèn)為數(shù)據(jù)頁(yè)內(nèi)部通過(guò)二分法來(lái)定位記錄
• 對(duì)于普通索引來(lái)說(shuō)查找到滿足條件的第一個(gè)記錄 (5,500) 后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足 k=5 條件的記錄
• 對(duì)于唯一索引來(lái)說(shuō)，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索

那么，這個(gè)不同帶來(lái)的性能差距會(huì)有多少呢？答案是，微乎其微

因?yàn)镮nnoDB 的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫(xiě)的

• 也就是說(shuō)，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤(pán)讀出來(lái)，而是以頁(yè)為單位，將其整體讀入內(nèi)存。在 InnoDB 中，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是 16KB
• 因?yàn)橐媸前错?yè)讀寫(xiě)的，所以說(shuō)，當(dāng)找到 k=5 的記錄的時(shí)候，它所在的數(shù)據(jù)頁(yè)就都在內(nèi)存里了
• 那么，對(duì)于普通索引來(lái)說(shuō)，要多做幾“查找和判斷下一條記錄”的操作，就只需要幾次指針尋找和計(jì)算
• 當(dāng)然，如果 k=5 這個(gè)記錄剛好是這個(gè)數(shù)據(jù)頁(yè)的最后一個(gè)記錄，那么要取下一個(gè)記錄，必須讀取下一個(gè)數(shù)據(jù)頁(yè)，這個(gè)操作會(huì)稍微復(fù)雜一些，但是對(duì)于整型字段，一個(gè)數(shù)據(jù)頁(yè)可以放近千個(gè) key，因此出現(xiàn)這種情況的概率會(huì)很低

所以，我們計(jì)算平均性能差異時(shí)，仍可以認(rèn)為這個(gè)操作成本對(duì)于現(xiàn)在的 CPU 來(lái)說(shuō)可以忽略不計(jì)

更新過(guò)程

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

• 當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新，而如果這個(gè)數(shù)據(jù)頁(yè)還沒(méi)有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InooDB 會(huì)將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤(pán)中讀入這個(gè)數(shù)據(jù)頁(yè)了
• 在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個(gè)頁(yè)有關(guān)的操作
• 將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁(yè)，得到最新結(jié)果的過(guò)程稱為 merge
• 除了訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)會(huì)觸發(fā) merge 外，系統(tǒng)有后臺(tái)線程會(huì)定期 merge。在數(shù)據(jù)庫(kù)正常關(guān)閉（shutdown）的過(guò)程中，也會(huì)執(zhí)行 merge 操作

顯然，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer，減少讀磁盤(pán)，語(yǔ)句的執(zhí)行速度會(huì)得到明顯的提升。而且，數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存，提高內(nèi)存利用率。

change buffer

• change buffer是一個(gè)特殊的數(shù)據(jù)結(jié)構(gòu)，當(dāng)更新的數(shù)據(jù)不在buffer pool里邊時(shí)，他會(huì)緩存二級(jí)索引頁(yè)面的更新

  The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations

• 用的是 buffer pool 里的內(nèi)存，因此不能無(wú)限增大

• change buffer 的大小，可以通過(guò)參數(shù) innodb_change_buffer_max_size 來(lái)動(dòng)態(tài)設(shè)置

• 這個(gè)參數(shù)設(shè)置為 50 的時(shí)候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%，而且最大只能設(shè)置為50

什么條件下可以使用change buffer

對(duì)于唯一索引來(lái)說(shuō)，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束

• 比如，要插入 (4,400) 這個(gè)記錄，就要先判斷現(xiàn)在表中是否已經(jīng)存在 k=4 的記錄，而這必須要將數(shù)據(jù)頁(yè)讀入內(nèi)存才能判斷
• 如果都已經(jīng)讀入到內(nèi)存了，那直接更新內(nèi)存會(huì)更快，就沒(méi)必要使用 change buffer 了

因此，唯一索引的更新就不能使用 change buffer，實(shí)際上也只有普通索引可以使用

插入一個(gè)新記錄 (4,400)，InnoDB 的處理流程

第一種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)在內(nèi)存中時(shí)

• 對(duì)于唯一索引來(lái)說(shuō)，找到 3 和 5 之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束
• 對(duì)于普通索引來(lái)說(shuō)，找到 3 和 5 之間的位置，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束
• 這樣看來(lái)，普通索引和唯一索引對(duì)更新語(yǔ)句性能影響的差別，只是一個(gè)判斷，只會(huì)耗費(fèi)微小的 CPU 時(shí)間。 但，這不是我們關(guān)注的重點(diǎn)

第二種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)不在內(nèi)存中時(shí)

• 對(duì)于唯一索引來(lái)說(shuō)，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束
• 對(duì)于普通索引來(lái)說(shuō)，則是將更新記錄在 change buffer，語(yǔ)句執(zhí)行就結(jié)束了
• 將數(shù)據(jù)從磁盤(pán)讀入內(nèi)存涉及隨機(jī) IO 的訪問(wèn)，是數(shù)據(jù)庫(kù)里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤(pán)訪問(wèn)，所以對(duì)更新性能的提升是會(huì)很明顯的

其實(shí)，這兩類索引在查詢能力上是沒(méi)差別的，主要考慮的是對(duì)更新性能的影響。所以，建議盡量選擇普通索引。 如果所有的更新后面，都馬上伴隨著對(duì)這個(gè)記錄的查詢，那么你應(yīng)該關(guān)閉 change buffer。而在其他情況下，change buffer 都能提升更新性能。 在實(shí)際使用中，你會(huì)發(fā)現(xiàn)，普通索引和 change buffer 的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。

change buffer 和 redo log

redo log是把更新操作的記錄先寫(xiě)到日志里，然后再空閑的時(shí)候批量寫(xiě)入到磁盤(pán)，來(lái)減少寫(xiě)磁盤(pán)的次數(shù)
change buffer是要更新的記錄不在內(nèi)存時(shí)，為了減少讀磁盤(pán)載入數(shù)據(jù)到內(nèi)存，他會(huì)把操作的數(shù)據(jù)緩存到change buffer里，后續(xù)查數(shù)據(jù)的時(shí)候和載入內(nèi)存的數(shù)據(jù)再進(jìn)行合并
一個(gè)是為了減少寫(xiě)磁盤(pán)的次數(shù)，一個(gè)是為了減少讀磁盤(pán)的次數(shù)

更新和查下過(guò)程中change buffer 和 redo log是如何配合完的

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

假設(shè) k1 所在的數(shù)據(jù)頁(yè)在內(nèi)存 (InnoDB buffer pool) 中，k2 所在的數(shù)據(jù)頁(yè)不在內(nèi)存中

插入數(shù)據(jù)時(shí)change buffer 的更新?tīng)顟B(tài)圖

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

做完上面這些，事務(wù)就可以完成了。所以執(zhí)行這條更新語(yǔ)句的成本很低，就是寫(xiě)了兩處內(nèi)存，然后寫(xiě)了一處磁盤(pán)（兩次操作合在一起寫(xiě)了一次磁盤(pán)），而且還是順序?qū)懙摹?同時(shí)，圖中的兩個(gè)虛線箭頭，是后臺(tái)操作，不影響更新的響應(yīng)時(shí)間

select * from t where k in (k1, k2)

兩個(gè)讀請(qǐng)求的流程圖, 假設(shè) 如果讀語(yǔ)句發(fā)生在更新語(yǔ)句后不久，內(nèi)存中的數(shù)據(jù)都還在

• 讀 Page 1 的時(shí)候，直接從內(nèi)存返回
• 要讀 Page 2 的時(shí)候，需要把 Page 2 從磁盤(pán)讀入內(nèi)存中，然后應(yīng)用 change buffer 里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果。 可以看到，直到需要讀 Page 2 的時(shí)候，這個(gè)數(shù)據(jù)頁(yè)才會(huì)被讀入內(nèi)存

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

是否使用唯一索引

首先，業(yè)務(wù)正確性優(yōu)先。此文章的前提是“業(yè)務(wù)代碼已經(jīng)保證不會(huì)寫(xiě)入重復(fù)數(shù)據(jù)”的情況下，討論性能問(wèn)題
如果業(yè)務(wù)不能保證，或者業(yè)務(wù)就是要求數(shù)據(jù)庫(kù)來(lái)做約束，那么沒(méi)得選，必須創(chuàng)建唯一索引
然后，在一些“歸檔庫(kù)”的場(chǎng)景，你是可以考慮使用普通索引的。比如，線上數(shù)據(jù)只需要保留半年，然后歷史數(shù)據(jù)保存在歸檔庫(kù)。這時(shí)候，歸檔數(shù)據(jù)已經(jīng)是確保沒(méi)有唯一鍵沖突了。要提高歸檔效率，可以考慮把表里面的唯一索引改成普通索引