テーブル最適化(や大量のDELETE)をほぼ無停止で行う

MySQLだとDELETEがとても重いです。10000件程度なら余裕ですが、100000件、1000000件にもなると手のつけようがないレベルです。その間MyISAMだとテーブルロックがかかるし、DELETEしすぎると今度は断片化してOPTIMIZE TABLEでもしないと速度低下が始まります。

OPTIMIZE TABLEやALTER TABLEはものすごく時間がかかることがあるんですよね。今回DELETEしようとしたテーブルはWebでの表示に必須の部分でして、どうせならWebをできるだけ落とさずにやってみるかという挑戦でした。

今回DELETEするのは27000000件くらいです。無理です。

処理を一度にできないと踏んだので、DELETEを増加量より多いLIMITで走らせればだんだんと減っていくのではないかと考え、実行。

ごり押しでLIMIT指定で絞ったDELETEを走らせた結果9000000件消せました。断片化してDELETEが遅くなった結果DELETEにかかる時間が長くなり、ロックの影響でWebの表示がほとんど出来なくなってたので高速化どころの話ではなかった。

とりあえずLIMITつけてDELETEを定期的に叩いてたプログラムを止めたので一息ついて方法を考える必要が出てきました。

以前バックアップもかねてマスタースレーブ構成にしたので、更新系をストップ後レプリケーション外して、スレーブでDELETEしている間にマスターでの表示をしつつ、DELETE終了後のmysqldumpをマスターで読み込むかとも考えました、素直にDELETEしてOPTIMIZEかけると丸一日かかって終わるんでしょうか。

ここで気がついたのが、消すことに夢中になっていましたがDELETEした後のデータってとっても小さいんですね。3000000件あるかくらいです。じゃあこれだけ引っ張ってくればいいんじゃないか?と思いましたが、そうはいかず。一回テーブルを消すならその中のデータは退避させたいのです。

ならテーブルを消さなければいいじゃないか、で最終的なSQLを思いつきました。

余分なデータのあるテーブルをxxxとします。

まず残すべきデータを上のSQLを叩いて抽出後、適切なインデックスやプライマリキー指定を追加します。CREATE TABLE (SELECT FROM)文はプライマリキー制約すら外れるので注意です。SELECT結果にはプライマリキーなんて関係ないので当たり前なのですが。

ここで必要なデータだけを持ったyyyテーブルが出来上がります。そして

と叩いて完成です。自分の中では、魔法でも使われたみたいにスカッと決まりました。階段がもう一段あると思っていたような感じですが一件落着。

実はこのエントリはSQLいじったしネタあるうちに適当に書いてしまおうと思って書き始めたのですが、苦しめられつつ納得のいくSQLを叩けたのでうれしかったのでしょうか。文字数が1300字となっています。

MySQLを乗りこなすのは骨が折れますが、こういう局面を一人で対処できる力は役に立つと信じて。

カテゴリー: MySQL, プログラミング関連 パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です