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';
..

どうでしょう。

【2021/05/21 18:31】

MySQL 8.0では取れる手が増えていてちょっと感動した。

日々の覚書: オプティマイザヒントでDELETEステートメントに使わせるインデックスを強制する(MySQL 8.0から)

​