sqlのorderbyによる負荷（rails）

##疑問

１．@hoge = Model.order("fuga DESC").take(5)で発行されるSELECT "models".* FROM "models" ORDER BY fuga DESC LIMIT ? [["LIMIT", 5]]をした時レコード数が莫大な場合の負荷は大きいのか？（５つ取り出しているだけだから特に負荷はないのか？）

２．負荷がかなり大きいのならば、ランキングのようにある属性の上位５つを取ってくる最適なクエリはどのように書けばよいのか？

##考え流れ

@hoge = Model.order("fuga DESC").take(5)
のようにactive_recordで指定してモデルを並べ替えてそのうちの5つをとってくるという文を書きました。そのため

SELECT "models".* FROM "models" ORDER BY fuga DESC LIMIT ? [["LIMIT", 5]]
のようなSQL文が発行されるかと思います。

これは、一度modelテーブルのレコードをすべて参照して並び替えてそのうちの５個をとってきているとおもっています。

SQL自体は1文で簡単なものかと思うのですが、レコード数が10万など（とっても多く）なった場合レコードをすべて参照しての部分でかなりの負荷がでてしまうのでは？と思いました。

しかし何かの項目上位５つを取り出す時、この負荷は避けられないきがしてしまいます。

実行環境はrailsです。
お時間あるときにでもよろしくお願いいたします。

行動規範の内容に同意します

回答3件

ベストアンサー

環境(サーバのスペック)や設定(my.cnf)にもよるので10万件とか20万件でどうなるかというのは、実際にお使いの環境で試したほうが早いです。
ぜひ試してみてください。

昔の偉い人がいった言葉で「推測するな計測せよ」というのがあるらしいんですけど、まさにその通りで、まずは試して速度や負荷を計ってみてはどうでしょうか？

20万件のレコードを入れるコードは、お分かりだと思いますが一応こんな感じですね。

ruby
1# rubyは数値リテラルにアンダースコアが使えるので見やすい位置で
2# アンダースコアを入れると分かりやすいです
3total = 200_000
4total.times.each do |n|
5  attrs = {
6    モデルのフィールドの設定
7  } 
8  model = Model.create!(attrs)
9  puts "#{n}/#{total}: #{model.inspect}"
10end

ランキングについては、リアルタイムである必要がなく、
前日のデータをもとにランキングを決めるという事で良い場合は、
あらかじめランキングテーブルを用意して、
バッチで集計した結果を入れておくという方法が割とメジャーだと思います。

投稿2018/03/12 02:47

mingos

総合スコア4025

domidomi

2018/03/12 06:57

回答ありがとうございます。＞あらかじめランキングテーブルを用意して、バッチで集計した結果を入れておくこんなメジャーそうな発想すら出てきておりませんでした。〇万件インサートは以前本番環境に１０万件いっきにいれてためそうとしたところ、全然入力が終了しなくてあきらめてしまっていたので、やることすらしませんでした申し訳ないです。＞推測するな計測せよ大事な事ですね、ありがとうございます

行動規範の内容に同意します

大抵のDBエンジンではorder by を前提にシステムが構築されているので
おそらくorder byが一番はやい処理になります。
とはいえexplainなどでチェックするとfilesortなどで実行されるケースが
多いので、完全な効率化がなされることはないでしょう
しかしランキング処理もしくはDBごとに用意されたランキング系の関数は
ロジック的にコストが高いので、それにくらべればorder byの方がマシだと
思います。
（実際にはテストをして効率が良い方を採用することになるでしょう）

投稿2018/03/12 02:36