ランダムセレクションとは【SQL アンチパターン】
Sal
sql RAND関数は行ごとにランダムな数字をだすので、この数字で並び替えを行うことでランダムなソートを生み出すことができるが、これをorder byとともに使うとデータ数の上昇に応じて速度が重くなってしまう。

ランダムセレクションとは【SQL アンチパターン】

  1. ケース(ランダムセレクション)
  2. アンチパターン:データをランダムにソートする
  3. 解決策1:順番を使わない
  4. 解決策2:SQLで選ばない
  5. 解決策3:欠番の穴の後にあるキーを選択する
  6. ランダムセレクションのまとめ
## ケース(ランダムセレクション)
あなたはweb広告を表示するアプリケーションの担当者だ。 広告は適当なテーブルからランダムに選ばれる仕組みだ。 ある日webサイトが日に日に重くなるインシデントが起票され、その原因があなたが担当するアプリケーションにあることがわかった サンプル行をフェッチする目的をどのように達成すれば良いか...
## アンチパターン:データをランダムにソートする
次の方法はランダムにソートを行い、最初の行を選択する方法。 ` SELECT * FROM Bugs ORDER BY RAND() LIMIT 1; ` これはわかりやすく実装も簡単だが、データが増えるとともに弱点が出てくる。 RAND関数は行ごとにランダムな数字をだすので、この数字で並び替えを行うことでランダムなソートを生み出すことができる。 しかしこの方法は通常のソートとは違い、「インデックス」の恩恵を受けられないことを意味する。 インデックスとは、テーブルにある情報を検索する場合に「どこに何があるか」をわかりやすくする索引のことです。 データの検索は上から1つずつ探していく方法もありますが、大量にデータがある場合は効率が悪いですよね。 多くの情報を扱う場合、本の最後のページにあるように「索引」が用意されていると、目的の情報をすぐに確認できて便利です。 このようにインデックスはデータの場所を指し示しソートの補助を可能にするが、RAND関数はインデックスには紐づかないのでソートの補助にはならない
## 解決策1:順番を使わない
「1から主キーの最大値までの間の値をランダムに選ばせる」というアルゴリズムを使う

select
    *
from
    Bugs AS b1
INNER JOIN  (
    select CEIL( RAND()*(select MAX(bug_id) FROM Bugs) ) AS rand_id
) AS b2 ON b1.bug_id = b2.rand_id
1. MAX(bug_id)で最大のbug_idを選ぶ 2. RAND()*は0~1のランダムな値を返すので、MAX(bug_id)を最大とするランダムな整数をかえす 3. 2で送られた値をCEILで整数に直す
## 解決策2:SQLで選ばない
SQLで選ばずに、プログラム側で選ぶことも考えられる。 テーブル全体のソートを回避できるし、各キーの値もほぼ平等に選択される。
## 解決策3:欠番の穴の後にあるキーを選択する
解決策1で主キーが連続した値になっていない場合に有効

select
    *
from
    Bugs AS b1
INNER JOIN  (
    select CEIL( RAND()*(select MAX(bug_id) FROM Bugs) ) AS rand_id
) AS b2 ON b1.bug_id >= b2.rand_id
ORDER BY b1.bug_id
LIMIT 1;
この方法であれば穴が空いた番号に値が設定されることはない。 問題があるとすると、 欠番の次の値が選ばれる可能性が「欠番の値がrand_idになる」「欠番の次の値がrand_idになる」の二通りになるため 確率が平等ではなくなる
## ランダムセレクションのまとめ
RAND関数は行ごとにランダムな数字をだすので、この数字で並び替えを行うことでランダムなソートを生み出すことができる が、これをorder byとともに使うとデータ数の上昇に応じて速度が重くなってしまう。 複数の行から一つを選ぶときにはわざわざソートする必要はない。 title:ランダムセレクションとは【SQL アンチパターン】 description:RAND関数は行ごとにランダムな数字をだすので、この数字で並び替えを行うことでランダムなソートを生み出すことができるが、これをorder byとともに使うとデータ数の上昇に応じて速度が重くなってしまう。 img:https://www.oreilly.co.jp/books/images/picture_large978-4-87311-589-4.jpeg