MySQL メモ9 OPTIMIZE
大量データを削除するした場合に、MySQLでは削除されたデータ分の容量が空くわけではない。
大量データを削除したテーブル内では断片化(フラグメンテーション)が発生してしまう。
また、データ抽出時にパフォーマンスにも影響が発生する。(Index統計情報が断片化したテーブルをもとに更新されるためらしい)
まずはテスト用のテーブルを作成する。
mysql> CREATE TABLE user_info(user_id int primary key auto_increment, name varchar(256),sex int, mail varchar(256),role_id int); mysql> insert into user_info () values (); mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> insert into user_info (user_id) select 0 from user_info; mysql> update user_info set name = concat('会員', user_id), sex = CEIL(RAND() * 2), mail = CONCAT(SUBSTRING(MD5(RAND()), 1, 10),'@example.com'), role_id = CEIL(RAND() * 10);
対象テーブルに対して、OPTIMIZEとテーブルサイズを確認する。
mysql> OPTIMIZE TABLE user_info; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | test.user_info | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.user_info | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (4.57 sec) テーブルサイズを確認 mysql>SELECT table_name , engine , table_rows AS tbl_rows , avg_row_length AS rlen , floor((data_length + index_length) / 1024 / 1024) AS all_mb , floor((data_length) / 1024 / 1024) AS data_mb , floor((index_length) / 1024 / 1024) AS index_mb FROM information_schema.tables WHERE table_schema = database() ORDER BY (data_length + index_length) DESC; +----------------+--------+----------+------+--------+---------+----------+ | table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb | +----------------+--------+----------+------+--------+---------+----------+ | user_info | InnoDB | 514593 | 170 | 83 | 83 | 0 | +----------------+--------+----------+------+--------+---------+----------+ 4 rows in set (0.00 sec)
data_mb :83 なので83メガバイトのデータ量となっている。
52万件のレコードを削除する。
mysql> select count(*) from user_info where sex=1; count(*) 524172 削除 mysql> delete from user_info where sex = 1; Query OK, 524172 rows affected (1.55 sec)
テーブルサイズを確認
mysql>SELECT table_name , engine , table_rows AS tbl_rows , avg_row_length AS rlen , floor((data_length + index_length) / 1024 / 1024) AS all_mb , floor((data_length) / 1024 / 1024) AS data_mb , floor((index_length) / 1024 / 1024) AS index_mb FROM information_schema.tables WHERE table_schema = database() ORDER BY (data_length + index_length) DESC; +----------------+--------+----------+------+--------+---------+----------+ | table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb | +----------------+--------+----------+------+--------+---------+----------+ | user_info | InnoDB | 514593 | 170 | 83 | 83 | 0 | +----------------+--------+----------+------+--------+---------+----------+ 4 rows in set (0.00 sec)
data_mb :83 なので83メガバイトのデータ量となっている。データ削除前とデータ量が変わっていない。
対象テーブルに対して、OPTIMIZEを実行する。
mysql> OPTIMIZE TABLE user_info; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | test.user_info | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.user_info | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (4.57 sec)
再度テーブルサイズを確認
mysql> SELECT table_name , engine , table_rows AS tbl_rows , avg_row_length AS rlen , floor((data_length + index_length) / 1024 / 1024) AS all_mb , floor((data_length) / 1024 / 1024) AS data_mb , floor((index_length) / 1024 / 1024) AS index_mb FROM information_schema.tables WHERE table_schema = database() ORDER BY (data_length + index_length) DESC; +----------------+--------+----------+------+--------+---------+----------+ | table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb | +----------------+--------+----------+------+--------+---------+----------+ | user_info | InnoDB | 521958 | 85 | 42 | 42 | 0 | +----------------+--------+----------+------+--------+---------+----------+ 4 rows in set (0.00 sec)
data_mb :42 なので42メガバイトのデータ量となっている。
5.6.17以降はOPTIMIZEの実行した場合ALTER TABLEが裏では実行されるとのこと。
OPTIMIZE TABLE によってトリガーされ、ALTER TABLE ... FORCE の下で実行されるテーブル再構築は現在、オンライン DDL (ALGORITHM=INPLACE) を使用して実行され、短期間しかテーブルをロックしないため、並列 DML 操作のためのダウンタイムが短縮されます。
引用:https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html
