Rails 3数据库索引和其他优化 [英] Rails 3 Database Indexes and other Optimization

问题描述

我一段时间以来一直在构建rails应用程序，但不幸的是，我的应用程序都没有大量的数据或流量。但现在我有一个正在获得动力。因此，我首先考虑扩展和优化我的应用程序。

I have been building rails apps for a while now, but unfortunately for me, none of my apps have had a large amount of data or traffic. But now I have one that is gaining steam. So I am diving in head first into scaling and optimizing my app.

执行此操作的第一步也是最简单的步骤是使用数据库索引。我有一个很好的索引列表，应该涵盖几乎所有的查询，但当我通过迁移将它们添加到我的数据库时，它只需要几秒钟来添加它们。出于某种原因，我认为他们必须经历我的所有条目（其中有数千条）并将它们编入索引。

It seems the first and easiest step to do this is with database indexes. I've got a good huge list of indexes that should cover pretty much all of my queries, but when I added them to my database via migrations it only took a few seconds to add them. For some reason I thought they would have to go through all of my entries (of which there are thousands) and index them.

这是否意味着我的索引尚未应用于我现有的数据？它们是否只会被添加到新条目中？

Does this mean my indexes haven't been applied to my already existing data? Will they only be added to new entries?

此外，我正在研究其他扩展解决方案，例如memcached，以及减少我的查询等等。

Additionally, I am looking into other scaling solutions, such as memcached, and all around slimming down my queries, etc.

如果有人能指出一些优秀资源来优化我的rails 3 app我会非常感激！

If anyone can point me to some good resources for optimizing my rails 3 app I would greatly appreciate it!

谢谢！

编辑：

感谢您对数据库索引的所有好答案！在优化和扩展我的应用程序方面，我还应该注意什么？ Memcached的？在优化方面具有最佳性能提升/努力比率是什么？

Thanks for all the great answers regarding database indexes! What else should I be looking at in terms of optimizing and scaling my app? Memcached? What has the best performance boost/effort ratio in terms of optimization?

推荐答案

向您的索引添加索引总是一个好主意所有ID和数据，你会在几次以上找到它们，例如电子邮件地址。同样，您可以安全地假设ID永远不会变为负数，因此从长远来看，使ID列无符号将受益。与任何DBA（数据库管理员）交谈，他们会多次告诉你这样做。

It is always a good idea to add index's to your all ID's and data you 'find_by' on more then a few occasions e.g. email_address. Likewise you can safely assume that ID will never go into negative, so making ID columns Unsigned will benefit in the long run. Speak to any DBA (Database Administrator) and they will, more times than not tell you to do this.

目前你很可能对你的所有ID列都有这样的东西...

Currently you most likely have something like this for all your ID Columns...

t.integer :column_name, :null => false

或...

t.references :column_name, :null => false

只需将此更改为...

By simply changing this to...

t.column :column_name, 'integer unsigned', :null => false

你会看到微小的增长。

索引很简单......

Index's are simple...

add_index :reviews, [:column_id, :column_type] # Polymorphic
add_index :reviews, :column_id # Standard

Rails API 应该为您提供所需的一切。

The Rails API should give you all you need to know.

Peepcode真正得到教学视频对我来说是一个很好的见解，非常值得花费12美元和37分钟的时间。像 MetaWhere 这样的宝石也可以为你提供帮助。

Peepcode have a really get tutorial video that was a great insight to me and well worth the $12 and 37 minutes of your time. There are Gems like MetaWhere which may be able to help you as well.

最重要的是，在Rails 3及更高版本中，是ActiveRelations。这是查询仅在需要时执行的位置。例如，关闭User.all，你可以调用User.scoped，当View中的迭代发生时，执行中的SQL。强大的东西和Rails的未来。

Most importantly, in Rails 3 and above, is ActiveRelations. This is where Queries are only executed when required. For example instead off User.all you could call User.scoped and when the iteration in the View occurs the SQL in executed. Powerful stuff and the Future of Rails.

告诉我们你是如何继续...最好的。

Let us know how you get on... All the best.

这篇关于Rails 3数据库索引和其他优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！