我怎样才能加快这个MySQL查询

Question

有人可以帮我这个查询吗？它工作正常，但需要太多时间。我确定这个问题是tb_activities中的OR语句太多。有没有人有任何想法使这个查询更快？

SELECT tb_posts.* 
FROM tb_posts 
WHERE EXISTS (SELECT c_id 
       FROM tb_users 
       WHERE tb_posts.c_uid = tb_users.c_id 
         AND tb_users.c_tokens > 0) 
     AND NOT EXISTS (SELECT c_id 
         FROM tb_activities 
         WHERE tb_posts.c_url = tb_activities.c_url 
           AND tb_activities.c_category = 'gplus' 
           AND (tb_activities.c_uid LIKE '%,6x1,%' 
            OR tb_activities.c_uid LIKE '%,6x1' 
            OR tb_activities.c_uid LIKE '6x1,%' 
            OR tb_activities.c_uid = '6x1')) 
     AND NOT EXISTS (SELECT c_id 
         FROM tb_blacklist 
         WHERE tb_posts.c_url LIKE Concat('%', tb_blacklist.c_url 
                , '%') 
           AND tb_blacklist.c_times > 2 
           AND tb_blacklist.c_category = 'gplus') 
     AND tb_posts.c_category = 'classic' 
     AND tb_posts.c_status = 'run' 
     AND tb_posts.c_nogplus = 0 
GROUP BY tb_posts.c_url 
ORDER BY tb_posts.c_cost DESC, 
      tb_posts.c_date DESC 
LIMIT 30 

Answer 1

我在这里做的是重写第一个WHERE EXISTS ...。为什么？ Here就是答案。另一个有趣的阅读是this。所以你可以考虑进一步重写查询。不幸的是我现在没有更多的时间了。但主要性能提升您将通过添加（复合）索引无论如何得到。将索引放在JOIN所基于的列或WHERE子句中经常使用的列上。

SELECT tb_posts.* 
FROM tb_posts 
INNER JOIN tb_users ON tb_posts.c_uid = tb_users.c_id 
WHERE tb_users.c_tokens > 0 
     AND NOT EXISTS (SELECT c_id 
         FROM tb_activities 
         WHERE tb_posts.c_url = tb_activities.c_url 
           AND tb_activities.c_category = 'gplus' 
           AND (tb_activities.c_uid LIKE '%,6x1,%' 
            OR tb_activities.c_uid LIKE '%,6x1' 
            OR tb_activities.c_uid LIKE '6x1,%' 
            OR tb_activities.c_uid = '6x1')) 
     AND NOT EXISTS (SELECT c_id 
         FROM tb_blacklist 
         WHERE tb_posts.c_url LIKE Concat('%', tb_blacklist.c_url, '%') 
           AND tb_blacklist.c_times > 2 
           AND tb_blacklist.c_category = 'gplus') 
     AND tb_posts.c_category = 'classic' 
     AND tb_posts.c_status = 'run' 
     AND tb_posts.c_nogplus = 0 
GROUP BY tb_posts.c_url 
ORDER BY tb_posts.c_cost DESC, 
      tb_posts.c_date DESC 
LIMIT 30 

而且你可能想了解EXPLAIN，所以你知道使用哪些索引（或不使用）。

作为一个旁注，彼得·凯斯关于改变WHERE条款顺序的提示是无稽之谈。查询优化器无论如何处理这个。