假設(shè)你現(xiàn)在有個(gè)公民表維護(hù)，有一個(gè)唯一身份證id拾氓，我們應(yīng)該怎么設(shè)計(jì)索引呢冯挎？

為什么主鍵索引不能過長？

身份證是唯一的那么是不是就可以把我們知道InnoDB使用的聚簇索引id和數(shù)據(jù)一起存，在通過二級(jí)索引（普通索引）查詢數(shù)據(jù)時(shí)是先找到主鍵索引再查詢數(shù)據(jù)房官，如果數(shù)據(jù)量比較大趾徽，主鍵id過長就會(huì)導(dǎo)致一個(gè)二級(jí)索引(普通非主鍵索引)樹所能存儲(chǔ)的主鍵id記錄就會(huì)變少，因?yàn)樗饕彌_區(qū)的內(nèi)存空間是有限的翰守。

主鍵索引為什么最好是自增的孵奶？

如果表使用自增主鍵，那么每次插入新的記錄蜡峰，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置了袁，當(dāng)一頁寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁湿颅。這樣就會(huì)形成一個(gè)「緊湊」的索引結(jié)構(gòu)载绿，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)油航，因此效率很高崭庸，也不會(huì)增加很多開銷在維護(hù)索引上。Mysql 為維護(hù)索引可能需要頻繁的刷新緩沖谊囚，增加了方法磁盤 IO 的次數(shù)怕享，而且時(shí)常需要對(duì)索引結(jié)構(gòu)進(jìn)行重組織。

應(yīng)該設(shè)置唯一索引還是普通索引镰踏？

唯一索引和普通索引有什么區(qū)別函筋？

先看看查詢操作兩者的區(qū)別。

• 對(duì)于普通索引來說余境，查找到滿足條件的第一個(gè)記錄(5,500)后驻呐，需要查找下一個(gè)記錄，直到碰到第一個(gè)不滿足k=5條件的記錄芳来。

• 對(duì)于唯一索引來說含末，由于索引定義了唯一性，查找到第一個(gè)滿足條件的記錄后即舌，就會(huì)停止繼續(xù)檢索佣盒。

上面的查詢區(qū)別對(duì)于性能而言是微乎其微的，InnoDB讀寫數(shù)據(jù)是按頁來的顽聂，也是說當(dāng)找到k=5的記錄的時(shí)候肥惭，它所在的數(shù)據(jù)?就都在內(nèi)存里了，那么我們只需要判斷下一條數(shù)據(jù)是不是等于5即可紊搪，直到出現(xiàn)不為5的值為止蜜葱，這些都是在內(nèi)存中操作的，所以性能微乎其微耀石。

再來看看更新插入操作牵囤。想了解更新操作對(duì)性能的影響先了解了解change buffer。

當(dāng)需要更新一個(gè)數(shù)據(jù)?時(shí)，如果數(shù)據(jù)?在內(nèi)存中就直接更新揭鳞，而如果這個(gè)數(shù)據(jù)?還沒有在內(nèi)存中的話炕贵，在不影響數(shù)據(jù)一致性的 前提下，InooDB會(huì)將這些更新操作緩存在change buffer中野崇，這樣就不需要從磁盤中讀入這個(gè)數(shù)據(jù)?了称开。在下次查詢需要訪問這個(gè)數(shù)據(jù)?的時(shí)候，將數(shù)據(jù)?讀入內(nèi)存乓梨，然后執(zhí)行change buffer中與這個(gè)?有關(guān)的操作鳖轰。通過這種方式就能保證這個(gè)數(shù)據(jù)邏 輯的正確性。

將change buffer中的操作應(yīng)用到原數(shù)據(jù)?督禽，得到最新結(jié)果的過程稱為merge脆霎。除了訪問這個(gè)數(shù)據(jù)?會(huì)觸發(fā)merge外总处，系統(tǒng)有后 臺(tái)線程會(huì)定期merge狈惫。在數(shù)據(jù)庫正常關(guān)閉(shutdown)的過程中，也會(huì)執(zhí)行merge操作鹦马。

如果能夠?qū)⒏虏僮飨扔涗浽赾hange buffer胧谈，減少讀磁盤，語句的執(zhí)行速度會(huì)得到明顯的提升荸频。而且菱肖，數(shù)據(jù)讀入內(nèi)存 是需要占用buffer pool的，所以這種方式還能夠避免占用內(nèi)存旭从，提高內(nèi)存利用率稳强。

什么情況下會(huì)使用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%谎替。

再了解了change buffer之后我們?cè)倏纯匆粭l插入流程是怎么樣的偷溺。

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í)間⌒ⅲ可以認(rè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的使用場(chǎng)景

change buffer只限于用在普通索引的場(chǎng) 景下齿椅，而不適用于唯一索引琉挖。普通索引的所有場(chǎng)景，使用change buffer都可以起到加速作用嗎?

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

寫多讀少的業(yè)務(wù)來說纱耻，?面在寫完以后?上被訪問到的概率比較小，此時(shí)change buffer的使用效果最好险耀。這種業(yè) 務(wù)模型常?的就是賬單類弄喘、日志類的系統(tǒng)。

假設(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反而起到了副作用请敦。

change buffer 和 redo log

假設(shè)我們要執(zhí)行一個(gè)插入操作镐躲，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)圖勾笆。

image.png

分析這條更新語句，你會(huì)發(fā)現(xiàn)它涉及了四個(gè)部分:內(nèi)存桥滨、redo log(ib_log_fileX)窝爪、 數(shù)據(jù)表空間(t.ibd)、系統(tǒng)表空間(ibdata1)齐媒。

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

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

這條更新語句的成本很低，就是寫了兩處內(nèi)存唬血，然后寫了一處磁盤 (兩次操作合在一起寫了一次磁盤)望蜡，而且還是順序?qū)懙摹?/p>

我們現(xiàn)在要執(zhí)行 select * from t where k in (k1, k2)。這里拷恨，我畫了這兩個(gè)讀請(qǐng)求的流程圖脖律。如果讀語句發(fā)生在更新語句后不久，內(nèi)存中的數(shù)據(jù)都還在腕侄，那么此時(shí)的這兩個(gè)讀操作就與系統(tǒng)表空間(ibdata1)和 redo log(ib_log_fileX)無關(guān)了小泉。所以芦疏，我在圖中就沒畫出這兩部分。

image.png

1. 讀Page 1的時(shí)候微姊，直接從內(nèi)存返回酸茴。WAL之后如果讀數(shù)據(jù)，是不是一定要讀盤兢交， 是不是一定要從redo log里面把數(shù)據(jù)更新以后才可以返回?其實(shí)是不用的弊决。你可以看一下圖3的這個(gè)狀態(tài)，雖然磁盤上還是 之前的數(shù)據(jù)魁淳，但是這里直接從內(nèi)存返回結(jié)果飘诗，結(jié)果是正確的。
2. 要讀Page 2的時(shí)候界逛，需要把Page 2從磁盤讀入內(nèi)存中昆稿，然后應(yīng)用change buffer里面的操作日志，生成一個(gè)正確的版本并返回結(jié)果息拜。

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