MySQL普通索引和唯一索引的深入講解

場(chǎng)景

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

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

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

4、建立索引

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

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

查詢(xún)過(guò)程

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

2、查詢(xún)過(guò)程

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

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

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

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

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

性能差異

1、性能差異：微乎其微

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

3、當(dāng)需要讀取一條記錄時(shí)，并不是將這個(gè)記錄本身從磁盤(pán)讀出來(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
• 不需要從磁盤(pán)讀入這個(gè)數(shù)據(jù)頁(yè)（隨機(jī)讀）
• 在下次查詢(xún)需要訪問(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ì)被寫(xiě)入到磁盤(pán)上

4、將更新操作先記錄在channge buffer，減少隨機(jī)讀磁盤(pán)，提升語(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ò)程

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

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

• 通過(guò)其他機(jī)制，臟頁(yè)會(huì)被刷新到對(duì)應(yīng)的物理磁盤(pán)頁(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ú)需再寫(xiě)change buffer

使用場(chǎng)景

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

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

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

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

• 雖然更新操作被記錄到change buffer，但之后馬上查詢(xún)，又會(huì)從磁盤(pán)讀取數(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è)值

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

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

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

索引選擇

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

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

• 如果所有的更新后面，都伴隨著對(duì)這個(gè)記錄的查詢(xún)
• 控制參數(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樹(shù)的狀態(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（磁盤(pán)順序?qū)懀?br />

4、至此事務(wù)完成，執(zhí)行更新語(yǔ)句的成本很低

• 寫(xiě)兩次內(nèi)存+一次磁盤(pán)

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è)，并未真正落盤(pán)）

2、讀Page 2，通過(guò)磁盤(pán)隨機(jī)讀將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后應(yīng)用change buffer里面的操作日志（merge）

• 生成一個(gè)正確的版本并返回

提升更新性能

1、redolog：節(jié)省隨機(jī)寫(xiě)磁盤(pán)的IO消耗（順序?qū)懀?br />

2、change buffer：節(jié)省隨機(jī)讀磁盤(pán)的IO消耗

參考資料

《MySQL實(shí)戰(zhàn)45講》

總結(jié)

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