Help us understand the problem. What is going on with this article?

MySQLで行をランダムに引く効率的な方法

MySQLで行をランダムに引く非効率的な方法を1つと、効率的な方法を2つ紹介します。

非効率的な方法

ORDER BY RAND()

各行にランダムな値を振って、その値でsortし、上からn件取る。

SELECT *
  FROM table1
  ORDER BY RAND()
  LIMIT n;
  • GOOD: n を調整することで、好きな数行だけ取得できる
  • BAD: table1 を端から端まで全部読むことになるし、ソートしてるのでO(n log(n))かかる。行数が大きいときにめちゃめちゃ時間がかかる

一番柔軟な方法ですが、テーブルが大きくなってくると厳しくなってきます。

効率的な方法

random_id generation

取得するIDをランダムに生成する。

例:

まず最大IDを取得する。

SELECT MAX(id) FROM table1;

1 ~ MAX(id) の範囲からランダムなidを生成する。

random_id = rand(1..max_id)

生成したidで一行引く。

SELECT *
  FROM table1
  WHERE id = <random_id>
  • GOOD: 速い
  • BAD_1: 1件しか引けない
  • BAD_2: idがsequentialかつ一様に分布していることを仮定しているため、途中の行がDELETEされて抜けていたり、そもそもidがsequentialでない場合は使えない

BAD_2を(部分的に)解決する方法として、WHERE id = <random_id> ではなく WHERE id >= <random_id>を使うというのがある。詳しく知りたい人は参考リンク1を読んでください。

idがだいたい一様に分布しており、1行だけ取得すればいいときに使えます。

RAND() < 0.1

各行に0 ~ 1の範囲のランダムな小数を振り、適当な値でフィルタする。
例えば、10,000行あるテーブルから100件ランダムに引きたいときは、以下のようなクエリを発行する。

SELECT * FROM tbl1 WHERE RAND() < 0.1;

RAND()0 ~ 1の範囲のランダムな小数を一様な分布から生成する(たぶん)。したがって、RAND() < 0.1 が真になる確率は10%である。このことから、10,000行あるテーブルに対してこのクエリを発行すると、だいたい100件ぐらいの行がランダムに取得できる。

  • GOOD_1: 速い(O(n))
  • GOOD_2: 好きな数だけ行を取れる
  • BAD_1: 狙った行数にならないことがある

テーブルが大きく、かつ取得する行数が厳密でなくてもよいときに使えます。

結論

用途に合わせて適切な方法を選びましょう。

参考

  1. http://jan.kneschke.de/projects/mysql/order-by-rand/
  2. https://docs.microsoft.com/en-us/previous-versions/software-testing/cc441928(v=msdn.10)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした