为什么ActiveRecord的destroy_all要花这么长时间？ [英] Why is ActiveRecord destroy_all taking so long?

问题描述

我有一个简单的Rails应用程序上的MySQL 5.5和Ruby 1.9.3运行文章和评论扶手3.2.12：

I have a simple rails app with articles and comments running on MySQL 5.5, Ruby 1.9.3 and rails 3.2.12:

class Article < ActiveRecord::Base                                                                                   
  attr_accessible :body, :title
  has_many :comments
end   

class Comment < ActiveRecord::Base
  attr_accessible :content
  belongs_to :article
end

我已经生成大量的注释的文章，我现在试图删除它们都在铁轨控制台：

I have generated lots of comments for an article, and am now trying to delete them all in the rails console:

$ rails c 
Loading development environment (Rails 3.2.12)
[1] pry(main)> a = Article.find(1)
   (2.0ms)  SET SQL_AUTO_IS_NULL=0
  Article Load (8.0ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`id` = 1 LIMIT 1
=> #<Article id: 1, title: "Test", body: "---\n- Est vel provident. Laboriosam dolor asperiore...", created_at: "2013-05-17 09:54:54", updated_at: "2013-05-21 14:52:18">
[2] pry(main)> require 'benchmark'
[3] pry(main)> puts Benchmark.measure { a.comments.destroy_all }
  Comment Load (896.0ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`article_id` = 1
  EXPLAIN (2.0ms)  EXPLAIN SELECT `comments`.* FROM `comments` WHERE `comments`.`article_id` = 1
EXPLAIN for: SELECT `comments`.* FROM `comments`  WHERE `comments`.`article_id` = 1
+----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+
| id | select_type | table    | type | possible_keys | key        | key_len | ref   | rows  | Extra       |
+----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | comments | ref  | article_id    | article_id | 5       | const | 48186 | Using where |
+----+-------------+----------+------+---------------+------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

  SQL (1.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 2
  SQL (2.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 3
  SQL (1.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 4
  SQL (1.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 5
  SQL (1.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 6
  SQL (5.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 7
  SQL (2.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 8
  SQL (2.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 9

. . .
  SQL (0.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 37360
  SQL (0.0ms)  DELETE FROM `comments` WHERE `comments`.`id` = 37361

最后一个查询删除最后一个注释，然后处理挂在那儿了的非常的很长一段时间，最后才返回，并承诺：

The last query is deleting the last comment, and the process then hangs there for a very long time before finally returning and committing:

   (1.9ms)  COMMIT
690.380000   1.390000 691.770000 (693.885877)

SHOW PROCESSLIST 确认，没有锁：

mysql> show processlist;
+----+----------+-----------+------------------+---------+------+-------+------------------+
| Id | User     | Host      | db               | Command | Time | State | Info             |
+----+----------+-----------+------------------+---------+------+-------+------------------+
|  6 | bloguser | localhost | blog_development | Query   |    0 | NULL  | show processlist |
|  7 | bloguser | localhost | blog_development | Sleep   |  459 |       | NULL             |
+----+----------+-----------+------------------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

DELETE_ALL 与相关：摧毁或相关：DELETE_ALL 显示了非常类似的行为。

delete_all with dependent: :destroy or dependent: :delete_all show a very similar behaviour.

的普遍看法似乎是，这个问题与 destroy_all 是它实例化的所有对象，并删除它们一个接一个，但它看起来并不像它的问题就在这里。什么是这么长时间的所有删除之后一下来处理已经被执行了，以前 COMMIT 最后叫什么名字？

The popular belief seems to be that the issue with destroy_all is that it instantiates all the objects and deletes them one by one, but it doesn't look like it's the problem here. What is taking so long to process after all the DELETEs have been executed, and before COMMIT is finally called?

推荐答案

挖掘这种更深层次，现在看来，这是删除从评论阵列需要很长时间。被删除的记录，然后从该阵列<一个除去href="https://github.com/rails/rails/blob/master/activerecord/lib/active_record/associations/collection_association.rb#L494"相对=nofollow>这里。

Digging into this deeper, it seems it is the deletion from the comments array that takes very long. Records that are deleted are then removed from the array here.

与大阵模拟，我们得到同样的缓慢行为：

Simulating this with a large array, we get the same slow behaviour:

1.9.3-p194 :001 > require 'benchmark'; require 'ostruct'
 => true 
1.9.3-p194 :002 > i = 0; a = []
 => [] 
1.9.3-p194 :003 > 35_000.times { i+=1; a << OpenStruct.new(value: i) }
 => 35000 
1.9.3-p194 :004 > puts Benchmark.measure { a.each { |i| a.delete(i) } }
623.560000   0.820000 624.380000 (625.244664)

ActiveRecord的很可能被优化办阵列＃明确中的情况下 destroy_all ...

ActiveRecord could probably be optimized to do a Array#clear in the case of a destroy_all...

这篇关于为什么ActiveRecord的destroy_all要花这么长时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！