MySQL中普通索引和唯一索引有什么區(qū)別？相信有很多人都不太了解，今天小編為了讓大家更加了解MySQL中普通索引和唯一索引，所以給大家總結(jié)了以下內(nèi)容，一起往下看吧。

成都創(chuàng)新互聯(lián)是一家專(zhuān)業(yè)提供偃師企業(yè)網(wǎng)站建設(shè),專(zhuān)注與網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、html5、小程序制作等業(yè)務(wù)。10年已為偃師眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專(zhuān)業(yè)網(wǎng)絡(luò)公司優(yōu)惠進(jìn)行中。

MySQL中普通索引和唯一索引的區(qū)別詳解

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

這兩類(lèi)索引在查詢(xún)能力上是沒(méi)差別的，主要考慮的是對(duì)更新性能的影響。建議盡量選擇普通索引。

1.1 MySQL 的查詢(xún)操作

■ 普通索引

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

■ 唯一索引

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

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

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

因此說(shuō)，在查詢(xún)數(shù)據(jù)方面，普通索引和唯一索引沒(méi)差別。

1.2 MySQL 的更新操作

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

■ 普通索引

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

■ 唯一索引

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

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

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

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

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

change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無(wú)限增大。change buffer 的大小，可以通過(guò)參數(shù) innodb_change_buffer_max_size 來(lái)動(dòng)態(tài)設(shè)置。這個(gè)參數(shù)設(shè)置為 50 的時(shí)候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

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

二、change buffer 和 redo log的區(qū)別

2.1 change buffer 的適用場(chǎng)景

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

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

適用于寫(xiě)多讀少的表。

2.2 change buffer 和 redo log 區(qū)別

我們舉一個(gè)例子用來(lái)理解 redo log 和 change buffer。我們執(zhí)行以下 SQL 語(yǔ)句：

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

假設(shè)，(id1,k1) 在數(shù)據(jù)頁(yè) Page 1 中，(id2,k2) 在數(shù)據(jù)頁(yè) Page 2 中。并且 Page 1 在內(nèi)存中，Page 2 不在內(nèi)存中。

執(zhí)行過(guò)程如下：

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

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

將以上兩個(gè)動(dòng)作記入redo log。

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

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

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

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

假設(shè)讀操作在更新后不久，此時(shí)內(nèi)存中還有 Page 1，沒(méi)有 Page 2，那么讀操作就和 redo log 以及 ibdata1 無(wú)關(guān)了。

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

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

需要注意的是：

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

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

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

（1）change buffer 寫(xiě)入數(shù)據(jù)到內(nèi)存，redo log 也已經(jīng)寫(xiě)入（ib-log-filex），但是未 commit，binlog中也沒(méi)有fsync到磁盤(pán)，這部分?jǐn)?shù)據(jù)會(huì)丟失；

（2）change buffer 寫(xiě)入數(shù)據(jù)到內(nèi)存，redo log 也已經(jīng)寫(xiě)入（ib-log-filex），但是未 commit，binlog 已寫(xiě)入到磁盤(pán)，這部分不會(huì)多丟失，異常重啟后會(huì)先從 binlog 恢復(fù) redo log，再?gòu)?redo log 恢復(fù) change buffer；

（3）change buffer 寫(xiě)入數(shù)據(jù)到內(nèi)存，redo log 和 binlog 都已經(jīng)fsync，直接從redo log 恢復(fù)，不會(huì)丟失。

redo log 主要節(jié)省的是隨機(jī)寫(xiě)磁盤(pán)的 IO 消耗（轉(zhuǎn)成順序?qū)懀?，?change buffer 主要節(jié)省的則是隨機(jī)讀磁盤(pán)的 IO 消耗

以上就是MySQL中普通索引和唯一索引有什么區(qū)別的詳細(xì)內(nèi)容了，看完之后是否有所收獲呢？如果想了解更多相關(guān)內(nèi)容，歡迎來(lái)創(chuàng)新互聯(lián)行業(yè)資訊！