はじめに
とあるテーブルからランダムな一行を取得したいときがたまにあります。テストデータとしてランダムなサンプルが欲しいときなどが良い例でしょう。
ランダムな結果を返すSQLについて「SQLアンチパターン」第15章に載っていたので自分なりにまとめます。
案1: データをランダムにソートする
SELECT * FROM Users ORDER BY RAND() LIMIT 1;
メリット
わかりやすく、実装が簡単。
デメリット
インデックスのメリットを得られず、テーブル全体をソートするため、行数が多くなるとパフォーマンスが下がる。
使いどころ
行数が少なく、今後大きく増えることもないであろう場合 ( 例えば47都道府県など ) 。
案2: 1と最大値の間のランダムなキーを選択する
SELECT u1.*
FROM Users AS u1
INNER JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM Users)) AS rand_id) AS u2
ON u1.id = u2.rand_id;
メリット
テーブル全体のソートを避けられる。
デメリット
主キーが連続していない ( 欠番が存在する ) 場合、ランダム値が欠番だった場合結果がなくなる。
使いどころ
1から主キーの最大値までのすべての値が使用されていることが確実な場合。
案3: 欠番の後にあるキーを選択する
SELECT u1.*
FROM Users AS u1
INNER JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM Users)) AS id) AS u2
ON u1.id >= u2.id
ORDER BY u1.id
LIMIT 1;
メリット
テーブル全体のソートを避けられ、欠番があっても結果がなくなることがない。
デメリット
欠番の一つ後ろのキー値が採用される確率が高くなる。
使いどころ
欠番がめったにない場合や、均等に選ばれることがそれほど重要ではない場合
案4: すべてのキーのリストを受け取り、ランダムに一つ選択する
SELECT id FROM Users;
-- 上の結果をリストに格納し、ランダムに一つ選択する
SELECT * FROM Users WHERE id = ?;
-- バインドパラメータには、選択したランダムな値を指定する
メリット
テーブル全体のソートを回避でき、キー値もほぼ平等に選択される。
デメリット
テーブルからすべての id を取得する際、リストが非常に大きくなり、メモリのリソースを超える可能性がある。
また、クエリを二回発行する必要がある。
使いどころ
ほどほどのサイズのテーブルからランダムな行を選択する場合。特に主キーが非連続なテーブルに対して便利。
案5: オフセットを用いてランダムに行を選択する
データセットの行数をカウントし、0と行数までの間の乱数を返す技法です。
SELECT ROUND(
RAND() * (SELECT COUNT(*) FROM Users)
) AS id_offset;
-- アプリケーションコードで id_offset 取得
SELECT * FROM Users LIMIT 1 OFFSET :offset;
-- :offset に id_offset を使用する
メリット
テーブル全体のソートを回避でき、キー値もほぼ平等に選択される。
デメリット
クエリを二回発行する必要がある。
使いどころ
キー値が連続しておらず、各行が平等に選択される必要がある場合。
備考: ベンダー依存の解決策
多くのデータベース製品で、ランダムな値を返すための独自の解決策が実装されているそうです。
お使いのデータベース製品にもより便利な方法が存在するかもしれませんので、調べてみてください。