mysql - 加速大型数据库的 "SQL_CALC_FOUND_ROWS"SQL 查询

Question

我正在使用名为 feedwordpress 的 WordPress 插件为了在 WordPress 上运行一个类似行星的网站(参见 here )。

这个插件很棒，除了一件事之外 - 它每周左右都会占用我的(VPS)服务器提交一次。

在最近与网络管理员的电子邮件交流中，他写道:

It does look like the increased mysql resource usage is being caused by slow queries being run by r-bloggers.com. Here is a copy of some of the logs that are being produced. You would need to optimize this site and database further to have it running as efficiently as possible. If these changes have already been made, your best option would be to look into a large upgrade for your VPS due to the high level or resources and traffic that your site needs and sees.

以下是日志:

# Time: 110614 16:11:35
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 104 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 1570, 10;
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 237 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (guid = 'http://www.r-bloggers.com/?guid=235cbefa4424d0cdb7b6213f15a95ded') OR (MD5(guid) = '235cbefa4424d0cdb7b6213f15a95ded')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:18:13
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 257 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (guid = 'http://www.r-bloggers.com/?guid=956e208f101562f6654e88e9711276e4') OR (MD5(guid) = '956e208f101562f6654e88e9711276e4')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 570, 10;
# Time: 110614 16:19:02
# User@Host: rblogger_rblogr[rblogger_rblogr] @ localhost []
# Query_time: 83 Lock_time: 0 Rows_sent: 0 Rows_examined: 54616
SELECT SQL_CALC_FOUND_ROWS wp_rb_posts.* FROM wp_rb_posts WHERE 1=1 AND ((guid = '6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (guid = 'http://www.r-bloggers.com/?guid=6c589e661f03a67b0529fab2f080bfd3') OR (MD5(guid) = '6c589e661f03a67b0529fab2f080bfd3')) AND wp_rb_posts.post_type IN ('post', 'page', 'attachment', 'revision', 'nav_menu_item') AND (wp_rb_posts.post_status = 'publish' OR wp_rb_posts.post_status = 'future' OR wp_rb_posts.post_status = 'draft' OR wp_rb_posts.post_status = 'pending' OR wp_rb_posts.post_status = 'trash' OR wp_rb_posts.post_status = 'auto-draft' OR wp_rb_posts.post_status = 'inherit' OR wp_rb_posts.post_status = 'private') ORDER BY wp_rb_posts.post_date DESC LIMIT 1440, 10;

这引出了我的问题 - 该日志中的哪些内容可能向我表明正在发生的事情(为什么此类查询需要这么长时间？)？是否可以优化这些？如果是这样，怎么办？

谢谢，塔尔

Answer 1

熟悉 WordPress，我认为您应该附加到有关该主题的正在进行的票证之一，或者创建一个新的票证。

http://core.trac.wordpress.org/ticket/10469

http://core.trac.wordpress.org/ticket/10964

http://core.trac.wordpress.org/search?q=SQL_CALC_FOUND_ROWS

或者，放弃并使用更适合该任务的工具。