MySQLのマニュアルを読めば、ORDER BY RAND() で行をランダムにし、LIMIT 1でそのうちの1行だけを取得することがわかるかもしれません。
SELECT name FROM random ORDER BY RAND() LIMIT 1;
この例は、たとえば 1000 行の場合のみ正常に動作し、高速になります。 忘れてはならないのは、私たちはほぼすべての行を捨てるためにソートするだけだということです。 そして、もっと良い方法があります。 最初の例では、be IDが1から始まっていると仮定し、1からIDの最大値までの間にnoholesを持っています。 MAX(id) == COUNT(id) として、1 から MAX(id) の間で乱数を生成し、それをデータベースに渡してランダムな行を取得します。
move the job into the database
But it really necessary to do it in the application ?それは本当にアプリケーションで行う必要があるのでしょうか。
さて、ランダムなIDを生成する方法はわかりましたが、その行を取得する方法は？ この方法はやめましょう。 これは最も明白ですが、最も間違った方法です。その理由は、WHERE 句の SELECT は、外側の SELECT がフェッチするすべての行に対して実行されるためです。
ソートもアプリケーションもなく、クエリのほとんどの部分が最適化されました。
数字に穴を開ける
最後のソリューションを一般化するために、DELETE 行のときのように穴の可能性を追加します。
ここで JOIN はランダム値以上のすべての ID を加え、直接一致しない場合は直接隣接したものだけを選択します。 しかし、1つの行が見つかるとすぐに停止します（LIMIT 1の部分）。 そして、インデックスに従って行を読み込む (ORDER BY id ASC) 。 = の代わりに >= を使用しているため、CEIL を削除して同じ結果を得ることができます。
Equal Distribution
ID の分布が等しくなくなると、行の選択も本当のランダムではなくなります。
この問題に対する本当の解決策はありませんが、データはほとんど一定なので、行番号と ID を対応付けるマッピング テーブルを追加することができます。
1000回取得した後、再び均等な分布が見られます。
穴を維持する
前と同じテーブルを使用します。
INSERT は非常に簡単ですが、DELETE の場合は、穴のない設定を維持するために equi-dist-id を更新する必要があります。 外部キー制約を維持するだけです:
Multiple Rows at once
複数行を返したい場合は、できます。
- クエリを複数回実行する
- クエリを実行し、結果を temp テーブルに格納するストアド プロシージャを書く
- UNION
a stored procedure
Stored Procedures はお気に入りのプログラミング言語から学んだ構造で提供されます。
- loop
- control structures
- procedures
- です。..
このタスクではLOOPだけが必要です：
問題の解決は読者に委ねます。
- 動的 SQL を使用し、一時テーブルの名前を渡す
- テーブルに UNIQUE インデックスを使用し、結果セット内の重複を削除するために UNIQUE キー違反をキャッチする
パフォーマンス
さて、パフォーマンスがどうなったか見ていきましょう。 私たちは、問題を解決するために3つの異なるクエリを持っています。 order by rand()
Q1 は N * log2(N) のコストとなり、Q2 と Q3 はほぼ一定です。
実値を得るために、テーブルに N 行（1000 から 100 万）埋め、各クエリーを 1000 回実行しました。