Back

干货 - 优化rails 查询

发布时间: 2015-08-06 05:50:00

如果没有经过优化， 

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.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` = 3  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` = 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` = 7  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 
13:50:15 DEBUG:   Position Load (0.3ms)  SELECT  `positions`.* FROM `positions`  WHERE `positions`.`company_id` = 11  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) 


a.positions.where('market_module_id = ?', 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.select { |position| position.market_module_id == 1 }

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

# 以下内容，假设 date, market_module 变量都是存在的. 
companies = Company.all
companies.each do | position |
    position = company.positions.where(' date = ? and market_module_id = ?', date, market_module.id).first
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) 

Back