Rails 误用 find_each 引起的性能问题

luolinae86 · 2017年05月09日 · 最后由 luolinae86 回复于 2017年05月12日 · 4554 次阅读

each 方法

SendLog.all.each do | log |
  # do something……
end

Model.all.each 会使 Active Record 一次性取回整个数据表,为每条记录创建模型对象,并把整个模型对象数组保存在内存中。 当表的记录很大的时候,整个模型对象数组需要占用的空间可能会超过应用服务器的内存容量,导致应用或者服务器崩溃。

find_each 方法

SendLog.find_each do | log |
  # do something……
end

find_each 方法检索一批记录,然后逐一把每条记录作为模型传入块。在上面的例子中,find_each 方法取回 1000 条记录(find_each 和 find_in_batches 方法都默认一次检索 1000 条记录),然后逐一把每条记录作为模型传入块。这一过程会不断重复,直到完成所有记录的处理

业务及应用场景

数据表

  • 数据记录大于 3000 万条
  • 为 record_date 和 user_id 创建了索引

查询

查询语句样例:

2.2.0 :043 > send_logs = SendLog.where("record_date >=? and record_date <=? and user_id in (?)",20170409,20170508,[1,2,3,4])

2.2.0 :043 > send_logs.count
[Shard: slave1]   (15.6ms)  SELECT COUNT(*) FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4))
 => 1732 

2.2.0 :051 >   send_logs.find_each do |log|
2.2.0 :052 >   end
[Shard: slave1]  SendLog Load (27.7ms)  SELECT  `send_logs`.* FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4))  ORDER BY `send_logs`.`id` ASC LIMIT 1000
[Shard: slave1]  SendLog Load (61894.6ms)  SELECT  `send_logs`.* FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4)) AND (`send_logs`.`id` > 235181319)  ORDER BY `send_logs`.`id` ASC LIMIT 1000
 => nil 

以上产生的 SQL 语句可以看出,find_each 每次 limit 1000 条记录,由于 send_logs 的记录数为 1732 条,所以 send_logs.find_each 将会产生 2 条 SQL 查询语句

第一次 SQL 操作,执行时间,花费 27.7ms,而第二次查询操作耗时 61894.6ms

慢查询分析

SendLog Load (61894.6ms)  SELECT  `send_logs`.* FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4)) AND (`send_logs`.`id` > 235181319)  ORDER BY `send_logs`.`id` ASC LIMIT 1000

以上耗时的 SQL,在前一条的基础上面,增加 AND (send_logs.id > 235181319) 的限制条件,用 explain 命令来查看 SQL 语句的执行情况,可以看到,该条查询没有用到索引,一次操作扫描了 1200 万条记录

mysql>explain SELECT  `send_logs`.* FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4)) AND (`send_logs`.`id` > 245317804)  ORDER BY `send_logs`.`id` ASC LIMIT 1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: send_logs
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: 
         rows: 12233849
        Extra: Using where

而第一条语句是可以用到索引的

mysql>explain SELECT  `send_logs`.* FROM `send_logs` WHERE (record_date >=20170409 and record_date <=20170508 and user_id in (1,2,3,4)) ORDER BY `send_logs`.`id` ASC LIMIT 1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: send_logs
         type: range
possible_keys: index_send_logs_on_user_id
          key: index_send_logs_on_user_id
      key_len: 5
          ref: 
         rows: 14292
        Extra: Using index condition; Using where; Using filesort

实际的业务场景中,根据条件查询出来的数据量,最多只有数千条,所以直接用 each 方法,取代 find_each 方法,这样就一次性把少量的数据加载到内存中,对记录进行访问时,提升系统性能。

2.2.0 :066 > time = Benchmark.ms {
2.2.0 :067 >       send_logs.each do |log|
2.2.0 :068 >       end
2.2.0 :069?>   }
 => 0.196017324924469 

总结及改进

  • find_each 生成的 SQL 语句,会忽略用户自己的:order 和 :limit 条件,固定用 ordey by table.id asc limit 1000
  • find_each 方法用于大量记录的批处理,记录数量很大以至于不能一次加载到内存,比如需要遍历全表记做数据处理。
  • 当遇到慢查询的时候,用 explain 命令,查看是否索引命中

之前也遇到了这个问题,然后自己写了个可以指定 order by field_column 的 find_in_batches。find_each 实际调用的是 find_in_batches。假设你的场景不是只有 1700+ 条记录,而是有 10w 条记录,你会怎么做? 你的分析是对的,是由于 (send_logs.id > 245317804) ORDER BY send_logs.id 这里的 sql 使得 MySQL 没用你的索引 (那是联合索引吗) 而用了 primary 导致了问题。实际上是 ORDER BY,导致你的索引没被使用。record_date 应该是日期吧?如果 你的 ORDER BY record_date 也许就用到索引了

find_each 方法用于大量记录的批处理,记录数量很大以至于不能一次加载到内存,比如需要遍历全表记做数据处理

这里 如果你的查询是有多个条件,导致不能使用索引,而使用了 key: PRIMARY , 同样会有性能问题,导致每次的 find_each 查询即使是 limit 1000 也很慢很慢 踩过坑的路过

mysql index 自动选择的问题,常见的坑,可以在查询里面加 FORCE INDEX 来解决

pathbox 回复

这位同学的诠释很好。 同时给作者点个赞,分析的也很透彻。

@pathbox 谢谢你回复及分析。 目前的业务使用场景

  • 的确是用到了多列索引,由于和具体业务相关,没有把所有的索引项全部列出来。
  • record_date 自己构建的 int 型 值为 Time.now.strftime("%Y%m%d").to_i 比如 20170509,方便用当前日期来做索引,从而使这种条件查询可以直接用到多列索引,SendLog.where("record_date=?and user_id=?",20170509,1)

你在上面提到的,如果返回记录在 10W+ 的场景,如果用默认的 find_each 一样会存在索引失效的问题,的确如你所说,需要自己指定 order by field_column。

对你的回复,再次表示感谢。

find_each 会忽略 order 吧 楼主说的是如何指定

luolinae86 回复

看上去,用 int 来代替 datetime,性能有了很大的提升?

你们是在什么规模的情况下改用 int 存储的?有没有这方面经验可以分享?

zlx_star 回复

用 int 代替 datetime,主要是为了满足这种场景,比如要查询 2017 年 5 月 12 号的记录,则可以用条件 record_date = 20170512,如果对 datetime 创建索引,则直接可以索引命中。 如果用 datetime 类型,则需要做条件判断,比如 record_date > "2017-05-12 00:00:00" and record_date < "2017-05-12 23:59:59"

luolinae86 回复

所以你们其实是用 int 记录了日期,作为日期快速查询,更精确的时间并没有用 int 表示?

zlx_star 回复

是的,更精确的还是用的 datetime 类型

需要 登录 后方可回复, 如果你还没有账号请 注册新账号