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

小伙伴想精準查找自己想看的MySQL文章？喏 →

MySQL江湖路 | 專欄目錄

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

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

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

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

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

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

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

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

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

select id from T where id_card = 666;

1

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

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

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

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

通過磁盤塊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的操作；

在數據庫正常關閉的時候。

跑遠了？言歸正傳~~上文提到普通索引（非唯一索引）會使用到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)

1

2

3

4

5

6

7

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)

1

2

3

4

5

6

7

MySQL系列文章匯總與《MySQL江湖路 | 專欄目錄》

往期熱門MySQL系列文章：

原創 | MySQL中特別實用的幾種SQL語句送給大家

原創 | SQL優化最干貨總結 - MySQL（2020最新版）

原創 | 為什么大家都說SELECT * 效率低

原創 | 面試讓HR都能聽懂的MySQL鎖機制，歡聲笑語中搞懂MySQL鎖

原創 | MySQL中的 utf8 并不是真正的UTF-8編碼 ! !

原創 | MySQL數據中有很多換行符和回車符！！該咋辦？

原創 | delete后加 limit是個好習慣么

MySQL SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。