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

ケース(ランダムセレクション)

あなたは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

MAX(bug_id)で最大のbug_idを選ぶ
RAND()*は0~1のランダムな値を返すので、MAX(bug_id)を最大とするランダムな整数をかえす
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

category_script:page_name.startswith("30")