なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLとRAND関数の挙動の整理 その1

MySQL Advent Calendar 2020 - Qiita の3日目の記事です。

昨年のアドベントカレンダーは1つも記事も書かなかったので、今年は1つくらいは。。。
(ていうか、アドベントカレンダーのタイトルがMySQLだったりMySQL Casualだったりしてるのね)

MySQLでテーブルからランダムにn件取り出したい

ひと昔ほど前に流行ったテーマっぽいですが、自重しません。
気になったタイミングで調べて、出せるタイミングでアウトプットする、それだけです。


過去にアップされたと思しき記事なんかも調べたりしつつ、やり方を洗い出したら、以下の3つくらいになりました。

(1)ORDER BY RAND() LIMIT n
(2)WHERE RAND() < x LIMIT n;
(3)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上の環境で、計測は1回のみですので、応答速度は参考程度にどうぞ。

なお、検証環境はMySQL 5.7.32とMySQL 8.0.22をCentOS7にyumでインストールしたものを使用し、実行計画やスロークエリログはMySQL5.7.32を貼っています。

(1)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



(2)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による乱数生成結果しだいでバラツキは出ますが。

(3)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の値をどの程度にするか、は、(2)と同様に重要です。
しかし、緩めにしないと必要件数(LIMIT n)取れないケースが出てくる一方で、条件を緩くしてもRows_examinedは減らず、応答も速くなりません。
ソートがあるかないかの違いです。

まとめ

  • RAND関数は、使用するテーブル全件に対して1件ずつ乱数生成処理を行う。
  • テーブル内からn件をランダムで抽出したい時、RAND関数を使用して取得する方法がいくつかある。
  • 有名な「ORDER BY RAND() LIMIT n」によるランダム抽出は、テーブルの件数が増えると劇的に遅いので要注意。


長くなったので2回に分けて書くことにしました。
よって、明日4日目の枠もいただきます。


このエントリとは直接関係ないけど、今MySQL関係で推してる本はこれなので貼っておきます。MySQL触る人で読んでない人は是非手にとって欲しい。