Rails 3数据库索引和其他优化。

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.

我已经为rails应用开发了一段时间了，但不幸的是，我的应用程序中没有一个有大量的数据或流量。但现在我有了一个正在增长的动力。所以我首先要做的就是扩展和优化我的应用。

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?

这是否意味着我的索引没有应用到我已有的数据上?它们将只被添加到新条目中吗?

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

此外，我还在研究其他扩展解决方案，比如缓存memcached，以及简化查询等等。

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

如果有人能指出一些好的资源来优化我的rails 3应用程序，我会非常感激的!

Thanks!

谢谢!

EDIT:

编辑:

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?

感谢所有关于数据库索引的精彩答案!在优化和扩展我的应用程序方面，我还应该关注什么?Memcached吗?在优化方面，什么具有最佳的性能提升/努力比率?

2 个解决方案

#1

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和数据中总是一个好主意，在更多的情况下，例如email_address。同样，您可以安全地假设ID永远不会变为负值，因此从长远来看，使ID列无符号将会带来好处。与任何DBA(数据库管理员)交谈，他们会多次告诉您这样做。

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

目前，您很可能对所有ID列都有这样的东西……

t.integer :column_name, :null => false

or...

还是……

t.references :column_name, :null => false

By simply changing this to...

通过简单地将其改为……

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

You will see a tiny increase.

你会看到一个微小的增长。

Index's are simple...

索引是很简单…

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

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

Rails API应该提供所有您需要知道的信息。

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.

Peepcode有一个很好的教程视频，对我来说很有启发，值得你花12和37分钟的时间。有一些宝石像元，可能也能帮助你。

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.

在Rails 3和上面，最重要的是活动。只有在需要时才执行查询。例如，关闭用户。所有你能调用的用户。作用域，当视图中的迭代发生时，执行中的SQL。强大的东西和Rails的未来。

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

让我们知道你的进展如何……愿一切都好！

#2

You wrote:

你写的:

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.

但是，当我通过迁移将它们添加到数据库中时，只需要几秒钟就可以添加它们。出于某种原因，我认为他们必须检查我的所有条目(其中有数千个)并对它们进行索引。

Indexing wont take long unless you have millions of records. DB Indexing is just a sort, and recording that sort to be used later.

索引不会花很长时间，除非你有数百万条记录。DB索引只是一种类型，稍后将使用它进行记录。

Your index applied to both new and existing records.

您的索引应用于新记录和现有记录。

UPDATE

更新

Biggest bang for your buck:

最大的收获:

Move long running processes to delayed_job (or similar)
将长时间运行的进程移动到delayed_job(或类似)
Get rid of n+1 queries
去掉n+1查询

Memcache is nice, but complicates your app, and you typically don't get the boost until your app is db read bound.

Memcache很好，但是会让你的应用程序变得复杂，通常只有在你的应用程序被db读绑定时才会得到提升。

#1