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

場景

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

2、業(yè)務(wù)代碼能保證不會寫入兩個重復(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é)點，即上圖中右下角的數(shù)據(jù)頁
• 在數(shù)據(jù)頁內(nèi)部通過二分法來定位具體的記錄

3、針對普通索引

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

4、針對唯一索引

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

性能差異

1、性能差異：微乎其微

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

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

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

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

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

change buffer

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

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

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

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

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里找出這個數(shù)據(jù)頁的change buffer記錄（可能多個）
  然后依次執(zhí)行，得到新版本的數(shù)據(jù)頁
• 寫入redolog，包含內(nèi)容：數(shù)據(jù)頁的表更+change buffer的變更
  

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

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

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

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

使用條件

1、對于唯一索引來說，所有的更新操作需要先判斷這個操作是否違反唯一性約束

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

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

使用場景

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

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

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

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

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

更新過程

插入(4,400)

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

• 對于唯一索引來說，找到3~5之間的位置，判斷沒有沖突，插入這個值
• 對于普通索引來說，找到3~5之間的位置，插入這個值
• 性能差異：微乎其微

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

1、對于唯一索引來說，需要將數(shù)據(jù)頁讀入內(nèi)存，判斷沒有沖突，插入這個值

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

對于普通索引來說，將更新操作記錄在change buffer即可

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

索引選擇

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

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

• 如果所有的更新后面，都伴隨著對這個記錄的查詢
• 控制參數(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、上述兩個動作計入redolog（磁盤順序?qū)懀?br />

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

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

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

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

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

讀過程

假設(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)存返回（此時Page 1有可能還是臟頁，并未真正落盤）

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

• 生成一個正確的版本并返回

提升更新性能

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

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

參考資料

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

總結(jié)

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