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

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

普通索引還是唯一索引？

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

select name from CUser where id_card='xxxxxxxyyyyyyzzzzz';

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

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

接下來，我們就從這兩種索引對查詢語句和更新語句的性能影響來進行分析。

查詢過程

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

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

那么，這個不同帶來的性能差距會有多少呢？答案是，微乎其微。

我們都知道，InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的。也就是說，當需要讀一條記錄的時候，并不是將這個記錄本身從磁盤讀出來，而是以頁為單位，將其整體讀入內存。在InnoDB中，每個數(shù)據(jù)頁的大小默認是16KB。

因為引擎是按頁讀寫的，所以說，當找到k=5的記錄的時候，它所在的數(shù)據(jù)頁就都在內存里了。那么，對于普通索引來說，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計算。

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

更新過程

為了說明普通索引和唯一索引對更新語句性能的影響這個問題，需要先了解一下change buffer的概念：

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

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

顯然，如果能夠將更新操作先記錄在change buffer，減少讀磁盤，語句的執(zhí)行速度會得到明顯的提升。而且，數(shù)據(jù)讀入內存是需要占用buffer pool的，所以這種方式還能夠避免占用內存，提高內存利用率。

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

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

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

現(xiàn)在，我們已經理解了change buffer的機制，我們再回過頭來分析這個問題，如果要在這張表中插入一個新記錄(4, 400)的話，InnoDB的處理流程是怎樣的。

第一種情況是，這個記錄要更新的目標頁在內存中。這時，InnoDB的處理流程如下：

• 對于唯一索引來說，找到3和5之間的位置，判斷到沒有沖突，插入這個值，語句執(zhí)行結束。
• 對于普通索引來說，找到3和5之間的位置，插入這個值，語句執(zhí)行結束。

這樣看來，普通索引和唯一索引對更新語句性能影響的差別，只是一個判斷，只會耗費微小的CPU時間。但是，這不是我們關注的重點。

第二種情況是，這個記錄要更新的目標頁不在內存中。這時，InnoDB的處理流程如下：

• 對于唯一索引來說，需要將數(shù)據(jù)頁讀入內存，判斷到沒有沖突，插入這個值，語句執(zhí)行結束。
• 對于普通索引來說，則是將更新記錄在change buffer，語句執(zhí)行就結束了。

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

change buffer的使用場景

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

因為merge的時候是真正進行數(shù)據(jù)更新的時刻，而change buffer的主要目的就是將記錄的變更動作緩存下來，所以在一個數(shù)據(jù)頁做merge之前，change buffer記錄的變更越多(也就是這個頁面上要更新的次數(shù)越多)，收益就越大。

因此，對于寫多讀少的業(yè)務來說，頁面在寫完以后馬上被訪問的概率比較小，此時change buffer的使用效果最好。這種業(yè)務模型常見的就是賬單類、日志類系統(tǒng)。

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

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

回到一開始的問題，普通索引和唯一索引應該怎么選擇。其實，這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新性能的影響。所以，這里建議盡量選擇普通索引。如果所有的更新后，都馬上伴隨著對這個記錄的查詢，那么應該關閉change buffer。而在其他情況下，change buffer都能提升更新性能。

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

change buffer和redo log

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

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

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

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

change buffer的更新過程

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

這條更新語句做了如下的操作(按照圖中的數(shù)字順序)：

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

做完上面這些，事務就可以完成了。所以，你會看到，執(zhí)行這條更新語句的成本很低，就是寫了兩處內存，然后寫了一次磁盤(兩次操作合在一起寫了一次磁盤)，而且還是順序寫的。同時，圖中的兩個虛線箭頭，是后臺操作，不影響更新的響應時間。那在這之后的讀請求，要怎么處理呢？

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

change buffer的讀過程

從圖中可以看到：

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

可以看到，直到需要讀Page2的時候，這個數(shù)據(jù)頁才會被讀入內存。所以，如果要簡單的對比這兩個機制在提升更新性能上的收益的話，redo log主要節(jié)省的是隨機寫磁盤的IO消耗(轉成順序寫)，而change buffer主要節(jié)省的則是隨機讀磁盤的IO消耗。

總結

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

最后補充：

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

思考：通過change buffer更新過程圖可以看到，change buffer一開始是寫內存的，那么如果這個時候機器掉電重啟，會不會導致change buffer丟失呢？change buffer丟失可不是小事兒，再從磁盤讀入數(shù)據(jù)可就沒有了merge過程，就等于是數(shù)據(jù)丟失了。會不會出現(xiàn)這種情況呢？

答案：不會丟失。雖然只是更新內存，但是在事務提交的時候，我們把change buffer的操作也記錄到了redo log里了，所以崩潰恢復的時候，change buffer也能找回來。

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

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

到這里merge過程就結束了。這時候，數(shù)據(jù)頁和內存中change buffer對應的磁盤位置都還沒有修改，屬于臟頁，之后各自刷回自己的物理數(shù)據(jù)，就是另外一個過程了。

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

最新評論