受取期限の過ぎたデータをMySQL上から削除する話

2022/01/11

sejima

こんにちわ。せじまです。今回は地味で泥臭い話をします。ただ、割と平易な内容かと思いますので、初学者の方にもオススメです。

はじめに

ゲームでは、受取期限のついたログインボーナス的なものがよくあります。ユーザが期限までに受け取らないと、ユーザからそのデータは不可視になりますが、必ずしも、不可視になった瞬間にデータベースから直ちに削除される、というわけでもありません。バッチジョブか何かで、ガベージコレクションのように削除するケースが多いのではないでしょうか。

また、論理削除という概念もあります。論理削除についてはいろいろ意見や考え方があるかと思いますので、ここでそれについては論じませんが、「削除フラグが立ってユーザから不可視になった後、三ヶ月以上経過したデータを削除したい」みたいなことは、ゲームに限らず、しばしばあるんじゃないかなと思います。

こういった、ユーザから不可視になってしばらく経過したデータをデータベースに蓄積し続けると、ストレージの使用量が増えていったり、MySQL的にはRows_examinedが増えたり、 buffer pool にテーブルが収まらなくなったりと、様々なデメリットが発生したりもします。そのため、古い消せるデータは削除した方が良いですよね、といった意で、次のスライドでも書かせていただきました。

InnoDBのすゝめ（仮） | PPT | Free Download

InnoDBのすゝめ（仮） - Download as a PDF or view online for free

SlideShare

ではこういったとき、どのようなことを考えながら消して行けばよいのか、メンテナンスを入れず、ユーザにも影響を与えないようにしながら削除していくにはどうすればよいのか、社内向けにドキュメントを書こうかと思ったのですが、社内に留まらず社外に向けて公開しても良いんじゃないかなと思ったので、blogに書くことにしました。

基本的にはMySQLやAurora MySQLなどを前提に書きますが、他のRDBMSなどでも、参考になるかもしれません。

テーブル定義

次のようなテーブルがあるとします。

mysql> show create table sejima\G
*************************** 1. row ***************************
       Table: sejima
Create Table: CREATE TABLE `sejima` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=458728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select min(id),max(id),count(*) from sejima;
+---------+---------+----------+
| min(id) | max(id) | count(*) |
+---------+---------+----------+
|       1 |  409578 |   294912 |
+---------+---------+----------+
1 row in set (0.01 sec)

mysql>

mysql> show create table sejima\G

*************************** 1. row ***************************

Table: sejima

Create Table: CREATE TABLE `sejima` (

`id` bigint NOT NULL AUTO_INCREMENT,

`value` varchar(255) DEFAULT NULL,

`created_at` datetime NOT NULL,

`updated_at` timestamp NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=458728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql> select min(id),max(id),count(*) from sejima;

+---------+---------+----------+

| min(id) | max(id) | count(*) |

+---------+---------+----------+

| 1 | 409578 | 294912 |

+---------+---------+----------+

1 row in set (0.01 sec)

mysql>

あるいは、次のようなテーブルがあるとします。

mysql> show create table sejima2\G
*************************** 1. row ***************************
       Table: sejima2
Create Table: CREATE TABLE `sejima2` (
  `user_id` bigint NOT NULL,
  `item_id` int NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`user_id`,`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select min(user_id),max(user_id),count(*) from sejima2;
+--------------+--------------+----------+
| min(user_id) | max(user_id) | count(*) |
+--------------+--------------+----------+
|            1 |       409578 |  1179648 |
+--------------+--------------+----------+
1 row in set (0.04 sec)

mysql>

mysql> show create table sejima2\G

*************************** 1. row ***************************

Table: sejima2

Create Table: CREATE TABLE `sejima2` (

`user_id` bigint NOT NULL,

`item_id` int NOT NULL,

`value` varchar(255) DEFAULT NULL,

`created_at` datetime NOT NULL,

`updated_at` timestamp NOT NULL,

PRIMARY KEY (`user_id`,`item_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

mysql> select min(user_id),max(user_id),count(*) from sejima2;

+--------------+--------------+----------+

| min(user_id) | max(user_id) | count(*) |

+--------------+--------------+----------+

| 1 | 409578 | 1179648 |

+--------------+--------------+----------+

1 row in set (0.04 sec)

mysql>

これらのテーブルで、 updated_at がある程度古いものを消していくケースを考えてみます。

先ずはまとめ

長々と読むのは手間でしょうから、最初にまとめを書いておきます。

PRIMARY KEY が AUTO_INCREMENT の場合

SELECT MIN(id), MAX(id) FROM sejima; でidの下限と上限を調べる。
${min_id}=MIN(id), ${max_id}=MAX(id), ${delta}=MAX(id)-MIN(id); とする
次のようなSELECTをreplicaに対して投げる。ユーザから参照されないバッチジョブ用replicaがあればなお良い
- SELECT id FROM sejima WHERE id BETWEEN ${min_id} AND ${min_id}+${delta}/10 AND updated_at < ? LIMIT ?;
  - 要するに、full table scan にならないよう、ざっくり 10%以下の行までしか探索しないようなヒントとして BETWEEN で指定する。LIMIT で制限する値は、例えば1000くらい
SELECTで取得されたidを使ってsourceにDELETEを投げる
- DELETE FROM sejima WHERE id IN (...,) AND updated_at < ?;
  - 更新されてないデータだけ確実に消したいのであれば、updated_at < ? も指定してかまわない
  - 一度に大量のデータを消すとロックの競合などが問題になる可能性もあるので、できればピークタイムをさけて削除した方が良い。また、削除時の負荷については、 IN (...,) で渡す id の量で調整する。
1～3秒程度 sleep する。（まとまったDELETEを休みなく source に投げ続けるのは重いので。）
3.のSELECTで id が返って来なくなるまで、 SELECTとDELETE&sleepを繰り返す。SELECTでidが返って来なくなったら、BETWEEN で指定する範囲をずらして再度 id を取得する
- 例：
  - SELECT id FROM sejima WHERE id BETWEEN ${min_id}+${delta}/10)+1 AND ${min_id}+(${delta}/10)*2 AND updated_at < ? LIMIT ?; など
  - SELECT id FROM sejima WHERE id BETWEEN ${min_id}+(${delta}/10)*9)+1 AND ${max_id} AND updated_at < ? LIMIT ?; まで繰り返す

PRIMARY KEY が複合キーの場合

SELECT MIN(user_id), MAX(user_id) FROM sejima2; で user_id（インデックスの左端のプリフィクス）の下限と上限を調べる。
- ${min_id}=MIN(user_id), ${max_id}=MAX(user_id), ${delta}=MAX(user_id)-MIN(user_id); とする
次のようなSELECTをreplicaに対して投げる。ユーザから参照されないバッチジョブ用replicaがあればなお良い
- SELECT user_id, item_id FROM sejima2 WHERE user_id BETWEEN ${min_id} AND ${min_id}+${delta}/10 AND updated_at < ? LIMIT ?;
  - 行コンストラクタ使ったときインデックス使えなかったとしても、SELECTして調べておけば、削除対象となる user_id, item_id の組み合わせをログなどに残すことができる。
DELETE&sleepする
- 行コンストラクタ使ってもインデックス使える場合
  - DELETE FROM sejima2 WHERE (user_id,item_id) IN ((?,?),(?,?),(?,?),(?,?)...,) AND updated_at < ?;
- 行コンストラクタ使ったときインデックス使えない場合（ IN で渡す user_id の個数とLIMITで負荷をコントロールする）
  - DELETE FROM sejima2 WHERE user_id IN (...,) AND updated_at < ? LIMIT ?;
    - LIMITつけて消す場合は、mysql_affected_rows() 相当のAPIで実際にDELETEされてるか確認する
  - あるいは、 binary log に更新履歴を細かく残したいなどあれば、次のようなDELETEも考えられる
    - DELETE FROM sejima2 WHERE user_id = ? AND item_id IN (...,) AND updated_at < ? LIMIT ?;
    - これは複数の user_id をINで指定するときより、DELETEの発行回数が増えるだろうから、テーブル内のデータの傾向やDELETEに要するトータルの時間など考慮して検討すれば良い
PRIMARY KEY が AUTO_INCREMENT の場合のときと同じように、SELECTとDELETE&sleepを繰り返す

かつてMySQLでは、行コンストラクタを使ったときにインデックスを適切に使えないケースがありました。

参考： Performance impact of row constructors is not properly documented

8.0.27で試すと、インデックス使えてるように見えますが、実際に使えてるかどうかは、MySQLのバージョンに依存する（MySQLのバージョンがあんまり古いとインデックス使えない）と考えられます。

mysql> explain select user_id,item_id from sejima2 where (user_id,item_id) IN ((1411,1412),(1413,1414),(1421,1422));
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | sejima2 | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select user_id,item_id,updated_at from sejima2 where (user_id,item_id) IN ((1411,1412),(1413,1414),(1421,1422));
+---------+---------+---------------------+
| user_id | item_id | updated_at          |
+---------+---------+---------------------+
|    1411 |    1412 | 2021-12-29 11:39:28 |
|    1413 |    1414 | 2021-12-29 11:39:28 |
|    1421 |    1422 | 2021-12-29 11:39:28 |
+---------+---------+---------------------+
3 rows in set (0.00 sec)

mysql>

mysql> explain select user_id,item_id from sejima2 where (user_id,item_id) IN ((1411,1412),(1413,1414),(1421,1422));

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> select user_id,item_id,updated_at from sejima2 where (user_id,item_id) IN ((1411,1412),(1413,1414),(1421,1422));

+---------+---------+---------------------+

| user_id | item_id | updated_at |

+---------+---------+---------------------+

| 1411 | 1412 | 2021-12-29 11:39:28 |

| 1413 | 1414 | 2021-12-29 11:39:28 |

| 1421 | 1422 | 2021-12-29 11:39:28 |

+---------+---------+---------------------+

3 rows in set (0.00 sec)

mysql>

行コンストラクタ使う場合は、お手元の環境で explain して見てください。
行コンストラクタ使ってPRIMARY KEY指定してDELETEできるのが望ましいですが、それができない場合は、 WHERE user_id IN (...,) AND updated_at < ? LIMIT ?; のように、user_id を絞りつつ LIMIT で負荷をコントロールするのが良いでしょう。
また、MySQLの行コンストラクタ関連で、過去にいくつかバグレポートが上がっていたように見受けられますので、 INで行コンストラクタ使ってDELETEするならば、AND updated_at < ? というような指定をしておくのは、より安全というか無難で悪くない気もします。

重要なポイントとしては

replica 側で削除対象となるPRIMARY KEYの値を収集する
- セカンダリインデックスを使ったり、BETWEENやLIMITをつけて絞り込んで、ある程度負荷を下げるようコントロールする
sourceにDELETEを投げるときは、PRIMARY KEY指定で投げる

といったところです。

ロックの競合を減らすために、DELETEはセカンダリインデックスではなく、PRIMARY KEY使って実行しましょう。

では、補足していきます。

対象とするテーブルを絞り込む

消せるデータはどんどん消していった方が良いと思いますが、そうはいっても、いままで消していなかったものを消すようにするのは、それなりに作業を要するかもしれません。よって、削除するためのバッチジョブを仕込むのは、費用対効果の大きいところだけにしたいと考えるのが自然です。

MySQLの面倒を見ている人は、 slow query log などはしばしば見ていると思いますが、一つの目安として

Rows_sent と Rows_examined の差分が大きい slow query は、最適化の余地が大きい

というのを、大前提にすれば良いかなと思います。

インデックスがうまく使えていない場合は、USE INDEX や FORCE INDEX、 IGNORE INDEX など、インデックスヒントを見直したり、インデックスを追加するなどしても良いかもしれません。ただ、 WHERE 句で created_at や ctime など、古いデータを参照しないような条件が指定されていたり、削除済みかどうか、フラグ管理されているようであれば

それ、そもそも消せば良いんじゃね？

ということで、バッチジョブで古いデータを削除することを検討しても良いかと思います。

replica から id を取得する

さいきんのMySQL的な言い方だと source/replica（かつての言い方だと master/slave、Aurora MySQL だと writer/reader ですが）、更新処理を行っている source に対して重いSELECTを投げるのは得策ではありません。バッチジョブからの重いSELECTは、せめてreplicaに投げましょう。

前述したスライドであったように、サービスから参照されないreplicaがあれば、そこにSELECTを投げれば良いんですが、そうもいかないケースもあるかと思います。そういうときは、できる範囲でSELECTが軽くなるよう配慮するのが良いかと思います。

まず、更新日時が一定以上古いレコードに対してSELECTを投げるのであれば、例えば次のようになるでしょうが

mysql> explain select id from sejima where updated_at < '2021-12-29 11:39';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | sejima | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 294712 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select id from sejima where updated_at < '2021-12-29 11:39';
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
9 rows in set (0.10 sec)

mysql>

mysql> explain select id from sejima where updated_at < '2021-12-29 11:39';

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select id from sejima where updated_at < '2021-12-29 11:39';

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

+----+

9 rows in set (0.10 sec)

mysql>

これは二重の意味で望ましくありません。

updated_at はインデックスはられてないので full table scan になる
WHERE句で指定されている条件次第で、ResultSetが巨大になる可能性がある。巨大なResultSetが返ってくると、バッチジョブを実行するサーバでバッチジョブが Out Of Memory になるリスクが有る
- 一つ一つのResultSetがそこまで大きくなかったとしても、バッチのプロセスを複数起動していたり、ResultSetからPHPなどでオブジェクトの配列を生成したとき、メモリ使用量が大きく増加するケースもある。

今回のサンプルはたかだか294712件と小さいですが、これが数億～数十億件以上といった巨大なテーブルになると、こういったSELECTは問題になる可能性が高いです。

先ずはせめて LIMIT をつけることを検討します。 LIMIT を使うことで以下の最適化が期待できます。

MySQL 8.0 リファレンスマニュアル | 8.2.1.19 LIMIT クエリーの最適化

MySQL は必要な数の行をクライアントに送信するとただちに、SQL_CALC_FOUND_ROWS が使用されていないかぎり、クエリーを中止します。

mysql> select id from sejima where updated_at < '2021-12-29 11:39' limit 1000;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
+----+
9 rows in set (0.11 sec)

mysql> select id from sejima where updated_at < '2021-12-29 11:39' limit 1000;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

+----+

9 rows in set (0.11 sec)

例えば、

対象のテーブルが適切に sharding されていて、一つ一つのテーブルが充分に小さい
ユーザから参照されないバッチジョブ用replicaにSELECTを投げる

のであれば、LIMITつけるだけで充分に軽くできるかもしれません。ただ、 sharding されていなかったり、バッチジョブ専用のreplicaが存在しないのであれば、もうちょっと軽くしたいところでしょう。

更新頻度が低いテーブルなら、 updated_at にインデックスをはっても良いかもしれませんが、もし、 updated_at が頻繁に更新されるテーブルであれば、インデックスをはるのは躊躇われるかもしれません。

その場合、 WHERE id BETWEEN ? AND ? で更に絞り込みます。

mysql> explain select id from sejima where id between 1 and 10000 and updated_at < '2021-12-29 11:39' limit 1000;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | sejima | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 12182 |    33.33 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

mysql> explain select id from sejima where id between 1 and 10000 and updated_at < '2021-12-29 11:39' limit 1000;

+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+

+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+

+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql>

where id between 1 and 10000 が指定されていなければ、 updated_at < '2021-12-29 11:39' を満たす行が1000件見つかるまで、テーブルがスキャンされ続けます。もし1000件見つからなければ、 full table scan になってしまいます。

しかし、 where id between 1 and 10000 が指定されていることにより、idが10000以下のデータで updated_at < '2021-12-29 11:39' を満たすものが1000件なかったとしても、そこでスキャンは打ち切られます。

PRIMARY KEY の上限と下限を、次のようなSELECTで求めると書きましたが

SELECT MIN(id), MAX(id) FROM sejima;

1 2	SELECT MIN(id), MAX(id) FROM sejima;

MySQLだとこれは最適化されます。

mysql> explain select min(id),max(id) from sejima;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain analyze format=tree select min(id),max(id) from sejima\G
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

1 row in set (0.00 sec)

mysql>

mysql> explain select min(id),max(id) from sejima;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> explain analyze format=tree select min(id),max(id) from sejima\G

*************************** 1. row ***************************

EXPLAIN: -> Rows fetched before execution (cost=0.00..0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=1)

1 row in set (0.00 sec)

mysql>

MySQL以外のRDBMSでも、PRIMARY KEYがB+Treeとかであれば、MINやMAXを求めるのは高速に実行できるかと思いますが、もしMySQL以外でやる場合は、お手元の環境で試してもらうのが良いのではないかと思います。

もし、 PRIMARY KEY が、UUIDなどこういった用途に向いてないものであるならば、 created_at ないし ctime にインデックスをはれないか、検討するのも良いかもしれません。初回にINSERTされたときのみ更新されるカラムは、セカンダリインデックスをはるのには悪くない対象だと思います。その場合、 SELECT id FROM table_name WHERE ctime BETWEEN ? AND ? LIMIT 1000; などのように ctime の範囲を制限することで、スキャンを高速に行うことができるでしょう。

前述した例では、 MAX(id) - MIN(id) の差分を求め、10%程度の id が該当するように BETWEEN で指定しましたが、場合によっては、10%未満、もっと少なくても構いません。 LIMIT 1000 と指定するのであれば、 LIMIT に対して充分な範囲の BETWEEN であれば、より軽いSELECTになる可能性があります（MySQL的に考えると、 Innodb_rows_read だけでなく、ロックの競合を減らせる可能性があるかと思います）。

sleepの時間と削除したい行数について

こういったガベージコレクション的なことを考えるのであれば、一件一件DELETEしてたら削除が追いつかないでしょうから、50～1000件くらいはまとめてDELETEしたいところでしょう。まとめてDELETEすると、ユーザからの更新処理とロックが競合しやすくなるでしょうから、sleepしつつDELETEした方が良いでしょう。
sleepしつつDELETEしても負荷が高いようなら、ピークタイムをさけてバッチジョブを実行した方が良いでしょう。仮に、夜中の3時から5時までの二時間、1000件DELETEして3秒sleepというのを繰り返したとします。

1000*((60*60*2)/3) = 2,400,000

2時間で最大240万件削除できます。これを一年間実行した場合

1000*((60*60*2)/3)*365 = 876,000,000

最大8億7600万件削除できます。MySQL の符号付きINTの最大値は2147483647なので、PRIMARY KEYが

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

であれば、一年で最大約4割くらいのデータを消せるので、充分な速度ではないでしょうか。

一年間で8億件のペースで削除しても追いつかない場合、例えば

PRIMARY KEY が BIGINT AUTO_INCREMENT で、もっと激しくINSERTされている
PRIMARY KEY は INT だけど、テーブルのサイズの増大の速度が激しい

という場合や

かなり負荷の高いデータベースなので、一度に1000件も消せない、あるいは、 sleep が3秒程度では足りない

といった場合が考えられますが、そういった場合は

素直に sharding したら良いのではないでしょうか？

sharding することで、ガベージコレクションのバッチジョブを、分割したクラスタごとに同時に実行できますし、それぞれの source に対して一度に1000件のDELETEを投げなくても、充分な速度で消せると思われます。また、そこまで高負荷なデータベースであれば、負荷対策の観点からも、shardingするメリットは大きいでしょう。

こういったガベージコレクション的なことを考える場合は

削除する対象の件数はどの程度か
どの程度の速度で消せばよいのか

といったことを、PRIMARY KEY の最大値などから考えれば良いのではないかなと思います。

おわりに

だいぶ地味な話かなと思いますが、一つのゲームやサービスを5～10年と継続していくと、あまり軽視できないことでもあるかなと思い、さっくり書いてみました。

今回は割とゆるふわな話だったかなと思います。ただ、こういったこともドキュメントとして残していった方が有益かもなぁと思ったので、今後もたまにはこういう話を書いていくつもりです。