GA

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';
..
どうでしょう。

【2021/05/21 18:31】
MySQL 8.0では取れる手が増えていてちょっと感動した。

2017/11/06

mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4' と言われないためのTIPS

前にいつかどこかでメモした気がしなくもないけれど見つからなかったので。  ⇒ 書き上げてから思い出した、 第33回 MySQLのオプションファイル my.cnfの豆知識[その2]:MySQL道普請便り だ。。

default-character-setmysql コマンドラインクライアントとか mysqldump あたりで使う、クライアントの文字コードを指定するためのオプション。
MySQL 5.0以前ではサーバー側のデフォルトの文字コードを指定するオプションも同じ名前だったけど、現在ではそっちは character-set-server に名前が変わっている。 MySQL 5.0では互換性のためにどちらの名前も使えたけれど、5.1で猶予期間が終わって大量の秘伝のタレを死に追いやったのはもはや懐かしい話。。
で、タイトルのエラーが出るような my.cnf
[client]
default-character-set=utf8mb4
と書いてあるケースがほとんどだと思う( [mysqlbinlog] セクションにわざわざ自分で default-character-set を書いたりしないだろうから)
[client] セクションは割と便利で、コマンドラインクライアントのほとんどがコンフィグファイルからそのセクションを読んでくれる。逆を言うと、 [client] セクションを読んでしまうプログラムが対応していないオプションを [client] セクションに書いてしまうと、タイトルのようなエラーが出ることになる。
どのプログラムがどのセクションを読むかは、ソースコードから load_default_groups でgrepするとぽこぽこ出てきたりする。 [client] セクションを読むやつだけだっと抜粋。
./client/check/mysqlcheck.cc:static const char *load_default_groups[] = { "mysqlcheck", "client", 0 };
./client/dump/program.cc:const char *load_default_groups[]=
{
  "client", /* Read settings how to connect to server. */
  "mysql_dump", /* Read special settings for mysql_dump. */
  0
};

./client/mysql.cc:static const char *load_default_groups[]= { "mysql","client",0 };
./client/mysql_secure_installation.cc:static const char *load_default_groups[]= { "mysql_secure_installation", "mysql", "client", 0 };
./client/mysqladmin.cc:static const char *load_default_groups[]= { "mysqladmin","client",0 };
./client/mysqlbinlog.cc:static const char *load_default_groups[]= { "mysqlbinlog","client",0 };
./client/mysqldump.c:static const char *load_default_groups[]= { "mysqldump","client",0 };
./client/mysqlimport.c:static const char *load_default_groups[]= { "mysqlimport","client",0 };
./client/mysqlshow.c:static const char *load_default_groups[]= { "mysqlshow","client",0 };
./client/mysqlslap.cc:static const char *load_default_groups[]= { "mysqlslap","client",0 };
./client/mysqltest.cc:static const char *load_default_groups[]= { "mysqltest", "client", 0 };
./client/upgrade/program.cc:const char *load_default_groups[]=
{
  "client", /* Read settings how to connect to server */
  "mysql_upgrade", /* Read special settings for mysql_upgrade*/
  0
};

./extra/resolveip.c:/*static char * load_default_groups[]= { "resolveip","client",0 }; */
結構色々出てくる。 ほとんどのものはそう滅多に使わないし、正直これを全部考慮するのは無理ゲーなので、ワーニングを許容するなら loose接頭辞 と組み合わせて設定するのがオススメだ。
[client]
loose-default-character-set=utf8mb4
このように loose 接頭辞をつけておくと、 default-character-set を理解しない mysqlbinlogmysqlslap でもエラーでアボートせずにワーニングだけ出力して勘弁してくれる。
$ mysqlslap
mysqlslap: [Warning] unknown variable 'loose-default-character-set=utf8mb4'
参考までにウチのmy.cnfからクライアント用のセクションだけ引っこ抜いたのはこんな感じだった。
[client]
port= __port__ #<<
socket= __datadir__/mysql.sock #<<
loose-default-character-set= utf8mb4

[mysqldump]
quick
max_allowed_packet= 1G
single-transaction
#lock-all-tables
events
routines
triggers
master-data=2
#dump-slave=2

[mysql]
no-auto-rehash
show-warnings
prompt= "__hostname__ [\d]> " #<<
#safe-updates
syslog

今日のネタ提供は @purple_jwl さんでした。ごちそうさまでした。