MySQL 普通索引和唯一索引的區別詳解

Posted on by

1 概念區分

  • 普通索引和唯一索引

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

  • 主鍵和唯一索引

主鍵保證數據庫裡面的每一行都是唯一的，比如身份證，學號等，在表中要求唯一，不重復。唯一索引的作用跟主鍵的作用一樣。 不同的是，在一張表裡面隻能有一個主鍵，主鍵不能為空，唯一索引可以有多個，唯一索引可以有一條記錄為空，即保證跟別人不一樣就行。 比如學生表，在學校裡面一般用學號做主鍵，身份證則弄成唯一索引；而到瞭教育局，他們就把身份證號弄成主鍵，學號換成瞭唯一索引。 選誰做表的主鍵，要看實際應用，主鍵不能為空。

2 案例引入

某居民系統，每人有唯一身份證號。如果系統需要按身份證號查姓名，就會執行類似如下SQL：

select name from CUser where id_card = 'ooxx';

然後你肯定會在id_card字段建索引。但id_card字段較大，不推薦將其做主鍵。於是現有倆選擇：

  1. 給id_card字段創建唯一索引
  2. 創建一個普通索引

假定業務代碼已保證不會寫入重復的身份證號，這兩個選擇邏輯上都正確。但從性能角度考慮，唯一索引還是普通索引呢？

再看如下案例：假設字段 k 上的值都不重復。

  • InnoDB的索引組織結構：

接下來分析性能。

3 查詢性能

select id from T where k=4

通過B+樹從樹根開始層序遍歷到葉節點，可認為數據頁內部是通過二分法搜索。

  • 普通索引，查找到滿足條件的第一個記錄(4,400)後，需查找下個記錄，直到碰到第一個不滿足k=4的記錄
  • 唯一索引，由於索引具備唯一性，查找到第一個滿足條件的記錄後，就會停止檢索

看起來性能差距很微小。

InnoDB數據按數據頁單位讀寫。即讀一條記錄時，並非將該一個記錄從磁盤讀出，而以頁為單位，將其整體讀入內存。

因此普通索引，要多做一次“查找和判斷下一條記錄”的操作，也就一次指針尋找和一次計算。 如果k=4記錄恰為該數據頁最後一個記錄，那麼要取下個記錄，還得讀取下個數據頁，操作稍微復雜。 對整型字段，一個數據頁可存近千key，因此這種情況概率其實也很低。因此計算平均性能差異時，可認為該操作成本對現在CPU開銷忽略不計。

我們知道 MySQL 有 change buffer。

4 更新性能

現在來看往表中插入一個新記錄(4,400)，InnoDB會做什麼？

需要區分該記錄要更新的目標頁是否在內存：

4.1 在內存

  • 唯一索引

找到3和5之間位置，判斷到沒有沖突，插入值，語句執行結束。

  • 普通索引

找到3和5之間位置，插入值，語句執行結束。

普通索引和唯一索引對更新語句性能影響的差別，隻是一個判斷，耗費微小CPU時間。

4.2 不在內存

  • 唯一索引

需將數據頁讀入內存，判斷到沒有沖突，插入值，語句執行結束。

  • 普通索引

將更新記錄在change buffer，語句執行結束。

將數據從磁盤讀入內存涉及隨機IO訪問，是數據庫裡面成本最高操作之一。而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所在數據頁在內存(InnoDB buffer pool)，k2數據頁不在內存。

  • 帶change buffer的更新流程圖，圖中兩個箭頭都是後臺操作，不影響更新響應。

該更新做瞭如下操作：

  1. Page1在內存，直接更新內存
  2. Page2不在內存，就在change buffer區，緩存下“往Page2插一行記錄”的信息
  3. 將前兩個動作記入redo log

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

6.2 怎麼處理之後的讀請求？

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

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

  • 帶change buffer的讀過程

讀Page1時，直接從內存返回。 WAL之後如果讀數據，是不是一定要讀盤，是不是一定要從redo log裡面把數據更新以後才可以返回？其實不用。 看上圖狀態，雖然磁盤上還是之前數據，但這裡直接從內存返回結果，結果正確。

要讀Page2時，需把Page2從磁盤讀入內存，然後應用change buffer裡面的操作日志，生成一個正確版本並返回結果。 可見直到需讀Page2時，該數據頁才被讀入內存。

所以，要簡單對比這倆機制對更新性能影響

  • redo log 主要節省隨機寫磁盤的IO消耗（轉成順序寫）
  • change buffer主要節省隨機讀磁盤的IO消耗

7 總結

由於唯一索引用不瞭change buffer的優化機制，因此如果業務可以接受，從性能角度，推薦優先考慮非唯一索引。

7.1 關於到底是否使用唯一索引

主要糾結在“業務可能無法確保”。本文前提是“業務代碼已經保證不會寫入重復數據”下，討論性能問題。

如果業務不能保證，或者業務就是要求數據庫來做約束，那麼沒得選，必須創建唯一索引。這種情況下，本文意義在於，如果碰上大量插入數據慢、內存命中率低時，多提供一個排查思路。
然後，在一些“歸檔庫”的場景，可考慮使用唯一索引的。比如，線上數據隻需保留半年，然後歷史數據保存在歸檔庫。此時，歸檔數據已是確保沒有唯一鍵沖突。要提高歸檔效率，可考慮把表的唯一索引改普通索引。

7.2 如果某次寫入使用change buffer，之後主機異常重啟，是否會丟失change buffer的數據？

不會丟失。 雖然是隻更新內存，但在事務提交時，我們把change buffer的操作也記錄到redo log，所以崩潰恢復時，change buffer也能找回。

7.3 merge的過程是否會把數據直接寫回磁盤？

merge執行流程

  1. 從磁盤讀入數據頁到內存（老版本數據頁）
  2. 從change buffer找出該數據頁的change buffer 記錄(可能有多個），依次應用，得到新版數據頁
  3. 寫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。

以上就是MySQL 普通索引和唯一索引的區別詳解的詳細內容，更多關於MySQL 普通索引和唯一索引的資料請關註WalkonNet其它相關文章！

推薦閱讀：