所謂普通索引，就是在創(chuàng)建索引時(shí)，不附加任何限制條件(唯一、非空等限制)。該類(lèi)型的索引可以創(chuàng)建在任何數(shù)據(jù)類(lèi)型的字段上。

所謂唯一索引，就是在創(chuàng)建索引時(shí)，限制索引的值必須是唯一的。通過(guò)該類(lèi)型的索引可以更快速地查詢某條記錄。

普通索引還是唯一索引？

假設(shè)你在維護(hù)一個(gè)市民系統(tǒng)，每個(gè)人都有一個(gè)唯一的身份證號(hào)，而且業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫(xiě)入兩個(gè)重復(fù)的身份證號(hào)。如果市民系統(tǒng)需要按照身份證號(hào)查姓名，就會(huì)執(zhí)行類(lèi)似這樣的SQL語(yǔ)句：

select name from CUser where id_card='xxxxxxxyyyyyyzzzzz';

所以你一定會(huì)考慮在id_card字段上建索引。由于身份證號(hào)字段比較大，這里不建議將身份證號(hào)當(dāng)做主鍵，現(xiàn)在有兩個(gè)選擇，要么給id_card字段創(chuàng)建唯一索引，要么創(chuàng)建一個(gè)普通索引。如果業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫(xiě)入重復(fù)的身份證號(hào)，那么這兩個(gè)選擇邏輯上都是正確的。

現(xiàn)在需要思考的是，從性能的角度考慮，我們應(yīng)該選擇唯一索引還是普通索引？選擇的依據(jù)又是什么呢？我們以<深入淺出索引(上)>中的例子來(lái)說(shuō)明，假設(shè)字段k上的值都不重復(fù)。

接下來(lái)，我們就從這兩種索引對(duì)查詢語(yǔ)句和更新語(yǔ)句的性能影響來(lái)進(jìn)行分析。

查詢過(guò)程

假設(shè)，執(zhí)行的查詢語(yǔ)句是select id from T where k=5。這個(gè)查詢語(yǔ)句在索引樹(shù)上查找的過(guò)程，先是通過(guò)B+樹(shù)從樹(shù)根開(kāi)始，按層搜索到葉子節(jié)點(diǎn)，也就是圖中右下角的這個(gè)數(shù)據(jù)頁(yè)，然后可以認(rèn)為數(shù)據(jù)頁(yè)內(nèi)部通過(guò)二分法來(lái)定位記錄。

• 對(duì)于普通索引來(lái)說(shuō)，查找到滿足條件的第一個(gè)記錄(5, 500)后，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足k=5條件的記錄。
• 對(duì)于唯一索引來(lái)說(shuō)，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止繼續(xù)檢索。

那么，這個(gè)不同帶來(lái)的性能差距會(huì)有多少呢？答案是，微乎其微。

我們都知道，InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁(yè)為單位來(lái)讀寫(xiě)的。也就是說(shuō)，當(dāng)需要讀一條記錄的時(shí)候，并不是將這個(gè)記錄本身從磁盤(pán)讀出來(lái)，而是以頁(yè)為單位，將其整體讀入內(nèi)存。在InnoDB中，每個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是16KB。

因?yàn)橐媸前错?yè)讀寫(xiě)的，所以說(shuō)，當(dāng)找到k=5的記錄的時(shí)候，它所在的數(shù)據(jù)頁(yè)就都在內(nèi)存里了。那么，對(duì)于普通索引來(lái)說(shuō)，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。

當(dāng)然，如果k=5這個(gè)記錄剛好是這個(gè)數(shù)據(jù)頁(yè)的最后一個(gè)記錄，那么要取下一個(gè)記錄，必須讀取下一個(gè)數(shù)據(jù)頁(yè)，這個(gè)操作會(huì)稍微復(fù)雜一些。但是我們之前計(jì)算過(guò)，對(duì)于整型字段，一個(gè)數(shù)據(jù)頁(yè)可以放近千個(gè)key，因此出現(xiàn)這種情況的概率會(huì)很低。所以，我們計(jì)算平均性能差異時(shí)，仍然可以認(rèn)為這個(gè)操作成本對(duì)于現(xiàn)在的CPU來(lái)說(shuō)可以忽略不計(jì)。

更新過(guò)程

為了說(shuō)明普通索引和唯一索引對(duì)更新語(yǔ)句性能的影響這個(gè)問(wèn)題，需要先了解一下change buffer的概念：

當(dāng)需要更新一個(gè)數(shù)據(jù)頁(yè)時(shí)，如果數(shù)據(jù)頁(yè)在內(nèi)存中就直接更新，而如果這個(gè)數(shù)據(jù)頁(yè)還沒(méi)有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB會(huì)將這些更新操作緩存在change buffer中，這樣就不需要從磁盤(pán)中讀入這個(gè)數(shù)據(jù)頁(yè)了。在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，然后執(zhí)行change buffer中與這個(gè)頁(yè)有關(guān)的操作。通過(guò)這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性。

需要說(shuō)明的是，雖然名字叫作change buffer，實(shí)際上它是可以持久化的數(shù)據(jù)。也就是說(shuō)，change buffer在內(nèi)存中有拷貝，也會(huì)被寫(xiě)入到磁盤(pán)上。將change buffer中的操作應(yīng)用到原數(shù)據(jù)頁(yè)，得到最新結(jié)果的過(guò)程稱(chēng)為merge。除了訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)會(huì)觸發(fā)merge外，系統(tǒng)有后臺(tái)線程會(huì)定期merge。在數(shù)據(jù)庫(kù)正常關(guān)閉(shutdown)的過(guò)程中，也會(huì)執(zhí)行merge操作。

顯然，如果能夠?qū)⒏虏僮飨扔涗浽赾hange buffer，減少讀磁盤(pán)，語(yǔ)句的執(zhí)行速度會(huì)得到明顯的提升。而且，數(shù)據(jù)讀入內(nèi)存是需要占用buffer pool的，所以這種方式還能夠避免占用內(nèi)存，提高內(nèi)存利用率。

那么，什么條件下可以使用change buffer呢？

對(duì)于唯一索引來(lái)說(shuō)，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束。比如，要插入(4, 400)這個(gè)記錄，就要先判斷現(xiàn)在表中是否已經(jīng)存在k=4的記錄，而這必須要將數(shù)據(jù)頁(yè)讀入內(nèi)存才能判斷。如果都已經(jīng)讀入到內(nèi)存了，那直接更新內(nèi)存會(huì)更快，就沒(méi)必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，實(shí)際上也只有普通索引可以使用。

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%。

現(xiàn)在，我們已經(jīng)理解了change buffer的機(jī)制，我們?cè)倩剡^(guò)頭來(lái)分析這個(gè)問(wèn)題，如果要在這張表中插入一個(gè)新記錄(4, 400)的話，InnoDB的處理流程是怎樣的。

第一種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)在內(nèi)存中。這時(shí)，InnoDB的處理流程如下：

• 對(duì)于唯一索引來(lái)說(shuō)，找到3和5之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束。
• 對(duì)于普通索引來(lái)說(shuō)，找到3和5之間的位置，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束。

這樣看來(lái)，普通索引和唯一索引對(duì)更新語(yǔ)句性能影響的差別，只是一個(gè)判斷，只會(huì)耗費(fèi)微小的CPU時(shí)間。但是，這不是我們關(guān)注的重點(diǎn)。

第二種情況是，這個(gè)記錄要更新的目標(biāo)頁(yè)不在內(nèi)存中。這時(shí)，InnoDB的處理流程如下：

• 對(duì)于唯一索引來(lái)說(shuō)，需要將數(shù)據(jù)頁(yè)讀入內(nèi)存，判斷到?jīng)]有沖突，插入這個(gè)值，語(yǔ)句執(zhí)行結(jié)束。
• 對(duì)于普通索引來(lái)說(shuō)，則是將更新記錄在change buffer，語(yǔ)句執(zhí)行就結(jié)束了。

將數(shù)據(jù)從磁盤(pán)讀入內(nèi)存涉及隨機(jī)IO的訪問(wèn)，是數(shù)據(jù)庫(kù)里面成本最高的操作之一。change buffer因?yàn)闇p少了隨機(jī)磁盤(pán)訪問(wèn)，所以對(duì)更新性能的提升會(huì)很明顯?，F(xiàn)實(shí)中就發(fā)生過(guò)這樣的事情，有個(gè)DBA的同學(xué)說(shuō)，他負(fù)責(zé)的某個(gè)業(yè)務(wù)的庫(kù)內(nèi)存命中率突然從99%降低到75%，整個(gè)系統(tǒng)處于阻塞狀態(tài)，更新語(yǔ)句全部堵住。而探究其原因后，才發(fā)現(xiàn)這個(gè)業(yè)務(wù)有大量插入數(shù)據(jù)的操作，而他在前一天把其中的某個(gè)普通索引改成了唯一索引。

change buffer的使用場(chǎng)景

通過(guò)以上分析，我們了解了使用change buffer對(duì)更新過(guò)程的加速作用，也清楚了change buffer只限于用在普通索引的場(chǎng)景下，而不適用于唯一索引。那么，現(xiàn)在有一個(gè)問(wèn)題就是：普通索引的所有場(chǎng)景，使用change buffer都可以起到加速作用嗎？

因?yàn)閙erge的時(shí)候是真正進(jìn)行數(shù)據(jù)更新的時(shí)刻，而change buffer的主要目的就是將記錄的變更動(dòng)作緩存下來(lái)，所以在一個(gè)數(shù)據(jù)頁(yè)做merge之前，change buffer記錄的變更越多(也就是這個(gè)頁(yè)面上要更新的次數(shù)越多)，收益就越大。

因此，對(duì)于寫(xiě)多讀少的業(yè)務(wù)來(lái)說(shuō)，頁(yè)面在寫(xiě)完以后馬上被訪問(wèn)的概率比較小，此時(shí)change buffer的使用效果最好。這種業(yè)務(wù)模型常見(jiàn)的就是賬單類(lèi)、日志類(lèi)系統(tǒng)。

反過(guò)來(lái)，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫(xiě)入之后馬上會(huì)做查詢，那么即使?jié)M足了條件，將更新先記錄在change buffer，但之后由于馬上要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)，會(huì)立即觸發(fā)merge過(guò)程。這樣隨機(jī)訪問(wèn)IO的次數(shù)不會(huì)減少，反而增加了change buffer的維護(hù)代價(jià)。所以，對(duì)于這種業(yè)務(wù)模式來(lái)說(shuō)，change buffer反而起到了副作用。

索引選擇和實(shí)戰(zhàn)

回到一開(kāi)始的問(wèn)題，普通索引和唯一索引應(yīng)該怎么選擇。其實(shí)，這兩類(lèi)索引在查詢能力上是沒(méi)差別的，主要考慮的是對(duì)更新性能的影響。所以，這里建議盡量選擇普通索引。如果所有的更新后，都馬上伴隨著對(duì)這個(gè)記錄的查詢，那么應(yīng)該關(guān)閉change buffer。而在其他情況下，change buffer都能提升更新性能。

在實(shí)際應(yīng)用中，你會(huì)發(fā)現(xiàn)，普通索引和change buffer的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。特別的，在使用機(jī)械硬盤(pán)的時(shí)候，change buffer這個(gè)機(jī)制的收效是非常顯著的。所以，當(dāng)你有一個(gè)類(lèi)似“歷史數(shù)據(jù)”的庫(kù)，并且出于成本考慮用的是機(jī)械硬盤(pán)時(shí)，那你應(yīng)該特別關(guān)注這些表里的索引，盡量使用普通索引，然后把change buffer盡量開(kāi)大，以確保這個(gè)“歷史數(shù)據(jù)”表的數(shù)據(jù)寫(xiě)入速度。

change buffer和redo log

理解了change buffer的原理，我們很容易聯(lián)想到之前學(xué)習(xí)的redo log和WAL。我們知道，WAL提升性能的核心機(jī)制，也的確是盡量減少隨機(jī)讀寫(xiě)，這兩個(gè)概念確實(shí)容易混淆。所以，這里把它們放到了同一個(gè)流程里來(lái)說(shuō)明，便于我們區(qū)分這兩個(gè)概念。

現(xiàn)在，我們要在表上執(zhí)行這個(gè)插入語(yǔ)句：

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

這里，我們假設(shè)當(dāng)前k索引樹(shù)的狀態(tài)，查找到位置后，k1所在的數(shù)據(jù)頁(yè)在內(nèi)存(InnoDB buffer pool)中，k2所在的數(shù)據(jù)頁(yè)不在內(nèi)存中。如圖所示是帶change buffer的更新?tīng)顟B(tài)圖。

change buffer的更新過(guò)程

分析這條更新語(yǔ)句，你會(huì)發(fā)現(xiàn)它涉及了四個(gè)部分：內(nèi)存、redo log(ib_log_fileX)、數(shù)據(jù)表空間(t.ibd)、系統(tǒng)表空間(ibdata1)。

這條更新語(yǔ)句做了如下的操作(按照?qǐng)D中的數(shù)字順序)：

• Page1在內(nèi)存中，直接更新內(nèi)存；
• Page2沒(méi)有在內(nèi)存中，就在內(nèi)存的change buffer區(qū)域，記錄下"我要往Page2插入一行"這個(gè)信息。
• 將上述兩個(gè)動(dòng)作記入redo log中(圖中3和4)。

做完上面這些，事務(wù)就可以完成了。所以，你會(huì)看到，執(zhí)行這條更新語(yǔ)句的成本很低，就是寫(xiě)了兩處內(nèi)存，然后寫(xiě)了一次磁盤(pán)(兩次操作合在一起寫(xiě)了一次磁盤(pán))，而且還是順序?qū)懙?。同時(shí)，圖中的兩個(gè)虛線箭頭，是后臺(tái)操作，不影響更新的響應(yīng)時(shí)間。那在這之后的讀請(qǐng)求，要怎么處理呢？

比如，我們現(xiàn)在要執(zhí)行select * from t where k in (k1, k2)。這里，給出了這兩個(gè)請(qǐng)求的流程圖：

change buffer的讀過(guò)程

從圖中可以看到：

• 讀Page1的時(shí)候，直接從內(nèi)存返回。這也說(shuō)明了，WAL之后如果讀數(shù)據(jù)，不一定要讀磁盤(pán)，也不一定要從redo log里面把數(shù)據(jù)更新以后才可以返回，圖中的狀態(tài)就反應(yīng)了，雖然磁盤(pán)上還是之前的數(shù)據(jù)，但是這里直接從內(nèi)存返回結(jié)果，結(jié)果是正確的。
• 要讀Page2的時(shí)候，需要把Page2從磁盤(pán)讀入內(nèi)存中，然后應(yīng)用change buffer里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果。

可以看到，直到需要讀Page2的時(shí)候，這個(gè)數(shù)據(jù)頁(yè)才會(huì)被讀入內(nèi)存。所以，如果要簡(jiǎn)單的對(duì)比這兩個(gè)機(jī)制在提升更新性能上的收益的話，redo log主要節(jié)省的是隨機(jī)寫(xiě)磁盤(pán)的IO消耗(轉(zhuǎn)成順序?qū)?，而change buffer主要節(jié)省的則是隨機(jī)讀磁盤(pán)的IO消耗。

總結(jié)

這次，我們從普通索引和唯一索引的選擇開(kāi)始，了解了數(shù)據(jù)的查詢和更新過(guò)程，然后說(shuō)明了change buffer的機(jī)制以及應(yīng)用場(chǎng)景，最好講到了索引選擇的實(shí)踐。由于唯一索引用不上change buffer的優(yōu)化機(jī)制，因此如果業(yè)務(wù)可以接收，從性能角度出發(fā)還是建議優(yōu)先考慮非唯一索引。

最后補(bǔ)充：

• 首先，業(yè)務(wù)正確性優(yōu)先，我們一開(kāi)始的前提就是"業(yè)務(wù)代碼已經(jīng)保證不會(huì)寫(xiě)入重復(fù)數(shù)據(jù)"的情況下，討論性能問(wèn)題。如果業(yè)務(wù)不能保證，或者業(yè)務(wù)就是要求數(shù)據(jù)庫(kù)來(lái)做約定，那么沒(méi)得選，必須創(chuàng)建唯一索引。這種情況下，本篇文章的意義在于，如果碰上了大量插入數(shù)據(jù)慢、內(nèi)存命中率低的時(shí)候，可以給你多提供一個(gè)排查的思路。
• 然后，在一些"歸檔庫(kù)"的場(chǎng)景，你是可以考慮使用普通索引的。比如，線上數(shù)據(jù)只需要保留半年，然后歷史數(shù)據(jù)保存在歸檔庫(kù)。這時(shí)候，歸檔數(shù)據(jù)已經(jīng)是確保沒(méi)有唯一鍵沖突了。要提高歸檔效率，可以考慮把表里面的唯一索引改成普通索引。

思考：通過(guò)change buffer更新過(guò)程圖可以看到，change buffer一開(kāi)始是寫(xiě)內(nèi)存的，那么如果這個(gè)時(shí)候機(jī)器掉電重啟，會(huì)不會(huì)導(dǎo)致change buffer丟失呢？change buffer丟失可不是小事兒，再?gòu)拇疟P(pán)讀入數(shù)據(jù)可就沒(méi)有了merge過(guò)程，就等于是數(shù)據(jù)丟失了。會(huì)不會(huì)出現(xiàn)這種情況呢？

答案：不會(huì)丟失。雖然只是更新內(nèi)存，但是在事務(wù)提交的時(shí)候，我們把change buffer的操作也記錄到了redo log里了，所以崩潰恢復(fù)的時(shí)候，change buffer也能找回來(lái)。

下面給出merge的執(zhí)行流程：

• 從磁盤(pán)讀入數(shù)據(jù)頁(yè)到內(nèi)存(老版本的數(shù)據(jù)頁(yè))。
• 從change buffer里找出這個(gè)數(shù)據(jù)頁(yè)的change buffer記錄(可能有多個(gè))，依次應(yīng)用，得到新版數(shù)據(jù)頁(yè)。
• 寫(xiě)redo log。這個(gè)redo log包含了數(shù)據(jù)的變更和change buffer的變更。

到這里merge過(guò)程就結(jié)束了。這時(shí)候，數(shù)據(jù)頁(yè)和內(nèi)存中change buffer對(duì)應(yīng)的磁盤(pán)位置都還沒(méi)有修改，屬于臟頁(yè)，之后各自刷回自己的物理數(shù)據(jù)，就是另外一個(gè)過(guò)程了。

到此這篇關(guān)于MySQL普通索引與唯一索引的使用與區(qū)別的文章就介紹到這了,更多相關(guān)MySQL 普通索引 唯一索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論