続続・歯抜けを埋めるSQL（WHERE句のRAND()関数に気をつけろ！編）

mysql

ひとつ前の日記で、こんなことを書きました。

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


具体的に発生している事象の詳細は、ひとつ前の日記をご覧ください。

さっそくおしえていただきました！

@tmtmsさんの以下のコメントから始まるスレッドを追ってもらえれば、全部書いてありますが、この日記でも少し整理しておきたいと思います。



こたえはコレ

あ、RANDってクエリの先頭で固定化されるわけじゃなく、1行につき1回ずつRANDを計算するのでそれ有り得ますね…

SQLむずい…
https://dev.mysql.com/doc/refman/8.0/ja/mathematical-functions.html#function_rand
> WHERE 句内の RAND() は、行ごと (テーブルから選択する場合) または行の組合せごと (複数テーブル結合から選択する場合) に評価されます。 したがって、オプティマイザのために、RAND() は定数値ではなく、インデックスの最適化に使用できません。

整理＆検証

　要するに今回 ひとつのSQL文を実行したわけですが、WHERE句に書かれたRAND()関数は、評価のたびに実施されます。　今回はWHERE句に他の絞り込み条件がないので、10万回のRAND()関数が内部では走ったということです。　
　（そんなの、SQL文を書いている時に想像もできないよね・・・）


　試しに WHERE句ではなく SELECT句にRAND()関数を書いたときは以下のようになります。この例では 14-14の行だけが「マッチ」したことになります。場合によってはひとつもマッチしないこともあるし、３つも４つもマッチすることもある、という事象も納得できますね。

mysql> SELECT id, FLOOR(RAND()*15) FROM t22 WHERE id<=15;
+----+------------------+
| id | FLOOR(RAND()*15) |
+----+------------------+
|  1 |                2 |
|  2 |                0 |
|  3 |               10 |
|  4 |                3 |
|  5 |                1 |
|  6 |               11 |
|  7 |                8 |
|  8 |                9 |
|  9 |                6 |
| 10 |                5 |
| 11 |                6 |
| 12 |                0 |
| 13 |               11 |
| 14 |               14 |
| 15 |                6 |
+----+------------------+

例のSQL文の改善案

　プロシジャ内に記述されたループの中での実行なので、
DELETEを行うSQL文内でRAND()関数を使用するのではなく、その前の行あたりで、

SET @rid=FLOOR(RAND()*100000);

して、WHERE句には、この @rid を使用するようにすれば良さそうです。

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


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

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

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

mysql> SELECT COUNT(*) FROM t22;
+----------+
| COUNT(*) |
+----------+
|    99007 |
+----------+
1 row in set (0.00 sec)

　1分半かかっていたDELETEのプロシジャが、一瞬で終わるようになりました。遅いのは削除処理ではなく、ループ内で毎回10万回のRAND()を評価している部分だったんですね。

お礼

　このRAND()関数のように、クエリ内で固定されない関数には要注意ですね。@tmtms さん、@yoku0825 さん、ありがとうございました！！！スッキリしました！　



おまけ

　ひとつのSQL文実行の中で固定化されない例としては、SYSDATE()があります。

+---------------------+---------------------+----------+---------------------+---------------------+
| SYSDATE()           | NOW()               | SLEEP(3) | SYSDATE()           | NOW()               |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2022-08-13 00:15:08 | 2022-08-13 00:15:08 |        0 | 2022-08-13 00:15:11 | 2022-08-13 00:15:08 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

Dolphins image by DALL-E