😆

SQLアンチパターン　ランダムセレクションについて

2023/09/10に公開

ランダムにデータを取得する時に発生するSQLアンチパターンのこと。
ランダムにしたデータから１件もしくは複数のデータを取得する方法により、データベースのパフォーマンスが落ちてしまう。
データ量が少ない場合は問題ないが、多いときはSQLクエリが非常に遅くなる。

ランダムセレクションの発生原因は、ランダム関数によりソート（並び替え）された値にはインデックス（目的のレコードを効率よく取得するための「索引」のこと）が存在しないためです。

通常であれば、データをソートする時、カラムの値を比較し、値の大小により昇順または降順に並び替えられます。また、2回目以降も同じ結果を得ることができます。

この時インデックスを作成することで、データの検索や並び替えを高速化することができます。

一方、ランダム関数によりソートされた値には、カラムの値の大小によらずソートされ、ランダム関数を実行するたびに昇順または降順が変化します。

また、ランダム関数によりソートされた値には、インデックスが存在しないため、インデックスのメリットのデータ検索の並び替えの高速化を受けることができません。

そこで、データベースは手作業でソートするテーブルスキャン（SQLで指定されたテーブルのすべてのデータを読み込み、検索条件を満たす行を1行ずつチェックする方法）という手法を用いて、データをソートします。

ただし、これはインデックスを用いたソートよりもはるかに遅く、データ量が大きくなるとパフォーマンスがさらに下がります。

また、ランダムな値のソートは、必要なレコードを1つ取得するためだけにデータ全体を毎回ソートするのはとても非効率であるという問題点もあります。

ランダムセレクションの解決策は、以下の通り。

①１と最大値の間のランダムなキー値（データベースのテーブル内の特定の列を識別できる値のこと）を選択する

②欠番（歯抜けの番号。1,2,4,5… の時、3がない時のようなこと）の穴の後にあるキー値を選択する

③すべてのキー値のリストを受け取り、ランダムに１つを選択する

④オフセットを用いてランダムに行を選択する

⑤ベンダー（データベース製品のこと。）依存の解決策

①の場合、１から最大値までの値に欠番（歯抜けの番号。1,2,4,5… の時、3がない時のようなこと）がある場合はNG。

理由は、ランダムに選ばれた値がレコードと一致しない可能性があるからです。

②の場合、データに欠番が存在しても、データを取得することができます。

ただし、欠番がある時、欠番の1つ上のキー値が選択される可能性が高くなります。

③の場合、取得したリストのサイズが大きくなり、メモリのリソースを超えてしまい、エラーになります。

また、クエリを2回実行しなければならない（1回目は、主キーのリストの取得。2回目は、ランダムに選択された主キーのレコードの取得。）ので、クエリ自体が複雑な場合、非効率になります。

rails の場合、④の offset メソッド（特定の位置からデータを取得するメソッド）を使用し、ランダムにしたデータから1件もしくは複数のデータを取得する方法がおすすめ。

offset メソッドの使用例は下記の通り。

# ランダムなUserから1件のみレコードを取得
User.offset(rand(User.count)).limit(1)