走過路過不要錯(cuò)過

點(diǎn)擊藍(lán)字關(guān)注我們

0 概念區(qū)分

普通索引和唯一索引

普通索引可以重復(fù)，唯一索引和主鍵一樣不能重復(fù)。

唯一索引可以作為數(shù)據(jù)的一個(gè)合法驗(yàn)證手段，例如學(xué)生表的身份證號(hào)碼字段，我們?nèi)藶橐?guī)定該字段不得重復(fù)，那么就使用唯一索引。

（一般設(shè)置學(xué)號(hào)字段為主鍵）

主鍵和唯一索引

主鍵保證數(shù)據(jù)庫里面的每一行都是唯一的，比如身份證，學(xué)號(hào)等，在表中要求唯一，不重復(fù)。

唯一索引的作用跟主鍵的作用一樣。

不同的是，在一張表里面只能有一個(gè)主鍵，主鍵不能為空，唯一索引可以有多個(gè)，唯一索引可以有一條記錄為空，即保證跟別人不一樣就行。

比如學(xué)生表，在學(xué)校里面一般用學(xué)號(hào)做主鍵，身份證則弄成唯一索引；

而到了教育局，他們就把身份證號(hào)弄成主鍵，學(xué)號(hào)換成了唯一索引。

選誰做表的主鍵，要看實(shí)際應(yīng)用，主鍵不能為空。

1 示例

一個(gè)市民系統(tǒng)，每個(gè)人都有個(gè)唯一身份證號(hào)；

業(yè)務(wù)代碼已保證不會(huì)寫入兩個(gè)重復(fù)的身份證號(hào)；

如果市民系統(tǒng)需要按照身份證號(hào)查姓名，就會(huì)執(zhí)行類似SQL：

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

相信你一定會(huì)在id_card字段上建索引。

由于身份證號(hào)字段比較大，不建推薦把身份證號(hào)做主鍵。
因此現(xiàn)在有兩個(gè)選擇

給id_card字段創(chuàng)建唯一索引

創(chuàng)建一個(gè)普通索引

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

但從性能角度考慮，唯一索引還是普通索引呢？
假設(shè)字段 k 上的值都不重復(fù)。

InnoDB的索引組織結(jié)構(gòu)

查詢語句

select id from T where k=5

該語句在索引樹查找的過程：
先通過B+樹從樹根開始，按層搜索到葉節(jié)點(diǎn)，即圖中右下角的數(shù)據(jù)頁，然后可認(rèn)為數(shù)據(jù)頁內(nèi)部是通過二分法定位記錄。

對(duì)普通索引，查找到滿足條件的第一個(gè)記錄(5,500)后，需查找下個(gè)記錄，直到碰到第一個(gè)不滿足k=5條件的記錄

對(duì)唯一索引，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后，就會(huì)停止檢索。

該不同點(diǎn)帶來的性能差距會(huì)有多少呢？
微乎其微！

InnoDB數(shù)據(jù)是按數(shù)據(jù)頁為單位讀寫。即當(dāng)需讀一條記錄時(shí)，并非將該記錄本身從磁盤讀出，而是以頁為單位，將其整體讀入內(nèi)存。

InnoDB中，每個(gè)數(shù)據(jù)頁的大小默認(rèn)是16KB。

因引擎按頁讀寫，所以，當(dāng)找到k=5記錄時(shí)，它所在數(shù)據(jù)頁就都在內(nèi)存了。
對(duì)普通索引，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算。
如果k=5記錄剛好是該數(shù)據(jù)頁的最后一個(gè)記錄，那么要取下個(gè)記錄，必須讀取下個(gè)數(shù)據(jù)頁，操作會(huì)稍微復(fù)雜。
對(duì)于整型字段，一個(gè)數(shù)據(jù)頁可存近千個(gè)key，因此這種情況概率很低。所以，計(jì)算平均性能差異時(shí)，仍可認(rèn)為該操作成本對(duì)現(xiàn)在的CPU可忽略不計(jì)。

需更新一個(gè)數(shù)據(jù)頁時(shí)

若數(shù)據(jù)頁在內(nèi)存，直接更新

若該數(shù)據(jù)頁不在內(nèi)存，在不影響數(shù)據(jù)一致性前提下，InooDB會(huì)將這些更新操作緩存在change buffer，無需從磁盤讀入該數(shù)據(jù)頁。

在下次查詢需要訪問該數(shù)據(jù)頁時(shí)，將數(shù)據(jù)頁讀入內(nèi)存，然后執(zhí)行change buffer中與這個(gè)頁有關(guān)的操作。

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

雖然叫change buffer，實(shí)際上是可持久化的數(shù)據(jù)。
即change buffer在內(nèi)存中有拷貝，也會(huì)被寫進(jìn)磁盤。

將change buffer中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程。

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

若能將更新操作先記錄在change buffer，減少讀盤，語句執(zhí)行速度會(huì)明顯提升。
且數(shù)據(jù)讀入內(nèi)存需要占用buffer pool，所以該方式還能避免占用內(nèi)存，提高內(nèi)存利用率。

對(duì)于唯一索引，所有更新操作要先判斷該操作是否違反唯一性約束。

比如，要插入(4,400)記錄，要先判斷表中是否已存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è)置。
參數(shù)設(shè)置為50時(shí)，表示change buffer的大小最多只能占用buffer pool的50%。

理解了change buffer機(jī)制，看看要在這張表中插入一個(gè)新記錄(4,400)，InnoDB處理流程。

分情況討論該記錄要更新的目標(biāo)頁是否在內(nèi)存中：

唯一索引

找到3和5之間位置，判斷到?jīng)]有沖突，插入值，語句執(zhí)行結(jié)束。

普通索引

找到3和5之間位置，插入值，語句執(zhí)行結(jié)束。

普通索引和唯一索引對(duì)更新語句性能影響的差別，只是一個(gè)判斷，只會(huì)耗費(fèi)微小CPU時(shí)間。

唯一索引

需要將數(shù)據(jù)頁讀入內(nèi)存，判斷到?jīng)]有沖突，插入值，語句執(zhí)行結(jié)束

普通索引

將更新記錄在change buffer，語句執(zhí)行結(jié)束

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

問題案例：某業(yè)務(wù)的庫內(nèi)存命中率突然從99%降低到了75%，整個(gè)系統(tǒng)處于阻塞狀態(tài)，更新語句全部堵住。
探究其原因，發(fā)現(xiàn)該業(yè)務(wù)有大量插入數(shù)據(jù)操作，而DBA在前天把其中的某個(gè)普通索引改成了唯一索引。

普通索引的所有場景，使用change buffer都可加速嗎？

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

對(duì)寫多讀少業(yè)務(wù)，頁面在寫完后馬上被訪問到的概率較小，change buffer使用效果最好。該類業(yè)務(wù)模型常見為賬單、日志類的系統(tǒng)。

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

普通索引和唯一索引如何抉擇。
這兩類索引在查詢性能上沒差別，主要考慮對(duì)更新性能影響。
所以，推薦盡量選擇普通索引。

如果所有更新后面，都緊跟對(duì)該記錄的查詢，那么該關(guān)閉change buffer。
而在其他情況下，change buffer都能提升更新性能。
普通索引和change buffer的配合使用，對(duì)于數(shù)據(jù)量大的表的更新優(yōu)化還是很明顯的。

在使用機(jī)械硬盤時(shí)，change buffer機(jī)制的收效非常顯著。
所以，當(dāng)你有一個(gè)類似“歷史數(shù)據(jù)”的庫，并且出于成本考慮用機(jī)械硬盤時(shí)，應(yīng)該關(guān)注這些表里的索引，盡量使用普通索引，把change buffer 開大，確?！皻v史數(shù)據(jù)”表的數(shù)據(jù)寫速度。

WAL 提升性能的核心機(jī)制，也是盡量減少隨機(jī)讀寫，這兩個(gè)概念易混淆。
所以，這里我把它們放到了同一個(gè)流程里來說明區(qū)分。

在表上

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)圖。

該更新語句涉及四部分：

內(nèi)存

redo log（ib_log_fileX）

數(shù)據(jù)表空間（t.ibd）

系統(tǒng)表空間（ibdata1）

該更新語句做了如下操作（按圖中數(shù)字順序）：

Page1在內(nèi)存，直接更新內(nèi)存

Page2沒有在內(nèi)存中，就在內(nèi)存的change buffer區(qū)，記錄下“我要往Page2插一行”的信息

將前兩個(gè)動(dòng)作記入redo log（圖中的3和4）

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

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

這之后的讀請(qǐng)求，怎么處理？
現(xiàn)在執(zhí)行

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

若讀語句緊隨在更新語句后，內(nèi)存中的數(shù)據(jù)都還在，那么此時(shí)這倆讀操作就與系統(tǒng)表空間（ibdata1）和 redo log（ib_log_fileX）無關(guān)。所以在圖中就沒畫這倆。

兩個(gè)讀請(qǐng)求的流程圖（帶change buffer的讀過程）

從圖中可見：
讀Page1時(shí)，直接從內(nèi)存返回。
WAL之后如果讀數(shù)據(jù)，是不是一定要讀盤，是不是一定要從redo log里面把數(shù)據(jù)更新以后才可以返回？其實(shí)不用。
看上圖狀態(tài)，雖然磁盤上還是之前數(shù)據(jù)，但這里直接從內(nèi)存返回結(jié)果，結(jié)果正確。

要讀Page2時(shí)，需把Page2從磁盤讀入內(nèi)存，然后應(yīng)用change buffer里面的操作日志，生成一個(gè)正確版本并返回結(jié)果。
可見直到需讀Page2時(shí)，該數(shù)據(jù)頁才被讀入內(nèi)存。

所以，要簡單對(duì)比這倆機(jī)制對(duì)更新性能影響

redo log 主要節(jié)省隨機(jī)寫磁盤的IO消耗（轉(zhuǎn)成順序?qū)懀?/p>

change buffer主要節(jié)省隨機(jī)讀磁盤的IO消耗

由于唯一索引用不了change buffer的優(yōu)化機(jī)制，因此如果業(yè)務(wù)可以接受，從性能角度，推薦優(yōu)先考慮非唯一索引。

主要糾結(jié)在“業(yè)務(wù)可能無法確?！薄１疚那疤崾恰皹I(yè)務(wù)代碼已經(jīng)保證不會(huì)寫入重復(fù)數(shù)據(jù)”下，討論性能問題。

如果業(yè)務(wù)不能保證，或者業(yè)務(wù)就是要求數(shù)據(jù)庫來做約束，那么沒得選，必須創(chuàng)建唯一索引。

這種情況下，本文意義在于，如果碰上大量插入數(shù)據(jù)慢、內(nèi)存命中率低時(shí)，多提供一個(gè)排查思路。

然后，在一些“歸檔庫”的場景，可考慮使用唯一索引的。比如，線上數(shù)據(jù)只需保留半年，然后歷史數(shù)據(jù)保存在歸檔庫。此時(shí)，歸檔數(shù)據(jù)已是確保沒有唯一鍵沖突。要提高歸檔效率，可考慮把表的唯一索引改普通索引。

參考并整理自《MySQL 實(shí)戰(zhàn) 45 講》

往期推薦

大廠Java面試題解(45)-來設(shè)計(jì)個(gè)高并發(fā)系統(tǒng)？

一看就懂的MySQL行鎖

一看就懂的圖文講解事務(wù)隔離

使用私有構(gòu)造器或枚舉來強(qiáng)化單例屬性吧

好文！點(diǎn)個(gè)好看！