我禿了！唯一索引、普通索引我該選誰？

作者：陳哈哈 2021-09-06 06:45:06

運維數據庫運維

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

[[421855]]

本文轉載自微信公眾號「MySQL江湖路」，作者陳哈哈。轉載本文請聯系MySQL江湖路公眾號。

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

那么當你處理一張市民信息表時，其中一列為市民的身份證號信息，你會怎么選擇哪個索引?為什么?

對于一個經歷過風風雨雨、日日夜夜的程序員來說，需要你考慮的東西可不僅是重不重復這類問題，而是...

開個玩笑~~應當結合實際情況，對各個場景進行綜合考慮。

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

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

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

假設，執行查詢的語句是:

select id from T where id_card = 666;

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

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

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

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

3.通過磁盤塊3 的P2指針加載磁盤塊7到內存，發生第三次 IO，同時根據二分查找找到666 查詢結束。

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

普通索引：查到第一條id_card=666 后，然后繼續往后查找直到碰到第一個 id_card<>666 的記錄時，結束。
唯一索引：由于索引定義了唯一性，查找到第一個滿足條件的記錄后，直接結束。

兩者在查詢方面的性能差距微乎其微。對于普通索引多的那一次操作，因為本身就是以數據頁為單位讀進內存，數據頁大小默認16KB(大概1000行)，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計算。當然，不可避免查詢的數據是該數據頁的最后一位，這樣還要再讀下一塊數據頁，算法會復雜一些。

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

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

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

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

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

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

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

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

有SQL線程訪問這個數據頁;
master thread線程每秒或每10秒進行一次merge change buffer的操作;
在數據庫正常關閉的時候。

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

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

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

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

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

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

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

change buffer 配置

innodb_change_buffer_max_size% 配置寫緩沖的大小，占整個緩沖池的比例，默認值是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配置是否緩存輔助索引頁的修改，默認為 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

責任編輯：武曉燕來源： MySQL江湖路

普通索引唯一