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

熟悉MySQL InnoDB存儲引擎的同學都應該知道，當我們執行delete的時候，數據并沒有被真正的刪除，只是對應數據的刪除標識deleteMark被打開了，這樣每次執行查詢的時候，如果發現數據存在但是deleteMark是開啟的話，那么依然返回空，因為這個細節，所以經常會出現“我明明刪除了數據，為什么空間沒釋放”的現象。

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

二、為什么不直接刪除，而是打個標記

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

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

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

三、只是打個標記的話，豈不是很浪費空間

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

四、基于頁的存儲方式

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

為了減少磁盤IO，MySQL采用B+樹的索引結構來組織數據，B+樹的特點是矮胖，一般樹的高度就代表了IO的次數，越矮的話，樹的高度越低，那么對應的IO次數就越少，還有一點需要知道的是數據最終都在葉子節點上，所以在B+樹上搜索的時候，一定是要檢索到最后一層葉子節點上，這是一種穩定性的表現。

1、行與頁

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

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

2、IO次數不一定等于樹的高度

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

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

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

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

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

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

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

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

六、有合并也有分裂

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

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

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

• 比如離散的插入，導致數據不連續。
• 把記錄更新成一個更大記錄，導致空間不夠用。

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

七、手動重建表

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

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

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

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

alter table xx engine=InnoDB

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

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

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

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

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

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

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