該文為《 MySQL 實戰 45 講》的學習筆記，感謝查看，如有錯誤，歡迎指正
一、查詢和更新上的區別
這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新性能的影響，建議盡量選擇普通索引，
1.1 MySQL 的查詢操作
- 普通索引
查找到第一個滿足條件的記錄后，繼續向后遍歷，直到第一個不滿足條件的記錄， - 唯一索引
由于索引定義了唯一性，查找到第一個滿足條件的記錄后，直接停止繼續檢索，
普通索引會多檢索一次，幾乎沒有影響，因為 InnoDB 的資料是按照資料頁為單位進行讀寫的，需要讀取資料時，并不是直接從磁盤讀取記錄，而是先把資料頁讀到記憶體，再去資料頁中檢索，
一個資料頁默認 16 KB，對于整型欄位，一個資料頁可以放近千個 key，除非要讀取的資料在資料頁的最后一條記錄，就需要再讀一個資料頁，這種情況很少，對CPU的消耗基本可以忽略了，
因此說，在查詢資料方面，普通索引和唯一索引沒差別，
1.2 MySQL 的更新操作
更新操作并不是直接對磁盤中的資料進行更新，是先把資料頁從磁盤讀入記憶體，再更新資料頁，
- 普通索引
將資料頁從磁盤讀入記憶體，更新資料頁， - 唯一索引
將資料頁從磁盤讀入記憶體，判斷是否唯一，再更新資料頁，
由于 MySQL 中有個 change buffer 的機制，會導致普通索引和唯一索引在更新上有一定的區別，
change buffer的作用是為了降低IO 操作，避免系統負載過高，change buffer將資料寫入資料頁的程序，叫做merge，
如果需要更新的資料頁在記憶體中時，會直接更新資料頁；如果資料不在記憶體中，會先將更新操作記入change buffer，當下一次讀取資料頁時，順帶merge到資料頁中，change buffer也有定期merge策略，資料庫正常關閉的程序中，也會觸發merge，
對于唯一索引，更新前需要判斷資料是否唯一（不能和表中資料重復），如果資料頁在記憶體中，就可以直接判斷并且更新，如果不在記憶體中，就需要去磁盤中讀出來，判斷一下是否唯一，是的話就更新，change buffer是用不到的，即使資料頁不在記憶體中，還是要讀出來，
change buffer 用的是 buffer pool 里的記憶體，因此不能無限增大，change buffer 的大小，可以通過引數 innodb_change_buffer_max_size 來動態設定，這個引數設定為 50 的時候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%，
結論：唯一索引用不了change buffer，只有普通索引可以用，
二、change buffer 和 redo log的區別
2.1 change buffer 的適用場景
change buffer 的作用是降低更新操作的頻率，快取更新操作，這樣會有一個缺點，就是更新不及時，對于讀操作比較頻繁的表，不建議使用 change buffer，
因為更新操作剛記錄進change buffer中，就讀取了該表，資料頁被讀到了記憶體中，資料馬上就merge到資料頁中了，這樣不僅不會降低性能消耗，反而會增加維護change buffer的成本，
適用于寫多讀少的表，
2.2 change buffer 和 redo log 區別
我們舉一個例子用來理解 redo log 和 change buffer，我們執行以下 SQL 陳述句：
mysql> insert into t(id,k) values(id1,k1),(id2,k2);
假設，(id1,k1) 在資料頁 Page 1 中，(id2,k2) 在資料頁 Page 2 中，并且 Page 1 在記憶體中，Page 2 不在記憶體中，
執行程序如下：
- 直接向 Page 1 中寫入
(id1,k1)； - 在
change buffer中記下"向 Page 2 中寫入(id2,k2)"這條資訊； - 將以上兩個動作記入redo log，
做完上面這些，事務就可以完成了，執行這條更新陳述句的成本很低，就是寫了兩處記憶體，然后寫了一處磁盤（兩次操作合在一起寫了一次磁盤），而且還是順序寫的，
這條更新陳述句，涉及了四個部分：記憶體、redo log（ib_log_fileX）、 資料表空間（t.ibd）、系統表空間（ibdata1），
如果要讀資料的話，程序是怎樣的？
mysql> select * from t where k in (k1, k2);
假設讀操作在更新后不久，此時記憶體中還有 Page 1，沒有 Page 2，那么讀操作就和 redo log 以及 ibdata1 無關了，
- 從記憶體中獲取到 Page 1 上的最新資料
(id1,k1)； - 將資料頁 Page 2 讀入記憶體，執行
merge操作，此時記憶體中的 Page 2 也有最新資料(id2,k2)；
需要注意的是：
- redo log中的資料，可能還沒有 flush 到磁盤，磁盤中的 Page 1 和 Page 2 中并沒有最新資料，但我們依然可以拿到最新資料（記憶體中的 Page 1 就是最新的，Page 2 雖然不是最新的，但是從磁盤讀到記憶體中后，執行了
merge操作，記憶體中的 Page 2 就是最新的了，） - 如果此時 MySQL 例外宕機了，比如服務器例外掉電，change buffer 中的資料會不會丟？
change buffer中的資料分為兩部分，一部分是已經merge到ibdata1中的資料，這部分資料已經持久化，不會丟失，另一部分資料，還在change buffer中，沒有merge到ibdata1，分 3 種情況：
（1）change buffer 寫入資料到記憶體，redo log 也已經寫入（ib-log-filex），但是未commit，binlog中也沒有fsync到磁盤，這部分資料會丟失；
（2）change buffer 寫入資料到記憶體，redo log 也已經寫入（ib-log-filex），但是未commit，binlog 已寫入到磁盤，這部分不會多丟失，例外重啟后會先從 binlog 恢復 redo log，再從 redo log 恢復 change buffer；
（3）change buffer 寫入資料到記憶體，redo log 和 binlog 都已經fsync，直接從redo log 恢復，不會丟失，
redo log 主要節省的是隨機寫磁盤的 IO 消耗（轉成順序寫），而 change buffer 主要節省的則是隨機讀磁盤的 IO 消耗
感謝閱讀，有興趣的小伙伴可以關注我的微信公眾號DevOps探索之旅，大家一起學習進步
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80287.html
標籤：MySQL