前言
本文介紹MySQL的更新快取Change Buffer，以及唯一索引和普通索引如何選擇，
唯一索引和普通索引的選擇
查詢程序
唯一索引下，查詢索引樹，找到第一條匹配的行就回傳；
普通索引下，查詢索引樹，找到第一條匹配的行之后，繼續往下遍歷，直到第一條不匹配的行為止，再回傳，
即使匹配的行跨了資料頁，但一個資料頁默認16KB，每行只存盤一個key且是整數，可以存盤近千個，那么普通索引下，最多也是多加載一次資料頁，所以唯一索引和普通索引的查詢效率基本相同，
更新程序
首先介紹下MySQL的更新快取Change Buffer，它使用的是MySQL Buffer Pool的存盤空間，可以設定Change Buffer在Buffer Pool中的占比，MySQL5.6默認為25%，最多可以開到50%，
顧名思義，Change Buffer就是用來存盤更新操作的，使得更新操作不需要加載對應的資料頁，直接更新到記憶體，當對Change Buffer中的行進行查詢時，就會將原始資料頁加載到記憶體，并將快取應用到原始資料頁，這個就是merge程序，Change Buffer的merge觸發時機：
- 當原始資料頁加載到
Buffer Pool時 - 后臺執行緒定期
merge - 資料庫正常關閉時
Change Buffer的刷盤觸發時機：
- 資料庫空閑時，會定時持久化
- 資料庫緩沖不夠用時
- 資料庫正常關閉之前
redo log寫滿時
你或許會疑問“萬一在刷盤之前資料庫宕機了，那之前的更新不就丟失了么？”
答案是否，簡單分析是，因為將更新操作寫到Change Buffer后，還會將更新操作寫到redo log并持久化到磁盤，資料庫宕機重啟之后，會將之前的更新快取資料恢復到記憶體，
具體分析如下：
（1）change buffer寫入,redo log雖然做了fsync但未commit,binlog未fsync到磁盤,這部分資料丟失
（2）change buffer寫入,redo log寫入但沒有commit,binlog以及fsync到磁盤,先從binlog恢復redo log,再從redo log恢復change buffer
（3）change buffer寫入,redo log和binlog都已經fsync.那么直接從redo log里恢復，
接下來分析兩種索引下的更新操作：
唯一索引下，查詢索引樹，加載對應的資料頁到記憶體，判斷是否違反一致性約束，再更新；
普通索引，查詢索引樹，直接更新記憶體中的Change Buffer，
因為唯一索引需要判斷更新操作是否違反一致性約束，所以必須加載資料頁，也就用不到Change Buffer，即Change Buffer只用于普通索引，
從上面的分析可見，在更新多于讀取操作的情況下，普通索引的更新操作效率要高于唯一索引，但如果是更新之后就有查詢的場景，那么Change Buffer不但沒有起到提效作用，反而占用的緩沖空間，所以，這種情況下，一般會關閉Change Buffer來避免它的副作用，
總結
總結來說，如果業務需要資料庫來對資料進行唯一性約束，那么優先還是考慮唯一索引；否則，如果是更新遠多于讀取操作的業務場景，比如歸檔，日志等，考慮用普通索引代替唯一索引，可以提高記憶體命中率和提高更新效率，但如果是更新之后就有查詢的場景，則建議關閉Change Buffer，來避免它的副作用，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/298534.html
標籤：其他
上一篇：按8小時制計算的作業時長統計函式