想象這樣一個(gè)場(chǎng)景，在設(shè)計(jì)一張用戶表時(shí)，每人的身份證號(hào)是唯一的，需要搜索。但由于身份證號(hào)字段較大，不好將其作為主鍵。在業(yè)務(wù)代碼已經(jīng)保證插入身份證唯一的情況下，可以選擇建立唯一索引和普通普通索引，這時(shí)該如何選擇呢？接下來(lái)，將從查詢和更新的執(zhí)行過(guò)程進(jìn)行分析。

查詢過(guò)程

假設(shè) k 是表 t 上的索引，在搜索 select id from t where k=5 時(shí)，會(huì)先從 k 這棵 B+ 的樹(shù)根開(kāi)始，按層搜索葉子節(jié)點(diǎn)，找到 k=5 的數(shù)據(jù)頁(yè)，然后在數(shù)據(jù)頁(yè)內(nèi)容進(jìn)行二分法定位。

對(duì)于普通索引，找到 k=5 的記錄后，會(huì)繼續(xù)向下查找一個(gè)，直到碰到第一個(gè)不是 5 的記錄結(jié)束。

對(duì)于唯一索引，由于取值唯一，找到后直接停止。

由于 InnoDB 是按照數(shù)據(jù)頁(yè)為單位(數(shù)據(jù)頁(yè)默認(rèn) 16 KB)進(jìn)行讀寫的，在讀取一條數(shù)據(jù)時(shí)，會(huì)將整個(gè)數(shù)據(jù)頁(yè)整體讀到內(nèi)存。 在讀入內(nèi)存的數(shù)據(jù)頁(yè)中，如果包含 k=5 的記錄，在查詢的情況下，唯一索引比普通索引多了一次查找和判斷的過(guò)程，可以忽略。

如果 k=5 是當(dāng)前數(shù)據(jù)頁(yè)的最后一條，就需要在讀取下一個(gè)數(shù)據(jù)頁(yè)。但這發(fā)生的概率較低，也可以忽略。

所以總得來(lái)說(shuō)，普通索引和唯一索引在查詢的過(guò)程中差異不大。

change buffer

在分析唯一索引和普通索引的影響前，先來(lái)認(rèn)識(shí)一下 change buffer 這個(gè)結(jié)構(gòu)。

什么是 change buffer ?

在執(zhí)行更新操作時(shí)，如果要更新的數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新，否則的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會(huì)將更新操作緩存在 change buffer 中，從而省去了從磁盤讀取數(shù)據(jù)頁(yè)的過(guò)程。在下次查詢操作讀取到恰好需要更新的數(shù)據(jù)頁(yè)時(shí)，會(huì)將 change buffer 的更新語(yǔ)句執(zhí)行，寫入數(shù)據(jù)頁(yè)。將操作應(yīng)用到硬盤的過(guò)程叫，merge. 后臺(tái)線程會(huì)定期 merge 或 數(shù)據(jù)庫(kù)正常關(guān)閉時(shí)，也會(huì)進(jìn)行 merge 操作。

change buffer 實(shí)際上是可以持久化到硬盤中的數(shù)據(jù)，也就是說(shuō)在內(nèi)存和硬盤上都 change buffer 的存在。change buffer 之前交 insert buffer，開(kāi)始只對(duì) insert buffer 有優(yōu)化，后來(lái)加上了對(duì) delete 和 update 的支持。

可以看到，先將更新操作記錄在 change buffer，減少了將磁盤數(shù)據(jù)頁(yè)讀取到內(nèi)存的過(guò)程，語(yǔ)句的執(zhí)行速度會(huì)有很明顯的提升。同時(shí)，將數(shù)據(jù)讀入內(nèi)存，會(huì)占用 buffer pool 內(nèi)存，所以減少讀操作，還提高了內(nèi)存使用率。

Buffer Pool 是內(nèi)存中的一個(gè)區(qū)域，InnoDB 在訪問(wèn)表和索引數(shù)據(jù)時(shí)會(huì)在其中進(jìn)行緩存。允許在內(nèi)存中直接更新經(jīng)常使用的數(shù)據(jù)，來(lái)加快處理速度。在一些專用的服務(wù)器上，會(huì)將 80% 的物理內(nèi)存分為 buffer pool.

可以通過(guò) innodb_change_buffer_max_size 來(lái)設(shè)置 change buffer 占用 buffer pool 的大小。

change buffer 應(yīng)用場(chǎng)景？

如上面提到，change buffer 預(yù)先保存了更新記錄，減少了讀取數(shù)據(jù)頁(yè)的過(guò)程，從而提高性能。也就是說(shuō)如果 change buffer 中針對(duì)不同的數(shù)據(jù)頁(yè)如果包含的更新記錄越多，其實(shí)收益也就越大。

因此對(duì)于寫多讀少的業(yè)務(wù)(更新完立即查詢)change buffer 發(fā)揮的作用也就越大。如常見(jiàn)的賬單類，日志類等系統(tǒng)。

如果業(yè)務(wù)是更新完立即查詢，雖然可以將更新記錄放在 change buffer 中，但由于之后要馬上查詢數(shù)據(jù)頁(yè)，所以會(huì)立即觸發(fā) merge 過(guò)程。這樣隨機(jī)訪問(wèn) IO 次數(shù)并不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)，起到反效果。

更新過(guò)程

對(duì)于唯一索引來(lái)說(shuō)，所有的更新操作都需要判斷是否違反唯一性約束。所以必須把所需要的數(shù)據(jù)頁(yè)讀入內(nèi)存，然后直接更新就可以，不需要使用 change buffer. 所以 change buffer 只對(duì)普通索引有用。

具體分析下，對(duì)于一張表插入一個(gè)新記錄：

如果新記錄要更新的數(shù)據(jù)頁(yè)在內(nèi)存中：

對(duì)于唯一索引，找到合適的位置，判斷有沒(méi)有沖突，插入值，語(yǔ)句結(jié)束。

對(duì)于普通索引：找到位置，插入值，語(yǔ)句結(jié)束。

所以數(shù)據(jù)頁(yè)在內(nèi)存時(shí)，唯一和普通索引就差一個(gè)判斷的過(guò)程。可以忽略。

如果新記錄要更新的數(shù)據(jù)頁(yè)不在內(nèi)存中：

對(duì)于唯一索引，將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷沖突，插入，語(yǔ)句結(jié)束。

對(duì)于普通索引，將語(yǔ)句記錄在 change buffer 中，語(yǔ)句結(jié)束。

由于從磁盤到內(nèi)存涉及隨機(jī) IO 訪問(wèn)，是數(shù)據(jù)庫(kù)成本最高的操作之一。普通索引比唯一索引減少的讀入操作，可以有很好的性能提升。

唯一或普通索引的選擇

通過(guò)在查詢和更新方面，兩者的比較。我們知道，在查詢過(guò)程中，除了極特殊情況，其實(shí)兩者的差異并不大。

主要的差異是在更新過(guò)程中，要更新的數(shù)據(jù)頁(yè)并不在內(nèi)容中的情況。這時(shí)唯一索引，由于需要唯一性檢查，不能利用 change buffer. 多了從磁盤到內(nèi)容讀取數(shù)據(jù)的過(guò)程，其中涉及隨機(jī) IO 的訪問(wèn)，相對(duì)來(lái)說(shuō)效率就低了。

所以如果業(yè)務(wù)需要更新不錯(cuò)的性能，這時(shí)可以選用普通索引。當(dāng)然一切都是建立在能保證數(shù)據(jù)準(zhǔn)確性的前提下。

當(dāng)如果更新后來(lái)緊接著查詢操作，可以考慮關(guān)掉 change buffer. 其他的情況，change buffer 都能有很好的提升。

特別針對(duì)機(jī)械硬盤，change buffer 效果很顯著。

redo log 和 change buffer 的比較

InnoDB 中 redo log 的出現(xiàn)使其具有了 crash-safe 的能力，同時(shí)還提高了效率，通過(guò) WAL 先寫日志，再寫磁盤。

而 change buffer 是節(jié)省了從磁盤讀入數(shù)據(jù)頁(yè)到內(nèi)存的隨機(jī)IO過(guò)程。

下面通過(guò)一條插入語(yǔ)句來(lái)分析下兩者間的關(guān)系：

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

假設(shè) k 為普通索引，k1 所插入的數(shù)據(jù)頁(yè)在內(nèi)存中， k2 不在。

執(zhí)行插入操作時(shí)，主要涉及了圖中這四部分的內(nèi)容：

InnoDB buffer pool：內(nèi)存區(qū)域

redo log：日志

system table space(ibdata1):系統(tǒng)表空間

data(t.idb): 數(shù)據(jù)表空間

innodb_file_per_table 開(kāi)啟時(shí)，表被創(chuàng)建在獨(dú)立的表空間下，否則的話被創(chuàng)建在系統(tǒng)的表空間下。

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

k1 所在的 page1 在內(nèi)存中，直接更新內(nèi)存

k2 所在的 page2 不在內(nèi)存中，記錄在 change buffer.

將 k1 和 k2 的操作記錄在 redo log.

提交事務(wù)。

可以看到這條更新語(yǔ)句(包括插入，刪除，更新操作)執(zhí)行成本很低，兩次寫入內(nèi)存，1次順序?qū)懭氪疟P。虛線的操作，是后臺(tái)操作，不影響響應(yīng)時(shí)間。

再來(lái)看一條查詢語(yǔ)句：

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

假設(shè)讀語(yǔ)句發(fā)生在更新語(yǔ)句不久，內(nèi)存數(shù)據(jù)還在，此時(shí)讀操作就和系統(tǒng)表空間和 redo log 無(wú)關(guān)。

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

讀取 k1 所在的 page1，在內(nèi)存中，直接返回。注意，并沒(méi)有讀磁盤上的數(shù)據(jù)，而且磁盤上的數(shù)據(jù)還有可能是之前的版本的。

讀取 k2 所在的 page2，這時(shí)需要將 page2 從磁盤加載到內(nèi)存，并應(yīng)用 change buffer 的內(nèi)容，然后返回正確的結(jié)果。從這里也能看出，change buffer 不適用于更新完立馬去讀的情況。

從更新的過(guò)程來(lái)看， redo log 將隨機(jī)寫磁盤的 IO 轉(zhuǎn)換成了順序?qū)?#xff0c;而 change buffer 則是節(jié)省了隨機(jī)讀磁盤的 IO 消耗。

參考

總結(jié)

以上是生活随笔為你收集整理的mysql唯一索引弊端_MySQL 关于唯一索引和普通索引的抉择的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。