MySQLでORDER BY RAND()を使わずにランダム行を取る方法

  • MySQL
  • SQL
  • Performance

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

背景

ORDER BY RAND() LIMIT n でランダムな行を取る方法はよく見るけど、これはテーブル全体に対してRAND()を評価してからソートするので、行数が多いテーブルだと遅い。インデックスも効かない。

代替として、JOINで先にランダムなIDを1つ作ってから絞り込むやり方がある。

やり方

SELECT t.*
FROM TableName AS t
JOIN (
  SELECT ROUND(RAND() * (SELECT MAX(id) FROM TableName)) AS rand_id
) AS r
ON t.id >= r.rand_id
ORDER BY t.id
LIMIT 1;

仕組み

  • サブクエリで MAX(id) を1回だけ取得する
  • RAND() を掛けて 0〜MAX(id) の範囲のランダムな値を1つ生成する
  • その値に対して id >= rand_id で一番近い行を1件取る

ORDER BY RAND() のように全行にRAND()を計算するわけではないので、大きいテーブルでも軽い。

注意点

  • idに欠番（削除された行など）があると、その付近の行が選ばれやすくなる。厳密な一様分布にはならない
  • id >= rand_id の条件で一番若いIDにヒットする仕組みなので、末尾側のIDがやや選ばれやすくなる傾向がある。実機で分布を検証したわけではないので、あくまで理屈の上での話
  • 複数件ほしい場合、このクエリをそのまま LIMIT 10 にしても1件しか返らない。複数件必要ならアプリ側でループを回すか、UNION で複数回実行する必要がある
  • 前提として、idが AUTO_INCREMENT の連番であること