MySQL中普通索引與唯一索引的差異詳解

這篇文章介紹了MySQL中普通索引和唯一索引的區別，講解很詳細，希望對學習MySQL的朋友有幫助！

MySQL中普通索引與唯一索引的差異詳解

一、查詢與更新上的差異

這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新效能的影響。建議盡量選擇普通索引。

（免費學習影片教學推薦：mysql影片教學）

#1.1 MySQL 的查詢操作

■普通索引

查找到第一個滿足條件的記錄後，繼續向後遍歷，直到第一個不滿足條件的記錄。

■ 唯一索引

由於索引定義了唯一性，在查找到第一個符合條件的記錄後，直接停止繼續檢索。

普通索引會多檢索一次，幾乎沒有影響。因為 InnoDB 的資料是依照資料頁為單位進行讀寫的，需要讀取資料時，並不是直接從磁碟讀取記錄，而是先把資料頁讀到內存，再去資料頁檢索。

一個資料頁預設16 KB，對於整數字段，一個資料頁可以放近千個key，除非要讀取的資料在資料頁的最後一筆記錄，就需要再讀一個資料頁，這種情況很少，對CPU的消耗基本上可以忽略了。

因此說，在查詢資料方面，普通索引和唯一索引沒差別。

1.2 MySQL 的更新操作

更新操作並不是直接對磁碟中的資料進行更新，是先把資料頁從磁碟讀入內存，然後再更新數據頁。

■ 普通索引

將資料頁從磁碟讀入內存，更新資料頁。

■ 唯一索引

將資料頁從磁碟讀入內存，判斷是否唯一，再更新資料頁。

由於 MySQL 中有一個 change buffer 的機制，會導致普通索引和唯一索引在更新上有一定的差異。

change buffer的作用是為了降低IO 操作，避免系統負載過高。 change buffer將資料寫入資料頁的過程，叫做merge。

如果需要更新的資料頁在記憶體中時，會直接更新資料頁；如果資料不在記憶體中，會先將更新操作記入change buffer，當下一次讀取資料頁時，順帶merge到數據頁中，change buffer也有定期merge策略。資料庫正常關閉的過程中，也會觸發merge。

對於唯一索引，更新前需要判斷資料是否唯一（不能和表中資料重複），如果資料頁在記憶體中，就可以直接判斷並且更新，如果不在記憶體中，就需要去磁碟中讀出來，判斷是否唯一，是的話就更新。 change buffer是用不到的。即使資料頁不在記憶體中，還是要讀出來。

change buffer 用的是 buffer pool 裡的內存，因此不能無限增大。 change buffer 的大小，可以透過參數 innodb_change_buffer_max_size 來動態設定。這個參數設定為 50 的時候，表示 change buffer 的大小最多只能佔用 buffer pool 的 50%。

結論：唯一索引用不了change buffer，只有普通索引可以用。

二、change buffer 和redo log的區別

#2.1 change buffer 的適用場景

change buffer的作用是降低更新操作的頻率，快取更新操作。這樣會有一個缺點，就是更新不及時，對於讀取操作比較頻繁的表，不建議使用 change buffer。

因為更新操作剛記錄進change buffer中，就讀取了該表，數據頁被讀到了內存中，數據馬上就merge到數據頁中了。這樣不但不會降低效能消耗，反而會增加維護change buffer的成本。

適用於寫多讀少的表。

2.2 change buffer 和 redo log 區別

#我們舉一個例子用來理解 redo log 和 change buffer。我們執行以下 SQL 語句：

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

假設，(id1,k1) 在資料頁 Page 1 中，(id2,k2) 在資料頁 Page 2 中。且 Page 1 在記憶體中，Page 2 不在記憶體中。

執行過程如下：

直接向Page 1 中寫入(id1,k1)；

在change buffer 中記下"向Page 2 寫入(id2 ,k2)"這條訊息；

將以上兩個動作記入redo log。

做完上面這些，事務就可以完成了。執行這條更新語句的成本很低，就是寫了兩個內存，然後寫了一處磁碟（兩次操作合在一起寫了一次磁碟），而且還是順序寫的。

這條更新語句，涉及了四個部分：記憶體、redo log（ib_log_fileX）、 資料表空間（t.ibd）、系統表空間（ibdata1）。

如果要读数据的话，过程是怎样的？

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

假设读操作在更新后不久，此时内存中还有 Page 1，没有 Page 2，那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1)；

将数据页 Page 2 读入内存，执行merge 操作，此时内存中的 Page 2 也有最新数据(id2,k2)；

需要注意的是：

redo log中的数据，可能还没有 flush 到磁盘，磁盘中的 Page 1 和 Page 2 中并没有最新数据，但我们依然可以拿到最新数据（内存中的 Page 1 就是最新的，Page 2 虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的 Page 2 就是最新的了。）

如果此时 MySQL 异常宕机了，比如服务器异常掉电，change buffer 中的数据会不会丢？

change buffer 中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在 change buffer 中，没有merge 到ibdata1，分 3 种情况：

（1）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

（2）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog 已写入到磁盘，这部分不会多丢失，异常重启后会先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer；

（3）change buffer 写入数据到内存，redo log 和 binlog 都已经fsync，直接从redo log 恢复，不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程，请关注PHP中文网！

以上是MySQL中普通索引與唯一索引的差異詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！