ひとつ前の日記で、こんなことを書きました。
1000回ループを回して1件ずつDELETEしたのに、1006件が消えている謎は、気持ち悪いので少し考えてみたいと思います。ナゾが解けた方はコメント欄やTwitterやご自身のブログなどで教えてください。
実はこの動作、2回実施しているのですが、1回目は 99029件になりました。こちらは既に削除済みのIDを再度削除に行くなどが発生したのだと理解していたのですが、見直してみるとこれも、ずいぶんと重複しすぎている感もありますね。(10万の中から数字をひとつ決めるのに、こんなに被らないだろうと)
具体的に発生している事象の詳細は、ひとつ前の日記をご覧ください。
さっそくおしえていただきました!
@tmtmsさんの以下のコメントから始まるスレッドを追ってもらえれば、全部書いてありますが、この日記でも少し整理しておきたいと思います。
試しに手で DELETE を叩いてみたら、ときどき 2行消してるやつがいますね… 🤔 pic.twitter.com/t2dWfpSVq2
— とみたまさひろ🍣🍺 (@tmtms) 2022年8月12日
こたえはコレ
あ、RANDってクエリの先頭で固定化されるわけじゃなく、1行につき1回ずつRANDを計算するのでそれ有り得ますね…
— yoku0825 (@yoku0825) 2022年8月12日
あ、RANDってクエリの先頭で固定化されるわけじゃなく、1行につき1回ずつRANDを計算するのでそれ有り得ますね…
SQLむずい…https://t.co/h7MFGGvpeT
— とみたまさひろ🍣🍺 (@tmtms) 2022年8月12日
> WHERE 句内の RAND() は、行ごと (テーブルから選択する場合) または行の組合せごと (複数テーブル結合から選択する場合) に評価されます。 したがって、オプティマイザのために、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の行だけが「マッチ」したことになります。場合によってはひとつもマッチしないこともあるし、3つも4つもマッチすることもある、という事象も納得できますね。
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)