[InnoDB] 要回刪除table資料後未被釋放的空間

最近發生公司的測試環境硬碟容量不足，導致MySQL寫入紀錄失敗。本以為是開發時不小心讓app server寫太多log導致，所以把年代久遠的log file清一清後就沒理它了，沒想到過幾天又出現硬碟容量不足的情況。繼續追查後發現原來MySQL也很肥大，於是也把一些不重要的免洗資料也砍一砍。

奇怪的是，砍完後硬碟使用率竟然沒有下降的跡象，原來這跟DB底層的運作有些關係。當我們下了DELETE FROM table WHERE ...語句後，DB其實只把這些紀錄的存檔註記刪除，並不會真的釋放這些空間還給系統，而是等待下次新增資料時，直接蓋掉舊的資料所佔的空間。

這樣做的好處是可以直接地節省硬碟I/O開銷，想想當我們刪除一筆記錄時，DB把空間還給系統，然後下一筆操作是個INSERT，DB又去把剛剛釋放的空間要回來，這樣在低速裝置一來一往的reclaim過程對要求高效的DB來說不值得。

可是面對今天只是測試環境，被分配到的硬體已經是低配了，我就是想要回那些資料被刪除的空間挪作他用怎麼辦呢。這時，可以執行以下語法：

OPTIMIZE TABLE my_huge_table;

這句操作會將table中現有的資料複製進新創的table中，接著將舊的table drop掉，最後再將新的table改名回舊的名稱。由於只搬未被刪除的資料進新的table，所以在硬碟上新table所佔的空間自然比舊的table還來得少，也就是那些被註記刪除的資料已經被我們還給系統了。

如果像我們公司一樣MySQL版本比較舊，當執行上面語句時DB出現了訊息説OPTIMIZE對InnoDB不管用，也可以使用以下第二種語句：

ALTER TABLE my_huge_table ENGINE='InnoDB';

這句跟第一句基本上是一樣的意思，就是在跟DB説我要更換table使用的引擎嘍，幫我從InnoDB換到InnoDB吧。而在這更換引擎的過程中，由於會重建table底層管理資料儲存的index，自然那些被註記刪除的空間就被還給系統了。

附上一張今天在測試環境中清掉那些未被DB釋放掉的空間，只單純optimize了幾張table就可以看到disk usage一個深深的plunge，真是神清氣爽呀。

P.S. 以上語句都只建議在自己的開發環境或測試環境執行，因為在重建的過程中是會鎖整張表的，正式環境經不起這樣的blocking。正式環境就乖乖放著讓DB自己未來在插入新資料時重新利用那些空間就好。