关于 Ruby on Rails、常量、belongs_to & 的问题数据库优化/性能
我使用 Ruby on Rails 和 MySQL 后端为我的一个客户开发了一个基于 Web 的销售点系统。这些公司发展得非常快，每天全公司范围内的交易量接近 10,000 笔。对于这个问题，我将以transactions表为例。目前，我将 transactions.status 作为字符串（即：“待处理”、“已完成”、“不完整”）存储在具有索引的 varchar(255) 字段中。一开始，当我尝试按不同状态查找记录时，情况还好，因为我不必担心那么多记录。随着时间的推移，使用查询分析器，我注意到性能已经恶化，并且 varchar 字段确实会降低数千次查找的查询速度。我一直在考虑利用 Transaction 模型中的 STATUS CONSTANT 将这些 varchar 字段转换为基于整数的状态字段，如下所示：
class Transaction < ActiveRecord::Base
STATUS = { :incomplete => 0, :pending => 1, :completed => 2 }
def expensive_query_by_status(status)
self.find(:all,
:select => "id, cashier, total, status",
:condition => { :status => STATUS[status.to_sym] })
end
这是我采取的最佳路线吗？你们有什么建议？我已经在各种查找字段上使用适当的索引，并尽可能使用 memcached 进行查询缓存。它们目前设置在由 3 台服务器组成的分布式服务器环境中，其中第 1 台用于应用程序，第 2 台用于数据库和数据库。第三个用于缓存（全部位于 1 个数据中心且位于同一 VLAN 上）。
I've developed a web based point of sale system for one of my clients in Ruby on Rails with MySQL backend. These guys are growing so fast that they are ringing close to 10,000 transactions per day corporate-wide. For this question, I will use the transactions table as an example. Currently, I store the transactions.status as a string (ie: 'pending', 'completed', 'incomplete') within a varchar(255) field that has an index. In the beginning, it was fine when I was trying to lookup records by different statuses as I didn't have to worry about so many records. Over time, using the query analyzer, I have noticed that performance has worsened and that varchar fields can really slowdown your query speed over thousands of lookups. I've been thinking about converting these varchar fields to integer based status fields utilizing STATUS CONSTANT within the Transaction model like so:
class Transaction < ActiveRecord::Base
STATUS = { :incomplete => 0, :pending => 1, :completed => 2 }
def expensive_query_by_status(status)
self.find(:all,
:select => "id, cashier, total, status",
:condition => { :status => STATUS[status.to_sym] })
end
Is this the best route for me to take? What do you guys suggest? I am already using proper indexes on various lookup fields and memcached for query caching wherever possible. They're currently setup on a distributed server environment of 3 servers where 1st is for application, 2nd for DB & 3rd for caching (all in 1 datacenter & on same VLAN).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是否在代表性数据库上尝试过替代方案？从给出的例子来看，我有点怀疑它是否会产生很大的影响，你看。如果只有三个状态，那么按状态进行查询可能会更好，根本不使用索引。
假设“已完成”包含表的 80% - 不涉及其他索引列，如果使用索引，您将需要更多读取。因此，随着表的增长，这种类型的查询几乎肯定会变慢。然而，“不完整”和“待处理”查询可能仍会受益于索引；只有当具有这些状态的行总数增加时，它们才会受到影响。
您是否经常在没有更具选择性的标准的情况下查看所有内容，无论是完整的还是其他内容？你能以某种（内部或外部）方式对表进行分区吗？例如，将已完成的事务存储在单独的表中，当新事务达到最终（？）状态时将其移至那里。我认为 MySQL 5.1 中引入了内部数据库分区 - 查看 文档 看来 RANGE 分区可能是合适的。
尽管如此，我确实认为不再将状态存储为字符串可能会有一些好处。撇开存储和带宽考虑不谈，您不太可能无意中拼错整数，或者更好的是常量或符号。
Have you tried the alternative on a representative database? From the example given, I'm a little sceptical that it's going to make much difference, you see. If there are only three statuses then a query by status may be better-off not using an index at all.
Say "completed" comprises 80% of your table - with no other indexed column involved, you're going to be requiring more reads if the index is used than not. So a query of that type is almost certainly going to get slower as the table grows. "incomplete" and "pending" queries would probably still benefit from an index, however; they'd only be affected as the total number of rows with those statuses grew.
How often do you look at everything, complete and otherwise, without some more selective criterion? Could you partition the table in some (internal or external) way? For example, store completed transactions in a separate table, moving new ones there as they reach their final (?) state. I think internal database partitioning was introduced in MySQL 5.1 - looking at the documentation it seems that a RANGE partition might be appropriate.
All that said, I do think there's probably some benefit to moving away from storing statuses as strings. Storage and bandwidth considerations aside, it's a lot less likely that you'll inadvertently mis-spell an integer or, better yet, a constant or symbol.
您可能想开始限制您的搜索（如果您还没有这样做），
#find(:all)在这种规模上是相当繁重的。此外，您可能需要考虑您的事务模型在转换为您的视图时要达到什么目的，并可能急切地加载这些视图以最大程度地减少对数据库的额外信息的请求。You might want to start limiting your searchings (if your not doing that already),
#find(:all)is pretty taxing on that scale. Also you might want to think about what your Transaction model is reaching out for as it gets translated into your views and perhaps eager load those to minimize requests to the db for extra information.