はじめまして、みんなのウェディングのエンジニアの大須賀です。 「みんなのウェディング Advent Calendar 2017」の13日目の記事です。
とあるプルリクエストで、「order by rand()が使われていたので、to_aしてshuffleしました」という修正があったのを見て思ったことを書きます。
なぜ、order by rand()を使わないほうがいいのか
まず「“Do not use ORDER BY RAND()” or “How to get random rows from table?”」 を読むのが良いと思います。日本語訳してくれている方もいるので、「ORDER BY RAND() 使うな」 のほうが読み易いです。
はい、読みましたね。使うなという理由は簡単で遅いからです。
ですから、使わないように修正するのは良いことだと思います。でも、修正の方法が良くなさそうです。order by rand() も遅いですが、通信も遅いのです。
ブログのタイトルだけでなく、記事の内容も正しく理解して使える知識にしないと間違ったことをしてしまいますので気を付けましょう。
あと、パフォーマンスのチューニングなら、計測結果が欲しいですね。
と、言うことで計測してみました。
計測
実際のケースの結果
そのときのプルリクエストの処理は、数十件のデータから2件をランダムで取得するという処理で、コード自体は見せられないですが、結果は下記の通りでした。該当処理を1000回実行させた時間です。
user system total real
order by rand() 7.380000 0.290000 7.670000 ( 25.953079)
array.shuffle 24.570000 0.760000 25.330000 ( 42.490036)
random offset 11.510000 0.360000 11.870000 ( 35.590089)
random id 10.280000 0.370000 10.650000 ( 30.114148)
ブログにあるような方法(random offset)は、逆に遅いという結果になりました。10,000行あるようなレコードからランダムで取り出すときにはorder by rand()を使うなと言っているので、今回のように数十件のデータから2件を取り出すときには当てはまらないのかもしれません。複数のテーブルをjoinするSQLであることも影響したのかもしれません。
array.shuffleは必要もないデータも全部アプリケーションサーバに持ってきているので、一番遅かったです。ということで修正した内容はよろしくないことがわかります。パフォーマンスのチューニングのための修正には、やはり計測が必要ですね。
以上なのですが、これだけだとコードもなくてつまらないので、見せられるコードで再度計測してみました。
別のケースで再計測
環境準備
dockerでmysqlのイメージを利用しました。
$ docker run -e MYSQL_ROOT_PASSWORD=password -d -p 13306:3306 mysql:5.7
テーブルは下記のように定義しました。
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `test`;
CREATE TABLE IF NOT EXISTS `reviews` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hall_id` int(10) unsigned NOT NULL,
`title` varchar(32) NOT NULL,
`body` text,
PRIMARY KEY (`id`),
KEY (`hall_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
テーブルを作ってデータを入れます。LOAD DATA LOCAL INFILEを使いたいので、--local_infile=1オプションをつけます。
$ mysql -uroot -h 127.0.0.1 -P 13306 -p --local_infile=1
様子としてはこんな感じです。LOAD DATAは便利ですね。
root@127.0.0.1[(none)]> source create_table.sql Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.21 sec)
root@127.0.0.1[test]> LOAD DATA LOCAL INFILE 'large_reviews_data.txt' INTO TABLE `reviews` IGNORE 1 LINES (@1, @2, @3) SET hall_id = @1, title = @2, body = @3;
Query OK, 100000 rows affected (19.81 sec)
Records: 100000 Deleted: 0 Skipped: 0 Warnings: 0
計測用コード
require 'active_record'
ActiveRecord::Base.establish_connection(
adapter: "mysql2",
encoding: "utf8mb4",
host: "127.0.0.1",
username: "root",
password: "password",
database: "test",
port: "13306",
)
class Review < ActiveRecord::Base
end
p "Review count:#{Review.count}"
Benchmark.bm 15 do |r|
r.report "order by rand()" do
review = Review.order("rand()").first
end
r.report "array.sample" do
review = Review.all.sample
end
r.report "random offset" do
count = Review.count
offset = [*0...count].sample
review = Review.order(:id).offset(offset).first
end
r.report "random id" do
review = Review.find_by(id: Review.pluck(:id).sample)
end
end
結果
$ ruby bench.rb
"Review count:100"
user system total real
order by rand() 0.000000 0.000000 0.000000 ( 0.007718)
array.sample 0.000000 0.000000 0.000000 ( 0.004651)
random offset 0.000000 0.000000 0.000000 ( 0.002056)
random id 0.010000 0.000000 0.010000 ( 0.003281)
$ ruby bench.rb
"Review count:1000"
user system total real
order by rand() 0.000000 0.000000 0.000000 ( 0.009665)
array.sample 0.010000 0.010000 0.020000 ( 0.040904)
random offset 0.010000 0.000000 0.010000 ( 0.003955)
random id 0.000000 0.000000 0.000000 ( 0.004916)
$ ruby bench.rb
"Review count:10000"
user system total real
order by rand() 0.000000 0.000000 0.000000 ( 0.051592)
array.sample 0.170000 0.040000 0.210000 ( 0.423818)
random offset 0.000000 0.000000 0.000000 ( 0.008596)
random id 0.020000 0.000000 0.020000 ( 0.020648)
$ ruby bench.rb
"Review count:100000"
user system total real
order by rand() 0.000000 0.000000 0.000000 ( 0.643647)
array.sample 1.530000 0.420000 1.950000 ( 4.217182)
random offset 0.010000 0.000000 0.010000 ( 0.040442)
random id 0.140000 0.040000 0.180000 ( 0.201886)
例のブログの記事のとおりの結果になりました。1つのテーブルからランダムで取り出すようなケースであれば、100件という少ないデータから取り出すのにもorder by rand()より、random offsetが速いようです。これは実際のケースで計測した結果とは異なっていますね。 全件取得してからアプリケーション側でランダムにピックアップするのはやはり良くないです。
実際のコードだともっと複雑になってきますので、order by rand() を見つけたからといって可読性を下げてまで無理に対応をしなくても良いのでは？と個人的には思ったりします。
まとめ
言いたいことはorder by rand() が遅いとか速いとかではなくて、まとめると3つです。
- 正しく理解し知識は適切に使う
- チューニングしたら計測する
- 特にありません
明日は@matsuhisa_hさんの「（仮）Rubocop の話」です
〜〜みんなのウェディングでは一緒に働く仲間を募集中！〜〜
本番環境でがんがんAWSのサービスを触りたい！チームでサービスをもっと成長させてみたい！そんなエンジニアやデザイナーの方は、ぜひお気軽に遊びに来て下さい！ 月1ペースでボードゲーム会もやっています🐺