😆

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

2023/09/10に公開

ランダムセレクション とは?

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



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

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

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

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

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

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

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

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


解決策

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

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

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

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

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

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



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

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

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

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

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

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



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

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

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


参考

参考にした書籍
https://www.amazon.co.jp/SQLアンチパターン-Bill-Karwin/dp/4873115892

ランダムセレクション
https://www.slideshare.net/NaoYamamoto/sql-15-31724159
https://qiita.com/eduidl/items/f6383517dea9ad13c6ce
https://rtam.xyz/articles/2017/06/sql-randam-selection/
http://blog.44uk.net/2012/12/22/get-random-record-using-activerecord/
https://stackoverflow.com/questions/17372886/whats-the-rails-4-way-of-finding-some-number-of-random-records

offsetメソッド
https://railsdoc.com/page/model_offset
https://api.rubyonrails.org/v7.0/classes/ActiveRecord/QueryMethods.html#method-i-offset
https://railsguides.jp/active_record_querying.html#limitとoffset

テーブルスキャン
https://anderson02.com/sqlserver/sqlserver-index/sqlserver-index-2-06/#i

インデックス
https://www.techscore.com/tech/sql/15_01
https://it-biz.online/it-skills/index/

Discussion