作者 | .NY&XX
出品 | CSDN博客
網上已經有很多關於唯一索引和普通索引的區別，這裡就不詳細闡述了，接下來我們深入討論如何根據不同業務場景，應該選擇普通索引還是唯一索引。比如維護一個社保管理系統，每個社保人員都有一個唯一的身份證號，而且業務代碼已經保證了不會寫入兩個重複的身份證號。如果該系統需要按照身份證號查詢姓名，就會執行這樣的SQL語句：
<code>select name from suser where id_card = ‘xxxxxxxxxxx’;
/<code>
所以一般會考慮在id_card 字段上建索引。由於身份證號字段比較大，不適合用來作主鍵，索引現在有兩個選擇，要麼給id_card字段創建唯一索引，要麼創建一個普通索引。如果業務代碼已經保證了不會寫入重複的身份證號，那麼這兩個選擇邏輯上都是正確的。但是要從性能角度上來考慮，選擇的依據應該是什麼呢？下面我們就從兩種索引對查詢過程和更新過程的性能影響來分析。
查詢操作
我們來看一下InnoDB索引組織機構，假設執行：
<code>select id from t where a=3/<code>
這個查詢語句在索引樹上查找的過程將如下：
先是通過B+樹從樹根開始，按層序遍歷的方式搜索到葉子節點，從而定位數據頁。
通過二分查找來定位記錄。
唯一索引而言，查找到滿足條件的第一個條目（比如 （3,300））後就會停止繼續檢索。普通索引查找到一個滿足條件的條目後將會繼續查找，直到碰到第一個不滿足a=3條件的條目。
它們的不同所帶來的性能差距卻是微乎其微的。因為InnoDB中是按數據頁為單位來讀寫的，也就是說，當讀取一個條目的時候並不是將條目從磁盤讀出來，而是以頁為單位，整體讀入內存。既然存儲引擎是按頁讀寫的，所以說當找到a=3的條目時，它所在的數據頁已經在內存裡了。那麼對於普通索引需要多做的“查找以及判斷條目是否滿足條件”的操作就只需要一次指針操作及計算。
更新操作
當需要更新一個數據頁時，如果數據頁在內存緩衝池（buffer pool）中就直接更新，並同時記錄redo log,但是如果這個數據頁不在內存中的話。在不影響一致性的前提下，InnoDB會將更新操作緩存在寫緩衝（change buffer）中，同時記錄redo log。
寫緩衝(change buffer)
那什麼是change buffer呢？
它的主要目的是將對二級索引的數據操作緩存下來，以此減少二級索引的隨機IO，並達到操作合併的效果。
在MySQL5.5之前的版本中，由於只支持緩存insert操作，所以最初叫做insert buffer，只是後來的版本中支持了更多的操作類型（操作類型包括insert、update、delete）緩存，才改叫change buffer。
change buffer的數據結構上是一顆b+樹，存儲在ibdata系統表空間中，根頁為ibdata的第4個page(FSP_IBUF_TREE_ROOT_PAGE_NO)。
將change buffer中的操作應用到原數據頁從而得到最新結果的過程被稱為merge。merge 的時候才是是真正進行數據更新的時刻，change buffer 將條目的變更動作進行緩存。在一個數據頁做 merge 之前，change buffer 記錄的變更越多（也就是這個頁面上要更新的次數越多),收益就越大。
一般來說，觸發merge的操作主要有以下幾種：
訪問這個數據頁；
master thread線程每秒或每10秒進行一次merge insert buffer的操作；
在數據庫正常關閉的時候。
此外，雖然名字叫做change buffer，但實際上它是可以持久化的數據，也就說它在內存中有拷貝，也會被寫入到磁盤上。
change buffer狀態查看
seg size 為插入緩衝區的總大小（頁的數量X16KB）；
merges表示已經合併的merge的數量；
merged operations: insert 插入記錄被merge的次數；
delete mark 刪除操作被merge的次數；
delete 更新操作被merge了多少次。
change buffer佔用buffer pool
數據讀入內存是需要佔用buffer pool的，採用這種方式能夠避免佔用內存，提升內存利用率。
change buffer用的是buffer pool的內存，因此不能無線增大，它通過參數innodb_change_buffer_max_size來設置，這個參數表示佔用內存的比例，默認是25%，最大值為50%，一般在寫多讀少的場景下才需要設置。
change buffer帶來什麼好處？
如果MySQL承擔大量的DML操作，則change buffer是必不可少的，他的存在就是儘量減小I/O的消耗，通過內存進行數據的合併操作，將多次操作操作儘量變為少量的I/O操作，從而提升了更新操作的速度。
什麼場景適合開啟change buffer？
change buffer只限於普通索引的場景下，不適用與唯一索引。為什麼呢？
因為，假設要
插入(3, 300)這個條目，首先要判斷這個條目是否在表中出現過。而這必須要將數據頁讀入內存才能判斷。如果都已經讀入到內存了，那直接更新內存會更快，就沒必要使用 change buffer 了。那麼InnoDB中插入的條目（3,300）的流程是如何的呢？
如果這個條目要更新的數據頁在內存中：
對於唯一索引，找到2和4的位置，判斷沒有衝突後，插入這個值，執行結束
對於普通索引，找到2和4的位置，插入這個值，執行結束
如果這個條目要更新的數據頁不在內存中：
對於唯一索引，需要將數據頁讀入內存，然後判斷有沒有衝突，然後進行插入 。
對於普通索引，只需要將條目更新操作記錄在change buffer就執行結束了。
不是所有場景都可以用change buffer
普通索引並不是所有場景使用change buffer都能受益，對於寫多讀少的業務來說，頁面在寫完以後馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好。
但是假設一個業務的更新模式是寫入之後馬上會做查詢，那麼即使滿足了條件，將更新先記錄在change buffer，但之後由於馬上要訪問這個數據頁，會立即觸發 merge 過程。這樣隨機訪問 IO 的次數不會減少，反而增加了 change buffer 的維護代價。所以，對於這樣類似的業務模式來說，change buffer 反而起到了副作用。
舉個例子：
假設要執行insert into t values(id1,a1),(id2,a2);
假設a1 所在的數據頁在內存 (InnoDB buffer pool) 中，a2 所在的數據頁不在的話，如圖所示：
如果a1 所在的Page1 在內存中，則直接更新內存；
如果a2 所在的Page2 沒有在內存中，則在change buffer中記錄下“要往 Page2 插入一行”這個信息；
將更新Page1這個動作記入到redo log 中；
將change buffer記錄插入信息這個動作記入到redo log中。
第3、4寫redo log的兩次操作合在一起寫磁盤。所以從執行過程中可以發現, 執行這條更新語句的成本很低，只寫了兩處內存，而且還是順序寫的。圖中的兩個紅色箭頭，都是後臺操作(空閒時或者必須時寫入磁盤)，不影響更新的響應時間。
那麼在之後的讀請求該怎麼處理呢，比如我們要執行select * from t where a in (a1, a2);
a1 本來就在內存中, 之前內存也更新了, 所以直接從內存返回。
讀取Page2的時候，需要把Page2從磁盤讀入內存，然後結合change buffer裡面的操作日誌生成一個新版本並返回結果。
總結
普通索引和唯一索引在查詢能力上是沒差別的，主要考慮的是更新的影響。一般建議使用普通索引。特別是在使用機械盤的場景下，儘量把change buffer開大從而確保數據的寫入速度。
聲明：本文為CSDN博主「.NY&XX」的原創文章，原文鏈接：https://blog.csdn.net/songguangfan/article/details/103059623。
閱讀更多 CSDN 的文章
