[[421855]]

本文轉(zhuǎn)載自微信公眾號「MySQL江湖路」，作者陳哈哈。轉(zhuǎn)載本文請聯(lián)系MySQL江湖路公眾號。

提到唯一索引和普通索引，相信大家都不陌生，當(dāng)同事小姐姐問你這倆有什么區(qū)別時(shí)?或許你會脫口而出：“這還用問?見名知意啊，一個(gè)是允許字段重復(fù)，一個(gè)不允許存在重復(fù)數(shù)據(jù)!”

是否解決小姐姐的疑問我不知道，但你在同事心目中，肯定不是啥好玩意兒~ 要知道，一眼就看出的答案，一般不會有人問，除非問傻子~

那么當(dāng)你處理一張市民信息表時(shí)，其中一列為市民的身份證號信息，你會怎么選擇哪個(gè)索引?為什么?

對于一個(gè)經(jīng)歷過風(fēng)風(fēng)雨雨、日日夜夜的程序員來說，需要你考慮的東西可不僅是重不重復(fù)這類問題，而是...

開個(gè)玩笑~~應(yīng)當(dāng)結(jié)合實(shí)際情況，對各個(gè)場景進(jìn)行綜合考慮。

其實(shí)，如果在業(yè)務(wù)代碼中保證了不會寫入重復(fù)的身份證號，那么這兩個(gè)選擇邏輯上都是正確的。但是在SELECT和DML場景中，唯一索引和普通索引卻有很多不同。

1、在SELECT中，唯一索引和普通索引的區(qū)別

本文測試引擎選擇我們最常用的InnoDB，版本為MySQL8.0;

假設(shè)，執(zhí)行查詢的語句是:

select id from T where id_card = 666; 

(身份證太長，咱們用簡單數(shù)據(jù)做演示)我們知道，MySQL的InnoDB采用的是B+樹實(shí)現(xiàn)的索引結(jié)構(gòu)，查找過程從B+樹的樹根起，按層搜索到666所在的葉子節(jié)點(diǎn)，然后取出該節(jié)點(diǎn)所在的數(shù)據(jù)頁，把數(shù)據(jù)頁讀到內(nèi)存后，通過二分法在數(shù)據(jù)頁中定位id_card=666的行數(shù)據(jù)。

B+ 樹的查找過程如上圖：

1.將磁盤塊1從磁盤加載到內(nèi)存，發(fā)生一次IO ,在內(nèi)存中使用二分查找方式找到 666 在600和700 之間，鎖定磁盤塊1的P2 指針。

2.通過磁盤塊1 的 P2 指針地址把磁盤塊3 加載到內(nèi)存，發(fā)生第二次IO ,鎖定磁盤塊3 的 P2 指針

3.通過磁盤塊3 的P2指針加載磁盤塊7到內(nèi)存，發(fā)生第三次 IO，同時(shí)根據(jù)二分查找找到666 查詢結(jié)束。

普通索引和唯一索引的定位方式：

• 普通索引：查到第一條id_card=666 后，然后繼續(xù)往后查找直到碰到第一個(gè) id_card<>666 的記錄時(shí)，結(jié)束。
• 唯一索引：由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，直接結(jié)束。

兩者在查詢方面的性能差距微乎其微。對于普通索引多的那一次操作，因?yàn)楸旧砭褪且詳?shù)據(jù)頁為單位讀進(jìn)內(nèi)存，數(shù)據(jù)頁大小默認(rèn)16KB(大概1000行)，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。當(dāng)然，不可避免查詢的數(shù)據(jù)是該數(shù)據(jù)頁的最后一位，這樣還要再讀下一塊數(shù)據(jù)頁，算法會復(fù)雜一些。

但你知道的，這種概率很小，我們程序員要相信逆墨菲定律：大概率不會出現(xiàn)且未被發(fā)現(xiàn)的BUG，在難以改動的前提下，你就當(dāng)不知道就完了，發(fā)生了又能咋地?有測試頂著呢!

??有同學(xué)問我了：普通索引為什么要繼續(xù)向下查找?繼續(xù)向下查找的原因是由于普通索引允許重復(fù)值，且B+Tree是天然有序的。SQL中并沒有指定limit 1，所以他還要往下查，看是否有同條件的數(shù)據(jù)一起返回，直到查到第一條不滿足條件的數(shù)據(jù)為止。

2、在DML中，唯一索引和普通索引的區(qū)別

ding!這是本篇文章的重點(diǎn)，在看之前，我們需要先了解什么是change buffer。

了解MySQL機(jī)制的同學(xué)們知道，當(dāng)執(zhí)行 DML(INSERT、UPDATE、DELETE)等操作時(shí)，InnoDB會利用 change buffer進(jìn)行加速寫操作，可以將寫操作的隨機(jī)磁盤訪問調(diào)整為局部順序操作，而在機(jī)械硬盤時(shí)代，隨機(jī)磁盤訪問(隨機(jī)I/O)也是數(shù)據(jù)庫操作中的最耗性能的硬傷。當(dāng)普通索引(非唯一索引)的數(shù)據(jù)頁發(fā)生寫操作時(shí)，把操作內(nèi)容寫到內(nèi)存中的change buffer后就可以立刻返回(執(zhí)行完成)了。

這里我以UPDATE操作為例，當(dāng)需要更新某一行數(shù)據(jù)時(shí)，會先判斷該行所在數(shù)據(jù)頁是否在內(nèi)存中，如果在就直接在內(nèi)存數(shù)據(jù)頁中更新，如果這個(gè)數(shù)據(jù)頁沒有內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會將這些UPDATE操作緩存在 change buffer 中，這樣就不需要從磁盤讀入數(shù)據(jù)頁，當(dāng)有SQL查詢需要訪問這個(gè)數(shù)據(jù)頁的數(shù)據(jù)時(shí)，將數(shù)據(jù)頁讀入內(nèi)存后，然后先執(zhí)行 change buffer 中與這個(gè)頁的相關(guān)UPDATE操作，通過這種方式保證這個(gè)數(shù)據(jù)頁的邏輯正確性。

可見，change buffer是會被從內(nèi)存持久化到磁盤中的，將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程被稱為 merge。除了訪問這個(gè)數(shù)據(jù)頁會觸發(fā) merge 外，系統(tǒng)有后臺線程會定期 merge。在數(shù)據(jù)庫正常關(guān)閉(shutdown)的過程中，也會執(zhí)行 merge 操作，相當(dāng)于刷臟頁啦(把已修改的數(shù)據(jù)更新到實(shí)際數(shù)據(jù)文件中)。

觸發(fā)merge的操作主要有以下幾種(**你該記住的點(diǎn)**)：

• 有SQL線程訪問這個(gè)數(shù)據(jù)頁;
• master thread線程每秒或每10秒進(jìn)行一次merge change buffer的操作;
• 在數(shù)據(jù)庫正常關(guān)閉的時(shí)候。

小朋友，你是否有很多問號??DB服務(wù)器宕機(jī)，數(shù)據(jù)不是就丟了?這就得redo log + binlog來保證了，可以參考作者另一篇文章《聽我講完redo log、binlog原理，面試官老臉一紅》[1]，本篇不再贅述。

跑遠(yuǎn)了?言歸正傳上文提到普通索引(非唯一索引)會使用到Change buffer進(jìn)行加速寫操作，聰明的你是不是已經(jīng)get到點(diǎn)了呢?

是的，唯一索引不會使用 Change buffer ,如果索引設(shè)置了唯一屬性，在進(jìn)行插入或者修改操作時(shí)，InnoDB 必須進(jìn)行唯一性檢查，如果不讀取索引頁到緩沖池，無法校驗(yàn)索引是否唯一，如果都把索引頁讀到內(nèi)存了，那直接更新內(nèi)存會更快，就沒必要使用change buffer了。

對于普通索引(非唯一索引)的DML操作來說，當(dāng)待更新的數(shù)據(jù)頁在內(nèi)存中時(shí)，找到前值和后值的區(qū)間插入即可;當(dāng)待更新的數(shù)據(jù)頁在不在內(nèi)存中時(shí)，直接把操作寫到Change buffer就完事兒了。舒服!

• 而對于唯一索引，當(dāng)待更新的數(shù)據(jù)頁在不在內(nèi)存中時(shí)，索引每次都得把數(shù)據(jù)頁讀到內(nèi)存中判斷唯一性，將數(shù)據(jù)從磁盤讀入內(nèi)存涉及大量隨機(jī)IO的訪問，慢的一批，當(dāng)遇到高頻寫操作時(shí)??唉，別想了，難受!

到這里，相信你對普通索引和唯一索引的取舍有了一定的概念，普通索引和唯一索引在查詢能力上是沒差別的，主要考慮的是更新的影響。還得結(jié)合實(shí)際業(yè)務(wù)場景來判斷，如果是讀取遠(yuǎn)大于更新和插入的表，唯一索引和普通索引都可以，但是如果業(yè)務(wù)需求相反，個(gè)人覺得應(yīng)該使用普通索引，當(dāng)然如果是那種更新完要求立即可見的需求，就是剛更新完就要再查詢的，這種情況下反而不推薦普通索引，因?yàn)檫@樣會頻繁的產(chǎn)生merge操作，起不到change buffer的作用，反而需要額外空間來維護(hù)change buffer就有點(diǎn)得不償失了。

當(dāng)我們使用普通索引，尤其在使用機(jī)械盤的場景下，盡量把change buffer開大從而確保數(shù)據(jù)的寫入速度。最后，通過列舉一下 change buffer 的配置，結(jié)束今天的分享，相信看到這里的都是有心人，也是喜愛MySQL的崽子，記得不要吝嗇你的點(diǎn)贊哦~~

change buffer 配置

• innodb_change_buffer_max_size% 配置寫緩沖的大小，占整個(gè)緩沖池的比例，默認(rèn)值是25%，可以通過修改該值提高InnoDB寫效率，最大值是50%。

mysql> show variables like '%innodb_change_buffer_max_size%'; 
+-------------------------------+-------+ 
| Variable_name                 | Value | 
+-------------------------------+-------+ 
| innodb_change_buffer_max_size | 25    | 
+-------------------------------+-------+ 
1 row in set (0.00 sec) 

• innodb_change_buffering配置是否緩存輔助索引頁的修改，默認(rèn)為 all，即緩存 INSERT/DELETE/UPDATE等DML操作。

mysql> show variables like '%innodb_change_buffering%'; 
+-------------------------+-------+ 
| Variable_name           | Value | 
+-------------------------+-------+ 
| innodb_change_buffering | all   | 
+-------------------------+-------+ 
1 row in set (0.00 sec) 

References

[1] 《聽我講完redo log、binlog原理，面試官老臉一紅》: https://blog.csdn.net/qq_39390545/article/details/115214802