場(chǎng)景

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名注冊(cè)、網(wǎng)頁(yè)空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、湘東網(wǎng)站維護(hù)、網(wǎng)站推廣。

1、維護(hù)一個(gè)市民系統(tǒng)，有一個(gè)字段為身份證號(hào)

2、業(yè)務(wù)代碼能保證不會(huì)寫入兩個(gè)重復(fù)的身份證號(hào)（如果業(yè)務(wù)無(wú)法保證，可以依賴數(shù)據(jù)庫(kù)的唯一索引來(lái)進(jìn)行約束）

3、常用SQL查詢語(yǔ)句：SELECT name FROM CUser WHERE id_card = 'XXX'

4、建立索引

• 身份證號(hào)比較大，不建議設(shè)置為主鍵
• 從性能角度出發(fā)，選擇普通索引還是唯一索引？

假設(shè)字段k上的值都不重復(fù)

查詢過(guò)程

1、查詢語(yǔ)句：SELECT id FROM T WHERE k=5

2、查詢過(guò)程

• 通過(guò)B+樹從樹根開(kāi)始，按層搜索到葉子節(jié)點(diǎn)，即上圖中右下角的數(shù)據(jù)頁(yè)
• 在數(shù)據(jù)頁(yè)內(nèi)部通過(guò)二分法來(lái)定位具體的記錄

3、針對(duì)普通索引

• 查找滿足條件的第一個(gè)記錄(5,500)，然后查找下一個(gè)記錄，直到找到第一個(gè)不滿足k=5的記錄

4、針對(duì)唯一索引

• 由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)查找

性能差異

1、性能差異：微乎其微

2、InnoDB的數(shù)據(jù)是按照數(shù)據(jù)頁(yè)為單位進(jìn)行讀寫的，默認(rèn)為16KB

3、當(dāng)需要讀取一條記錄時(shí)，并不是將這個(gè)記錄本身從磁盤讀出來(lái)，而是以數(shù)據(jù)頁(yè)為單位進(jìn)行讀取的

4、當(dāng)找到k=5的記錄時(shí)，它所在的數(shù)據(jù)頁(yè)都已經(jīng)在內(nèi)存里了

5、對(duì)于普通索引而言，只需要多一次指針尋找和多一次計(jì)算 – CPU消耗很低

• 如果k=5這個(gè)記錄恰好是所在數(shù)據(jù)頁(yè)的最后一個(gè)記錄，那么如果要取下一個(gè)記錄，就需要讀取下一個(gè)數(shù)據(jù)頁(yè)
• 概率很低：對(duì)于整型字段索引，一個(gè)數(shù)據(jù)頁(yè)（16KB，compact格式）可以存放大概745個(gè)值

change buffer

1、當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新

2、如果這個(gè)數(shù)據(jù)頁(yè)不在內(nèi)存中，在不影響數(shù)據(jù)一致性的前提下

• InnoDB會(huì)將這些更新操作緩存在change buffer
• 不需要從磁盤讀入這個(gè)數(shù)據(jù)頁(yè)（隨機(jī)讀）
• 在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存
  然后執(zhí)行change buffer中與這個(gè)數(shù)據(jù)頁(yè)有關(guān)的操作（merge）

3、change buffer是可以持久化的數(shù)據(jù)，在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上

4、將更新操作先記錄在channge buffer，減少隨機(jī)讀磁盤，提升語(yǔ)句的執(zhí)行速度

5、另外數(shù)據(jù)頁(yè)讀入內(nèi)存需要占用buffer pool，使用channge buffer能避免占用內(nèi)存，提高內(nèi)存利用率

6、change buffer用到是buffer pool里的內(nèi)存，不能無(wú)限增大，控制參數(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ù)頁(yè)

2、merge的執(zhí)行過(guò)程

• 從磁盤讀入數(shù)據(jù)頁(yè)到內(nèi)存（老版本的數(shù)據(jù)頁(yè)）
• 從change buffer里找出這個(gè)數(shù)據(jù)頁(yè)的change buffer記錄（可能多個(gè)）
  然后依次執(zhí)行，得到新版本的數(shù)據(jù)頁(yè)
• 寫入redolog，包含內(nèi)容：數(shù)據(jù)頁(yè)的表更+change buffer的變更
  

3、merge執(zhí)行完后，內(nèi)存中的數(shù)據(jù)頁(yè)和change buffer所對(duì)應(yīng)的磁盤頁(yè)都還沒(méi)修改，屬于臟頁(yè)

• 通過(guò)其他機(jī)制，臟頁(yè)會(huì)被刷新到對(duì)應(yīng)的物理磁盤頁(yè)

4、觸發(fā)時(shí)機(jī)

• 訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)
• 系統(tǒng)后臺(tái)線程定期merge
• 數(shù)據(jù)庫(kù)正常關(guān)閉

使用條件

1、對(duì)于唯一索引來(lái)說(shuō)，所有的更新操作需要先判斷這個(gè)操作是否違反唯一性約束

2、唯一索引的更新無(wú)法使用change buffer，只有普通索引可以使用change buffer

• 主鍵也是無(wú)法使用change buffer的
• 例如要插入(4,400)，必須先判斷表中是否存在k=4的記錄，這個(gè)判斷的前提是將數(shù)據(jù)頁(yè)讀入內(nèi)存
• 既然數(shù)據(jù)頁(yè)已經(jīng)讀入到了內(nèi)存，直接更新內(nèi)存中的數(shù)據(jù)頁(yè)就好，無(wú)需再寫change buffer

使用場(chǎng)景

1、一個(gè)數(shù)據(jù)頁(yè)在merge之前，change buffer記錄關(guān)于這個(gè)數(shù)據(jù)頁(yè)的變更越多，收益越大

2、對(duì)于寫多讀少的業(yè)務(wù)，頁(yè)面在寫完后馬上被訪問(wèn)的概率極低，此時(shí)change buffer的使用效果最好

• 例如賬單類、日志類的系統(tǒng)

3、如果一個(gè)業(yè)務(wù)的更新模式為：寫入之后馬上會(huì)做查詢

• 雖然更新操作被記錄到change buffer，但之后馬上查詢，又會(huì)從磁盤讀取數(shù)據(jù)頁(yè)，觸發(fā)merge過(guò)程
• 沒(méi)有減少隨機(jī)讀，反而增加了維護(hù)change buffer的代價(jià)

更新過(guò)程

插入(4,400)

目標(biāo)頁(yè)在內(nèi)存中

• 對(duì)于唯一索引來(lái)說(shuō)，找到3~5之間的位置，判斷沒(méi)有沖突，插入這個(gè)值
• 對(duì)于普通索引來(lái)說(shuō)，找到3~5之間的位置，插入這個(gè)值
• 性能差異：微乎其微

目標(biāo)頁(yè)不在內(nèi)存中

1、對(duì)于唯一索引來(lái)說(shuō)，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷沒(méi)有沖突，插入這個(gè)值

• 磁盤隨機(jī)讀，成本很高

對(duì)于普通索引來(lái)說(shuō)，將更新操作記錄在change buffer即可

• 減少了磁盤隨機(jī)讀，性能提升明顯

索引選擇

1、普通索引與唯一索引，在查詢性能上并沒(méi)有太大差異，主要考慮的是更新性能，推薦選擇普通索引

2、建議關(guān)閉change buffer的場(chǎng)景

• 如果所有的更新后面，都伴隨著對(duì)這個(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，只能對(duì)insert操作進(jìn)行優(yōu)化

change buffer + redolog

更新過(guò)程

當(dāng)前k樹的狀態(tài)：找到對(duì)應(yīng)的位置后，k1所在的數(shù)據(jù)頁(yè)P(yáng)age 1在內(nèi)存中，k2所在的數(shù)據(jù)頁(yè)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í)行更新語(yǔ)句的成本很低

• 寫兩次內(nèi)存+一次磁盤

5、由于在事務(wù)提交時(shí)，會(huì)把change buffer的操作記錄也記錄到redolog

• 因此可以在崩潰恢復(fù)時(shí)，恢復(fù)change buffer

虛線為后臺(tái)操作，不影響更新操作的響應(yīng)時(shí)間

讀過(guò)程

假設(shè)：讀語(yǔ)句發(fā)生在更新語(yǔ)句后不久，內(nèi)存中的數(shù)據(jù)都還在，與系統(tǒng)表空間（ibdata1）和redolog（ib_logfileX）無(wú)關(guān)

SELECT * FROM t WHERE k IN (k1,k2);

1、讀Page 1，直接從內(nèi)存返回（此時(shí)Page 1有可能還是臟頁(yè)，并未真正落盤）

2、讀Page 2，通過(guò)磁盤隨機(jī)讀將數(shù)據(jù)頁(yè)讀入內(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é)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)創(chuàng)新互聯(lián)的支持。

文章名稱：MySQL普通索引和唯一索引的深入講解
本文地址：http://vcdvsql.cn/article6/gjesog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁(yè)設(shè)計(jì)公司、建站公司、App開(kāi)發(fā)、自適應(yīng)網(wǎng)站、網(wǎng)站策劃、網(wǎng)站內(nèi)鏈

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)