MySQL のストレージ容量は DELETE しただけでは空かない

MySQL で DELETE してもストレージは空かない。むしろストレージを食った。なぜか？

基本的に MySQL では DELETE してデータを物理的に削除しても, 削除した分のストレージ容量が増えることはありません. これは MySQL のデータベースのデータを保持しておくための ibdata というファイルがどんどん拡張してストレージの容量を食っていくのですが, データを DELETE しても ibdata ファイル上ではその削除データ分の空き容量は確保されたままの状態になり, 削除しても容量は減らないということだそうです.

via. 大規模ソーシャルゲーム運用で経験した MySQL(RDS) のストレージ管理で押さえておくべき勘所 - Qiita

行を削除する時、その行はディスク上で削除されたとマークされるだけで、実際は後で行をinsert/updateした時に使われるようInnoDBのファイル内では確保されたままになり、容量は減らない。これは非常に古いMySQLのバグだ。

しかし、innodb_file_per_tableを使っているなら、そのテーブルにOPTIMIZE TABLEを実行することで容量を開放することができる。OPTIMIZE TABLEは同じスキーマの空のテーブルを作る。それから1行ずつデータを古いテーブルから新しいテーブルにコピーする。このプロセスの中で、新しい.ibd表領域が作られ、ディスク容量が解放される。

via. innodb_file_per_tableが有効な時にディスク容量を開放するには | Yakst

OPTIMIZE TABLE が必要

ということでストレージを開放するためには、要するにOPTIMIZE TABLE が必要だということになる。

Amazon RDS で optimze table したときのロックなどの挙動に関しては下記を参照されたし。

2021-09-03 MySQL OPTIMIZE TABLE / ロック状態の確認

.ibd ファイルとは何か？

.ibd ファイル

file-per-table テーブルスペースおよび一般テーブルスペースのデータファイル。

File-per-table テーブルスペースの .ibd ファイルには、単一のテーブルおよび関連するインデックスデータが含まれます。 一般テーブルスペース .ibd ファイルには、複数のテーブルのテーブルおよびインデックスデータを含めることができます。

via. MySQL 用語集