MySQL的普通索引和唯一索引到底什么區別？

# Mysql的普通索引和唯一索引到底什么區別？

1 概念區分

普通索引 V.S 唯一索引

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

唯一索引可作為數據的一個合法驗證手段，例如學生表的身份證號碼字段，人為規定該字段不得重復，那么就使用唯一索引。（一般設置學號字段為主鍵）

主鍵 V.S 唯一索引

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

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

但在一張表里面只能有一個主鍵，不能為空，唯一索引可有多個。唯一索引可有一條記錄為null。

比如學生表：

在學校，一般用學號做主鍵，身份證號作為唯一索引

在教育局，就把身份證號弄成主鍵，學號作為唯一索引

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

2 案例

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

select name from CUser where id_card = 'ooxx';

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開大，確保“歷史數據”表的數據寫性能。

6 change buffer 和 redo log

WAL 提升性能的核心機制，也是盡量減少隨機讀寫，它們有啥區別？

6.1 插入流程

insert into t(id,k) values (id1,k1),(id2,k2);

假設當前k索引樹的狀態，查找到位置后：

k1所在數據頁在內存(buffer pool)

k2數據頁不在內存

看如下流程：

圖中箭頭都是后臺操作，不影響更新請求的響應。

該更新做了如下操作：

Page1在內存，直接更新內存

Page2不在內存，就往change buffer區，緩存一個“往Page2插一行記錄”的信息

將前兩個動作記入redo log

至此，事務完成。執行該更新語句成本很低，只是寫兩處內存，然后寫一處磁盤（前兩次操作合在一起寫了一次磁盤），還是順序寫。

6.2 處理之后的讀請求

select * from t where k in (k1, k2);

讀語句緊隨更新語句之后，這時內存中的數據都還在，所以此時這倆讀操作就與系統表空間和 redo log 無關。

讀Page1時，直接從內存返回。

WAL之后若讀數據，是否一定要讀盤？一定要從redo log將數據更新后才能返回？

其實不用。看上圖狀態，雖然磁盤上還是之前的數據，但這里直接從內存返回結果，結果是正確的。

讀Page2時，需將Page2從磁盤讀入內存，然后應用change buffer里的操作日志，生成一個正確版本并返回結果。所以一直到需要讀Page2時，該數據頁才會被從磁盤讀入內存。

綜上，這倆機制的更新性能：

redo log 主要節省隨機寫磁盤的I/O消耗（轉成順序寫）

change buffer主要節省隨機讀磁盤的I/O消耗

7 總結

因為唯一索引用不了change buffer，若業務可以接受，從性能角度，優先考慮非唯一索引。

到底何時使用唯一索引

問題就在于“業務可能無法確保”，而本文前提是“業務代碼已保證不會寫入重復數據”，才討論的性能問題。

若業務無法保證或業務就是要求數據庫來做約束

沒有撤退可言，必須創建唯一索引。那本文意義就在于，若碰上大量插入數據慢、內存命中率低時，多提供了一個排查思路

“歸檔庫”場景，可考慮使用唯一索引

比如線上數據只需保留半年，然后歷史數據存在歸檔庫。此時，歸檔數據已是確保沒有唯一鍵沖突。要提高歸檔效率，可考慮把表的唯一索引改為普通索引。

若某次寫入使用了change buffer，之后主機異常重啟，是否會丟失change buffer數據

不會！雖然是只更新內存，但在事務提交時，change buffer的操作也被記錄到了redo log。所以崩潰恢復時，change buffer也能找回。

merge時是否會把數據直接寫回磁盤

從磁盤讀入數據頁到內存（老版本數據頁）

從change buffer找出該數據頁的change buffer 記錄(可能多個），依次應用，得到新版數據頁

寫redo log

該redo log包含數據的變更和change buffer的變更

至此merge結束。

這時，數據頁和內存中change buffer對應磁盤位置都尚未修改，是臟頁，之后各自刷回自己物理數據，就是另外一過程。

在構造第一個例子的過程，通過session A的配合，讓session B刪除數據后又重新插入一遍數據，然后就發現explain結果中，rows字段從10001變成37000多。

而如果沒有session A的配合，只是單獨執行delete from t 、call idata()、explain這三句話，會看到rows字段其實還是10000左右。這是什么原因呢？

如果沒有復現，檢查

隔離級別是不是RR（Repeatable Read，可重復讀）

創建的表t是不是InnoDB引擎

為什么經過這個操作序列，explain的結果就不對了？

delete 語句刪掉了所有的數據，然后再通過call idata()插入了10萬行數據，看上去是覆蓋了原來10萬行。

但session A開啟了事務并沒有提交，所以之前插入的10萬行數據是不能刪除的。這樣，之前的數據每行數據都有兩個版本，舊版本是delete之前數據，新版本是標記deleted的數據。

這樣，索引a上的數據其實有兩份。

不對啊，主鍵上的數據也不能刪，那沒有使用force index的語句，使用explain命令看到的掃描行數為什么還是100000左右？（潛臺詞，如果這個也翻倍，也許優化器還會認為選字段a作為索引更合適）

是的，不過這個是主鍵，主鍵是直接按照表的行數來估計的。而表的行數，優化器直接用的是show table status的值。

大家的機器如果IO能力比較差的話，做這個驗證的時候，可以把innodb_flush_log_at_trx_commit 和 sync_binlog 都設成0。

參考

https://dev.Mysql.com/doc/refman/8.0/en/innodb-change-buffer.html

MySQL

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

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