MySQLのInnoDBはレコードを削除しても容量が減らない？

以前仕事で、MySQLが動かなくなる障害が発生しました。
原因を調べてみると、データベースサーバーのMySQL用のディスク（スライス）の使用率が100%になっていることがわかりました。
元々構築した際に、想定する容量を見誤っていた（全く足りなかった）ことはさて置き、いくらレコードを削除しても使用率は100%のまま変わらなかったので、ディスクを追加する事態になりました。

レコードを削除しても容量は減らない

普通に考えると、データベースと言えど結局はファイルなので、ファイルの中身を削除するとその分の容量は減るはずですが、MySQLで使用するテーブルのストレージエンジンによって異なります。

ストレージエンジンとは

ドンピシャな記事がありましたので引用します。

SQL文を受け取って結果を返すデータベースエンジン部分　⇨ つまりテーブルにデータを書き込んだり読みだしたりするプログラム
複数の種類があり、データの取得方法、保存方法、処理方法がストレージエンジンによって異なる
MySQLの場合、テーブル毎にストレージエンジンを分けられる

引用：MySQLのストレージエンジンについて – Qiita

参考サイトにも書いてあるが、主に使用されているストレートエンジンは「InnoDB」と「MyISAM」の2種類になります。
それぞれの性能は一旦脇に置いて、削除（SQLで言うDELETE）した場合にどうなるか説明します。

InnoDBとMyISAMのDELETE

最初に、SQLのDELETEはどストレージエンジンだろうと物理的にレコードを削除するのは変わりません。
ただし、MyISAMの場合は削除した分のデータ領域を開放するのに対し、InnoDBは削除した分のデータ領域も開放せず確保し続けるので、容量が減ることなくそのまま増え続けます。
なので、VMWareの仮想ディスクで言うところの”シン“と”シック“みたいな感じだろうと勝手にイメージしています。（間違ってたら教えてねｗ）

レコードを削除して容量を減らすには

著者が調べた限りでは、以下の3つの方法があります。

では、この3つを実例でご紹介します。

テーブルを最適化する

テーブルを最適化する方法いくつかありますが、代表的なものを少しご紹介します。
ちなみに、やり方は違えど裏で行っている処理はほぼ同じです。

SQLを実行して最適化する

テーブルの構造を変更するSQLとして知られている「ALTER TABLE」を使って、確保している空き領域を減らすことができます。
使い方は以下の通りです。

> ALTER TABLE [対象テーブル] ENGINE INNODB;

また、innodb_file_per_tableオプションが有効な状態なら、以下のSQLも使用することができます。

> OPTIMIZE TABLE [対象テーブル];

ただし、MySQLのバージョンによっては、実行中にテーブルがロックされてしまうため注意が必要です。

GUIで最適化する（phpMyAdmin）

皆さんお馴染みのphpMyAdminから最適化する方法です。
いつも通リ、データベースを選択してテーブル一覧を表示します。
最適化したいテーブルにチェックを入れて選択します。
テーブル一覧の下にある「チェックしたものを:」のプルダウンメニューから「テーブルを最適化する」を選択すると、勝手に処理が始まり、ただ待っていれば完了です！

バックアップ＆リストアをする

この方法は、MySQLの停止が必要な作業になるし、もし失敗すれば全データが消えてしまう可能性もあるためオススメできませんが、知識として知っておいても損はないと思います。
まず、バックアップでお馴染みのmysqldumpで、すべてのデータベースのバックアップをします。

# mysqldump -u root -p --opt --all-databases > /tmp/backup.sql
Enter password: ********

バックアップが完了したらMySQLを停止します。

# systemctl stop mysql

データディレクトリに移動して中身をすべて削除します。
デフォルトではこの場所ですね。

# cd /var/lib/mysql
# ll
# rm -rf *

削除できたらMySQLを起動します。

systemctl start mysql

最後に、バックアップしたものをリストアしたら完了です！

# mysql -u root -p < /tmp/backup.sql 
# Enter password: ********

一応、正常にバックアップできているか確認してから進めた方が良いでしょう。
また、ファイル削除の部分も気をつけてくださいね！

ストレージエンジンを変更する

稀に私のような「もうストレージエンジンはMyISAMでいいや！」って面倒くさがりの方向けに、最後はストレージエンジンの変更方法についてご紹介したいして終わりたいと思います。
ここからは、GUI（phpMyAdmin）での変更方法のみご紹介します。

当ブログでも利用しているエックスサーバーでは、古いサーバーから性能の良い新しいサーバーに無料で移行できる「新サーバー簡単移行」というサービスを提供していますが、移行できるデータベースの容量が2GBまでとなっています。
いくつかアンテナサイトを運営していますが、特に情報量が多いサイトではすぐに2GBを超えてしまうので、新サーバーへの移行ができませんでした。
そこで、phpMyAdminから2GBを超えているデータベースの中でも、特に容量の多い以下の投稿に関するテーブルのレコードを大幅に削除しましたが、もちろんデフォルトはInnoDBなので一向に容量が減りませんでした。

そんなに重要なサイトでもないので、思い切ってストレージエンジンをInnoDBからMyISAMに変更してみました！
手順はたったコレだけです。

あとは少し待てば完了です！
ちなみに、ストレージエンジンを変更する方法も危険が伴うので、できれば皆さんは最適化していただければと思います。

• Original:https://minory.org/mysql-innodb-delete.html
• Source:Minory
• Author:管理者

Amazonベストセラー