筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 （本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除） 9） --普通索引和唯一索引，應該怎麼選擇？ 假如你在維護一個市民系統，每個人都有唯一的身份證號，而且業務代碼也已經保證了不會寫入兩個相同的身份證號。如果需要按身份證號來查找，你可能會 ...
筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》
（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除）
9） --普通索引和唯一索引，應該怎麼選擇？
假如你在維護一個市民系統，每個人都有唯一的身份證號，而且業務代碼也已經保證了不會寫入兩個相同的身份證號。如果需要按身份證號來查找，你可能會執行類似這樣的SQL語句：
select name from CUser where id_card = 'xxxxxxxxyyyyyzzz';
由於身份證號id_card欄位較長，不建議將身份證號當做主鍵，那麼現在你有兩個選擇。要麼給id_card建立一個普通索引，要麼給它建立一個唯一索引。由於業務代碼已經保證了身份證號的唯一性，這兩個選擇在邏輯上都是正確的。那麼，從性能角度來考慮，你會選擇普通索引還是唯一索引呢？為什麼呢？
前面我們提到過，在InnoDB中，索引是以B+樹的形式存在的。查詢語句在索引樹的查找過程，先是通過B+樹從樹根開始，按層搜索到葉子節點，然後再進行判斷.(當然如果查詢條件比較多，可能需要回表，具體請參考之前的文章。)
- 對於普通索引來說，查找到滿足條件的id_card之後，需要再去查找下一個記錄，直到碰到id_card不符合要求的記錄。
- 對於唯一索引，由於索引保證了唯一性，查找到第一個符合條件的id_card之後就會立即返回，不再進行檢索了。
那麼這兩個索引之間的不同帶來的性能差異會有多少呢？答案是，微乎其微。InnoDB的數據是按數據頁為單位來讀寫的。也就是說，當需要一條記錄的時候，並不是將這個記錄本身從磁碟讀出來，而是以頁為單位，將其整體讀入記憶體。在InnoDB中，每個數據頁的大小預設是16KB。因為引擎是按頁讀寫的，所以，當找到符合id_card的記錄的時候，它所在的數據頁就都在記憶體中了。那麼，對於普通索引來說，只不過是多做一次“查找和判斷下一條記錄”的操作，只需要一次指針尋找和一次計算。當然，如果這條記錄恰好是這個數據頁的最後一條，那麼就必須讀取下一個數據頁才能進行判斷。但，一個數據頁可以放下很多個key，這種情況出現的概率會很低。所以，對於查詢來說，這兩種索引的性能差異是不大的。
更新過程：
這裡，我們需要先介紹一下Change buffer。當需要更新一個數據頁的時候，如果這個數據頁在記憶體中就直接更新，如果這個數據頁不在記憶體中，在不影響數據一致性的前提下，InnoDB會將這些更新操作緩存在change buffer中，這樣就不需要從磁碟中讀入這個數據頁了。在下次查詢需要訪問這個數據頁的時候，將數據頁讀入記憶體中，然後執行change buffer中與這個頁有關的操作。通過這種方式就能保證這個數據邏輯的正確性。需要說明的是，雖然名叫Change buffer,實際上它是可以持久化的數據。也就是說，change buffer在記憶體中有拷貝，也會被寫入到磁碟上。
將Change buffer中的操作應該用到原數據頁，得到最新結果的過程叫做merge.除了訪問這個數據頁會觸發merge外，系統後臺線程會定期merge。在資料庫正常關閉(shutdown)的過程中，也會執行merge操作。顯然，如果能夠將更新操作先記錄在change buffer，減少讀磁碟，語句的執行速度會得到明顯的提升。而且，數據讀入記憶體是需要占用buffer pool的，所以這種方式還能避免占用記憶體，提高記憶體利用率。那麼，什麼條件下可以使用change buffer呢？
對於唯一索引來說，所有的更新操作都要先判斷這個操作是否違反了唯一性約束。比如要插入某條記錄，就要先判斷現在在表中是否已經存在了索引相同的記錄，而這必須要將數據頁讀入到記憶體中才能進行判斷。如果都已經讀入到記憶體中了，那麼直接更新記憶體會更快，沒必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，實際上也只有普通索引可以使用？
change buffer用的是buffer pool里的記憶體，因此不能無限增大。change buffer的大小，可以通過參數innodb_change_buffer_max_size來動態設置。這個參數設置為50的時候，表示change_buffer的大小最多只能占用buffer pool的50%。那麼，如果要在一張表裡插入一條新記錄的時候InnoDB的處理流程是怎樣的呢?
第一種情況，這個記錄要更新的目標在記憶體中。這時，InnoDB的處理流程如下：
- 對於唯一索引來說，找到要插入的位置，判斷有沒有衝突，插入這個值，語句執行結束
- 對於普通索引來說，找到要插入的位置，插入這個值，語句執行結束。
這樣看來，兩種方式的差別知識一個判斷，影響很小。我們再來看看第二種情況，這個記錄要更新的目標不再記憶體中，這時，處理的流程如下：
- 對於唯一索引，需要將數據頁讀入記憶體，判斷有沒有衝突，插入這個值，語句執行結束。
- 對於普通索引，將更新記錄在change buffer，語句執行結束。
將數據從磁碟讀入記憶體涉及隨機IO訪問，是資料庫里成本最高的操作之一。Change buffer因為減少了隨機磁碟訪問，所以對更新的提升是明顯的。
索引的選擇和實踐：
這兩類索引再查詢能力上是沒差別的，主要考慮的是對更新性能的影響。所以建議儘量選擇普通索引。如果所有的更新後面，都馬上伴隨著對這個記錄的查詢，那麼你應該關閉change buffer.而在其他情況下，change buffer都能提升更新性能。實際上，普通索引和change buffer的配合使用，對於數據量大的表的更新優化是很明顯的。
上期問題：
上期問題可以點擊這裡查看 。兩種方式為：
問題：
change buffer一開始是寫記憶體的，那麼如果這個時候及其掉電重啟，會不會導致change buffer丟失呢？change buffer丟失可不是小事，因為丟失以後就無法再進行merge了，等於是數據丟失了，會不會出現這種情況呢？
