( ´-｀).oO(そういえばこれも ORDER BY FIELD と一緒で構文だと思っていた人がいたな。。
これもまあRAND()関数を使ってるだけなので、select_listに放り込めば何やってるかわかりやすい。
mysql56> SELECT num, val, RAND() AS rand_val FROM t1 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 94164 | e8d2546088e6be7ff164964c7a07bdb3 | 0.000012977980089353379 | | 4354 | 46d0671dd4117ea366031f87f3aa0093 | 0.00001926440747386255 | | 11573 | 2d6304a207cd9469f776e651e81ed7f8 | 0.000023321248612665803 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.09 sec)
余談だけど、RAND()関数は引数にシード値を取れる（引数を取らない場合はテキトーにシード値が設定される）ので、ランダムっぽいけど再現可能な並び順を作り出すこともできる。
mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 | | 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 | | 34076 | 6dcfff2b73388f6307994658463a9341 | 0.00004350300882337895 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.08 sec) mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 | | 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 | | 34076 | 6dcfff2b73388f6307994658463a9341 | 0.00004350300882337895 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.06 sec)
なおこの場合、「そのシードで何番目に生成された乱数か」がキモになるので、WHERE句評価後 *何番目に* その行がフェッチされるかでrand_valの値は変わる。WHERE句を評価した後に、取り出したレコードの順番にRAND()を計算してソートするからだ。
mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num > 30000 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 78089 | 67ebeaa4f6391a89d2b629860fff2c9d | 0.000006978400058092922 | | 64076 | 6a3f8e5443504151a7306f2a13fae303 | 0.00004350300882337895 | | 30140 | 8befb4efe8ce6cdf0e1a84974d452a9f | 0.00004702340815870409 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.06 sec) mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num < 80000 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 | | 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 | | 34076 | 6dcfff2b73388f6307994658463a9341 | 0.00004350300882337895 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.10 sec)
だから本当に再現可能なランダムっぽい何かをしたい場合、（あるなら）サロゲートキーを渡してゴニョる方がいい気がする。これなら常に「そのシードで1番目の乱数」を使うことになるので、「結果セットの先頭から何番目にあるか」が変わってもRAND()関数の戻す値は変わらない（はず） 並び順をズラす場合、サロゲートキーに何かしら足してやったりかけてやったりすればいい。
mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 | | 51034 | 861c3ad6224d443664f925552d2255a1 | 0.00001504737885207625 | | 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.06 sec) mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num < 90000 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 | | 51034 | 861c3ad6224d443664f925552d2255a1 | 0.00001504737885207625 | | 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.06 sec) mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 | | 51034 | 861c3ad6224d443664f925552d2255a1 | 0.00001504737885207625 | | 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.08 sec) mysql56> SELECT num, val, RAND(num * 2) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3; +-------+----------------------------------+-------------------------+ | num | val | rand_val | +-------+----------------------------------+-------------------------+ | 25517 | 362f278d9150aaf7894f586b5682de06 | 0.00001504737885207625 | | 43266 | faeddbfcef4331221e71ed4186e0c65b | 0.000025210902118320486 | | 61015 | 67f68835939b9fa291a4e417312b4ec1 | 0.00003538932654577245 | +-------+----------------------------------+-------------------------+ 3 rows in set (0.06 sec)
さて本題。
ORDER BY RAND()はWHERE句でフィルターされた後の行全てに対してRAND()関数を適用し、その結果でソートするので、WHERE句でフィルターした後の行が多ければ多いほど重くなるし、WHERE句で十分フィルターが聞いてもUsing temporaryに落ちる。
昔からよく言われることではあるが、アプリケーション側で乱数を作ってWHERE句に指定するのはよくあるやり方だ。その場合、ORDER BY FIELD() はやっぱり役に立つかもしれない。
アプリケーション側と言いながらSQLでやってるのは気にしない。
mysql56> SELECT MAX(num) FROM t1 INTO @max_num; Query OK, 1 row affected (0.01 sec) mysql56> SET @r1 := CAST(@max_num * RAND() + 1 AS signed), @r2 := CAST(@max_num * RAND() + 1 AS signed), @r3 := CAST(@max_num * RAND() + 1 AS signed); Query OK, 0 rows affected (0.00 sec) mysql56> SELECT num, val FROM t1 WHERE num IN (@r1, @r2, @r3) ORDER BY FIELD(num, @r1, @r2, @r3); +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 68966 | 3ccf50b9d73ea11f758bd030e5ac593f | | 51533 | 2f6a0826437abc6688b22dfd89d783c0 | | 50767 | 8deda01d4df57c1d3cb6b1e4a0391fbe | +-------+----------------------------------+ 3 rows in set (0.01 sec)
実際問題、auto_increment（の値とは限らないけれど）の値が一貫して抜けがないことは期待できない（innodb_autoinc_lock_mode= 0だってDELETEすれば抜ける訳で、= 1ならトランザクションをロールバックしたりDuplicate Key Errorを起こすだけでautoincは進む）ので、こんな風に書くしかなかろうか。
mysql56> SELECT MAX(num) FROM t1 INTO @max_num; Query OK, 1 row affected (0.00 sec) mysql56> SET @r := @max_num * RAND() + 1; Query OK, 0 rows affected (0.00 sec) mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1; +------+----------------------------------+ | num | val | +------+----------------------------------+ | 8564 | 621eb0b827c09dd1804e87bd74f79383 | +------+----------------------------------+ 1 row in set (0.00 sec) mysql56> SET @r := @max_num * RAND() + 1; Query OK, 0 rows affected (0.00 sec) mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 67575 | fd85263468f2e1315a31116cf7b12a00 | +-------+----------------------------------+ 1 row in set (0.00 sec) mysql56> SET @r := @max_num * RAND() + 1; Query OK, 0 rows affected (0.00 sec) mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 12181 | b75867b590e9a1a38ceaea9f8cb9cf45 | +-------+----------------------------------+ 1 row in set (0.00 sec)
INで仕留めた場合でもLIMIT 1で仕留めた場合でも、SQLのレイヤーでは重複（たとえばnum = 1が2回引っかかる）や空振り（その条件にマッチするレコードが1件もない）を検出しないので、それが嫌ならアプリケーション側で重複判定をする必要がある。それに、必要な件数が揃うまで複数回クエリーを投げる必要があるので、それが嫌な感じはもちろんする（とはいえ、速度的には大概の場合お釣りがくる）。あとはサロゲートキーが極端に偏ってるとこれは死ぬかも知れない。1, 2, 3, .., 1000の次が10万だったりすると、max_num * rand()を超えるレコードはかなりの高確率でnum= 10万だ。
もう一つ、WHERE RAND()という手法もある。`tail -f .. | perl -nle 'if (rand() < 0.01) {print}'` って話を聞いて思い付いただけだけれども。
mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 99966 | 44d3377fd88bc32cd46acd38d716abd3 | | 99899 | a9cfebcdb4e20ed975e82b7fd877693f | | 99790 | 7aab6ba599620439ed28d3cee272c3af | +-------+----------------------------------+ 3 rows in set (0.00 sec) mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 99954 | 758f0bc5e561543556e9f4e0d23335cc | | 99663 | 10ef53cc7b761466d851d05dda6b82e3 | | 99650 | 02aecc1719dc308a7efac6064861cf93 | +-------+----------------------------------+ 3 rows in set (0.00 sec)
DESCにしてるのは趣味なのでASCでもいいかも知れない（けど、古いものからやるよりは新しいものから引いた方がバッファプール効率がいい） もうちょっと分散させたい場合はRAND()と比較する定数の値を小さくすれば良くて、1/100でLIMIT 3なら、期待値として直近300行をhandler_read_prevして、その中から3件が選ばれる。飽くまで1クエリーで1つのテーブルから取ってくるので、重複はない。が、確率で取ってくることになるのであまりタイトなサンプリングをすると空振り（LIMITで指定した件数が集まらない）はあり得る。あと、この形はサロゲートキーに依存せずインデックスさえあれば好きなインデックスでORDER BYできる。
mysql56> SHOW SESSION STATUS LIKE 'handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 1 | | Handler_read_next | 0 | | Handler_read_prev | 312 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec)
ほぼ期待値通り。 OORDEER BY RAND()とWHEREで3回引くケースはこんな感じだった。
mysql56> SHOW SESSION STATUS LIKE 'handler_read%'; +-----------------------+--------+ | Variable_name | Value | +-----------------------+--------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 3 | | Handler_read_rnd_next | 200002 | +-----------------------+--------+ 7 rows in set (0.00 sec) mysql56> SHOW SESSION STATUS LIKE 'handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 4 | | Handler_read_last | 1 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.00 sec)
直近のPRIMARY KEYはバッファプールに載ってる（INSERTされたままバッファプールに残ってる）率が高い気がするので、偏りが許せるなら実用的だと思う（し、本当に完全ランダムでなきゃいけないならRAND()関数とか使っちゃいけない気がする） これだけ件数が絞れてれば、FROM句に閉じ込めてORDER BY RAND()しても許せなくはないくらいのパフォーマンスのはず。パッと見、numでORDER BYされてるとは思えないような感じに仕上がる（かもしれない）
mysql56> SELECT * FROM (SELECT num, val FROM t1 WHERE rand() < 1/3000 ORDER BY num DESC LIMIT 3) AS dummy ORDER BY RAND(); +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 96640 | 3076ef0ad4d1e7c6dec15fb4541b6997 | | 95735 | 3b2016665210c18767dfe611b76ffbea | | 97248 | 32d32773f19f2f421ebc2d41da4ef5a9 | +-------+----------------------------------+ 3 rows in set (0.00 sec) mysql56> show profile cpu; +--------------------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------------------------+----------+----------+------------+ | starting | 0.000046 | 0.000000 | 0.000000 | | Waiting for query cache lock | 0.000005 | 0.000000 | 0.000000 | | init | 0.000008 | 0.000000 | 0.000000 | | checking query cache for query | 0.000348 | 0.000000 | 0.000000 | | checking permissions | 0.000016 | 0.000000 | 0.000000 | | Opening tables | 0.000195 | 0.000000 | 0.000000 | | init | 0.000019 | 0.000000 | 0.000000 | | System lock | 0.000018 | 0.000000 | 0.000000 | | optimizing | 0.000004 | 0.000000 | 0.000000 | | optimizing | 0.000019 | 0.000000 | 0.000000 | | statistics | 0.000035 | 0.000000 | 0.000000 | | preparing | 0.000019 | 0.000000 | 0.000000 | | Sorting result | 0.000003 | 0.000000 | 0.000000 | | statistics | 0.000003 | 0.000000 | 0.000000 | | preparing | 0.000006 | 0.000000 | 0.000000 | | Creating tmp table | 0.000022 | 0.000000 | 0.000000 | | Sorting result | 0.000004 | 0.000000 | 0.000000 | | executing | 0.000014 | 0.000000 | 0.000000 | | Sending data | 0.000012 | 0.000000 | 0.000000 | | executing | 0.000002 | 0.000000 | 0.000000 | | Sending data | 0.001889 | 0.001999 | 0.000000 | | Creating sort index | 0.000045 | 0.000000 | 0.000000 | | end | 0.000002 | 0.000000 | 0.000000 | | removing tmp table | 0.000025 | 0.000000 | 0.000000 | | end | 0.000009 | 0.000000 | 0.000000 | | query end | 0.000007 | 0.000000 | 0.000000 | | closing tables | 0.000002 | 0.000000 | 0.000000 | | removing tmp table | 0.000004 | 0.000000 | 0.000000 | | closing tables | 0.000010 | 0.000000 | 0.000000 | | freeing items | 0.000176 | 0.001000 | 0.000000 | | cleaning up | 0.000041 | 0.000000 | 0.000000 | +--------------------------------+----------+----------+------------+ 31 rows in set, 1 warning (0.00 sec)
サロゲートキーへの依存度はmax_num * RAND()より小さいけど、なんかどこかに落とし穴がありそうだよなぁ。。
( ´-｀).oO(query_cache_type= DEMANDなんだけど、これ切れば300usくらい速くなるな。。
最後に、ランダムピックしたものをそもそもキャッシュする方法も考え付いた。そもそも元の行数が少なければ、ORDER BY RAND()でも戦えるんじゃないか、って話。
キャッシュは定期的に更新（というかスワップ）してやればいい。
mysql56> CREATE TABLE rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000; Query OK, 10000 rows affected (0.17 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 2533 | 4de754248c196c85ee4fbdcee89179bd | | 78915 | d3272a819b09ced96c69e22f183cc88e | | 16351 | dcb8e02b8527b08dbd8acb146bccc612 | +-------+----------------------------------+ 3 rows in set (0.00 sec) mysql56> CREATE TABLE tmp_rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000; Query OK, 10000 rows affected (0.14 sec) Records: 10000 Duplicates: 0 Warnings: 0 mysql56> RENAME TABLE rand_cache TO old_rand_cache, tmp_rand_cache TO rand_cache; Query OK, 0 rows affected (0.01 sec) mysql56> DROP TABLE old_rand_cache; Query OK, 0 rows affected (0.01 sec) mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3; +-------+----------------------------------+ | num | val | +-------+----------------------------------+ | 98937 | 73a3320fa46a5e4fad268056af61cd42 | | 54927 | a11d83c11ed8c95a32b3628a762cf41f | | 38444 | d3e8129138f2c76dc6e4048281160fe0 | +-------+----------------------------------+ 3 rows in set (0.01 sec)
何も考えないORDER BY RAND()はORDER BY RAND()で、「空振りが無い（WHERE句でフィルターした結果がLIMIT未満でなければ）」、「重複がない」、「サロゲートキーに依存しない」というメリットもあるのだなぁと思った。そりゃあ使いたがる人多くてもわかるは。
【2020/05/13 16:01】
奥さん の ブコメ で(4年前に)教えてもらっていたもうちょっとスマートそうなやり方。
WHERE RAND() LIMIT するくらいなら、SELECT primary_key ORDER BY RAND() LIMIT 5した結果をセルフジョインするほうがいいんじゃ / top-N sortは最近のmysqlでは… https://t.co/VTcw6HQkgs— Kazuho Oku (@kazuho) March 1, 2016
4年が経って手元の環境が既に5.6ではないけれど、吊るしのORDER BY RAND()に比べて10msくらい節約できるみたい（これは平均行サイズが大きくなれば大きくなるほど差も大きくなるはず）
ただしMySQL 8.0ではテンポラリーテーブルの方式が変わっている(TempTableストレージエンジン)ので5.7とそれ以前だと傾向は変わるかも知れないし変わらないかも知れない。
mysql80 88> SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num | val |
+-------+----------------------------------+
| 88253 | a885315b8b17d78a1121b875bc0bc198 |
| 67289 | fde520a1553f284433c0140de0bab59a |
| 85943 | f324bc6faedca902976e58abd5d8a9d1 |
+-------+----------------------------------+
3 rows in set (0.06 sec)
mysql80 122> SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;
+-------+----------------------------------+
| num | val |
+-------+----------------------------------+
| 63347 | 65b553cedfe36b1a168b7600ba146140 |
| 22114 | fb70c5b7a51b1a13deec0dcf7026459d |
| 2169 | bd0cc810b580b35884bd9df37c0e8b0f |
+-------+----------------------------------+
3 rows in set (0.06 sec)
mysql80 123> EXPLAIN SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 99750 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql80 123> EXPLAIN SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | eq_ref | num | num | 8 | _dummy.num | 1 | 100.00 | NULL |
| 2 | UNCACHEABLE DERIVED | t1 | NULL | index | NULL | num | 8 | NULL | 99750 | 100.00 | Using index; Using temporary; Using filesort |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)
$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot --query="SELECT num, val FROM d1.t1 ORDER BY RAND() LIMIT 3" --number-of-queries=1000
Benchmark
Average number of seconds to run all queries: 52.038 seconds
Minimum number of seconds to run all queries: 52.038 seconds
Maximum number of seconds to run all queries: 52.038 seconds
Number of clients running queries: 1
Average number of queries per client: 1000
$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot --query="SELECT t1.num, t1.val FROM d1.t1 JOIN (SELECT num FROM d1.t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num" --number-of-queries=1000
Benchmark
Average number of seconds to run all queries: 42.276 seconds
Minimum number of seconds to run all queries: 42.276 seconds
Maximum number of seconds to run all queries: 42.276 seconds
Number of clients running queries: 1
Average number of queries per client: 1000
4年越しですがありがとうございます！
0 件のコメント :
コメントを投稿