诗歌rails之counter cache
这次就是讲用_count字段来缓存has_many的计数
看Project和Task的例子:
上面的页面代码对所有的@projects显示tasks.size,看下log:
上面显示了对每个project都使用SQL来count tasks,我们采用eager loading看看能否改进性能:
我们看到,使用eager loading确实只用一条SQL语句就完成工作,但是缺点是把tasks表所有的字段信息都取出来了,很多信息是
没有用的。
我们来看看更好的解决方案:
我们新建一个migration,给projects表添加一个叫tasks_count的列:
我们还需要告诉Task类开启counter cache:
好了,我们把ProjectsController的index方法改回lazy loading,刷新页面,再看看log:
可以看出,现在数据库只查询projects表的所有字段,查询时间也比前面的方案都短,性能大大提升。
看Project和Task的例子:
- <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>
- 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)
- class ProjectsController < ApplicationController
- def index
- @projects = Project.find(:all, :include => :tasks)
- end
- end
再来看看log:
- 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
没有用的。
我们来看看更好的解决方案:
- ruby script/generate migration add_tasks_count
- 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
- class Task < ActiveRecord::Base
- belongs_to :projects, :counter_cache => true
- end
- Project Lood (0.000295) SELECT * FROM projects
莫愁前路无知己,天下无人不识君。