続・歯抜けを埋めるSQL（10万件編） - sakaikの日々雑感～(T)編

先日、こんな日記を書きました。
sakaik.hateblo.jp

この中で、

　この方法で、件数が多くなった場合にどれくらいの速度で動きますかね。
10万件かそこらでは意外とすんなり動く気もしていますが、1000万件となるときついかもという気もします。

と書いたのですが、10万件程度ならすぐに試せそうなので、やってみました。

条件と手順

1から10万までの primary key auto_increment な値を持つ列がある
そのうち 1000件ほどを歯抜けにしてみる
前回作った「歯抜けを埋めるSQL」を試してみる

テーブルとデータの準備

テーブル作成

mysql> CREATE TABLE t22 (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), somedata VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)

10万件のデータを作る。最初は100万でやろうとしていたのにゼロ書き間違えて10万になってしまったので、プロシジャ名と一致していないのはご愛敬。

mysql> delimiter //
mysql> CREATE PROCEDURE make100man()
    -> BEGIN
    ->   declare  i int default 0;
    ->   while i<100000 do
    ->     INSERT INTO t22 VALUES (null, "sampledata", "oh!!");
    ->     SET i=i+1;
    ->   end while;
    -> END;
    -> //
ERROR 1304 (42000): PROCEDURE make100man already exists
mysql> delimiter ;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL make100man();
Query OK, 1 row affected (5.19 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.11 sec)

5秒ほどで10万件のデータが作成されました。

ここからランダムにだいたい1000件くらいのデータを削除します。

mysql> delimiter //
mysql> CREATE PROCEDURE del1000ken()
    -> BEGIN
    ->   declare  i int default 0;
    ->   while i<1000 do
    ->     DELETE FROM t22 WHERE id = FLOOR(RAND()*100000);
    ->     SET i=i+1;
    ->   end while;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL del1000ken();
Query OK, 1 row affected (1 min 34.72 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

削除は、やはり時間がかかりますね。1分半ほどかかりました。

一応件数確認。

mysql> SELECT COUNT(*) FROM t22;
+----------+
| COUNT(*) |
+----------+
|    98994 |
+----------+

　あれ。。。ランダムなので重複あった場合、1000件未満の削除しかなされないことは予想していたのですが、消えすぎてる・・・？　1000回しかループ回してないのに1000以上消えてる。。なんかおかしいですが、今回の作業に影響はないので、先に進みます。（いやほんと気持ち悪い。何がおきてるんだ・・）

例のSQLを試す

　ということで、先日の日記の結論となったSQLを流してみます。今回はINSERTはせずに、見つけ出すところまで（SELECTだけ）を実施することにします。

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM t22))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN t22 ON (num.n=t22.id) WHERE t22.id IS NULL;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

　あらら。エラーになってしまいました。 CTE内でrecursive階層に制限があるのでした。一時的にこの値を上限開放して続行することにします。

mysql> SET @@cte_max_recursion_depth=100005;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM t22))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN t22 ON (num.n=t22.id) WHERE t22.id IS NULL;
+-------+------+------+
| id    | NULL | NULL |
+-------+------+------+
|    75 | NULL | NULL |
|    85 | NULL | NULL |
|   168 | NULL | NULL |
|   291 | NULL | NULL |
:
| 99023 | NULL | NULL |
| 99044 | NULL | NULL |
| 99075 | NULL | NULL |
| 99110 | NULL | NULL |
| 99153 | NULL | NULL |
| 99522 | NULL | NULL |
| 99840 | NULL | NULL |
| 99858 | NULL | NULL |
| 99896 | NULL | NULL |
+-------+------+------+
1006 rows in set (0.19 sec)

　0.2秒程度で返ってきました。件数もちょうど歯抜け部分の件数と一致しています。

なお、このMySQLは、MySQL 8.0.30、メモリ系のパラメタはほぼデフォルトのままで動作させているものです。10万件の中の1000件を埋める程度なら、予想通り楽勝ですね。

未練

1000回ループを回して1件ずつDELETEしたのに、1006件が消えている謎は、気持ち悪いので少し考えてみたいと思います。ナゾが解けた方はコメント欄やTwitterやご自身のブログなどで教えてください。
　実はこの動作、2回実施しているのですが、1回目は 99029件になりました。こちらは既に削除済みのIDを再度削除に行くなどが発生したのだと理解していたのですが、見直してみるとこれも、ずいぶんと重複しすぎている感もありますね。（10万の中から数字をひとつ決めるのに、こんなに被らないだろうと）