Mysql普通索引與唯一索引的選擇詳析

假設(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 語句：

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ù)

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

查詢過程

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

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

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

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

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

更新過程

為了說明普通索引和唯一索引對(duì)更新語句性能的影響這個(gè)問題，需要先介紹一下 change buffer

• 當(dāng)需要更新一個(gè)數(shù)據(jù)頁時(shí)，如果數(shù)據(jù)頁在內(nèi)存中就直接更新，
• 而如果這個(gè)數(shù)據(jù)頁還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下：
  

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

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

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

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

Ps.  除了訪問這個(gè)數(shù)據(jù)頁會(huì)觸發(fā) merge 外，系統(tǒng)有后臺(tái)線程會(huì)定期 merge。在數(shù)據(jù)庫正常關(guān)閉（shutdown）的過程中，也會(huì)執(zhí)行 merge 操作。

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

顯然，如果能夠?qū)⒏虏僮飨扔涗浽?change buffer，減少讀磁盤，語句的執(zhí)行速度會(huì)得到明顯的提升。

而且，數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存，提高內(nèi)存利用率。

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

對(duì)于唯一索引來說，所有的更新操作都要先判斷這個(gè)操作是否違反唯一性約束。

比如，要插入 (4,400) 這個(gè)記錄，就要先判斷現(xiàn)在表中是否已經(jīng)存在 k=4 的記錄，而這必須要將數(shù)據(jù)頁讀入內(nèi)存才能判斷。

如果都已經(jīng)讀入到內(nèi)存了，那直接更新內(nèi)存會(huì)更快，就沒必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，實(shí)際上也只有普通索引可以使用。

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

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

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

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

1、第一種情況是：這個(gè)記錄要更新的目標(biāo)頁在內(nèi)存中。

•  這時(shí)，InnoDB 的處理流程如下：對(duì)于唯一索引來說，找到 3 和 5 之間的位置，判斷到?jīng)]有沖突，插入這個(gè)值，語句執(zhí)行結(jié)束；
• 對(duì)于普通索引來說，找到 3 和 5 之間的位置，插入這個(gè)值，語句執(zhí)行結(jié)束。
  

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

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

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

將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機(jī) IO 的訪問，是數(shù)據(jù)庫里面成本最高的操作之一。change buffer 因?yàn)闇p少了隨機(jī)磁盤訪問，所以對(duì)更新性能的提升是會(huì)很明顯的。

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

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

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

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

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

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

反過來，假設(shè)一個(gè)業(yè)務(wù)的更新模式是寫入之后馬上會(huì)做查詢，那么即使?jié)M足了條件，將更新先記錄在 change buffer，但之后由于馬上要訪問這個(gè)數(shù)據(jù)頁，會(huì)立即觸發(fā) merge 過程。這樣隨機(jī)訪問 IO 的次數(shù)不會(huì)減少，反而增加了 change buffer 的維護(hù)代價(jià)。所以，對(duì)于這種業(yè)務(wù)模式來說，change buffer 反而起到了副作用。（如果立即對(duì)普通索引的更新操作結(jié)果執(zhí)行查詢，就會(huì)觸發(fā)merge操作，磁盤中的數(shù)據(jù)會(huì)和change buffer 的操作記錄進(jìn)行合并，產(chǎn)生大量io）

索引選擇和實(shí)踐

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

其實(shí)，這兩類索引在查詢能力上是沒差別的，主要考慮的是對(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ù)”的庫，應(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è)插入語句：

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

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

圖3  帶 change buffer 的更新過程

分析這條更新語句，你會(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)表空間：用來放系統(tǒng)信息，如數(shù)據(jù)字典等，對(duì)應(yīng)的磁盤文件是“ibdata1”

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

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

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

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

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

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

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

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

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

圖 4 帶 change buffer 的讀過程

從圖中可以看到：讀 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ù)頁才會(huì)被讀入內(nèi)存。

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

思考題：

1、通過圖 3 可以看到，change buffer 一開始是寫內(nèi)存的，那么如果這個(gè)時(shí)候機(jī)器掉電重啟，會(huì)不會(huì)導(dǎo)致 change buffer 丟失呢？change buffer 丟失可不是小事兒，再?gòu)拇疟P讀入數(shù)據(jù)可就沒有了 merge 過程，就等于是數(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ù)頁的修改以及change buffer新寫入的信息

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

情況又分為以下幾種:

(1)change buffer寫入,redo log雖然做了fsync但未commit,binlog未fsync到磁盤,這部分?jǐn)?shù)據(jù)丟失
(2)change buffer寫入,redo log寫入但沒有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)文章希望大家以后多多支持腳本之家！