環境
- MySQL
状況
MySQLで数百GBまで肥大化した1テーブルをダイエットすべく、古いレコードを大量にDELETEした。
仮に300GBで3億レコードあったテーブルから2億レコード消して1億レコードにしたとする。単純計算なら100GBになってほしいところだが、そうはならない。DELETEしてもディスク上のファイルサイズは1バイトも減らない。
ネットで調べてみるとOPTIMIZE TABLEを実行すれば実サイズが減るらしい。ただ、OPTIMIZEした場合としなかった場合で、以降のINSERTでファイルサイズがどう変わるのかがよく分からなかったので調べてみた。
Optimize table後の挙動
まず前提として、MyISAMではDELETEしても.MYDや.MYIファイルそのものは縮まない。削除されたレコードの領域は「空き領域」として内部的にマークされるだけで、OSから見たファイルサイズは変わらないままだ。OPTIMIZE TABLEはこの空き領域を詰め直してファイルを作り直す処理なので、実行して初めてサイズが減る。
今回の想定を整理するとこうなる。
- 削除前: 300GB（3億レコード）
- 2億レコードをDELETE後: ファイルサイズは300GBのまま
- OPTIMIZE TABLE後: 100GB
- 空き領域相当: 約200GB
- そのあとX件追加したときの実データ増加: 約10GB
ここで気になるのが、OPTIMIZEせずに放置したテーブルにX件（10GB分）をINSERTしたら300 + 10で310GBになるのか？という点。
結論から言うと310GBにはならず、300GBのままになる可能性が高い。
MyISAMはINSERT時にいきなりファイル末尾へ追記するのではなく、まずファイル内部の空き領域を再利用しようとするからだ。今回は200GBという十分すぎる空きがあるので、10GB分のレコードはその中に収まる。ファイルの枠自体を広げる必要がないので、サイズは300GBのまま据え置きになる。
つまり概念的にはこういう違いになる。
- OPTIMIZEした場合: 空き領域を除去して100GBに縮小 → X件追加で110GB
- OPTIMIZEしなかった場合: ファイルは300GBのまま、内部の約200GBが再利用可能領域 → X件追加分の10GBがそこに収まるので約300GBのまま
ただし「きっちり300GBのまま」を保証するものではない。次のようなケースでは多少増えることがある。
- 挿入する行が既存の空き領域のブロックに収まらない
- 可変長行で空き領域が細かく断片化している
- インデックス領域を完全には再利用できない
- concurrent_insertの設定や挿入方法によって末尾へ追記される
- 追加行のサイズやキー分布が以前と異なる
なので実際には301GBや305GBに増えることはあるし、条件次第で310GBを超えることもありうる。とはいえ「削除した分がまるごと無駄になり続ける」わけではなく、消した領域はちゃんと再利用される、というのが要点だ。
ディスクを今すぐ空けたいならOPTIMIZE TABLEを打つしかないが、また同じ規模までデータが増えるのが分かっているなら、テーブル全体のロックと作り直しのコストを払ってまで急いで実行する必要はない、という判断もできる。
確認するSQL
空き領域がどれくらい溜まっているかはSHOW TABLE STATUSのData_free、もしくはinformation_schema.tablesのdata_freeで確認できる。テーブル単位でまとめて見たいならこっちのほうが扱いやすい。
SELECT table_name, engine, ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_gb, ROUND(index_length / 1024 / 1024 / 1024, 2) AS index_gb, ROUND(data_free / 1024 / 1024 / 1024, 2) AS free_gb, ROUND(100 * data_free / NULLIF(data_length, 0), 2) AS free_pct FROM information_schema.tables WHERE table_schema = 'YOUR_DATABASE' AND table_name = 'uids';
free_gbが実データ（data_gb）に対して無視できない割合になっていれば、それがDELETEで空いた再利用待ちの領域ということになる。free_pctが数百％みたいな数字になっていたら、まさに今回のケースだ。
注意点として、data_freeの値がそのまま100％効率よく再利用されるとは限らない。断片化していれば実際に使える量はこれより少なくなるので、あくまで目安として見ておくのがいい。
