場景
1、維護(hù)一個(gè)市民系統(tǒng)，有一個(gè)字段為身份證號
2、業(yè)務(wù)代碼能保證不會(huì)寫入兩個(gè)重復(fù)的身份證號（如果業(yè)務(wù)無法保證，可以依賴數(shù)據(jù)庫的唯一索引來進(jìn)行約束）
3、常用SQL查詢語句：SELECT name FROM CUser WHERE id_card = 'XXX'
4、建立索引
- 身份證號比較大，不建議設(shè)置為主鍵
- 從性能角度出發(fā)，選擇普通索引還是唯一索引？
假設(shè)字段k上的值都不重復(fù)
查詢過程
1、查詢語句：SELECT id FROM T WHERE k=5
2、查詢過程
- 通過B+樹從樹根開始，按層搜索到葉子節(jié)點(diǎn)，即上圖中右下角的數(shù)據(jù)頁
- 在數(shù)據(jù)頁內(nèi)部通過二分法來定位具體的記錄
3、針對普通索引
- 查找滿足條件的第一個(gè)記錄(5,500)，然后查找下一個(gè)記錄，直到找到第一個(gè)不滿足k=5的記錄
4、針對唯一索引
- 由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)查找
性能差異
1、性能差異：微乎其微
2、InnoDB的數(shù)據(jù)是按照數(shù)據(jù)頁為單位進(jìn)行讀寫的，默認(rèn)為16KB
3、當(dāng)需要讀取一條記錄時(shí)，并不是將這個(gè)記錄本身從磁盤讀出來，而是以數(shù)據(jù)頁為單位進(jìn)行讀取的
4、當(dāng)找到k=5的記錄時(shí)，它所在的數(shù)據(jù)頁都已經(jīng)在內(nèi)存里了
5、對于普通索引而言，只需要多一次指針尋找和多一次計(jì)算 – CPU消耗很低
- 如果k=5這個(gè)記錄恰好是所在數(shù)據(jù)頁的最后一個(gè)記錄，那么如果要取下一個(gè)記錄，就需要讀取下一個(gè)數(shù)據(jù)頁
- 概率很低：對于整型字段索引，一個(gè)數(shù)據(jù)頁（16KB，compact格式）可以存放大概745個(gè)值
change buffer
1、當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在內(nèi)存中就直接更新
2、如果這個(gè)數(shù)據(jù)頁不在內(nèi)存中，在不影響數(shù)據(jù)一致性的前提下
- InnoDB會(huì)將這些更新操作緩存在change buffer
- 不需要從磁盤讀入這個(gè)數(shù)據(jù)頁（隨機(jī)讀）
-
在下次查詢需要訪問這個(gè)數(shù)據(jù)頁的時(shí)候，將數(shù)據(jù)頁讀入內(nèi)存
然后執(zhí)行change buffer中與這個(gè)數(shù)據(jù)頁有關(guān)的操作（merge）
3、change buffer是可以持久化的數(shù)據(jù)，在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上
4、將更新操作先記錄在channge buffer，減少隨機(jī)讀磁盤，提升語句的執(zhí)行速度
5、另外數(shù)據(jù)頁讀入內(nèi)存需要占用buffer pool，使用channge buffer能避免占用內(nèi)存，提高內(nèi)存利用率
6、change buffer用到是buffer pool里的內(nèi)存，不能無限增大，控制參數(shù)innodb_change_buffer_max_size
# 默認(rèn)25，最大50 mysql> SHOW VARIABLES LIKE '%innodb_change_buffer_max_size%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | innodb_change_buffer_max_size | 25 | +-------------------------------+-------+
merge
1、merge：將change buffer中的操作應(yīng)用到原數(shù)據(jù)頁
2、merge的執(zhí)行過程
- 從磁盤讀入數(shù)據(jù)頁到內(nèi)存（老版本的數(shù)據(jù)頁）
-
從change buffer里找出這個(gè)數(shù)據(jù)頁的change buffer記錄（可能多個(gè)）
然后依次執(zhí)行，得到新版本的數(shù)據(jù)頁 - 寫入redolog，包含內(nèi)容：數(shù)據(jù)頁的表更+change buffer的變更
3、merge執(zhí)行完后，內(nèi)存中的數(shù)據(jù)頁和change buffer所對應(yīng)的磁盤頁都還沒修改，屬于臟頁
- 通過其他機(jī)制，臟頁會(huì)被刷新到對應(yīng)的物理磁盤頁
4、觸發(fā)時(shí)機(jī)
- 訪問這個(gè)數(shù)據(jù)頁
- 系統(tǒng)后臺線程定期merge
- 數(shù)據(jù)庫正常關(guān)閉
使用條件
1、對于唯一索引來說，所有的更新操作需要先判斷這個(gè)操作是否違反唯一性約束
2、唯一索引的更新無法使用change buffer，只有普通索引可以使用change buffer
- 主鍵也是無法使用change buffer的
- 例如要插入(4,400)，必須先判斷表中是否存在k=4的記錄，這個(gè)判斷的前提是將數(shù)據(jù)頁讀入內(nèi)存
- 既然數(shù)據(jù)頁已經(jīng)讀入到了內(nèi)存，直接更新內(nèi)存中的數(shù)據(jù)頁就好，無需再寫change buffer
使用場景
1、一個(gè)數(shù)據(jù)頁在merge之前，change buffer記錄關(guān)于這個(gè)數(shù)據(jù)頁的變更越多，收益越大
2、對于寫多讀少的業(yè)務(wù)，頁面在寫完后馬上被訪問的概率極低，此時(shí)change buffer的使用效果最好
- 例如賬單類、日志類的系統(tǒng)
3、如果一個(gè)業(yè)務(wù)的更新模式為：寫入之后馬上會(huì)做查詢
- 雖然更新操作被記錄到change buffer，但之后馬上查詢，又會(huì)從磁盤讀取數(shù)據(jù)頁，觸發(fā)merge過程
- 沒有減少隨機(jī)讀，反而增加了維護(hù)change buffer的代價(jià)
更新過程
插入(4,400)
目標(biāo)頁在內(nèi)存中
- 對于唯一索引來說，找到3~5之間的位置，判斷沒有沖突，插入這個(gè)值
- 對于普通索引來說，找到3~5之間的位置，插入這個(gè)值
- 性能差異：微乎其微
目標(biāo)頁不在內(nèi)存中
1、對于唯一索引來說，需要將數(shù)據(jù)頁讀入內(nèi)存，判斷沒有沖突，插入這個(gè)值
- 磁盤隨機(jī)讀，成本很高
對于普通索引來說，將更新操作記錄在change buffer即可
- 減少了磁盤隨機(jī)讀，性能提升明顯
索引選擇
1、普通索引與唯一索引，在查詢性能上并沒有太大差異，主要考慮的是更新性能，推薦選擇普通索引
2、建議關(guān)閉change buffer的場景
- 如果所有的更新后面，都伴隨著對這個(gè)記錄的查詢
- 控制參數(shù)innodb_change_buffering
mysql> SHOW VARIABLES LIKE '%innodb_change_buffering%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_change_buffering | all | +-------------------------+-------+ # Valid Values (>= 5.5.4) none / inserts / deletes / changes / purges / all # Valid Values (<= 5.5.3) none / inserts # change buffer的前身是insert buffer，只能對insert操作進(jìn)行優(yōu)化
change buffer + redolog
更新過程
當(dāng)前k樹的狀態(tài)：找到對應(yīng)的位置后，k1所在的數(shù)據(jù)頁P(yáng)age 1在內(nèi)存中，k2所在的數(shù)據(jù)頁P(yáng)age 2不在內(nèi)存中
INSERT INTO t(id,k) VALUES (id1,k1),(id2,k2);
# 內(nèi)存：buffer pool # redolog：ib_logfileX # 數(shù)據(jù)表空間：t.ibd # 系統(tǒng)表空間：ibdata1
1、Page 1在內(nèi)存中，直接更新內(nèi)存
2、Page 2不在內(nèi)存中，在changer buffer中記錄：add (id2,k2) to Page 2
3、上述兩個(gè)動(dòng)作計(jì)入redolog（磁盤順序?qū)懀?/p>
4、至此事務(wù)完成，執(zhí)行更新語句的成本很低
- 寫兩次內(nèi)存+一次磁盤
5、由于在事務(wù)提交時(shí)，會(huì)把change buffer的操作記錄也記錄到redolog
- 因此可以在崩潰恢復(fù)時(shí)，恢復(fù)change buffer
虛線為后臺操作，不影響更新操作的響應(yīng)時(shí)間
讀過程
假設(shè)：讀語句發(fā)生在更新語句后不久，內(nèi)存中的數(shù)據(jù)都還在，與系統(tǒng)表空間（ibdata1）和redolog（ib_logfileX）無關(guān)
SELECT * FROM t WHERE k IN (k1,k2);
1、讀Page 1，直接從內(nèi)存返回（此時(shí)Page 1有可能還是臟頁，并未真正落盤）
2、讀Page 2，通過磁盤隨機(jī)讀將數(shù)據(jù)頁讀入內(nèi)存，然后應(yīng)用change buffer里面的操作日志（merge）
- 生成一個(gè)正確的版本并返回
提升更新性能
1、redolog：節(jié)省隨機(jī)寫磁盤的IO消耗（順序?qū)懀?/p>
2、change buffer：節(jié)省隨機(jī)讀磁盤的IO消耗
參考資料
《MySQL實(shí)戰(zhàn)45講》
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對服務(wù)器之家的支持。
