公衆号：假裝懂編程

熟悉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在删除數據的時候，真的是把數據從磁盤上擦除，那麼這時候：

1. 别的事務通過undo log是無法找到原始數據
2. 可重複讀這個特性會被破壞

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

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

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

行與頁：這裡需要知道的是，我們最終通過B 樹檢索到的不是我們的目标行數據，而是目标行數據所在的頁，這個頁上有很多數據，都是索引序号相鄰的，當找到目标頁後，會把目标頁加載到内存中，然後通過二分法找到目标數據，也許你會問，那搜索的開銷不僅僅是磁盤IO，還有在二分法查找的開銷。這裡不可否認，但是我們一般忽略這部分開銷，因為cpu在内存裡檢索的速度很快，并且一頁也就16k，數據并不多。

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。

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

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

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

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

• 如果要重建的索引是普通索引，使用這種方式還好，需要注意的是假如你的業務TPS很大，建議在業務低峰期執行，因為雖然mysql支持online ddl，但是重建索引的過程還是很耗cpu和io資源的。
• 如果你要重建的是主鍵索引，那麼問題來了，首先如果你的主鍵索引設置的是自增長，是不支持drop的。其次如果你的主鍵沒設置成自增長，直接drop也不是我們想象的那樣，我們知道普通索引除了記錄本身的索引字段外，還會記錄主鍵的值，如果drop是直接删除索引，那麼通過普通引将找不到對應的行記錄，所以InnoDB是要求必須有主鍵索引的，這時InnoDB會嘗試去表中找個唯一索引來當主鍵，如果沒有唯一索引，那就自動創建一個默認的主鍵索引rowid，當新的主鍵索引建立好之後，還要去修改相關的普通索引讓其存儲新的主鍵，但是如果按照這種方法來修改的話，開銷會很大，特别是普通索引很多的情況下，于是InnoDB幹脆選擇重建表。對于緊接着執行的add index操作，同樣也會發生主鍵索引的變更，所以也會選擇重建表，最終可以發現在主鍵索引上的drop和add其實幹了一樣的事情。

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

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

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

1. 掃描原表主鍵索引的所有記錄
2. 生成新的b 樹記錄到臨時文件
3. 生成臨時文件的過程中，新的變更記錄到一個中轉日志row log中
4. 在臨時文件生成後，将期間row log的變更應用到新的臨時文件中
5. 然後替換臨時文件為當前文件

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

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

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

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

最後，如果你有任何疑問，歡迎聯系我，如果我的文章有問題，也歡迎指正，如果你愛學習，喜歡鑽研，可以關注我。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!