MySQL:如何使用两个相同的子查询优化查询？

Question

我的查询工作正常，但它有 2 个相同的子查询

SELECT *, 
    (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) AS poffers 
FROM `product` P 
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14)) 
WHERE P.deleted is NULL 
    AND (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) > 0 

我尝试使用 JOIN 进行优化，但新查询返回空结果

SELECT *, OJ.pcount AS poffers
FROM `product` P 
JOIN product_section PS ON PS.product_id = P.id AND PS.section_id IN (14)
JOIN (SELECT count(O.id) AS pcount, O.product_id FROM `offer` O ) 
    AS OJ ON OJ.product_id = P.id
WHERE P.deleted is NULL AND OJ.pcount > 0 

比我尝试变量，但我认为我使用错误，查询返回空结果

SELECT *,  
    @o := (SELECT count(O.id) FROM `offer` O WHERE O.product_id = P.id) 
    AS poffers 
FROM `product` P 
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14)) 
WHERE P.deleted is NULL 
    AND @o > 0 

Answer 1

避免依赖子查询。相反，进行一个将产品与报价计数相关联的查询。您可以在多个地方使用此查询。它很可能会临时存储在内存中，避免重复计算，但数据库引擎也可能会做更聪明的事情。

SELECT *, O.cnt AS poffers
FROM product P
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14))
JOIN (SELECT product_id, count(id) AS cnt
      FROM offer
      GROUP BY product_id
     ) O ON (O.product_id = P.id)
WHERE P.deleted is NULL AND O.cnt > 0

如果 P 和 PS 之间的关系是一对一的，那么您甚至可以在没有任何子查询的情况下工作:

SELECT *, COUNT(O.id) AS poffers
FROM product P
JOIN product_section PS ON (PS.product_id = P.id AND PS.section_id IN (14))
JOIN offer O ON (O.product_id = P.id)
WHERE P.deleted is NULL
GROUP BY P.id

您还应该考虑是否有充分的理由对特定列进行计数。在大多数情况下，COUNT(*) 会比 COUNT(col) 更快。使用后者的唯一原因是您明确希望从计数中排除 NULL 值。