想定ケースとしては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では取れる手が増えていてちょっと感動した。
