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

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

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

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

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

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

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

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

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

四、基于頁的存儲方式

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

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

1、行與頁

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

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

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

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

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

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

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

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

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

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

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

六、有合并也有分裂

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

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

頁分裂會造成頁的利用率降低，造成頁分裂的原因有很多，比如：

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

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

七、手動重建表

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

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

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

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

alter table xx engine=InnoDB

這個命令可以重建我們這個表，但是前提是我們的表是獨(dú)占表空間的?；贛ySQL的online ddl，這個過程它是不影響正常的讀寫的，它的過程如下：

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

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

八、重建表不一定會收縮空間

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

但是因為這個預(yù)留操作，某些情況下會導(dǎo)致重建后的表空間反而會變大。

• 如果你的表本身就很緊湊，因為預(yù)留1/16會變大。
• 在第一次重建表后，因為新的插入導(dǎo)致用掉了預(yù)留空間的一部分(這里需要注意的是預(yù)留空間沒用完，還剩一部分)，但是沒有用到新的頁，所以整體的空間沒有變化，這時候如果再次重建表，就會因為要預(yù)留1/16，導(dǎo)致申請的新的頁，那么空間就會變大。