MySQLで複数の行を削除した後にテーブルを最適化する方法

はじめに

MySQLでは、DELETE文で行を削除しても、ディスク上の領域はすぐには解放されません。削除によって生じた断片化（フラグメーション）を解消し、テーブルのストレージ効率とクエリ性能を回復させるには、OPTIMIZE TABLEコマンドを使用します。

OPTIMIZE TABLE テーブル名;

この記事では、実際にテーブルを作成し、行を削除してから最適化を実行するまでの一連の手順を例とともに解説します。

1. テーブルの作成

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100)
);
Query OK, 0 rows affected (1.38 sec)

2. レコードの挿入

INSERT文を使って、いくつかのレコードを追加します。

mysql> insert into DemoTable(Name) values('Chris');
Query OK, 1 row affected (0.27 sec)

mysql> insert into DemoTable(Name) values('Robert');
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable(Name) values('Bob');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DemoTable(Name) values('David');
Query OK, 1 row affected (0.38 sec)

3. レコードの確認

SELECT文ですべてのレコードを表示します。

mysql> select * from DemoTable;

実行結果は以下の通りです。

+----+--------+
| Id | Name   |
+----+--------+
|  1 | Chris  |
|  2 | Robert |
|  3 | Bob    |
|  4 | David  |
+----+--------+
4 rows in set (0.00 sec)

4. 行の削除

次に、DELETE文を使って特定の行を削除します。ここではIdが1と3のレコードを削除してみましょう。

mysql> delete from DemoTable where Id IN(1,3);
Query OK, 2 rows affected (0.29 sec)

削除後のテーブルを確認します。

mysql> select * from DemoTable;
+----+--------+
| Id | Name   |
+----+--------+
|  2 | Robert |
|  4 | David  |
+----+--------+
2 rows in set (0.00 sec)

この時点では、削除された行分の領域は内部的に「空き」としてマークされるだけで、物理的には解放されていません。

5. OPTIMIZE TABLEの実行

行を削除した後、以下のクエリでテーブルを最適化します。

mysql> optimize table DemoTable;

実行結果は次のようになります。

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                            |
+------------------+----------+----------+-------------------------------------------------------------------+
| web.DemoTable    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| web.DemoTable    | optimize | status   | OK                                                                    |
+------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (4.32 sec)

実行結果の見方

InnoDBテーブルの場合、「Table does not support optimize, doing recreate + analyze instead」というnoteメッセージが表示されることがありますが、これはエラーではありません。InnoDBはOPTIMIZE TABLEを直接サポートしていないため、代わりにテーブルの再作成（recreate）と解析（analyze）が自動的に実行されます。最終的に「status: OK」と表示されていれば、最適化は正常に完了しています。

まとめ

行を頻繁に削除するテーブルでは、断片化が進んでストレージや検索性能に影響を与えることがあります。OPTIMIZE TABLEを定期的に実行することで、不要な領域を回収し、テーブルを健全な状態に保つことができます。ただし、大きなテーブルに対しては処理に時間がかかる場合があるため、アクセスが少ない時間帯に実行することをおすすめします。