はじめに
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 |
データ件数が少ない場合は、クエリがシンプルなほうが早いという結果になってますね。