Rails 3 中订单查询的效率
所以，我想知道以下哪一个更有效。最好的答案实际上是，我将来如何自己解决这个问题。
这两个查询是：
# id integer
# created_at datetime
# collection_id integer
# updated_at datetime
class Post < ActiveRecord::Base
belongs_to :collection
end
Post.where(:collection_id => collection_id).order(:updated_at).last
vs
Post.where(:collection_id => collection_id).order("updated_at DESC").first
所以，问题分为两部分：
1）哪一个语句更有效？ （除非我有错字，否则它们应该是等效的）
2）我怎么能自己回答这个问题？
我假设没有索引。我也很想知道答案是否会根据查询的 bycollect_id 部分的结果集的预期大小而变化。
So, I'd like to know which of the following is more efficient. The best answer would actually be, how can I figure this out for myself in the future.
The two queries are:
# id integer
# created_at datetime
# collection_id integer
# updated_at datetime
class Post < ActiveRecord::Base
belongs_to :collection
end
Post.where(:collection_id => collection_id).order(:updated_at).last
vs
Post.where(:collection_id => collection_id).order("updated_at DESC").first
So, 2 part question:
1) which of those statements is more efficient? (Unless I have a typo, they should be equivalent)
2) how could I have answered this question for myself?
I'm assuming no indexes. I'm also curious to know if the answer changes depending on the expected size of the result set of the by collect_id portion of the query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
至少在 Rails 3.1 中，两个查询实际上都会在数据库上运行相同的查询。 Rails 足够聪明，知道在查询上调用
.last应该只是颠倒顺序并将其限制为单个请求。因此，下面的行实际上只是将ORDER BY Updated_at DESC LIMIT 1添加到查询的末尾。换句话说，这一行不返回数据库中的所有记录，为每条记录创建ActiveRecord对象，然后然后返回最后一条记录，这样会少很多高效的。At least in Rails 3.1, both of your queries will actually run the same query on the database. Rails is smart enough to know that calling
.laston a query should just reverse the order and limit it to a single request instead. So, the line below will really just addORDER BY updated_at DESC LIMIT 1to the end of your query. In other words, this line does not return all the records from the database, create ActiveRecord objects for each of them, and then return the last one, which would be much less efficient.如果您观察控制台，您将看到 (1) 实际执行的查询（或多个查询），以及 (2) 执行所花费的时间。这将是跟踪效率的基本方法。
在生产中，还有像 NewRelic RPM 这样的工具，可以为您提供执行查询的速度/时间的警告/分析。我认为免费版本会进行效率分析，但只保留前 30 分钟应用程序运行时间的记录。
一般来说（但不严格来说），发送的查询数量越少越好，部分原因是它涉及 Rails 和数据库之间的往返次数更少。
如果您想要一些免费的，并且可能更具可重复性的东西，那么您可以添加一些性能测试，在其中一遍又一遍地运行查询，对每次运行进行计时，并确保查询/控制器操作的运行时间更少比您选择的某个基准更好，或者多次执行的平均时间低于某个阈值。
此方法还可以用作应用程序中最常发生的操作的观察者，如果它们花费的时间比预期长，则失败。该测试仅涵盖模拟环境，当然，可以用作关键操作是否完全不符合预期性能的一般指示。
If you watch the console, you'll see (1) what query (or queries) are actually being executed, and (2) the time it took to execute. This would be the basic way to track efficiency.
In production there are also tools like NewRelic RPM that will give you warnings/analysis of the speed/time to execute queries. I think the free version will do efficiency analysis, but only keeps records for the previous 30 minutes of application runtime.
Generally (but not strictly speaking), the fewer the number of queries you send, the better, partially because it involves fewer round trips between Rails and the DB.
If you want something that's also free, and perhaps more repeatable, then you can add some performance tests where you run queries over and over, timing them with each run, and just making sure that the run time for the query/controller action is less than some benchmark of your choosing, or that the average time over multiple executions is below some threshold.
This method can also be utilized as a watcher for actions that get hit most often in your app, failing if they take longer than expected. The test would only cover a simulated environment, of course, be can be used as a general indication of whether or not critical actions are getting totally out of line with expected performance.