mysql - 缓慢的 mysql 查询，包括 Left Join 和 Selects

Question

SELECT SQL_CALC_FOUND_ROWS null as `rows` , 
   (SELECT count(*) FROM `ci_user_smiley` where post_id = P.post_id) as smiley, 
   (SELECT count(*) FROM `ci_user_tag` where post_id = P.post_id) as tag, 
   (SELECT count(*) FROM `ci_comments` where post_id = P.post_id) as comment_count, 
    P.*, U.point_1 + U.point_2 + U.point_3 as `total`, F.*, Fi.*, U.* 

FROM `ci_posts` P 

LEFT JOIN `ci_users` U 
     ON P.`user_id` = U.`user_id`
LEFT JOIN `ci_filters` F
     ON P.`filter` = F.filter_id 
LEFT JOIN `ci_files` Fi 
     ON P.file = Fi.file_id 

WHERE P.`approve` = 1 AND U.active = 1 AND P.post_type = 'post-color'
     AND 1 ORDER BY P.post_date DESC LIMIT 0,20 

执行此查询需要 5 分钟，我该如何改进它!？那是因为 LEFT JOINs 还是 Selects？请注意，有些表的行数在 10K 到 100k 之间我很感激任何建议!

Answer 1

优化它需要考虑的几件事。

总体而言，阅读http://use-the-index-luke.com/

首先:使用 post_id 上的索引，像这样的子查询会更快。

SELECT count(*) FROM ci_user_smiley where post_id = P.post_id

第二:聪明的程序员极力避免使用 SELECT ... table.* 或任何形式的 SELECT 列通配符 * 字符，而是给出所需列的列表查询。当查询规划器知道它可以从结果集中省略某些列时，它通常可以优化查询。

第三:这是一个臭名昭著的查询反模式。

 SELECT lots of columns
   FROM table JOIN table .... lots of tables
  ORDER BY column
  LIMIT small number

为什么？它告诉查询规划器生成一个巨大的结果集，对其进行排序，然后丢弃除少量 行之外的所有行。这很浪费。

如果查询包含这样的内容，您可能会做得更好

 WHERE p.post_id IN (SELECT p.post_id 
                       FROM ci_posts p
                       JOIN `ci_users` u 
                                 ON p.`user_id` = u.`user_id`
                      WHERE p.approve = 1
                        AND p.post_type = 'post-color'
                        AND u.active = 1
                      ORDER BY p.post_date DESC LIMIT 20)

IN 子句只获取二十个有趣的 post_id 值。这会将加载/排序/丢弃操作限制在 post_id 和 post_date 列，这会便宜得多。 ci_posts (post_type, approve, post_date, user_id) 上的复合索引会有很大帮助。

我使用 JOIN 而不是 LEFT JOIN 因为 u.active = 1 子句将 LEFT JOIN 变成一个 JOIN 无论如何。