MySQL 查询太慢？

Question

所以我有一个查询:

SELECT EWRporta2_articles.*, xf_thread.*, xf_forum.*, xf_user.*, xf_post.message,
    IF(NOT ISNULL(xf_user.user_id), xf_user.username, xf_thread.username) AS username
FROM EWRporta2_articles
    INNER JOIN xf_thread ON (xf_thread.thread_id = EWRporta2_articles.thread_id)
    INNER JOIN xf_forum ON (xf_forum.node_id = xf_thread.node_id)
    INNER JOIN xf_post ON (xf_post.post_id = xf_thread.first_post_id)
    LEFT JOIN xf_user ON (xf_user.user_id = xf_thread.user_id)
WHERE EWRporta2_articles.article_date < 1417987751
    AND xf_thread.discussion_state = 'visible'
ORDER BY EWRporta2_articles.article_date DESC
LIMIT 0, 5

此查询在 0.0012 秒内执行...这很好。此查询的作用是查询文章列表，然后将它们链接到我的论坛上的主题。

但是，我正在尝试稍微改变查询。虽然上面的查询要求线程存在文章行。我想找到链接到特定文章或存在于特定论坛节点 ID 中的线程。因此，即使某个线程不存在文章行，如果它具有特定的 node_id，它仍然会显示。这是我的查询:

SELECT EWRporta2_articles.*, xf_thread.*, xf_forum.*, xf_user.*, xf_post.message,
    IF(EWRporta2_articles.article_date IS NULL, xf_thread.post_date, EWRporta2_articles.article_date) AS article_date,
    IF(NOT ISNULL(xf_user.user_id), xf_user.username, xf_thread.username) AS username
FROM xf_thread
    LEFT JOIN EWRporta2_articles ON (EWRporta2_articles.thread_id = xf_thread.thread_id)
    INNER JOIN xf_forum ON (xf_forum.node_id = xf_thread.node_id)
    INNER JOIN xf_post ON (xf_post.post_id = xf_thread.first_post_id)
    LEFT JOIN xf_user ON (xf_user.user_id = xf_thread.user_id)
WHERE ( xf_thread.node_id IN ('66','78') OR EWRporta2_articles.article_date IS NOT NULL )
    AND IF(EWRporta2_articles.article_date IS NULL, xf_thread.post_date, EWRporta2_articles.article_date) < 1417987751
    AND xf_thread.discussion_state = 'visible'
ORDER BY article_date DESC
LIMIT 0, 5

此查询的问题是它在 0.5683 秒内执行。

我可以做些什么来提高这里的性能吗？

Answer 1

首先 - 永远不要这样做:

AND IF(EWRporta2_articles.article_date IS NULL, xf_thread.post_date
           , EWRporta2_articles.article_date) < 1417987751

使用函数可以防止任何数据库使用索引来优化这样的表达式。

将其重写为以下形式:

EWRporta2_articles.article_date IS NULL AND xf_thread.post_date < 1417987751
OR
EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751

<小时/>

现在使用 bool 代数定律来简化查询条件，特别是使用以下定律:AND 对 OR 的分布性:http://en.wikipedia.org/wiki/Boolean_algebra
X 与 ( Y 或 Z ) => X 与 Y 或 X 与 Z



如果您将此法律应用于这种情况:

WHERE 
( xf_thread.node_id IN ('66','78') OR EWRporta2_articles.article_date IS NOT NULL )
AND
(
    EWRporta2_articles.article_date IS NULL AND xf_thread.post_date < 1417987751
    OR
    EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751
)

您将得到:

xf_thread.node_id IN ('66','78') AND  EWRporta2_articles.article_date IS NULL AND xf_thread.post_date < 1417987751
OR
xf_thread.node_id IN ('66','78') AND EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751
OR
EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date IS NULL AND xf_thread.post_date < 1417987751
OR
EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751

<小时/>

第三个条件始终为假，因此我们可以跳过它:

EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date IS NULL ... 

<小时/>

第二个和第四个:

xf_thread.node_id IN ('66','78') AND EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751
OR
EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751

可以简化为:

EWRporta2_articles.article_date IS NOT NULL AND EWRporta2_articles.article_date < 1417987751

它可以进一步简化为:

EWRporta2_articles.article_date < 1417987751

<小时/>

最终我们会得到:

WHERE (
     xf_thread.node_id IN ('66','78') 
     AND  
     EWRporta2_articles.article_date IS NULL 
     AND    
     xf_thread.post_date < 1417987751
     OR
     EWRporta2_articles.article_date < 1417987751
  )
  AND xf_thread.discussion_state = 'visible'

<小时/>

现在将查询分为两个单独的子查询，然后以这种方式合并它们的结果:

SELECT *
FROM (
     SELECT ......
     JOIN ... JOIN ... JOIN ...
     WHERE 
         xf_thread.node_id IN ('66','78') 
         AND EWRporta2_articles.article_date IS NULL 
         AND xf_thread.post_date < 1417987751
         AND xf_thread.discussion_state = 'visible'
     -- ORDER BY article_date DESC
     LIMIT 0, 5
) q1
UNION
SELECT *
FROM (
     SELECT ......
     JOIN ... JOIN ... JOIN ...
     WHERE 
         EWRporta2_articles.article_date < 1417987751
         AND xf_thread.discussion_state = 'visible'
     ORDER BY article_date DESC
     LIMIT 0, 5
) q2
ORDER BY article_date DESC
LIMIT 0, 5

请注意，在第一个子查询中，子句 ORDER BYarticle_date DESC 被跳过 - 因为条件为:AND EWRporta2_articles.article_date IS NULL，因此日期始终为 NULL ，我们可以跳过多余的排序操作，因为这是浪费时间。