一、刪除并不是真正的刪除

熟悉MySQL InnoDB存儲(chǔ)引擎的同學(xué)都應(yīng)該知道，當(dāng)我們執(zhí)行delete的時(shí)候，數(shù)據(jù)并沒(méi)有被真正的刪除，只是對(duì)應(yīng)數(shù)據(jù)的刪除標(biāo)識(shí)deleteMark被打開(kāi)了，這樣每次執(zhí)行查詢的時(shí)候，如果發(fā)現(xiàn)數(shù)據(jù)存在但是deleteMark是開(kāi)啟的話，那么依然返回空，因?yàn)檫@個(gè)細(xì)節(jié)，所以經(jīng)常會(huì)出現(xiàn)“我明明刪除了數(shù)據(jù)，為什么空間沒(méi)釋放”的現(xiàn)象。

15M  7  6 18:46 user_info.ibd #刪除前
15M 10  4 16:47 user_info.ibd #刪除后

二、為什么不直接刪除，而是打個(gè)標(biāo)記

我們知道InnoDB存儲(chǔ)引擎是支持MVCC的，即多版本控制，得益于MVCC，MySQL在事務(wù)里查詢數(shù)據(jù)的時(shí)候不需要加鎖，可以提供很好的并發(fā)性，同時(shí)提供可重復(fù)讀這個(gè)很重要的特性。那么它是怎么到的呢?答案是undo log，可以簡(jiǎn)單的理解為，每次更新數(shù)據(jù)的時(shí)候?qū)⒏虑暗臄?shù)據(jù)先寫入undo log中，這樣當(dāng)需要回滾的時(shí)候，只需要順著undo log找到歷史數(shù)據(jù)即可。undo log與原始數(shù)據(jù)之間是用指針鏈接起來(lái)的，即每條數(shù)據(jù)都有個(gè)回滾指針指向undo log。

如果InnoDB在刪除數(shù)據(jù)的時(shí)候，真的是把數(shù)據(jù)從磁盤上擦除，那么這時(shí)候：

• 別的事務(wù)通過(guò)undo log是無(wú)法找到原始數(shù)據(jù)。
• 可重復(fù)讀這個(gè)特性會(huì)被破壞。

三、只是打個(gè)標(biāo)記的話，豈不是很浪費(fèi)空間

MySQL里面有個(gè)purge線程，它的工作中有一項(xiàng)任務(wù)就是專門檢查這些有deleteMark的數(shù)據(jù)，當(dāng)有deleteMark的數(shù)據(jù)如果沒(méi)有被其他事務(wù)引用時(shí)，那么會(huì)被標(biāo)記成可復(fù)用，因?yàn)槿~子節(jié)點(diǎn)數(shù)據(jù)是有序的原因，這樣當(dāng)下次有同樣位置的數(shù)據(jù)插入時(shí)，可以直接復(fù)用這塊磁盤空間。當(dāng)整個(gè)頁(yè)都可以復(fù)用的時(shí)候，也不會(huì)把它還回去，會(huì)把可復(fù)用的頁(yè)留下來(lái)，當(dāng)下次需要新頁(yè)時(shí)可以直接使用，從而減少頻繁的頁(yè)申請(qǐng)。

四、基于頁(yè)的存儲(chǔ)方式

我們知道MySQL數(shù)據(jù)是存儲(chǔ)在磁盤上的，磁盤的速度想必大家都知道，特別是當(dāng)發(fā)生隨機(jī)IO的時(shí)候。這里簡(jiǎn)單解釋下什么叫IO，以機(jī)械磁盤為例，我們最終的數(shù)據(jù)都是落在磁盤的一個(gè)一個(gè)扇區(qū)上的，當(dāng)一個(gè)扇區(qū)寫滿了，就得換下一個(gè)扇區(qū)，這時(shí)就要通過(guò)盤片的轉(zhuǎn)動(dòng)找到目標(biāo)扇區(qū)，這是物理運(yùn)動(dòng)。如果要寫入的下一個(gè)扇區(qū)和當(dāng)前的扇區(qū)是緊挨著的，這叫順序IO，如果要寫入的扇區(qū)和當(dāng)前的扇區(qū)中間隔了幾個(gè)扇區(qū)，這叫隨機(jī)IO，很明顯隨機(jī)IO需要更長(zhǎng)的轉(zhuǎn)動(dòng)時(shí)間。所以查詢一個(gè)數(shù)據(jù)的時(shí)候，減少IO是非常關(guān)鍵的，特別是隨機(jī)IO。

為了減少磁盤IO，MySQL采用B+樹的索引結(jié)構(gòu)來(lái)組織數(shù)據(jù)，B+樹的特點(diǎn)是矮胖，一般樹的高度就代表了IO的次數(shù)，越矮的話，樹的高度越低，那么對(duì)應(yīng)的IO次數(shù)就越少，還有一點(diǎn)需要知道的是數(shù)據(jù)最終都在葉子節(jié)點(diǎn)上，所以在B+樹上搜索的時(shí)候，一定是要檢索到最后一層葉子節(jié)點(diǎn)上，這是一種穩(wěn)定性的表現(xiàn)。

1、行與頁(yè)

這里需要知道的是，我們最終通過(guò)B+樹檢索到的不是我們的目標(biāo)行數(shù)據(jù)，而是目標(biāo)行數(shù)據(jù)所在的頁(yè)，這個(gè)頁(yè)上有很多數(shù)據(jù)，都是索引序號(hào)相鄰的，當(dāng)找到目標(biāo)頁(yè)后，會(huì)把目標(biāo)頁(yè)加載到內(nèi)存中，然后通過(guò)二分法找到目標(biāo)數(shù)據(jù)。

也許你會(huì)問(wèn)，那搜索的開(kāi)銷不僅僅是磁盤IO，還有在二分法查找的開(kāi)銷。這里不可否認(rèn)，但是我們一般忽略這部分開(kāi)銷，因?yàn)閏pu在內(nèi)存里檢索的速度很快，并且一頁(yè)也就16k，數(shù)據(jù)并不多。

2、IO次數(shù)不一定等于樹的高度

前面我們說(shuō)到樹的高度等于IO的次數(shù)，這其實(shí)不是很準(zhǔn)確，我們知道樹的根節(jié)點(diǎn)一定是在內(nèi)存里的，那么對(duì)于一顆高度為3的數(shù)據(jù)，只用2次IO即可，這其實(shí)可以理解，畢竟根節(jié)點(diǎn)只占用一頁(yè)的空間，一頁(yè)才16K，放在內(nèi)存里綽綽有余。但有時(shí)候樹的第二層也可以放在內(nèi)存里。

假設(shè)現(xiàn)在主鍵是bigint，bigint我們知道占用8個(gè)字節(jié)，對(duì)于一個(gè)索引來(lái)說(shuō)除了類型本身占用空間之外，還有一個(gè)指針，這個(gè)指針占用6個(gè)字節(jié)，那么對(duì)于根節(jié)點(diǎn)來(lái)說(shuō)它大概能存 16K/(8+6)B = 1170 個(gè)數(shù)據(jù)，每個(gè)數(shù)據(jù)都可以指向一頁(yè)(也就是它的下一層)，這樣整個(gè)樹的第二層大概占用 1170*16K = 18M 的空間，這也不是一個(gè)很大的數(shù)字，對(duì)于機(jī)器的內(nèi)存來(lái)說(shuō)，幾乎也是滄海一粟，所以第二層往往也在內(nèi)存里，所以最終在B+樹上檢索數(shù)據(jù)所消耗的IO應(yīng)該比理論的要低。

通過(guò)上面我們知道檢索一條數(shù)據(jù)的快慢，主要受樹的高度影響的，這和你的數(shù)據(jù)表的大小并沒(méi)有太大的關(guān)系，現(xiàn)實(shí)中有人可能在數(shù)據(jù)表達(dá)到百萬(wàn)級(jí)別的時(shí)就考慮分表，個(gè)人認(rèn)為這有點(diǎn)低估B+樹的能力了。還是以bigint類型的主鍵索引為例，假設(shè)一行數(shù)據(jù)占用1K(理論上已經(jīng)足夠大了)，那么一頁(yè)可以存下 16K/1K=16 條數(shù)據(jù)，對(duì)于一顆高度為3的B+樹來(lái)說(shuō)，它可以存下 1170*1170*16=21902400 的數(shù)據(jù)，將近2千萬(wàn)，如果你的數(shù)據(jù)行占用的空間更小，就可以存下更多的數(shù)據(jù)，所以只是簡(jiǎn)單的根據(jù)數(shù)據(jù)行數(shù)來(lái)判斷是否需要分表不是那么的合理。

五、可復(fù)用的空間一直沒(méi)有被利用咋辦

前面我們說(shuō)到刪除的數(shù)據(jù)不會(huì)被真的刪除，只是打上個(gè)deleteMark的標(biāo)識(shí)，然后會(huì)被復(fù)用，但是如果一直沒(méi)被復(fù)用，那么空間不就是白白的浪費(fèi)了，更糟糕的是，如果刪除的很多數(shù)據(jù)空間都沒(méi)有被復(fù)用，就會(huì)造成頁(yè)空間存在大量的碎片，為了解決這種情況，MySQL內(nèi)部有個(gè)叫頁(yè)合并的功能，這是什么意思呢?簡(jiǎn)單理解就是頁(yè)A現(xiàn)在有很多可以被復(fù)用的空間，它的鄰居頁(yè)B也有很多可以復(fù)用的空間，此時(shí)頁(yè)A就可以和頁(yè)B合并，如果合并后能省出來(lái)一頁(yè)，那么多出來(lái)的一頁(yè)就可以被下次使用，從而達(dá)到頁(yè)最大利用的效果。

合并的關(guān)鍵需要當(dāng)前頁(yè)的前一頁(yè)或者后一頁(yè)也有大量的碎片空間，這里為何要「大量」很關(guān)鍵，合并的動(dòng)作可以簡(jiǎn)單理解就是把別的頁(yè)的數(shù)據(jù)移動(dòng)過(guò)來(lái)，如果兩個(gè)頁(yè)pageA和pageB都只有少量的可復(fù)用空間，那么合并后，即使pageA可以填滿，但是另一個(gè)頁(yè)P(yáng)age也還是有碎片空間的，并且碎片更大，這時(shí)候數(shù)據(jù)移動(dòng)的開(kāi)銷可能要大于存儲(chǔ)的開(kāi)銷，得不償失。

而且還會(huì)有個(gè)嚴(yán)重的問(wèn)題，pageB可能會(huì)和pageC合并，那么pageC的碎片更大...，這樣的話似乎是個(gè)無(wú)底洞，導(dǎo)致很多頁(yè)都在移動(dòng)數(shù)據(jù)。因此一個(gè)合理的合并條件很關(guān)鍵，InnoDB中何時(shí)合并受MERGE_THRESHOLD這個(gè)參數(shù)影響，它的默認(rèn)值是50%，50%的意圖很明顯，兩個(gè)50%就可以省出一個(gè)頁(yè)。

我們看個(gè)例子，pageA已經(jīng)有50%的數(shù)據(jù)被刪除了，它的鄰居pageB只使用了不到50%的數(shù)據(jù)，這時(shí)候會(huì)將pageB的數(shù)據(jù)移動(dòng)到pageA上，那么整個(gè)pageB就是空頁(yè)了，可以提供給別的數(shù)據(jù)使用。這里需要知道的是除了刪除會(huì)觸發(fā)頁(yè)合并外，更新可能也會(huì)觸發(fā)頁(yè)合并。

六、有合并也有分裂

合并頁(yè)是提升頁(yè)的利用率的方式，但是有時(shí)候我們又不得不分裂頁(yè)，我們知道葉子節(jié)點(diǎn)的頁(yè)之間是用雙向鏈表串接起來(lái)的，并且頁(yè)與頁(yè)之間的數(shù)據(jù)是有序的。

以上圖為例，當(dāng)我們要插入5這條數(shù)據(jù)，按道理應(yīng)該嘗試放在pageA里面，但是pageA目前沒(méi)有足夠的空間來(lái)存放一條數(shù)據(jù)，于是嘗試找到pageA的相鄰頁(yè)pageB，但是此時(shí)很不幸的是pageB也沒(méi)有足夠的空間來(lái)存放一條數(shù)據(jù)，由于要求數(shù)據(jù)的連續(xù)性，數(shù)據(jù)5必須在數(shù)據(jù)4和數(shù)據(jù)6之間，那么只能新建一個(gè)頁(yè)，新建一個(gè)頁(yè)后，會(huì)嘗試從pageA中移動(dòng)一部分?jǐn)?shù)據(jù)到新的頁(yè)上，并且會(huì)重新組織頁(yè)與頁(yè)之間的關(guān)系，即在pageA和pageB之間會(huì)隔一道新頁(yè)pageC。

頁(yè)分裂會(huì)造成頁(yè)的利用率降低，造成頁(yè)分裂的原因有很多，比如：

• 比如離散的插入，導(dǎo)致數(shù)據(jù)不連續(xù)。
• 把記錄更新成一個(gè)更大記錄，導(dǎo)致空間不夠用。

還有一點(diǎn)需要知道的是：不管是頁(yè)的合并還是頁(yè)的分裂，都是相對(duì)耗時(shí)的操作，除了移動(dòng)數(shù)據(jù)的開(kāi)銷外，InnoDB也會(huì)在索引樹上加鎖。

七、手動(dòng)重建表

頁(yè)的合并和分裂主要是在插入、刪除或更新的時(shí)候，并且正好滿足某些條件才發(fā)生的，那如果這些條件一直不滿足，碎片就無(wú)法得到清理，這時(shí)候往往會(huì)出現(xiàn)"我的表明明沒(méi)多少數(shù)據(jù)，為什么還占用這么大空間"這個(gè)現(xiàn)象，針對(duì)這個(gè)現(xiàn)象有人說(shuō)重建索引，這個(gè)是對(duì)的，重建索引可以讓數(shù)據(jù)更加緊湊，頁(yè)的利用率達(dá)到更高。但是如何重建索引?第一時(shí)間你可能會(huì)想到先drop index然后add index，這個(gè)似乎不是那么準(zhǔn)確。

如果要重建的索引是普通索引，使用這種方式還好，需要注意的是假如你的業(yè)務(wù)TPS很大，建議在業(yè)務(wù)低峰期執(zhí)行，因?yàn)殡m然MySQL支持online ddl，但是重建索引的過(guò)程還是很耗cpu和io資源的。

如果你要重建的是主鍵索引，那么問(wèn)題來(lái)了，首先如果你的主鍵索引設(shè)置的是自增長(zhǎng)，是不支持drop的。其次如果你的主鍵沒(méi)設(shè)置成自增長(zhǎng)，直接drop也不是我們想象的那樣，我們知道普通索引除了記錄本身的索引字段外，還會(huì)記錄主鍵的值，如果drop是直接刪除索引，那么通過(guò)普通引將找不到對(duì)應(yīng)的行記錄，所以InnoDB是要求必須有主鍵索引的，這時(shí)InnoDB會(huì)嘗試去表中找個(gè)唯一索引來(lái)當(dāng)主鍵，如果沒(méi)有唯一索引，那就自動(dòng)創(chuàng)建一個(gè)默認(rèn)的主鍵索引rowid，當(dāng)新的主鍵索引建立好之后，還要去修改相關(guān)的普通索引讓其存儲(chǔ)新的主鍵，但是如果按照這種方法來(lái)修改的話，開(kāi)銷會(huì)很大，特別是普通索引很多的情況下，于是InnoDB干脆選擇重建表。對(duì)于緊接著執(zhí)行的add index操作，同樣也會(huì)發(fā)生主鍵索引的變更，所以也會(huì)選擇重建表，最終可以發(fā)現(xiàn)在主鍵索引上的drop和add其實(shí)干了一樣的事情。

綜上所述，一般在你的表出現(xiàn)很多頁(yè)碎片的時(shí)候，建議使用：

alter table xx engine=InnoDB

這個(gè)命令可以重建我們這個(gè)表，但是前提是我們的表是獨(dú)占表空間的。基于MySQL的online ddl，這個(gè)過(guò)程它是不影響正常的讀寫的，它的過(guò)程如下：

• 掃描原表主鍵索引的所有記錄
• 生成新的b+樹記錄到臨時(shí)文件
• 生成臨時(shí)文件的過(guò)程中，新的變更記錄到一個(gè)中轉(zhuǎn)日志row log中
• 在臨時(shí)文件生成后，將期間row log的變更應(yīng)用到新的臨時(shí)文件中
• 然后替換臨時(shí)文件為當(dāng)前文件

這里需要注意的是重建表的過(guò)程涉及到數(shù)據(jù)的copy，得保證磁盤有足夠的空間，至少是現(xiàn)在磁盤空間的1倍，如果磁盤空間不足，那么是不會(huì)重建成功的。

八、重建表不一定會(huì)收縮空間

在重建表的過(guò)程中，有一點(diǎn)需要知道：InnoDB不會(huì)讓重建后的頁(yè)充滿數(shù)據(jù)，會(huì)預(yù)留個(gè)「1/16」的空間，這個(gè)意圖很明顯，如果不預(yù)留，選擇占滿整個(gè)頁(yè)，這時(shí)候去更新一條需要更大空間的老數(shù)據(jù)，就會(huì)需要新的頁(yè)，寫入新的頁(yè)后，往往又會(huì)造成碎片，所以提前預(yù)留一點(diǎn)空間是有用的。

但是因?yàn)檫@個(gè)預(yù)留操作，某些情況下會(huì)導(dǎo)致重建后的表空間反而會(huì)變大。

• 如果你的表本身就很緊湊，因?yàn)轭A(yù)留1/16會(huì)變大。
• 在第一次重建表后，因?yàn)樾碌牟迦雽?dǎo)致用掉了預(yù)留空間的一部分(這里需要注意的是預(yù)留空間沒(méi)用完，還剩一部分)，但是沒(méi)有用到新的頁(yè)，所以整體的空間沒(méi)有變化，這時(shí)候如果再次重建表，就會(huì)因?yàn)橐A(yù)留1/16，導(dǎo)致申請(qǐng)的新的頁(yè)，那么空間就會(huì)變大。