Rails宝典之第二十三式: counter cache

最新推荐文章于 2021-02-12 10:33:13 发布

最新推荐文章于 2021-02-12 10:33:13 发布 · 185 阅读

文章标签：

#Cache #Rails #SQL #ActiveRecord #Ruby

Ruby 专栏收录该内容

317 篇文章

订阅专栏

本文介绍Rails中通过添加计数缓存字段优化has_many关联查询性能的方法，利用Eager Loading减少查询次数，并通过Counter Cache进一步减少数据库负载。

请看[url=http://hideto.iteye.com/blog/70236]Rails里的Magic Column Names[/url]

这次就是讲用_count字段来缓存has_many的计数

看Project和Task的例子:
[code]
<h1>Projects</h1>

<table>
<% for project in @projects %>
<tr>
<td><%= link_to project.name, poject_path(project) %></td>
<td><small>(<%= pluralize project.tasks.size, 'task' %>)</small></td>
</tr>
<% end %>
</table>
[/code]
上面的页面代码对所有的@projects显示tasks.size，看下log:
[code]
SQL (0.006385) SELECT count(*) AS count_all FROM tasks WHERE (tasks.project_id = 326)
SQL (0.000220) SELECT count(*) AS count_all FROM tasks WHERE (tasks.project_id = 327)
SQL (0.000383) SELECT count(*) AS count_all FROM tasks WHERE (tasks.project_id = 328)
SQL (0.000197) SELECT count(*) AS count_all FROM tasks WHERE (tasks.project_id = 329)
SQL (0.000215) SELECT count(*) AS count_all FROM tasks WHERE (tasks.project_id = 330)
[/code]
上面显示了对每个project都使用SQL来count tasks，我们采用eager loading看看能否改进性能:
[code]
class ProjectsController < ApplicationController
def index
@projects = Project.find(:all, :include => :tasks)
end
end
[/code]
再来看看log:
[code]
Project Lood Incluing Associations (0.000954) SELECT projects.'id' AS t0_r0, projects.'name' AS t0_r1, tasks.'id'
AS t1_r0, tasks.'name' AS t1_r1, tasks.'project_id' AS t1_r2 FROM projects LEFT OUTER JOIN tasks ON tasks.project
_id = projects.id
[/code]
我们看到，使用eager loading确实只用一条SQL语句就完成工作，但是缺点是把tasks表所有的字段信息都取出来了，很多信息是
没有用的。

我们来看看更好的解决方案:
[code]
ruby script/generate migration add_tasks_count
[/code]
我们新建一个migration，给projects表添加一个叫tasks_count的列:
[code]
class AddTasksCount < ActiveRecord::Migration
def self.up
add_column :projects, :tasks_count, :integer, :default => 0

Project.reset_column_information
Project.find(:all).each do |p|
p.update_attribute :tasks_count, p.tasks.length
end
end

def self.down
remove_column :projects, :tasks_count
end
end
[/code]
我们还需要告诉Task类开启counter cache:
[code]
class Task < ActiveRecord::Base
belongs_to :projects, :counter_cache => true
end
[/code]
好了，我们把ProjectsController的index方法改回lazy loading，刷新页面，再看看log:
[code]
Project Lood (0.000295) SELECT * FROM projects
[/code]
可以看出，现在数据库只查询projects表的所有字段，查询时间也比前面的方案都短，性能大大提升。