使用count的mysql多个左连接

Question

我已经研究了几个小时了，我想出的最好的代码来自于我在overstack上找到的一个例子。我已经经历了几次派生，但以下是唯一返回正确数据的查询，问题是它只返回30行数据需要139秒(超过2分钟)。我被卡住了。(life_p是一个'likes‘

SELECT
  logos.id,
  logos.in_gallery,
  logos.active,
  logos.pubpriv,
  logos.logo_name,
  logos.logo_image,
  coalesce(cc.Count, 0) as CommentCount,
  coalesce(lc.Count, 0) as LikeCount
FROM logos

left outer join(
  select comments.logo_id, count( * ) as Count from comments group by comments.logo_id
) cc on cc.logo_id = logos.id

left outer join(
  select life_p.logo_id, count( * ) as Count from life_p group by life_p.logo_id
) lc on lc.logo_id = logos.id

WHERE logos.active = '1'
  AND logos.pubpriv = '0'
GROUP BY logos.id
ORDER BY logos.in_gallery desc
LIMIT 0, 30

我不知道出了什么问题。如果我对它们做了非常有意义的操作，那么删除coalece和其中一个连接：

SELECT
  logos.id,
  logos.in_gallery,
  logos.active,
  logos.pubpriv,
  logos.logo_name,
  logos.logo_image,
  count( * ) as lc
FROM logos

left join life_p on life_p.logo_id = logos.id

WHERE logos.active = '1'
  AND logos.pubpriv = '0'
GROUP BY logos.id
ORDER BY logos.in_gallery desc
LIMIT 0, 30

运行时间不到半秒(2-300ms )...

这里有一个到解释的链接：https://logopond.com/img/explain.png

Answer 1

MySQL有一个特殊的特性，它允许group by子句不列出所有非聚集列。这不是一件好事，您应该始终在group by子句中指定所有非聚集列。

注意，在对连接的表进行计数时，知道COUNT()函数忽略NULL是很有用的，因此对于可能出现NULL的左连接，不要使用COUNT(*)，而是使用连接的表中的列，并且只计算该表中的行。从这几点出发，我建议使用以下查询结构。

SELECT
        logos.id
      , logos.in_gallery
      , logos.active
      , logos.pubpriv
      , logos.logo_name
      , logos.logo_image
      , COALESCE(COUNT(cc.logo_id), 0) AS CommentCount
      , COALESCE(COUNT(lc.logo_id), 0)   AS LikeCount
FROM logos
        LEFT OUTER JOIN comments cc ON cc.logo_id = logos.id
        LEFT OUTER JOIN life_p lc ON lc.logo_id = logos.id
WHERE logos.active = '1'
AND logos.pubpriv = '0'
GROUP BY
        logos.id
      , logos.in_gallery
      , logos.active
      , logos.pubpriv
      , logos.logo_name
      , logos.logo_image
ORDER BY logos.in_gallery DESC
LIMIT 0, 30

如果您仍然有性能问题，那么使用执行计划，并考虑添加索引以适应需要。

Answer 2

您可以在连接字段上创建一些索引：

ALTER TABLE table ADD INDEX idx__tableName__fieldName (field)

在你的例子中是这样的：

ALTER TABLE cc添加索引idx__cc__logo_id (logo_id)；

Answer 3

我真的不喜欢它，因为我总是读到子查询很糟糕，连接在压力下表现得更好，但在这种情况下，子查询似乎是在半秒内持续提取正确数据的唯一方法。感谢大家的建议。

SELECT 
    logos.id,
    logos.in_gallery,
    logos.active,
    logos.pubpriv,
    logos.logo_name,
    logos.logo_image,
(Select COUNT(comments.logo_id) FROM comments
    WHERE comments.logo_id = logos.id) AS coms,
(Select COUNT(life_p.logo_id) FROM life_p 
   WHERE life_p.logo_id = logos.id) AS floats 

FROM logos
WHERE logos.active = '1' AND logos.pubpriv = '0'
ORDER BY logos.in_gallery desc
LIMIT ". $start .",". $pageSize ."