Mysql性能:一个总是停滞不前/缓慢的大查询

Question

我有以下查询，因为“等待表级锁定”，这总是会使整个网站陷入瘫痪：

/*IPS\Patterns\_ActiveRecordIterator::count:246*/ SELECT SQL_CALC_FOUND_ROWS forums_posts . * , forums_topics . * 
FROM  `forums_posts` 
LEFT JOIN  `forums_topics` ON forums_posts.topic_id = forums_topics.tid
LEFT JOIN  `core_permission_index` ON core_permission_index.app =  'forums'
AND core_permission_index.perm_type =  'forum'
AND core_permission_index.perm_type_id = forums_topics.forum_id
LEFT JOIN  `forums_forums` ON forums_topics.forum_id = forums_forums.id
WHERE (
NULLIF( forums_topics.moved_to,  '' ) IS NULL
)
AND (
forums_forums.password IS NULL 
OR (
(
FIND_IN_SET( 2, forums_forums.password_override )
)
)
)
AND (
forums_forums.can_view_others =1
OR forums_topics.starter_id IS NULL
)
AND forums_forums.min_posts_view <=0
AND queued =0
AND forums_topics.approved =1
AND (
(
(
FIND_IN_SET( 2, perm_2 )
)
)
OR perm_2 =  '*'
)
ORDER BY post_date DESC 
LIMIT 185725 , 25

我可以做些什么来提高这个查询的性能，而不是在它完成之前杀死进程/等待10分钟？

Answer 1

如果这是一个“爬虫”，正在冲洗你的网站，你真的必须摆脱OFFSET的使用。或者使用一些过滤器来有效地防止DOS (拒绝服务)攻击。至少告诉搜索机器人，以避免你的网站。(这可能是简单的答案。)

让我们真心实意。LIMIT 185725 , 25 --这是第7429页。你真的翻了那么多页吗？

你有什么有这么多行，但没有方便的方式“搜索”或“合并”数据？

其他问题：

OR是昂贵的--它阻止了索引的使用。

LEFT JOIN通常需要查看“正确”的表，但是当没有任何东西的时候却很高兴。这可能比普通的JOIN性能差。(然而，业务逻辑可能需要它。)

由于查询的复杂性，SQL_CALC_FOUND_ROWS可能要花费几乎与不使用LIMIT的所有行相同的时间。请注意搜索引擎曾经说过“大约18万”的搜索结果，甚至放弃了这一点。也许你的应用程序也应该这样做。

当然，有一种更简单的方法来做NULLIF( forums_topics.moved_to, '' ) IS NULL。

你用什么引擎做桌子？如果使用MyISAM，则切换到InnoDB；这可能会消除“表级锁”。

Answer 2

请检查表类型是否为InnoDB。如果是MyISAM，则将其转换为InnoDB。当其他线程/查询正在更新表中的任何行时，MyISAM无法读取表中的数据。它使用表级锁。

InnoDB使用MVCC在更新/插入/删除表时启用选择。