ヅラッシュ！ by NI-Lab.

環境: MySQL 4.1 on Debian GNU/Linux Sarge

hogedb データベースの t_hoge テーブルに不要なデータがたくさんあったので、不要な一部のデータを削除してみる。

レコード削除の過程

mysql コマンドで。

全レコード数取得は速い。

mysql> select count(*) from t_hoge;
+----------+
| count(*) |
+----------+
|   189737 |
+----------+
1 row in set (0.00 sec)

条件に合うレコード数の取得は遅い。
MySQLの日付型で絞込み(20070701より前)。

mysql> select count(*) from t_hoge where cachedtime < 20070701;
+----------+
| count(*) |
+----------+
|   159082 |
+----------+
1 row in set (41.67 sec)

条件に合うレコードを削除するのは遅い。
MySQLの日付型で絞込み(20070701より前)。

mysql> delete from t_hoge where cachedtime < 20070701;
Query OK, 159080 rows affected (5 min 16.85 sec)
 
mysql> select count(*) from t_hoge where cachedtime < 20070701;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1 min 30.70 sec)

全レコード数取得は速い。

mysql> select count(*) from t_hoge;
+----------+
| count(*) |
+----------+
|    87485 |
+----------+
1 row in set (0.00 sec)

テーブルの最適化

レコードをたくさん削除したが、実際のデータベースのファイル(*.MYD とか *.MYI)のファイルサイズは小さくならなかった。

そこで、optimize table.

OPTIMIZE TABLE は、テーブルの大部分を削除したり、可変長レコードを持つテーブル（VARCHAR カラム、BLOB カラム、または TEXT カラムを持つテーブル）に多くの変更を加えた場合に使用します。 削除されたレコードはリンクされたリストに保持され、元のレコード位置は、後続の INSERT 操作により再利用されます。OPTIMIZE TABLE を使用して未使用領域を解放し、データファイルを最適化することができます。

ほとんどの場合、OPTIMIZE TABLE を実行する必要はありません。可変長レコードに更新を多く行う場合でも、週または月に一度、特定のテーブルだけに実行するだけで十分です。

現在のところ、OPTIMIZE TABLE は MyISAM テーブルと BDB テーブルにのみ有効です。BDB テーブルでは、OPTIMIZE TABLE は現在 ANALYZE TABLE にマップされます。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 4.6.1 OPTIMIZE TABLE 構文

optimize table を実行。

mysql> optimize table t_hoge;
+-------------------+----------+----------+----------+
| Table             | Op       | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| hogedb.t_hoge     | optimize | status   | OK       |
+-------------------+----------+----------+----------+
1 row in set (1 min 25.11 sec)

optimize table 実行前。

# ls -la /var/lib/mysql/hogedb | grep hoge
-rw-rw----  1 mysql mysql 1299050568 Jul 24 20:58 t_hoge.MYD
-rw-rw----  1 mysql mysql   22189056 Jul 24 20:58 t_hoge.MYI
-rw-rw----  1 mysql mysql       8678 Jan  7  2007 t_hoge.frm

optimize table 実行後。

# ls -la /var/lib/mysql/hogedb | grep hoge
-rw-rw----  1 mysql mysql 502637068 Jul 24 21:03 t_hoge.MYD
-rw-rw----  1 mysql mysql   8668160 Jul 24 21:03 t_hoge.MYI
-rw-rw----  1 mysql mysql      8678 Jan  7  2007 t_hoge.frm

ファイルサイズがちゃんと減っている(^_^)

データベースのロックについて

ISAM/MyISAM および HEAP テーブルのテーブルロック、BDB テーブルのページレベルロック、InnoDB テーブルの行レベルロックのみが、MySQL で現在サポートされています。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: E.4 ロック方法

他の処理も同時に走らせてたけど、それがぜんぜん返ってこないと思ったら……
もしかしてテーブルロックされていたのでは？ (((( ；ﾟДﾟ))))ガクガクブルブル

-MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 5.3.1 MySQL のテーブルロック方法
-MySQLの内部構造 - 2-10 ロック
-鈴木 啓修 著 / MySQL全機能リファレンス