はじめに

DBからランダムに1件データを抽出するというお話です。

この機能を実現するにあたっては、初学者でも比較的簡単にできると思います。
しかし、この前たまたま目にした記事が「これは怖い予感がする😨」という内容だったので、実際に怖さを検証してみました。

どんな怖いコード？

以下が怖いコードの実例です。

sample_post = Post.all.shuffle.first

全てのレコードを取得してからサンプリングしてるので、時間がかかる & メモリを消費する という問題があります。
（ちなみにshuffle.firstはsampleに置き換えられるという小さな問題もあります）

数百レコード程度であれば、目に見えるほどの影響はないかもしれません。
サンプル範囲が狭い場合であればこのようなやり方でも許容できるかもしれませんが、基本的には無駄な時間とメモリの消費は避けるべきです。

しかし実際にどのぐらい問題があるか体験したことがなかったので、検証してみようと思います。

検証してみる

100万件レコードがあれば結構な問題になるだろうと思ったので、100万件でテストしてみます

ローカルで使用した各環境は以下のとおりです

実行環境

アプリケーション関連
- Rails 7.0.2.2
- Ruby 3.1.0
- PostgreSQL 14.2
ハード関連
- macOS Monterey 12.3.1
- Apple M1 Pro
- メモリ 32GB

サンプルデータ

SELECT * FROM posts;
 id |   title   | content |         created_at         |         updated_at
----+-----------+---------+----------------------------+----------------------------
  1 | 1_1_test  | test    | 2022-07-03 22:37:23.549165 | 2022-07-03 22:37:23.549165
  2 | 1_2_test  | test    | 2022-07-03 22:37:23.549165 | 2022-07-03 22:37:23.549165
  3 | 1_3_test  | test    | 2022-07-03 22:37:23.549165 | 2022-07-03 22:37:23.549165
...

titleが1_1_test ~ 1000_1000_testまでの100万レコードで用意しました。
contentカラムはvarchar型で中身は全てtestにしてます
（100万件のレコードを準備するやり方は別な記事で紹介しようと思います）

検証用コードの用意と実行

検証するサンプリング方法は3パターン用意しました。

class Post < ApplicationRecord

  class << self
    def random_test
      Benchmark.bm 10 do |r|
        # DBの関数を使用(MySQLとかだとrandという関数になります)
        r.report 'random function' do
          order('random()').first
          put_memory_usage
        end

        # 全件数を取得してから、その件数以下の数字をランダムに選びlimitとoffsetで1件取得
        r.report 'offset' do
          rand_offset_number = rand(count)
          limit(1).offset(rand_offset_number).first
          put_memory_usage
        end

        # 全件取得してからサンプリング(怖い方法)
        r.report 'shuffle' do
          all.sample
          put_memory_usage
        end
      end
    end

    # 使用してるメモリ出力
    def put_memory_usage
      p "#{(ObjectSpace.memsize_of_all * 0.001 * 0.001).round(1)} MB"
    end
  end
end

結果発表おおおお（浜ちゃん風）

# 一応レコード数を確認
[1] pry(main)> Post.count
  Post Count (45.6ms)  SELECT COUNT(*) FROM "posts"
=> 1000000

# 検証
[2] pry(main)> Post.random_test
                 user     system      total        real
random function  Post Load (183.2ms)  SELECT "posts".* FROM "posts" ORDER BY random() LIMIT $1  [["LIMIT", 1]]
"63.0 MB"
  0.020798   0.003376   0.024174 (  0.233853)
offset       Post Count (14.7ms)  SELECT COUNT(*) FROM "posts"
  Post Load (24.8ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" ASC LIMIT $1 OFFSET $2  [["LIMIT", 1], ["OFFSET", 529166]]
"63.0 MB"
  0.012095   0.000161   0.012256 (  0.051814)
shuffle      Post Load (343.3ms)  SELECT "posts".* FROM "posts"
"925.0 MB"
  7.256302   2.916320  10.172622 ( 10.408513)

少しわかりやすく表示してみます

方法	処理時間 /s	メモリ消費 /MB
DBのrandom関数使用	0.024174	63.0
count + offset使用	0.012256	63.0
全件取得してからサンプリング	10.172622	925.0

CPUはM1 Proなので割と処理は早いと思いますが、それでもこれだけ時間がかかってます。
1レコードあたりのサイズはかなり小さいはずですが、メモリは結構食ってます。
（postsテーブルを想定すると、本来はcontentカラムがtext型でもっと中身があるはずなので、実際のユースケースではもっと膨大なメモリを消費するはずです。よほどメモリに余裕を持っておかないとアプリサーバーが落ちそうです）

少し紹介

ちなみにDBのrandom関数を使うのは一見問題なさそうに思えますが、SQLアンチパターンという書籍によると、アンチパターンとされています。

これは以下のような理由からです。

ランダムにソートを行うため、インデックスのメリットを得られない
必要な行が最初の1行のみであるにも関わらず、データセット全体をソートしてしまう

こちらの記事に概略があるので、時間がある方は覗いてみると良いかもしれません。↓

もっと時間に余裕のある方は書籍を読むことをお薦めしておきます↓

おまけ

10件でテストした時の結果です。

[1] pry(main)> Post.random_test
                 user     system      total        real
random function  Post Load (5.0ms)  SELECT "posts".* FROM "posts" ORDER BY random() LIMIT $1  [["LIMIT", 1]]
"62.9 MB"
  0.019082   0.002568   0.021650 (  0.055337)
offset       Post Count (0.6ms)  SELECT COUNT(*) FROM "posts"
  Post Load (0.9ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" ASC LIMIT $1 OFFSET $2  [["LIMIT", 1], ["OFFSET", 4]]
"63.0 MB"
  0.010359   0.000295   0.010654 (  0.013060)
shuffle      Post Load (0.4ms)  SELECT "posts".* FROM "posts"
"63.0 MB"
  0.008740   0.000120   0.008860 (  0.009192)

方法	処理時間 /s	メモリ消費 /MB
DBのrandom関数使用	0.021650	62.9
count + offset使用	0.010654	63.0
全件取得してからサンプリング	0.008860	63.0

データ件数が少ない場合は、クエリがシンプルなほうが早いという結果になってますね。