MySQL實戰45講——普通索引和唯一索引

文章摘抄自林曉斌老師《MySQL實戰45講》。

不同的業務場景下，應該選擇普通索引，還是唯一索引？

假設你在維護一個市民系統，每個人都有一個唯一的身份證號，而且業務程式碼已經保證了不會寫入兩個重複的身份證號。如果市民系統需要按照身份證號查姓名，就會執行類似這樣的SQL語句：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

所以，你一定會考慮在id_card欄位上建索引。

由於身份證號欄位比較大，我不建議你把身份證號當做主鍵，那麼現在你有兩個選擇，要麼給id_card欄位建立唯一索引，要麼建立一個普通索引。如果業務程式碼已經保證了不會寫入重複的身份證號，那麼這兩個選擇邏輯上都是正確的。

現在我要問你的是， 從效能的角度考慮， 你選擇唯一索引還是普通索引呢？ 選擇的依據是什麼呢？假設欄位 k 上的值都不重複。

接下來， 我們就從這兩種索引對查詢語句和更新語句的效能影響來進行分析。

2、查詢過程

假設，執行查詢的語句是 select id from T where k=5。這個查詢語句在索引樹上查詢的過程，先是通過B+樹從樹根開始，按層搜尋到葉子節點，也就是圖中右下角的這個資料頁，然後可以認為資料頁內部通過二分法來定位記錄。

對於普通索引來說，查詢到滿足條件的第一個記錄(5,500)後，需要查詢下一個記錄，直到碰到第一個不滿足k=5條件的記錄。

對於唯一索引來說，由於索引定義了唯一性，查詢到第一個滿足條件的記錄後，就會停止繼續檢索。

那麼，這個不同帶來的效能差距會有多少呢？答案是，微乎其微。

你知道的，InnoDB的資料是按資料頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在InnoDB中，每個資料頁的大小預設是16KB。

因為引擎是按頁讀寫的，所以說，當找到k=5的記錄的時候，它所在的資料頁就都在記憶體裡了。那麼，對於普通索引來說，要多做的那一次“查詢和判斷下一條記錄”的操作，就只需要一次指標尋找和一次計算。

當然，如果k=5這個記錄剛好是這個資料頁的最後一個記錄，那麼要取下一個記錄，必須讀取下一個資料頁，這個操作會稍微複雜一些。

但是，我們之前計算過，對於整型欄位，一個資料頁可以放近千個key，因此出現這種情況的概率會很低。所以，我們計算平均效能差異時，仍可以認為這個操作成本對於現在的CPU來說可以忽略不計。

3、更新過程

（1）change buffer

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新，而如果這個資料頁還沒有在記憶體中的話，在不影響資料一致性的前提下，InooDB會將這些更新操作快取在change buffer中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要訪問這個資料頁的時候，將資料頁讀入記憶體，然後執行change buffer中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正確性。

需要說明的是，雖然名字叫作change buffer，實際上它是可以持久化的資料。也就是說，change buffer在記憶體中有拷貝，也會被寫入到磁碟上。

將change buffer中的操作應用到原資料頁，得到最新結果的過程稱為merge。除了訪問這個資料頁會觸發merge外，系統有後臺執行緒會定期merge。在資料庫正常關閉（shutdown）的過程中，也會執行merge操作。

顯然，如果能夠將更新操作先記錄在change buffer，減少讀磁碟，語句的執行速度會得到明顯的提升。而且，資料讀入記憶體是需要佔用buffer pool的，所以這種方式還能夠避免佔用記憶體，提高記憶體利用率。

那麼，什麼條件下可以使用change buffer呢？

對於唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入(4,400)這個記錄，就要先判斷現在表中是否已經存在k=4的記錄，而這必須要將資料頁讀入記憶體才能判斷。如果都已經讀入到記憶體了，那直接更新記憶體會更快，就沒必要使用change buffer了。

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

change buffer用的是buffer pool裡的記憶體，因此不能無限增大。change buffer的大小，可以通過引數innodb_change_buffer_max_size來動態設定。這個引數設定為50的時候，表示change buffer的大小最多隻能佔用buffer pool的50%。

現在，你已經理解了change buffer的機制，那麼我們再一起來看看如果要在這張表中插入一個新記錄(4,400)的話，InnoDB的處理流程是怎樣的。

第一種情況是，這個記錄要更新的目標頁在記憶體中。這時，InnoDB的處理流程如下：

對於唯一索引來說，找到3和5之間的位置，判斷到沒有衝突，插入這個值，語句執行結束；

對於普通索引來說，找到3和5之間的位置，插入這個值，語句執行結束。

這樣看來，普通索引和唯一索引對更新語句效能影響的差別，只是一個判斷，只會耗費微小的CPU時間。

但，這不是我們關注的重點。

 

第二種情況是，這個記錄要更新的目標頁不在記憶體中。這時，InnoDB的處理流程如下：

對於唯一索引來說，需要將資料頁讀入記憶體，判斷到沒有衝突，插入這個值，語句執行結束；

對於普通索引來說，則是將更新記錄在change buffer，語句執行就結束了。

將資料從磁碟讀入記憶體涉及隨機IO的訪問，是資料庫裡面成本最高的操作之一。change buffer因為減少了隨機磁碟訪問，所以對更新效能的提升是會很明顯的。

 

4、change buffer的使用場景

使用change buffer對更新過程的加速作用，也清楚了change buffer只限於用在普通索引的場景下，而不適用於唯一索引。那麼，現在有一個問題就是：普通索引的所有場景，使用change buffer都可以起到加速作用嗎？

因為merge的時候是真正進行資料更新的時刻，而change buffer的主要目的就是將記錄的變更動作快取下來，所以在一個資料頁做merge之前，change buffer記錄的變更越多（也就是這個頁面上要更新的次數越多），收益就越大。

因此，對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的概率比較小，此時change buffer的使用效果最好。這種業務模型常見的就是賬單類、日誌類的系統。

反過來，假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記錄在change buffer，但之後由於馬上要訪問這個資料頁，會立即觸發merge過程。這樣隨機訪問IO的次數不會減少，反而增加了change buffer的維護代價。所以，對於這種業務模式來說，change buffer反而起到了副作用。

 

5、索引選擇和實踐.

其實，這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新效能的影響。所以，我建議你儘量選擇普通索引。

如果所有的更新後面，都馬上伴隨著對這個記錄的查詢，那麼你應該關閉change buffer。而在其他情況下，change buffer都能提升更新效能。

 在實際使用中，你會發現，普通索引和change buffer的配合使用，對於資料量大的表的更新優化還是很明顯的。

 特別地，在使用機械硬碟時，change buffer這個機制的收效是非常顯著的。所以，當你有一個類似“歷史資料”的庫，並且出於成本考慮用的是機械硬碟時，那你應該特別關注這些表裡的索引，儘量使用普通索引，然後把change buffer 儘量開大，以確保這個“歷史資料”表的資料寫入速度。

 

6、redo log 和change buffer

理解了change buffer的原理， 你可能會聯想到我在前面文章中和你介紹過的redo log和WAL。

在前面文章的評論中， 我發現有同學混淆了redo log和change buffer。 WAL 提升效能的核心機制， 也的確是儘量減少隨機讀寫， 這兩個概念確實容易混淆。 所以， 這裡我把它們放到了同一個流程裡來說明， 便於你區分這兩個概念。

現在，我們要在表上執行這個插入語句：

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

這裡， 我們假設當前k索引樹的狀態， 查詢到位置後， k1所在的資料頁在記憶體(InnoDB bufferpool)中， k2所在的資料頁不在記憶體中。 如圖所示是帶change buffer的更新狀態圖。

圖2 帶change buffer的更新過程
 

 分析這條更新語句，你會發現它涉及了四個部分：記憶體、redo log（ib_log_fileX）、 資料表空間（t.ibd）、系統表空間（ibdata1）。

這條更新語句做了如下的操作（按照圖中的數字順序）：

Page 1在記憶體中，直接更新記憶體；

Page 2沒有在記憶體中，就在記憶體的change buffer區域，記錄下“我要往Page 2插入一行”這個資訊

將上述兩個動作記入redo log中（圖中3和4）。

做完上面這些，事務就可以完成了。所以，你會看到，執行這條更新語句的成本很低，就是寫了兩處記憶體，然後寫了一處磁碟（兩次操作合在一起寫了一次磁碟），而且還是順序寫的。

同時，圖中的兩個虛線箭頭，是後臺操作，不影響更新的響應時間。

那在這之後的讀請求，要怎麼處理呢？

比如，我們現在要執行 select * from t where k in (k1, k2)。這裡，我畫了這兩個讀請求的流程圖。

如果讀語句發生在更新語句後不久，記憶體中的資料都還在，那麼此時的這兩個讀操作就與系統表空間（ibdata1）和 redo log（ib_log_fileX）無關了。

圖3 帶change buffer的讀過程
 

從圖中可以看到：

讀Page 1的時候，直接從記憶體返回。有幾位同學在前面文章的評論中問到，WAL之後如果讀資料，是不是一定要讀盤，是不是一定要從redo log裡面把資料更新以後才可以返回？其實是不用的。你可以看一下圖3的這個狀態，雖然磁碟上還是之前的資料，但是這裡直接從記憶體返回結果，結果是正確的。

要讀Page 2的時候，需要把Page 2從磁碟讀入記憶體中，然後應用change buffer裡面的操作日誌，生成一個正確的版本並返回結果。

可以看到，直到需要讀Page 2的時候，這個資料頁才會被讀入記憶體。

如果要簡單地對比這兩個機制在提升更新效能上的收益的話，redo log 主要節省的是隨機寫磁碟的IO消耗（轉成順序寫），而change buffer主要節省的則是隨機讀磁碟的IO消耗

 

7、小結

從普通索引和唯一索引的選擇開始，資料的查詢和更新過程，然後說明了change buffer的機制以及應用場景，最後講到了索引選擇的實踐。

由於唯一索引用不上change buffer的優化機制，因此如果業務可以接受，從效能角度出發建議優先考慮非唯一索引。

 

8、思考

通過圖2你可以看到， change buffer一開始是寫記憶體的， 那麼如果這個時候機器掉電重啟， 會不會導致change buffer丟失呢？ change buffer丟失可不是小事兒， 再從磁碟讀入資料可就沒有了merge過程， 就等於是資料丟失了。 會不會出現這種情況呢？