假設(shè)一個(gè)用戶管理系統(tǒng)，每個(gè)人注冊(cè)都有一個(gè)唯一的手機(jī)號(hào)，而且業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫入兩個(gè)重復(fù)的手機(jī)號(hào)。如果用戶管理系統(tǒng)需要按照手機(jī)號(hào)查姓名，就會(huì)執(zhí)行類似這樣的 SQL 語(yǔ)句：

select name from users where mobile = '15202124529';

通常會(huì)考慮在 mobile 字段上建索引。由于手機(jī)號(hào)字段相對(duì)較大，通常基本不會(huì)把手機(jī)號(hào)當(dāng)做主鍵，那么現(xiàn)在就有兩個(gè)選擇:

1.  給 id_card 字段創(chuàng)建唯一索引
2.  創(chuàng)建一個(gè)普通索引

如果業(yè)務(wù)代碼已經(jīng)保證了不會(huì)寫入重復(fù)的身份證號(hào)，那么這兩個(gè)選擇邏輯上都是正確的。

從性能的角度考慮，選擇唯一索引還是普通索引？

如圖：假設(shè)字段 k 上的值都不重復(fù)

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

查詢過(guò)程

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

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

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

原因：除非 Key 的列非常大，有連續(xù)多個(gè) Key 占滿了一個(gè) page，才會(huì)引起一次 page 的 IO，這樣才會(huì)產(chǎn)生比較明顯的性能差異，從均攤上看，差異幾乎可以不算。

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

更新過(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ù)一致性的前提下：
  

1.  InnoDB 會(huì)將這些 更新操作 緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)頁(yè)了。
2. 在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候，將數(shù)據(jù)頁(yè)讀入內(nèi)存，
3. 然后執(zhí)行 change buffer 中與這個(gè)頁(yè)有關(guān)的操作。
   

    通過(guò)這種方式就能保證這個(gè)數(shù)據(jù)邏輯的正確性

需要說(shuō)明的是，雖然名字叫作 change buffer，實(shí)際上它是可以持久化的數(shù)據(jù)。也就是說(shuō)，change buffer 在內(nèi)存中有拷貝，也會(huì)被寫入到磁盤上。

把change buffer中的操作，應(yīng)用到舊的數(shù)據(jù)頁(yè)，得到新的數(shù)據(jù)頁(yè)的過(guò)程，應(yīng)該稱為merge。

Ps.  除了訪問(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 操作。

(change buffer的merge操作，先把change buffer的操作更新到內(nèi)存的數(shù)據(jù)頁(yè)中，此操作寫到redo log中，mysql未宕機(jī)，redo log寫滿后需要移動(dòng)check point點(diǎn)時(shí)，通過(guò)判斷內(nèi)存中數(shù)據(jù)和磁盤是否一致即是否是臟頁(yè)來(lái)刷新到磁盤中，當(dāng)mysql宕機(jī)后沒(méi)有內(nèi)存即沒(méi)有臟頁(yè),通過(guò)redo log來(lái)恢復(fù)。)

顯然，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer，減少讀磁盤，語(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%。

Ps. 數(shù)據(jù)庫(kù)緩沖池(buffer pool) https://www.jianshu.com/p/f9ab1cb24230

分析：插入一個(gè)新記錄 InnoDB 的處理流程

理解了 change buffer 的機(jī)制，那么如果要在這張表中插入一個(gè)新記錄 (4,400) 的話，InnoDB 的處理流程是怎樣的

1、第一種情況是：這個(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)

2、第二種情況是，這個(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ù)從磁盤讀入內(nèi)存涉及隨機(jī) IO 的訪問(wèn)，是數(shù)據(jù)庫(kù)里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問(wèn)，所以對(duì)更新性能的提升是會(huì)很明顯的。

change buffer主要是將更新操作緩存起來(lái),異步處理. 這樣每次更新過(guò)來(lái),直接記下change buffer即可,速度很快，將多次寫磁盤變?yōu)橐淮螌懘疟P

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

通過(guò)上面的分析，已經(jīng)清楚了使用 change buffer 對(duì)更新過(guò)程的加速作用，也清楚了 change buffer 只限于用在普通索引的場(chǎng)景下，而不適用于唯一索引。

普通索引的所有場(chǎng)景，使用 change buffer 都可以起到加速作用嗎？

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

因此，對(duì)于寫多讀少的業(yè)務(wù)來(lái)說(shuō)，頁(yè)面在寫完以后馬上被訪問(wèn)到的概率比較小，此時(shí) change buffer 的使用效果最好。這種業(yè)務(wù)模型常見的就是賬單類、日志類的系統(tǒng)。（適合寫多讀少的場(chǎng)景，讀多寫少反倒會(huì)增加change buffer的維護(hù)代價(jià)）

反過(guò)來(lái)，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫入之后馬上會(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 反而起到了副作用。（如果立即對(duì)普通索引的更新操作結(jié)果執(zhí)行查詢，就會(huì)觸發(fā)merge操作，磁盤中的數(shù)據(jù)會(huì)和change buffer 的操作記錄進(jìn)行合并，產(chǎn)生大量io）

索引選擇和實(shí)踐

綜上分析，普通索引和唯一索引應(yīng)該怎么選擇：

其實(shí)，這兩類索引在查詢能力上是沒(méi)差別的，主要考慮的是對(duì)更新性能的影響。所以，建議盡量選擇普通索引。

如果所有的更新后面，都馬上伴隨著對(duì)這個(gè)記錄的查詢，那么應(yīng)該關(guān)閉 change buffer。

而在其他情況下，change buffer 都能提升更新性能。在實(shí)際使用中，普通索引和 change buffer 的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。

Ps. 特別地，在使用機(jī)械硬盤時(shí)，change buffer 這個(gè)機(jī)制的收效是非常顯著的。所以，當(dāng)有一個(gè)類似“歷史數(shù)據(jù)”的庫(kù)，應(yīng)該特別關(guān)注這些表里的索引，盡量使用普通索引，然后把 change buffer 盡量開大，以確保這個(gè)“歷史數(shù)據(jù)”表的數(shù)據(jù)寫入速度。

change buffer 和 redo log

理解了 change buffer 的原理，可能會(huì)聯(lián)想到 redo log 和 WAL(Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤)。

WAL 提升性能的核心機(jī)制，也的確是盡量減少隨機(jī)讀寫

在表上執(zhí)行這個(gè)插入語(yǔ)句：

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

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

圖3  帶 change buffer 的更新過(guò)程

分析這條更新語(yǔ)句，你會(huì)發(fā)現(xiàn)它涉及了四個(gè)部分：

內(nèi)存、redo log（ib_log_fileX）、 數(shù)據(jù)表空間（t.ibd）、系統(tǒng)表空間（ibdata1）。

數(shù)據(jù)表空間：就是一個(gè)個(gè)的表數(shù)據(jù)文件，對(duì)應(yīng)的磁盤文件就是“表名.ibd”； 系統(tǒng)表空間：用來(lái)放系統(tǒng)信息，如數(shù)據(jù)字典等，對(duì)應(yīng)的磁盤文件是“ibdata1”

數(shù)據(jù)表空間 和 系統(tǒng)表空間 似乎代表的就是B+樹對(duì)應(yīng)的那個(gè)復(fù)雜的結(jié)構(gòu)

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

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

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

change buffer和redo log顆粒度不一樣，因?yàn)閏hange buffer只是針對(duì)如果更改的數(shù)據(jù)所在頁(yè)不在內(nèi)存中才暫時(shí)儲(chǔ)存在change buffer中。而redo log會(huì)記錄一個(gè)事務(wù)內(nèi)進(jìn)行數(shù)據(jù)更改的所有操作，即使修改的數(shù)據(jù)已經(jīng)在內(nèi)存中了，那也會(huì)記錄下來(lái)

同時(shí)，圖中的兩個(gè)虛線箭頭，是后臺(tái)操作，不影響更新的響應(yīng)時(shí)間。

那在這之后的讀請(qǐng)求，要怎么處理呢？

比如，我們現(xiàn)在要執(zhí)行 select * from t where k in (k1, k2) 。

如果讀語(yǔ)句發(fā)生在更新語(yǔ)句后不久，內(nèi)存中的數(shù)據(jù)都還在，那么此時(shí)的這兩個(gè)讀操作就與系統(tǒng)表空間（ibdata1）和 redo log（ib_log_fileX）無(wú)關(guān)了。

圖 4 帶 change buffer 的讀過(guò)程

從圖中可以看到：讀 Page 1 的時(shí)候，直接從內(nèi)存返回。

WAL 之后如果讀數(shù)據(jù)，是不是一定要讀盤，是不是一定要從 redo log 里面把數(shù)據(jù)更新以后才可以返回？

其實(shí)是不用的。雖然磁盤上還是之前的數(shù)據(jù)，但是這里直接從內(nèi)存返回結(jié)果，結(jié)果是正確的。要讀 Page 2 的時(shí)候，需要把 Page 2 從磁盤讀入內(nèi)存中，然后應(yīng)用 change buffer 里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果。可以看到，直到需要讀 Page 2 的時(shí)候，這個(gè)數(shù)據(jù)頁(yè)才會(huì)被讀入內(nèi)存。

如果要簡(jiǎn)單地對(duì)比這兩個(gè)機(jī)制在提升更新性能上的收益的話，redo log 主要節(jié)省的是隨機(jī)寫磁盤的 IO 消耗（轉(zhuǎn)成順序?qū)懀?change buffer 主要節(jié)省的則是隨機(jī)讀磁盤的 IO 消耗。

思考題：

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

答：

1.change buffer有一部分在內(nèi)存有一部分在ibdata.

做purge操作,應(yīng)該就會(huì)把change buffer里相應(yīng)的數(shù)據(jù)持久化到ibdata

2.redo log里記錄了數(shù)據(jù)頁(yè)的修改以及change buffer新寫入的信息

如果掉電,持久化的change buffer數(shù)據(jù)已經(jīng)purge,不用恢復(fù)。主要分析沒(méi)有持久化的數(shù)據(jù)

情況又分為以下幾種:

(1)change buffer寫入,redo log雖然做了fsync但未commit,binlog未fsync到磁盤,這部分?jǐn)?shù)據(jù)丟失
(2)change buffer寫入,redo log寫入但沒(méi)有commit,binlog以及fsync到磁盤,先從binlog恢復(fù)redo log,再?gòu)膔edo log恢復(fù)change buffer

(3)change buffer寫入,redo log和binlog都已經(jīng)fsync.那么直接從redo log里恢復(fù)。

總結(jié)

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