文章詳情頁

MySQL唯一索引和普通索引選哪個？

瀏覽：73日期：2023-10-10 15:31:19

想象這樣一個場景，在設計一張用戶表時，每人的身份證號是唯一的，需要搜索。但由于身份證號字段較大，不好將其作為主鍵。在業(yè)務代碼已經保證插入身份證唯一的情況下，可以選擇建立唯一索引和普通索引，這時該如何選擇呢？接下來，將從查詢和更新的執(zhí)行過程進行分析。

查詢過程

假設 k 是表 t 上的索引，在搜索 select id from t where k=5 時，會先從 k 這棵 B+ 的樹根開始，按層搜索葉子節(jié)點，找到 k=5 的數據頁，然后在數據頁內容進行二分法定位。

對于普通索引，找到 k=5 的記錄后，會繼續(xù)向下查找一個，直到碰到第一個不是 5 的記錄結束。

對于唯一索引，由于取值唯一，找到后直接停止。

由于 InnoDB 是按照數據頁為單位（數據頁默認 16 KB）進行讀寫的，在讀取一條數據時，會將整個數據頁整體讀到內存。在讀入內存的數據頁中，如果包含 k=5 的記錄，在查詢的情況下，唯一索引比普通索引多了一次查找和判斷的過程，可以忽略。

如果 k=5 是當前數據頁的最后一條，就需要在讀取下一個數據頁。但這發(fā)生的概率較低，也可以忽略。

所以總得來說，普通索引和唯一索引在查詢的過程中差異不大。

change buffer

在分析唯一索引和普通索引的影響前，先來認識一下 change buffer 這個結構。

什么是 change buffer ?

在執(zhí)行更新操作時，如果要更新的數據頁在內存中就直接更新，否則的話，在不影響數據一致性的前提下，InnoDB 會將更新操作緩存在 change buffer 中，從而省去了從磁盤讀取數據頁的過程。在下次查詢操作讀取到恰好需要更新的數據頁時，會將 change buffer 的更新語句執(zhí)行，寫入數據頁。將操作應用到硬盤的過程叫 merge. 后臺線程會定期 merge 或數據庫正常關閉時，也會進行 merge 操作。

merge 的執(zhí)行流程：

從磁盤讀入老版本數據頁。從 change buffer中找出和該數據頁關聯的記錄，依次應用，得到新版數據頁。寫 redo log，記錄數據的變更和 change buffer 的變更。

change buffer 實際上是可以持久化到硬盤中的數據，也就是說在內存和硬盤上都 change buffer 的存在。change buffer 之前叫 insert buffer，開始只對 insert buffer 有優(yōu)化，后來加上了對 delete 和 update 的支持，進而改名叫 change buffer。

可以看到，先將更新操作記錄在 change buffer，減少了將磁盤數據頁讀取到內存的過程，語句的執(zhí)行速度會有很明顯的提升。同時，將數據讀入內存，會占用 buffer pool 內存，所以減少讀操作，還提高了內存使用率。

Buffer Pool 是內存中的一個區(qū)域，InnoDB 在訪問表和索引數據時會在其中進行緩存。允許在內存中直接更新經常使用的數據，來加快處理速度。在一些專用的服務器上，會將 80% 的物理內存分為 buffer pool.

可以通過 innodb_change_buffer_max_size 來設置 change buffer 占用 buffer pool 的大小。

change buffer 應用場景？

如上面提到，change buffer 預先保存了更新記錄，減少了讀取數據頁的過程，從而提高性能。也就是說如果 change buffer 中針對不同的數據頁如果包含的更新記錄越多，其實收益也就越大。

因此對于寫多讀少的業(yè)務（更新完立即查詢）change buffer 發(fā)揮的作用也就越大。如常見的賬單類，日志類等系統。

如果業(yè)務是更新完立即查詢，雖然可以將更新記錄放在 change buffer 中，但由于之后要馬上查詢數據頁，所以會立即觸發(fā) merge 過程。這樣隨機訪問 IO 次數并不會減少，反而增加了 change buffer 的維護代價，起到反效果。

更新過程

對于唯一索引來說，所有的更新操作都需要判斷是否違反唯一性約束。所以必須把所需要的數據頁讀入內存，然后直接更新就可以，不需要使用 change buffer. 所以 change buffer 只對普通索引有用。

具體分析下，對于一張表插入一個新記錄：

如果新記錄要更新的數據頁在內存中：

對于唯一索引，找到合適的位置，判斷有沒有沖突，插入值，語句結束。

對于普通索引：找到位置，插入值，語句結束。

所以數據頁在內存時，唯一和普通索引就差一個判斷的過程。可以忽略。

如果新記錄要更新的數據頁不在內存中：

對于唯一索引，將數據頁讀入內存，判斷沖突，插入，語句結束。

對于普通索引，將語句記錄在 change buffer 中，語句結束。

由于從磁盤到內存涉及隨機 IO 訪問，是數據庫成本最高的操作之一。普通索引比唯一索引減少的讀入操作，可以有很好的性能提升。

唯一或普通索引的選擇

通過在查詢和更新方面，兩者的比較。我們知道，在查詢過程中，除了極特殊情況，其實兩者的差異并不大。

主要的差異是在更新過程中，要更新的數據頁并不在內容中的情況。這時唯一索引，由于需要唯一性檢查，不能利用 change buffer. 多了從磁盤到內容讀取數據的過程，其中涉及隨機 IO 的訪問，相對來說效率就低了。

所以如果業(yè)務需要更新不錯的性能，這時可以選用普通索引。當然一切都是建立在能保證數據準確性的前提下。

當如果更新后來緊接著查詢操作，可以考慮關掉 change buffer. 其他的情況，change buffer 都能有很好的提升。

特別針對機械硬盤，change buffer 效果很顯著。

redo log 和 change buffer 的比較

InnoDB 中 redo log 的出現使其具有了 crash-safe 的能力，同時還提高了效率，通過 WAL 先寫日志，再寫磁盤。