MySQL PARTITION BY RANGを利用して負荷をかけずに1億レコードを削除する

CREATE TABLE `log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(255) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ( `id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

毎日何百万件というレコードが記録される。こんなlogテーブルがあるとします。
普通にDELETE文を発行して削除すると

mysql> DELETE FROM log WHERE created_at < '2010-11-19 00:00:00';
Query OK, 10000000 rows affected (4 min 24.70 sec)

と、このように4分20秒も時間がかかるだけでなく、最大26.8MBものI/Oが発生しました。
この規模のテーブルに対して稼働中にDELE文発行は負荷が高すぎます。
負荷をかけずにデータを削除する効率的な方法をご紹介します。
それは、MySQLのPARTITION機能を利用することです。

CREATE TABLE `log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` varchar(255) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ( `id`, `created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

先ほどのテーブルをこんな感じに構造を変更します。
さらにこのテーブルに対してPARTITIONを設定します。

ALTER TABLE log PARTITION BY RANGE ( to_days(created_at))
(
PARTITION p20101106 VALUES LESS THAN ( TO_DAYS('2010-11-07') )
 COMMENT = '2010-11-06のデータ LESS THAN 2010-11-07',
PARTITION p20101107 VALUES LESS THAN ( TO_DAYS('2010-11-08') )
 COMMENT = '2010-11-07のデータ LESS THAN 2010-11-08',
PARTITION p20101108 VALUES LESS THAN ( TO_DAYS('2010-11-09') )
 COMMENT = '2010-11-08のデータ LESS THAN 2010-11-09',
PARTITION p20101109 VALUES LESS THAN ( TO_DAYS('2010-11-10') )
 COMMENT = '2010-11-09のデータ LESS THAN 2010-11-10'
ENGINE = InnoDB
);

こうすることで一つのテーブルの中で日付別にPARTITIONが切られます。

 EXPLAIN PARTITIONS SELECT * from log where created_at = '2010-11-09 01:00:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | log   | p20101109  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

こんな感じにEXPLAINするとSQLで該当するPARTITIONはどれが使われているのかがわかります。
こうすることによりDROP PARTITIONが発行して、レコードを削除することが出来ます。

mysql> ALTER TABLE log DROP PARTITION p20101109;
Query OK, 0 rows affected (1.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

先ほどの1000万件あるデータも1.24秒で消せて、I/Oも560KBと、限りなく低負荷になりました。
注意点としては、PARTITIONを永遠と日付別で設定してあげる必要があります。

ALTER TABLE log ADD PARTITION (
 PARTITION p20101110 VALUES LESS THAN ( TO_DAYS('2010-11-11') )
 COMMENT = '2010-11-10のデータ LESS THAN 2010-11-11' ENGINE = InnoDB
);

こんな感じのSQLを翌日文のPARTITIONを作成する形で、cronなどで発行してあげましょう。
11/10 ALTER TABLEのIOはピークで560KB処理時間は1.14秒でした。