问题描述
我继承了一个Rails 3应用程序，存储其中很大一部分的数据作为一个相当复杂的树状结构。该应用程序的工作原理pretty的好一般，但我们注意到一些问题的表现，大多是围绕数据库交互。
I have inherited a Rails 3 app that stores much of it's data as a fairly sophisticated tree structure. The application works pretty well in general but we are noticing some problems with performance, mostly around database interactions.
我看到了很多的查询沿着这些显示在我的记录了行：
I am seeing a lot of queries along the lines of these showing up in my logs:
SELECT `messages`.* FROM `messages` WHERE `messages`.`context_type` = 'Node' AND `messages`.`context_id` IN (153740, 153741, /* about a thousand records... */ 154837, 154838, 154839, 154840, 154841, 154842, 154843)
其次是多单查询，它看起来好像相同的记录被查询一次又一次的：
Followed by many single queries where it looks as though the same record is being queried time and again:
[1m[35mCACHE (0.0ms)[0m SELECT `plans`.* FROM `plans` WHERE `plans`.`type` IN ('Scenario') AND `plans`.`id` = 1435 LIMIT 1
我的日志都有详细的查询大约80时间─现在我猜，最初的缓存消息意味着它可能被从缓存中拉出，而不是要回数据库中的每个时间，但它仍然看起来像了很多，这种类型的事情反复发生。
My log has that exact query roughly eighty times- now I'm guessing that initial Cache message means it is probably being pulled from a cache rather than going back to the database every time, but it still looks like a lot and this type of thing is happening repeatedly.
我猜测，上述查询的关联被拉出向后，使消息belongs_to的计划和它加载的所有信息，然后拔出计划为每一个而不是像人们可能会做一个理智的世界，开始了计划，然后加载所有的消息。
I am guessing that the above queries are an association being pulled out backwards so that message belongs_to plan and it is loading all the messages then pulling out the plan for each one rather than, as one might do in a sane world, starting with the plan and then loading all the messages.
工作在这方面，一个单一的请求中包含的 1641 SELECT语句，似乎很有可能，我认为数据库流量的绝对量（更不用说连续数 LIMIT 1 在同一个表中相邻数据查询）是一个显著的瓶颈。我不愿意发表过多code，但，这是较大的查询中的一个典型的例子：
Working in this vein, a single request contains 1641 SELECT statements and it seems very likely to me that the sheer amount of database traffic ( not to mention the number of sequential LIMIT 1 queries for neighbouring data in the same table ) is a significant bottleneck. I am reluctant to post too much code but this is a typical example of one of the larger queries:
def nodes
include_objects = [:sector, :market, :messages, :node_user_prefs, :reference_node, :project, {:available_events => :available_event_nodes}, :customer_factors, :active_components, :tags, { :event_histories => :node}, {:event_histories => :user }]
project = self
@cached_nodes ||= begin
all_nodes = orig_nodes.includes(include_objects)
all_nodes = all_nodes.map { |n| n.tap { |i| i.cached_project = project } }
all_node_ids = all_nodes.map(&:id)
all_nodes.select{ |n| n.type == 'InitialNode' || all_node_ids.include?(n.parent_node_id) }
end
end
显然，查询是pretty的多样化和应用是大的，但是这是相当的所采取的标准方法重presentative
Obviously, the queries are pretty diverse and the application is large, but this is fairly representative of the standard approach taken.
什么是易胜与ActiveRecord的，我可以用它来尝试和速度？我可以很轻松地放在一起，将拉所有需要的数据都在一个往返，但如何容易会是形成一个查询 - 冗余和所有 - 在我的模型层次结构？我可以使用哪些其他技术来帮助在这里？
What are the easy wins with ActiveRecord that I can use to try and speed things up? I can easily enough put together a query that would pull all the required data out in a single round trip, but how easy would it be to form that - redundancies and all - into my model hierarchy? What other techniques can I use to help out here?
推荐答案
祖先宝石
不以任何方式直接解决，但你不妨考虑 血统宝石 -
Not a direct fix by any means, but you may wish to consider the ancestry gem -
这会给你一个方法来创建一个树形结构，即你可以调用单记录和放大器;然后让他们的后代叫你的愿望。这将削减你的SQL查询：
This will give you a way to create a tree structure, whereby you'll be able to call single records & then have their descendents called a you wish. This will cut back on your SQL queries:
如果您设置节点/对象以这种方式，它可以让你给你打电话再次要求与放大器的记录;祖先将填充其余部分。如果你要我透露更多相关信息，请让我知道在评论和放大器;我将详细介绍更多的细节。
If you set up your nodes / objects in this fashion, it will allow you to call the records you require once & ancestry will populate the rest. If you want me to divulge more information on this, please let me know in the comments & I'll detail more specifics
这篇关于ActiveRecord的高效查询的树形结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！