計測さえすればいい。〜MySQLのorder by rand()を通して思ったこと〜

はじめまして、みんなのウェディングのエンジニアの大須賀です。 「みんなのウェディング 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ペースでボードゲーム会もやっています🐺