數(shù)據(jù)庫是一種以某種方式存儲在一起的數(shù)據(jù)集合，可以與多個用戶共享，具有最小的可能冗余，并且獨(dú)立于應(yīng)用程序。可以將其視為電子文件柜-存儲電子文件的地方，用戶可以在文件中添加，查詢，更新和刪除數(shù)據(jù)。數(shù)據(jù)庫的種類有很多，其中MySQL數(shù)據(jù)庫中有普通索引和唯一索引兩種，很多人都不知道二者之間的區(qū)別。

　　MySQL普通索引和唯一索引有哪些區(qū)別？

　　一、查詢和更新上的區(qū)別

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

　　1.1MySQL的查詢操作

?普通索引

查找到第一個滿足條件的記錄后，繼續(xù)向后遍歷，直到第一個不滿足條件的記錄。

?唯一索引

由于索引定義了唯一性，查找到第一個滿足條件的記錄后，直接停止繼續(xù)檢索。

普通索引會多檢索一次，幾乎沒有影響。因?yàn)镮nnoDB的數(shù)據(jù)是按照數(shù)據(jù)頁為單位進(jìn)行讀寫的，需要讀取數(shù)據(jù)時，并不是直接從磁盤讀取記錄，而是先把數(shù)據(jù)頁讀到內(nèi)存，再去數(shù)據(jù)頁中檢索。

一個數(shù)據(jù)頁默認(rèn)16KB，對于整型字段，一個數(shù)據(jù)頁可以放近千個key，除非要讀取的數(shù)據(jù)在數(shù)據(jù)頁的最后一條記錄，就需要再讀一個數(shù)據(jù)頁，這種情況很少，對CPU的消耗基本可以忽略了。

因此說，在查詢數(shù)據(jù)方面，普通索引和唯一索引沒差別。

　　1.2MySQL的更新操作

更新操作并不是直接對磁盤中的數(shù)據(jù)進(jìn)行更新，是先把數(shù)據(jù)頁從磁盤讀入內(nèi)存，再更新數(shù)據(jù)頁。

?普通索引

將數(shù)據(jù)頁從磁盤讀入內(nèi)存，更新數(shù)據(jù)頁。

?唯一索引

將數(shù)據(jù)頁從磁盤讀入內(nèi)存，判斷是否唯一，再更新數(shù)據(jù)頁。

由于MySQL中有個changebuffer的機(jī)制，會導(dǎo)致普通索引和唯一索引在更新上有一定的區(qū)別。

changebuffer的作用是為了降低IO操作，避免系統(tǒng)負(fù)載過高。changebuffer將數(shù)據(jù)寫入數(shù)據(jù)頁的過程，叫做merge。

如果需要更新的數(shù)據(jù)頁在內(nèi)存中時，會直接更新數(shù)據(jù)頁;如果數(shù)據(jù)不在內(nèi)存中，會先將更新操作記入changebuffer，當(dāng)下一次讀取數(shù)據(jù)頁時，順帶merge到數(shù)據(jù)頁中，changebuffer也有定期merge策略。數(shù)據(jù)庫正常關(guān)閉的過程中，也會觸發(fā)merge。

對于唯一索引，更新前需要判斷數(shù)據(jù)是否唯一（不能和表中數(shù)據(jù)重復(fù)），如果數(shù)據(jù)頁在內(nèi)存中，就可以直接判斷并且更新，如果不在內(nèi)存中，就需要去磁盤中讀出來，判斷一下是否唯一，是的話就更新。changebuffer是用不到的。即使數(shù)據(jù)頁不在內(nèi)存中，還是要讀出來。

changebuffer用的是bufferpool里的內(nèi)存，因此不能無限增大。changebuffer的大小，可以通過參數(shù)innodb_change_buffer_max_size來動態(tài)設(shè)置。這個參數(shù)設(shè)置為50的時候，表示changebuffer的大小最多只能占用bufferpool的50%。

結(jié)論：唯一索引用不了changebuffer，只有普通索引可以用。

　　二、changebuffer和redolog的區(qū)別

2.1changebuffer的適用場景

changebuffer的作用是降低更新操作的頻率，緩存更新操作。這樣會有一個缺點(diǎn)，就是更新不及時，對于讀操作比較頻繁的表，不建議使用changebuffer。

因?yàn)楦虏僮鲃傆涗涍M(jìn)changebuffer中，就讀取了該表，數(shù)據(jù)頁被讀到了內(nèi)存中，數(shù)據(jù)馬上就merge到數(shù)據(jù)頁中了。這樣不僅不會降低性能消耗，反而會增加維護(hù)changebuffer的成本。

2.2changebuffer和redolog區(qū)別

我們舉一個例子用來理解redolog和changebuffer。我們執(zhí)行以下SQL語句：

mysql>insertintot(id,k)values(id1,k1),(id2,k2);

假設(shè)，(id1,k1)在數(shù)據(jù)頁P(yáng)age1中，(id2,k2)在數(shù)據(jù)頁P(yáng)age2中。并且Page1在內(nèi)存中，Page2不在內(nèi)存中。

執(zhí)行過程如下：

?直接向Page1中寫入(id1,k1)；

?在changebuffer中記下"向Page2中寫入(id2,k2)"這條信息；

?將以上兩個動作記入redolog。

做完上面這些，事務(wù)就可以完成了。執(zhí)行這條更新語句的成本很低，就是寫了兩處內(nèi)存，然后寫了一處磁盤（兩次操作合在一起寫了一次磁盤），而且還是順序?qū)懙摹?/p>

這條更新語句，涉及了四個部分：內(nèi)存、redolog(ib_log_fileX)、數(shù)據(jù)表空間(t.ibd)、系統(tǒng)表空間(ibdata1)。

如果要讀數(shù)據(jù)的話，過程是怎樣的？

mysql>select*fromtwherekin(k1,k2)；

假設(shè)讀操作在更新后不久，此時內(nèi)存中還有Page1，沒有Page2，那么讀操作就和redolog以及ibdata1無關(guān)了。

?從內(nèi)存中獲取到Page1上的最新數(shù)據(jù)(id1,k1)；

?將數(shù)據(jù)頁P(yáng)age2讀入內(nèi)存，執(zhí)行merge操作，此時內(nèi)存中的Page2也有最新數(shù)據(jù)(id2,k2)；

　　需要注意的是：

?redolog中的數(shù)據(jù)，可能還沒有flush到磁盤，磁盤中的Page1和Page2中并沒有最新數(shù)據(jù)，但我們依然可以拿到最新數(shù)據(jù)（內(nèi)存中的Page1就是最新的，Page2雖然不是最新的，但是從磁盤讀到內(nèi)存中后，執(zhí)行了merge操作，內(nèi)存中的Page2就是最新的了。）

?如果此時MySQL異常宕機(jī)了，比如服務(wù)器異常掉電，changebuffer中的數(shù)據(jù)會不會丟？

changebuffer中的數(shù)據(jù)分為兩部分，一部分是已經(jīng)merge到ibdata1中的數(shù)據(jù)，這部分?jǐn)?shù)據(jù)已經(jīng)持久化，不會丟失。另一部分?jǐn)?shù)據(jù)，還在changebuffer中，沒有merge到ibdata1，分3種情況：

?changebuffer寫入數(shù)據(jù)到內(nèi)存，redolog也已經(jīng)寫入(ib-log-filex)，但是未commit，binlog中也沒有fsync到磁盤，這部分?jǐn)?shù)據(jù)會丟失；

?changebuffer寫入數(shù)據(jù)到內(nèi)存，redolog也已經(jīng)寫入(ib-log-filex)，但是未commit，binlog已寫入到磁盤，這部分不會多丟失，異常重啟后會先從binlog恢復(fù)redolog，再從redolog恢復(fù)changebuffer；

?changebuffer寫入數(shù)據(jù)到內(nèi)存，redolog和binlog都已經(jīng)fsync，直接從redolog恢復(fù)，不會丟失。

redolog主要節(jié)省的是隨機(jī)寫磁盤的IO消耗（轉(zhuǎn)成順序?qū)懀鴆hangebuffer主要節(jié)省的則是隨機(jī)讀磁盤的IO消耗。

以上就是關(guān)于MySQL普通索引和唯一索引有哪些區(qū)別的全部內(nèi)容介紹，想了解更多關(guān)于數(shù)據(jù)庫的信息，請繼續(xù)關(guān)注中培偉業(yè)。