php - 优化相关子查询

Question

尝试使用关联子查询优化此查询

SELECT g.*, g.instaLikes + (  SELECT COUNT( * )  FROM gallery_likes AS l WHERE l.gallery_id = g.id ) AS likes 
FROM gallery AS g 
WHERE (  STATUS =  'approved' ) 
ORDER BY g.moderated_at DESC , g.id ASC

没有子查询的 SELECT 非常快，当我尝试 LEFT JOIN 时它甚至更慢。

SELECT g.*, (g.instaLikes + COUNT( l.id ) ) AS likes 
FROM gallery AS g
LEFT JOIN gallery_likes AS l ON ( g.id = l.gallery_id ) 
WHERE ( STATUS =  'approved)
GROUP BY g.id
ORDER BY g.moderated_at DESC , g.id ASC 

似乎最快的选项是分别执行查询，然后在 PHP 循环中将字段一起添加，因为我可以运行子查询一次，而不是在库中运行 n 次。

目前 gallery_likes 中有 2000 行，gallery_likes 中有 15000 行。我想我还不需要优化，因为这些数字看起来很低，但第一个查询需要 20-30 秒，而 LEFT JOIN 需要 100 多秒!

Answer 1

在 PHP 中进行单独的查询和连接数据是个不错的主意。但是如果您想在 SQL 中做本质上相同的事情，您可以。您可以像对待表一样对待查询(即在其上加入)，它们被称为派生表。这通常是优化这些类型的查询的一种更快、更容易的方法。

SELECT g.*, g.instaLikes + l.c
FROM gallery AS g
LEFT JOIN (
    SELECT gallery_id, COUNT( * ) c  FROM gallery_likes GROUP BY gallery_id
) AS l ON g.id=l.gallery_id
WHERE (  STATUS =  'approved' )
ORDER BY g.moderated_at DESC , g.id ASC

这是对来自 gallery_likes 的所有记录进行分组，这可能是非常低效的。所以你应该添加某种过滤器。如果子选择(派生表)加入库并使用与外部查询相同的过滤器，则发生事件。这几乎就像您的第二个查询，但它在加入之前对数据进行分组。而不是加入然后分组。

当您需要加入摘要数据(即分组依据)时，这通常是一个很好的解决方案，但有时它可能效率低下。您通常可以通过 EXPLAIN 来判断。