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

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

0 概念區(qū)分

普通索引和唯一索引

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

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

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

主鍵和唯一索引

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

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

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

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

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

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

1 示例

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

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

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

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

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

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

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

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

如果業(yè)務(wù)代碼已保證不會(huì)寫入重復(fù)的身份證號偷拔，那這兩個(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)部是通過二分法定位記錄彻坛。

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

對唯一索引，由于索引定義了唯一性茵瀑，查找到第一個(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)存了哗伯。
對普通索引荒揣，要多做的那一次“查找和判斷下一條記錄”的操作，就只需要一次指針尋找和一次計(jì)算焊刹。
如果k=5記錄剛好是該數(shù)據(jù)頁的最后一個(gè)記錄系任，那么要取下個(gè)記錄，必須讀取下個(gè)數(shù)據(jù)頁伴澄，操作會(huì)稍微復(fù)雜赋除。
對于整型字段，一個(gè)數(shù)據(jù)頁可存近千個(gè)key非凌，因此這種情況概率很低举农。所以，計(jì)算平均性能差異時(shí)敞嗡，仍可認(rèn)為該操作成本對現(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)存利用率叫倍。

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

比如吆倦，要插入(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é)束讼油。

普通索引和唯一索引對更新語句性能影響的差別杰赛，只是一個(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ī)磁盤訪問含友，所以對更新性能提升明顯。

問題案例：某業(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ù)越多），收益越大诗轻。

對寫多讀少業(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à)瞧壮。
所以登馒，對于這種業(yè)務(wù)模式，change buffer起副作用咆槽。

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

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

在使用機(jī)械硬盤時(shí)钩述，change buffer機(jī)制的收效非常顯著。
所以谬擦，當(dāng)你有一個(gè)類似“歷史數(shù)據(jù)”的庫切距，并且出于成本考慮用機(jī)械硬盤時(shí)朽缎，應(yīng)該關(guān)注這些表里的索引惨远，盡量使用普通索引，把change buffer 開大话肖，確北被啵“歷史數(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í)間。

這之后的讀請求抗悍，怎么處理羔飞？
現(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è)讀請求的流程圖（帶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)存遣疯。

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

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ù)可能無法確保”虐急。本文前提是“業(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è)好看蚂踊！