MySQL道普請便り

第35回OPTIMIZE TABLEでテーブルを最適化する

MySQLを開発環境等で実行していると、容量が足りなくなってしまうことはありませんか? そんな時にDELETE文を実行して容量を空けようとしても、削除したはずのデータのディスク領域が何故か解放されなくて困ってしまうことがあるかと思います。

そこで今回はOPTIMEZE TABLE構文を使って、ディスク領域を最適化する方法を紹介したいと思います。

検証環境

今回は第23回 mysqlslapを使って負荷テストをしてみようで使用したCentOS7にSysBenchをインストールして実行しています。MySQLのバージョンは5.7.17を使用しています。

また、今回使用するデータは「第2回 MySQLにはじめてのデータを入れてみる」で紹介をしている郵便番号のテーブルを用いて紹介を行います。

削除した時のテーブルのサイズを確認する

CentOSを使ってyumでMySQLをインストールした場合に、テーブルのファイルは以下のように/var/lib/mysql/データベース名配下に設置されます。今回はzipcodeデータベースの中身を確認したいので、/var/lib/mysql/zipcodeに移動してls -lhコマンドでファイルを確認します。

# cd /var/lib/mysql/zipcode 
# ls -lh
total 28M
-rw-r-----. 1 mysql mysql  67 Dec 13 00:05 db.opt
-rw-r-----. 1 mysql mysql 13K Dec 13 00:05 zipcode.frm
-rw-r-----. 1 mysql mysql 27M Dec 13 00:08 zipcode.ibd

実行した結果、3個のファイルがあることがわかります。

それぞれについて簡単に説明をすると、db.optには名前が表す通りデータベースのオプションが入っています。今回は第2回で設定した文字コードの設定が記述されています。

# cat db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci

zipcode.frmファイルには、zipcodeテーブル構造のメタ定義を管理するためのデータが入っています。zipcode.ibdファイルに実際のデータやindexが含まれています。今回住所データを読み込んだ時に、zipcode.ibdファイルに27Mのデータが書き込まれていることがわかります。

次項からは、DELETE構文で削除した場合と、TRUNCATE構文で削除した場合について見ていきたいと思います。

DELETE構文で削除した場合

この場合にどのような結果になるのか、実際に以下のSQLを実行してファイルサイズがどうなるか確認してみます。

mysql> SELECT COUNT(*) FROM zipcode;
+----------+
| COUNT(*) |
+----------+
|   123948 |
+----------+
1 row in set (0.14 sec)
mysql> DELETE FROM zipcode;
Query OK, 123948 rows affected (0.52 sec)

DELETE構文を使って全件削除を行いました。この時のファイルサイズは以下のようになっています。

# ls -lh
total 28M
-rw-r-----. 1 mysql mysql  67 Dec 13 00:05 db.opt
-rw-r-----. 1 mysql mysql 13K Dec 13 00:05 zipcode.frm
-rw-r-----. 1 mysql mysql 27M Dec 13 00:27 zipcode.ibd

以上のようにファイルサイズが変化していないことがわかります。今回は全件削除したのですが、WHERE句に条件をつけて付けて削除をした場合は27MBの領域の中にデータが点在してしまうので、効率的にデータが保存できていません。

TRUNCATE 構文で削除した場合

データ削除にはDELETE構文の他にもTRUNCATE構文があります。こちらを使ってテーブルの一括削除を行った場合は話が変わって、自動的にディスク領域が解放されます。以下のようにSQLを実行し、確認してみます。

mysql> SELECT COUNT(*) FROM zipcode;
+----------+
| COUNT(*) |
+----------+
|   123948 |
+----------+
1 row in set (0.14 sec)
mysql> TRUNCATE TABLE zipcode;
Query OK, 0 rows affected (0.05 sec)

この時のファイルサイズは以下のようになってます。

# ls -lh
total 116K
-rw-r-----. 1 mysql mysql  67 Dec 13 00:05 db.opt
-rw-r-----. 1 mysql mysql 13K Dec 13 00:40 zipcode.frm
-rw-r-----. 1 mysql mysql 96K Dec 13 01:23 zipcode.ibd

全件削除を単純に行いたい場合は、TRUNCATE構文を使った方がディスク効率が良くなるとわかります。

OPTIMIZE TABLEを実行してみる

さて前項のDELETE構文を使ってデータを削除した場合、データベースから削除しているにもかかわらず、ディスク領域が解放されていないことがわかります。実際には確保している領域をInnoDBが使い回して再利用してくれるのですが、このように一度に大量の削除が走った後はどうしてもフラグメンテーションが発生してしまいます。そんな時にデータ領域を最適化したい場合には、OPTIMIZE TABLEを使用します。前項のDELETE構文を使った削除が終わった状態から実行してみます。

mysql> OPTIMIZE TABLE zipcode;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| zipcode.zipcode | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zipcode.zipcode | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.06 sec)

この時にファイルサイズを確認してみると、以下のようにTRUNCATEした時とおなじ結果が得られました。

# ls -lh
total 116K
-rw-r-----. 1 mysql mysql  67 Dec 13 00:05 db.opt
-rw-r-----. 1 mysql mysql 13K Dec 13 01:27 zipcode.frm
-rw-r-----. 1 mysql mysql 96K Dec 13 01:27 zipcode.ibd

このように大量のデータを削除した場合にOPTIMIZE TABLEを実行するとディスク領域が解放されました。しかし、OPTIMIZE TABLEにはMySQLのバージョンによって注意することがいくつかありますので、次項でそれらに付いて説明を行っていきます。

MySQLのバージョンによる動作の違い

5.6.17以前を使っている場合

OPTIMIZE TABLEですが、5.6.17以下を使っている場合は注意が必要です。5.6.17以下ではOPTIMIZE TABLEが更新ステートメントをブロックしてしまうため、本番環境で使っているMySQLにOPTIMIZE TABLEを行う場合には時間的な余裕を持って行うか、mysqldumpを使ってdumpしたSQLを使用して別テーブルとして用意したのちにRENAME TABLEをするなど、別の方法を検討をする必要があります。

5.6.5以前を使っている場合

MySQLの5.6.6からデフォルトで有効になった設定の1つに、InnoDB File-Per-Tableモードというものがあります。この設定は、InnoDBのテーブルのデータとインデックスの保存領域をファイルに分けて保存する設定です。設定を確認する方法は以下のようになります。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.08 sec)

この設定がOFFになっている場合は全てのテーブルが一つにまとまったファイルに保存されてしまうため、OPTIMIZE TABLEを行っても領域の解放がされません。ただし、空いている領域はInnoDBで必要に応じて再利用されます。

まとめ

今回はOPTIMIZE TABLEを使ってディスク領域を解放する方法を紹介しました。DELETEが多く発生する環境で作業を行っている場合はフラグメンテーションが発生している可能性が高いので、年末の大掃除としてぜひ一度試してみてはいかがでしょうか?

おすすめ記事

記事・ニュース一覧