MyISAMでテーブルが巨大すぎるとDELETEできなくなる件（MERGEテーブル解説）

mysql

MySQL4.1でMyISAMを使っていて、ふと気づいたら1つのテーブルに4千万件のレコードを挿入してしまいました。
MyISAMで4千万行のテーブルを作るとどうなるかというと、

  • INSERT -> やや重いけどいける
  • UPDATE -> やや重いけどいける
  • TRUNCATE/DROP -> 一瞬
  • DELETE -> 破滅

という感じになることが分かりました。
このテーブルには、挿入日を示すカラムがあって、挿入から時間の経った古いレコードを削除したかったんです。

DELETE FROM large_table WHERE created_at > :expire_date

この large_table が4千万行あるのですが、このDELETE文を実行したら7日間くらい返ってきませんでしたし、その間ずっと disk busy になったりしてサーバの調子が悪くなりました。
created_atにインデックスが張ってあってもなくても破滅します。

MERGEテーブル

解決策をいろいろ調べたのですが、そもそもそんなデカいテーブルを作るなアホが。という雰囲気が伝わってきたので、テーブルを分割することにしました。
MyISAMにはMERGEテーブルという素晴らしい機能があって、これを使うと複数の同じスキーマを持ったテーブルを、ひとつのテーブルに見せかけることができます。

MySQL :: MySQL 4.1 リファレンスマニュアル :: 7.2 MERGE テーブル

前述の通り、テーブルが巨大になっても、DROP は一瞬（0.03sとか）で実行されるので、テーブルを日付ごとに分割しておいて、古くなったテーブルをテーブルごと DROP するという戦略にしました。結果を先にいうと、これが非常に快適に動きました。

MERGEテーブルの作り方

まず、分割されたテーブルを作ります。今回は日付別に作ります。この「スキーマ」は全部一緒である必要があります。

CREATE TABLE small_table_20091204 (スキーマ) ENGINE=MyISAM;
CREATE TABLE small_table_20091205 (スキーマ) ENGINE=MyISAM;
CREATE TABLE small_table_20091206 (スキーマ) ENGINE=MyISAM;

次に、マージテーブルを作ります。このスキーマは、small_tableのスキーマと一緒にする必要があります。

CREATE TABLE merge_table (スキーマ) ENGINE=MERGE
  UNION=(small_table_20091204, small_table_20091205, small_table_20091206);

以上。簡単でしょ？
ここで重要なのは、MERGEテーブルの作成は非常に高速に実行されるということです。なので、カジュアルに作ったり壊したりが可能です。

MERGEテーブルは何ができるか

上記のようにして作ったマージテーブルは、普通のテーブルと同じような感じに、

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • DROP

などができます。
マージテーブルに対してINSERTをした場合は、デフォルトでは、UNIONで指定した一番最後のsmall_tableに対して挿入されます。
どのテーブルに対して挿入するかは、INSERT_METHOD オプションで指定することができます。

CREATE TABLE merge_table (スキーマ) ENGINE=MERGE
  UNION=(small_table_20091204, small_table_20091205, small_table_20091206)
  INSERT_METHOD=LAST;

INSERT_METHODのデフォルトはLAST（最後のテーブルに挿入）です。

MERGEテーブルをDROPすると、MERGEテーブルのみがDROPされます。

MERGEテーブルは何ができないか

できないことがいくつかあるので、注意深く使う必要があります。
ここでは省略するので以下を読んでください。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 7.2.1 MERGE テーブルの問題

罠1：UNION 範囲の変更

今回の例では、「古くなったテーブルを削除」をしようとしています。
たとえば、「small_table_20091204」が古くなった場合、以下の手順を踏む必要があります。

  1. small_table_20091204 を DROP
  2. merge_table の MERGE 範囲を変更

この順番を逆にすると、MERGE テーブルが壊れます。かならずこの順番で行って下さい。

DROP TABLE small_table_20091204;
ALTER TABLE merge_table UNION=(small_table_20091205, small_table_20091206);

このように ALTER で MERGE 範囲を変更できます。この ALTER は非常に高速です。

公式リファレンスには、以下のように書かれています。

"開いている" MERGE テーブルによってマップされているテーブルには、DROP TABLE、ALTER TABLE、WHERE 節なしの DELETE FROM table_name、REPAIR TABLE、TRUNCATE TABLE、OPTIMIZE TABLE、または ANALYZE TABLE を実行できない。これを実行すると、MERGE テーブルが元のテーブルを参照するおそれがあり、予期しない結果を得ることがある。この問題を最も簡単に回避するには、FLUSH TABLES コマンドを発行して "開いている" MERGE テーブルを残さないようにする。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 7.2 MERGE テーブル

これは、今回の例でいう small_table に対する制限です。
UNIONされている small_table を安全に DROP するときは、 FLUSH TABLES をあらかじめ発行する必要がある、と書いてあります。
（ちなみに FLUSH TABLE のためには RELOAD 権限が必要です）
が、実はこの記述は罠で、 FLUSH TABLES をしたからといって、UNIONされている子テーブルを DROP してしまうと、MERGE テーブルが壊れてしまい、SELECT もなにもできなくなります。
もし MERGE テーブルが壊れてしまったら、DROP して CREATE し直してください。

罠2：空のMERGEテーブルの作成（MySQL4.1のみ）

※下記はMySQL5以降では解決されています

もし、small_table_* が3つとも古くなってしまった場合、3つともを DROP し、 merge_table の中身を空にしたいですね。
そのような場合に、以下のような文を発行するとエラーになります。

ALTER TABLE merge_table UNION=();

MySQL4.1ではこのように、UNIONの中身を空にしてALTERやCREATEをすることはできません。このようなことをしたい場合には、

DROP TABLE merge_table;
CREATE TABLE merge_table (スキーマ) ENGINE=MERGE;

とすることで、空のマージテーブルを作ることができます。UNIONを指定していないのがポイントです。UNION=()があると（4.1では）エラーになります。

実はここに罠があって、この空の状態のマージテーブルを mysqldump などでダンプすると、

CREATE TABLE `merge_table` (スキーマ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8  UNION=();

のように UNION=() がついてしまい、これは MySQL4.1では実行できないので、使えないダンプになってしまいます。ひどいバグですね。あ、MySQL5系では空UNIONでも問題なく動きますので心配なく。

まとめ

MySQL4.1なんか古いしバグ多いし使わない方がいいですよ。
5系ではMERGEテーブルの発展形であるパーティショニングが使えますし。