浅く広くをモットーに - WEBプログラマ メモ

■[MySQL][チューニング]テーブルの最適化（OPTIMIZE）って何？

前回、charとvarcharについて解説したついでに、OPTIMIZEについても解説したいと思います。

テーブルの最適化っていうけど、最適化って何？って人、意外と多いのではないでしょうか。今回はデータファイルのフラグメンテーションに焦点を絞ります。テスト環境はMySQL4.1.16のMyISAM。

■DELETE
mysql> create table test (id int primary key, name varchar(15));
mysql> insert into test values (1, 1);
Query OK, 1 row affected (0.00 sec)

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00

varcharカラムを含むDynamic Tableを用意し、1レコード挿入しました。

mysql> delete from test where id = 1;

# od -tx1 test.MYD
0000000    00  00  00  14  ff  ff  ff  ff  ff  ff  ff  ff  ff  ff  ff  ff
0000020    ff  ff  ff  ff

続いてWHERE句をつけた削除を実行しました。データ領域はなくなるのではなく、DELETEフラグが立った形で残っていることが確認できます（何バイト目がDELETEフラグか等の説明は割愛）。では本題のOPTIMIZEを実施。

mysql> optimize table test;

# od -tx1 test.MYD
#

OPTIMIZEを実行した時点で初めて、データファイルが空っぽになっていることが確認できます。つまり、OPTIMIZEが実行されるまでは削除領域が無駄になっているということです。この無駄（フラグメンテーション）を解消することがデフラグメンテーション、所謂デフラグというヤツです。

ただし削除領域はINSERTによって再利用されますので「大量DELETE ＆ INSERTなしで長期間放置」のようなことがない限り、無駄になることはありません。

mysql> insert into test values (1, 1);

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00

削除領域が再利用されたことが確認できます。

続いて更新。

■UPDATE

mysql> create table test (id int primary key, name varchar(15));
mysql> insert into test values (1, 1);

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00

mysql> insert into test values (2, 123456789012345);

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00  03  00  16  02  02  fe  02  00  00  00  0f  31
0000040    32  33  34  35  36  37  38  39  30  31  32  33  34  35  00  00

mysql> update test set name = 1 where id = 2;

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00  03  00  08  10  02  fe  02  00  00  00  01  31
0000040    00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00

mysql> optimize table test;

# od -tx1 test.MYD
0000000    03  00  08  08  02  fe  01  00  00  00  01  31  00  00  00  00
0000020    00  00  00  00  03  00  08  10  02  fe  02  00  00  00  01  31
0000040    00  00  00  00  00  00  00  00  00  00  00  00  00  00  00  00

このOPTIMIZEでは変化なし。続いてデータ領域が拡張されるようなSQLを実行。

mysql> update test set name = '123456789012345';

# od -tx1 test.MYD
0000000    05  00  16  00  07  00  00  00  00  00  00  00  30  02  fe  01
0000020    00  00  00  0f  03  00  16  02  02  fe  02  00  00  00  0f  31
0000040    32  33  34  35  36  37  38  39  30  31  32  33  34  35  00  00
0000060    09  00  0f  01  31  32  33  34  35  36  37  38  39  30  31  32
0000100    33  34  35  00

mysql> optimize table test;

# od -tx1 test.MYD
0000000    03  00  16  02  02  fe  01  00  00  00  0f  31  32  33  34  35
0000020    36  37  38  39  30  31  32  33  34  35  00  00  03  00  16  02
0000040    02  fe  02  00  00  00  0f  31  32  33  34  35  36  37  38  39
0000060    30  31  32  33  34  35  00  00

今度は、OPTIMIZEの実行後、データ領域が変更されていることが確認できます。

以上、イメージし易くする為にあえて単純な形にして、フラグメンテーションについてみてみました。実際にはMyISAMのデータ管理はもっと複雑でしょうから、こんな単純な話にはなり得ない点、ご注意ください。例えば、1つ疑問点を挙げるとすると、これ。

http://dev.mysql.com/doc/refman/4.1/ja/optimize-table.html

OPTIMIZE TABLE  は、テーブルの大部分を削除したり、可変長レコードを持つテーブル（VARCHAR  カラム、
BLOB カラム、または TEXT  カラムを持つテーブル）に多くの変更を加えた場合に使用します。 

上記例はStaticテーブルに対しても同様の結果となりました。同様というのはOPTIMIZE発行後データ領域が変化する、ということです。なのにどうしてOPTIMIZEすることなく無駄な領域が発生し得ないんでしょうね。ここから先はMyISAMのデータ管理の深部といったところでしょうか。

時間もないので今回はここまで ＾＾；