MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因

問(wèn)題描述

在 MySQL 中使用 delete 語(yǔ)句刪除數(shù)據(jù)之后，監(jiān)控視圖中可用的磁盤空間沒(méi)有增加，磁盤使用率沒(méi)有下降等等。

解決方案

delete 不釋放空間是 MySQL 自身機(jī)制的原因，需要重建表才可以釋放磁盤空間，可以參考的操作：

1. 執(zhí)行 optimize table ${table_name}。
2. 如果是 InnoDB 的表，執(zhí)行 alter table ${table_name} engine = innodb。
   

需要注意以下兩個(gè)問(wèn)題：

這兩個(gè)命令都會(huì)重建表，盡量不要在磁盤空間緊張（>90%）的時(shí)候進(jìn)行操作，先擴(kuò)容磁盤，操作完之后再縮容。
這兩個(gè)命令在開始和結(jié)束的時(shí)候都會(huì)嘗試獲取 metadata lock，所以盡量不要在業(yè)務(wù)高峰期執(zhí)行。

問(wèn)題分析

在 MySQL 的機(jī)制中，delete 刪除的行只是被標(biāo)記為刪除狀態(tài)，如果刪除的行很多，整個(gè)數(shù)據(jù)頁(yè)（innodb_page）的行都會(huì)被刪除的時(shí)候，數(shù)據(jù)頁(yè)也只會(huì)標(biāo)記為刪除，都不會(huì)真正的物理刪除，而是一直占用，等待被復(fù)用。

例如：

可以看到 delete 前后，data_length 并沒(méi)有發(fā)生變化，但是 data_free 增加了很多。這說(shuō)明數(shù)據(jù)雖然刪了，但是并沒(méi)有被釋放，仍舊被 test1 表占用，只是顯示處于 free 狀態(tài)，以后再寫入新數(shù)據(jù)的時(shí)候就可以直接復(fù)用，而不需要在申請(qǐng)新的磁盤空間了。

這個(gè)時(shí)候使用alter table test1 engine = innodb 看看效果：

可以看到 data_length 和 data_free 都變成了空表的狀態(tài)，僅有一個(gè) innodb_page (默認(rèn) 16k)。

PS：data_free 本身也可以用來(lái)評(píng)估表的空間碎片，當(dāng)這個(gè)數(shù)字非常高的時(shí)候，可以考慮用同樣的方法重建表，回收一部分磁盤空間。

以上就是MySQL Delete 刪數(shù)據(jù)后磁盤空間未釋放的原因的詳細(xì)內(nèi)容，更多關(guān)于MySQL 刪數(shù)據(jù)后磁盤空間未釋放的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！