2017年11月28日火曜日

MySQLから大量のレコードをちまちま削除するメモ

想定ケースとしては1億行くらいのテーブルから7000万行くらい消すクエリーを、レプリケーションが遅れずバッファプールも食い切らない程度にちまちま消すようにする。
DELETE FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28'
「ちまちま削除する」なので、トランザクションでAll or Nothingを保証したい場合は使えない。 id をプライマリーキー(ただしサロゲートキーかどうかは問わない)、 hoge, last_update が本来消し込みに使いたいカラムだとする。 プライマリーキー(またはユニークキー)がないテーブルのことは考えない。

KEY(hoge, last_update) がある場合

  • ターゲットのプライマリーキーを取り出して DELETE .. WHERE id IN .. の形に落とし込む
  • 行ロックに落とし込める
  • idの型を選ばない(varcharだろうと使える)
  • 自前でINリストを作るのが面倒ならGROUP_CONCATという手もあるけどその場合は group_concat_max_len に注意
  • DELETE の方でもとの条件をANDしておくのを忘れると事故ることがある。。
  • プライマリーキーに対するWHEREがあるので実行計画で暴発しにくい…?
    • けどあんまりINのリストを長くするとテーブルスキャン選びやがった on 5.7
SELECT id FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' ORDER BY id LIMIT 1000;
DELETE FROM t1 WHERE id IN (.., ..) AND hoge = 1 AND last_update < '2017-11-28';

SELECT id FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' ORDER BY id LIMIT 1000;
DELETE FROM t1 WHERE id IN (.., ..) AND hoge = 1 AND last_update < '2017-11-28';

..

(KEY(hoge) のみがある、または削除に使えるキーがない) && idが数値型の場合

  • idのレンジを細かく区切ってループさせる
  • 最終的には全レコードにアクセスしないといけないので一度にやるとバッファプールが荒れる。適度にsleepを入れること
  • ネクストキーロックなので、DELETEしてる範囲(+α)にINSERTやUPDATEが来るとブロックされる
DELETE FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' AND id BETWEEN 1 AND 1000;
DELETE FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' AND id BETWEEN 1001 AND 2000;
DELETE FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' AND id BETWEEN 2001 AND 3000;
..

KEY(hoge) のみがある && idが文字列型の場合

  • idの最小値を取ってORDER BY id LIMITで少しずつ行く
  • 最終的には全レコードにアクセスしないといけないので一度にやるとバッファプールが荒れる。適度にsleepを入れること
  • ネクストキーロックなので、DELETEしてる範囲(+α)にINSERTやUPDATEが来るとブロックされる
  • 実行計画が暴発すると地獄が見えるのでDELETEの方はUSE INDEXした方が良さげ。DELETEでUSE INDEXするには テーブルリファレンス構文 の方を使う
  • 正直この辺まで来るとtsvにでも吐き出させて WHERE id IN (..) の形にした方が良いような気がする
SELECT MIN(id) AS min_id FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' ;
DELETE t1 FROM t1 USE INDEX(PRIMARY) WHERE id >= $min_id AND hoge = 1 AND last_update < '2017-11-28' ORDER BY id LIMIT 1000;

SELECT MIN(id) AS min_id FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28' ;
DELETE t1 FROM t1 USE INDEX(PRIMARY) WHERE id >= $min_id AND hoge = 1 AND last_update < '2017-11-28' ORDER BY id LIMIT 1000;
..

削除に使えるキーがなくてidが文字列型の場合

  • 悔い改めなさい
  • 一度tsvにでも吐き出させて WHERE id IN (..) の形に落とし込む
  • SELECTで1回テーブルスキャン、これでバッファプールが荒れそう。ぶん回しても良いバックアップとかバッチ用とかのMySQLがあればここが無視できるからこのやり方でも安定しそう
SELECT id FROM t1 WHERE hoge = 1 AND last_update < '2017-11-28'  INTO OUTFILE '/tmp/target.txt; /* これが時間かかるはずなのでたっぷり後悔できる */

DELETE FROM t1 WHERE id IN (.., ..) AND hoge = 1 AND last_update < '2017-11-28'; /* お好みの言語でファイルを処理してINリストを作る */
DELETE FROM t1 WHERE id IN (.., ..) AND hoge = 1 AND last_update < '2017-11-28';
..
どうでしょう。

0 件のコメント :

コメントを投稿