MySQLとRAND関数の挙動の整理 その1
MySQL Advent Calendar 2020 - Qiita の3日目の記事です。
昨年のアドベントカレンダーは1つも記事も書かなかったので、今年は1つくらいは。。。
（ていうか、アドベントカレンダーのタイトルがMySQLだったりMySQL Casualだったりしてるのね）
MySQLでテーブルからランダムにn件取り出したい
ひと昔ほど前に流行ったテーマっぽいですが、自重しません。
気になったタイミングで調べて、出せるタイミングでアウトプットする、それだけです。
過去にアップされたと思しき記事なんかも調べたりしつつ、やり方を洗い出したら、以下の3つくらいになりました。
（１）ORDER BY RAND() LIMIT n
（２）WHERE RAND() < x LIMIT n;
（３）WHERE RAND() < x ORDER BY RAND() LIMIT n;
それぞれ処理の特性、応答性能に差があるので、解説していきます。
なお、テストで使用しているテーブルは以下のような感じで、INTEGERでAUTO INCREMENTな列1つで1600万件超の欠番なしデータです。
mysql> SHOW CREATE TABLE test_seq\G
*************************** 1. row ***************************
Table: test_seq
Create Table: CREATE TABLE `test_seq` (
`col_id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`col_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16777217 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM test_seq;
+----------+
| COUNT(*) |
+----------+
| 16777216 |
+----------+
1 row in set (2.03 sec)ibdファイルサイズが400MB程度、innodb_buffer_pool_sizeは2GB確保しているので、バッファにはだいたい乗ってると思いますが、VirtualBox上の環境で、計測は１回のみですので、応答速度は参考程度にどうぞ。
なお、検証環境はMySQL 5.7.32とMySQL 8.0.22をCentOS7にyumでインストールしたものを使用し、実行計画やスロークエリログはMySQL5.7.32を貼っています。
（１）ORDER BY RAND() LIMIT n
検索すると真っ先にヒットするのがこの方法ですが、テーブル上のレコード全件にRAND()した結果をソートするので、レコードが多いと遅くなります。
しかも、実行計画上はテーブル上の件数全件分程度なのに、スロークエリを見ると、Rows_examinedが、実際のテーブル全件の2倍なんですね。
MySQL8.0.22だと、Rows_examinedも実際のテーブル全件相当の数値になっており、参考とはいえ応答速度も短くなっていましたが、他の方法に比べて最も遅いことには違いはありませんでした。
サンプルとなるSQLの実行結果、スロークエリログの出力状況、実行計画は以下の通りです。
mysql> SELECT * FROM test_seq ORDER BY rand() LIMIT 10; +----------+ | col_id | +----------+ | 11338433 | | 1497473 | | 10461953 | | 15074637 | | 4050548 | | 7326068 | | 15541748 | | 8975348 | | 7629671 | | 6759755 | +----------+ 10 rows in set (9.36 sec) -- スロークエリログ # Query_time: 9.359164 Lock_time: 0.000251 Rows_sent: 10 Rows_examined: 33554442 -- 実行計画 +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+----------------------------------------------+ | 1 | SIMPLE | test_seq | NULL | index | NULL | PRIMARY | 4 | NULL | 16333383 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+----------------------------------------------+
いきなり「ORDER BY rand()」って出てくるの気持ち悪いけど、「SELECT col_id, rand() as random_value FROM test_seq ORDER BY random_value LIMIT 10」でrandom_value を表示しないのと同じ意味ですね。
これ、「ORDER BY FIELD()」の時の復習。
ORDER BYで、単純な昇順降順「以外」で並べる！ - なからなLife
（２）WHERE RAND() < x LIMIT n;
全件に順次RAND()するものの、「< 0.000001」を満たす結果が10件に到達した時点で処理をやめるので早く処理が終わります。
ただし、選択したインデックスのデータ並び順で処理されるので、必然的に若いものが優先的に選択されることとなり、出現率は偏ります。
実行計画上はテーブル上の件数全件分程度、スロークエリログのRows_examinedは、最大で全件、乱数生成状況によってばらつきます。
サンプルとなるSQLの実行結果、スロークエリログの出力状況、実行計画は以下の通りです。
mysql> SELECT * FROM test_seq WHERE rand() < 0.000001 LIMIT 10; +---------+ | col_id | +---------+ | 397959 | | 470777 | | 557559 | | 1173277 | | 1446877 | | 1974150 | | 2300264 | | 4395033 | | 4698408 | | 5255799 | +---------+ 10 rows in set (0.63 sec) -- スロークエリログ # Query_time: 0.623954 Lock_time: 0.000118 Rows_sent: 10 Rows_examined: 5255799 -- 実行計画 mysql> EXPLAIN SELECT * FROM test_seq WHERE rand() < 0.000001 LIMIT 10; +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ | 1 | SIMPLE | test_seq | NULL | index | NULL | PRIMARY | 4 | NULL | 16333383 | 100.00 | Using where; Using index | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
「WHERE RAND() < x」のxの値をどの程度にするか、も重要です。
RAND()は、「0 <= v < 1.0」を返しますが、これを数百万回、数千万回繰り返すにあたって、あまりにも出現率が低い（精度が高すぎる）値を指定すると、空振りが続き、全件分処理しても欲しい件数が取れないケースが出てきます。
少し緩めにしておくと、真評価となるレコードの数が早い段階で「LIMIT n」に到達します。
SELECT * FROM test_seq WHERE rand() < 0.00001 LIMIT 10;
と、先程の10倍の値を指定すると、
# Query_time: 0.069581 Lock_time: 0.000100 Rows_sent: 10 Rows_examined: 546408
のように、Rows_examined自体が減っていきます。
とはいえ、RANDによる乱数生成結果しだいでバラツキは出ますが。
（３）WHERE RAND() < x ORDER BY RAND() LIMIT n;
全件にRAND()した結果から、「< 0.000001」を満たしたものだけをソートします。
RANDの結果はインデックスが効きませんから、基本的には全部エグゼキュータに渡り、フィルタした結果をソートし、そのソート結果に対してLIMITすることになります。
よって、 (1)よりは速いですが(2)より遅いです。
サンプルとなるSQLの実行結果、スロークエリログの出力状況、実行計画は以下の通りです。
mysql> SELECT * FROM test_seq WHERE rand() < 0.000001 ORDER BY rand() LIMIT 10; +----------+ | col_id | +----------+ | 15111593 | | 12418545 | | 12484198 | | 8559172 | | 10924371 | | 13130277 | | 13440443 | | 10564842 | | 2737424 | | 3337261 | +----------+ 10 rows in set (1.99 sec) -- スロークエリログ # Query_time: 1.991477 Lock_time: 0.000122 Rows_sent: 10 Rows_examined: 16777243 -- 実行計画 +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------------------------------+ | 1 | SIMPLE | test_seq | NULL | index | NULL | PRIMARY | 4 | NULL | 16333383 | 100.00 | Using where; Using index; Using temporary; Using filesort | +----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-----------------------------------------------------------+
「WHERE RAND() < x」のxの値をどの程度にするか、は、（２）と同様に重要です。
しかし、緩めにしないと必要件数（LIMIT n）取れないケースが出てくる一方で、条件を緩くしてもRows_examinedは減らず、応答も速くなりません。
ソートがあるかないかの違いです。
