ruby on rails 优化rails查询

如果没有经过优化， 

 
 

  
  position = company.positions  
 
 

 
 

  
  会产生: 
 
 
 
 

  
  

 
 

 
 

  
  13:50:15 DEBUG:   MarketModule Load (0.1ms)  SELECT `market_modules`.* FROM `market_modules`  WHERE `market_modules`.`market_id` = 1 
 
 

 
 

  
  13:50:15 DEBUG:   Price Load (0.2ms)  SELECT  `prices`.* FROM `prices`  WHERE `prices`.`market_module_id` = 1 AND ( date = '2015-08-06' )  ORDER BY `prices`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.4ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 1  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 3  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.4ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 2  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 

 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 4  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 7  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 5  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 

 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 6  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 11  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 

 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 8  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 

 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 9  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 
 
 

  
  13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 10  ORDER BY `positions`.`id` ASC LIMIT 1 
 
 

所以，我们需要使用 includes 来 预先加载（eager load  ）

 
 

  
  a = Company.includes(:positions).first
 
 

 
 

  
  14:06:41 DEBUG:   Company Load (0.1ms)  SELECT  `companies`.* FROM `companies`   ORDER BY `companies`.`id` ASC LIMIT 1 
 
 
 
 

  
  14:06:41 DEBUG:   Position Load (0.2ms)  SELECT `positions`.* FROM `positions`  WHERE `positions`.`company_id` IN (1) 
 
 

 
 

  
  

 
 
 
 

  
  14:07:19 DEBUG:   Position Load (2.0ms)  SELECT `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 1 AND (market_module_id = 1)
 
 
 
 

  
  

 
 

 
 

  
  a.positions.where('market_module_id = ?', 1)
 
 
 
 

  
  

 
 
 
 

  
  a.positions.select { |position| position.market_module_id == 1 }
 
 

所以， 针对之前最耗费查询的代码：

 
 

  
  # 以下内容，假设 date, market_module 变量都是存在的. 
 
 

 
 

  
  companies = Company.all
 
 
 
 

  
      position = company.positions.where(' date = ? and market_module_id = ?', date, market_module.id).first
 
 
 
 

  
  companies.each do | position |
 
 
 
 

  
  end
 
 

我们需要把它改成：

 
 

  
  # 这里使用了 includes 来预先加载
 
 

 
 

  
  companies = Company.includes(:positions).all 
 
 

 
 

  
  companies.each do | position |
 
 
 
 

  
      # 这里使用 操作 纯ruby object的方式来操作， 而不是使用 active record的方法。
 
 
 
 

  
      position = company.positions.select{ |position| position.date == date && market_module_id == market_module.id }[0]
 
 

 
 

  
  end
 
 
 
 

  
  

 
 

可以看到， 后台的log中，查询只有两条：

 
 

  
  11:38:07 DEBUG:   CACHE (0.0ms)  SELECT `companies`.* FROM `companies` 
 
 

 
 

  
  11:38:07 DEBUG:   Position Load (1.5ms)  SELECT `positions`.* FROM `positions`  WHERE `positions`.`company_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)