问题描述
我有一个简单的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 DELETE
s have been executed, and before COMMIT
is finally called?
推荐答案
挖掘这种更深层次,现在看来,这是删除从评论
阵列需要很长时间。被删除的记录,然后从该阵列这里。
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要花这么长时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!