MySQLを開発環境等で実行していると、
そこで今回はOPTIMEZE TABLE構文を使って、
検証環境
今回は第23回 mysqlslapを使って負荷テストをしてみようで使用したCentOS7にSysBenchをインストールして実行しています。MySQLのバージョンは5.
また、
削除した時のテーブルのサイズを確認する
CentOSを使ってyumでMySQLをインストールした場合に、/var/配下に設置されます。今回はzipcodeデータベースの中身を確認したいので、/var/に移動して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
実行した結果、
それぞれについて簡単に説明をすると、db.には名前が表す通りデータベースのオプションが入っています。今回は第2回で設定した文字コードの設定が記述されています。
# cat db.opt default-character-set=utf8mb4 default-collation=utf8mb4_general_ci
zipcode.ファイルには、zipcode.ファイルに実際のデータやindexが含まれています。今回住所データを読み込んだ時に、zipcode.ファイルに27Mのデータが書き込まれていることがわかります。
次項からは、
DELETE構文で削除した場合
この場合にどのような結果になるのか、
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
以上のようにファイルサイズが変化していないことがわかります。今回は全件削除したのですが、
TRUNCATE 構文で削除した場合
データ削除にはDELETE構文の他にもTRUNCATE構文があります。こちらを使ってテーブルの一括削除を行った場合は話が変わって、
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
全件削除を単純に行いたい場合は、
OPTIMIZE TABLEを実行してみる
さて前項のDELETE構文を使ってデータを削除した場合、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)
この時にファイルサイズを確認してみると、
# 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ですが、OPTIMIZE TABLEが更新ステートメントをブロックしてしまうため、OPTIMIZE TABLEを行う場合には時間的な余裕を持って行うか、RENAME TABLEをするなど、
5.6.5以前を使っている場合
MySQLの5.
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を行っても領域の解放がされません。ただし、
まとめ
今回はOPTIMIZE TABLEを使ってディスク領域を解放する方法を紹介しました。DELETEが多く発生する環境で作業を行っている場合はフラグメンテーションが発生している可能性が高いので、