MySQL普通索引和唯一索引到底什麼差別?（上）

1 概念區分

普通索引 V.S 唯一索引

普通索引可重複，唯一索引和主鍵一樣不能重複。

唯一索引可作為資料的一個合法驗證手段，例如學生表的身份證号碼字段，我們人為規定該字段不得重複，那麼就使用唯一索引。（一般設定學号字段為主鍵）

主鍵 V.S 唯一索引

主鍵保證DB的每一行都是唯一、不重複，比如身份證，學号等，不重複。

唯一索引的作用跟主鍵一樣。

不同的是，在一張表裡面隻能有一個主鍵，主鍵不能為空，但唯一索引可以有多個。唯一索引可以有一條記錄為null。

比如學生表：

• 在學校，一般用學号做主鍵，身份證号作為唯一索引
• 在教育局，就把身份證号弄成主鍵，學号作為唯一索引

是以選誰做主鍵，取決于業務需求。

2 案例

某居民系統，每人有唯一身份證号。若系統要按身份證号查姓名：

select name from CUser where id_card = 'ooxx';      

估計你會在id_card建索引。但id_card字段較大，不推薦做主鍵。于是現在有如下選擇：

在id_card建立唯一索引

建立一個普通索引

假定業務代碼已經確定不會寫入重複身份證号，這兩個選擇邏輯上都是正确的。

性能優化角度考慮，選擇唯一索引還是普通索引呢？

假設字段 k 上的值都不重複。

InnoDB索引結構

接下來分析性能。

3 查詢性能

select id from T where k=4      

通過B+樹從root開始層序周遊到葉節點，資料頁内部通過二分搜尋：

普通索引

查找到滿足條件的第一個記錄(4,400)後，需查找下個記錄，直到碰到第一個不滿足k=4的記錄

唯一索引

由于索引具備唯一性，查到第一個滿足條件的，就會停止搜尋

看起來性能差距很小。

InnoDB資料按資料頁機關讀寫。即讀一條記錄時，并非将該一個記錄從磁盤讀出，而以頁為機關，将其整體讀入記憶體。

是以普通索引，多了一次“查找和判斷下一條記錄”的操作，即一次指針尋找和一次計算。

若k=4記錄恰為該資料頁的最後一個記錄，則此時要取下個記錄，還得讀取下個資料頁。

對整型字段，一個資料頁可存近千個key，是以這種情況機率其實也很低。是以計算平均性能差異時，可認為該操作成本對現在CPU開銷忽略不計。

4 更新性能

往表中插入一個新記錄(4,400)，InnoDB會有什麼反應？

這要看該記錄要更新的目标頁是否在記憶體：

在記憶體

• 找到3和5之間的位置，插入值，結束。
• 找到3和5之間的位置，

  判斷到沒有沖突

  ，插入值，結束。隻是一個判斷的差别，耗費微小CPU時間。

不在記憶體

• 将資料頁讀入記憶體，判斷到沒有沖突，插入值，結束。
• 将更新記錄在change buffer，結束。
• 将資料從磁盤讀入記憶體涉及随機I/O通路，是DB裡成本最高的操作之一。而change buffer可以減少随機磁盤通路，是以更新性能提升明顯。

5 索引選擇的最佳實踐

普通索引、唯一索引在查詢性能上無差别，主要考慮更新性能。是以，推薦盡量選擇普通索引。

若所有更新後面，都緊跟對該記錄的查詢，那就該關閉change buffer。其它情況下，change buffer都能提升更新性能。

普通索引和change buffer的配合使用，對于資料量大的表的更新優化還是明顯的。

在使用機械硬碟時，change buffer的收益也很大。

是以，當你有個類似“曆史資料”的庫，并且出于成本考慮用機械硬碟，應該關注這些表裡的索引，盡量使用普通索引，把change buffer 開大，確定“曆史資料”表的資料寫性能。