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之间的关系是一个对一个，那么你甚至可以不用任何subquerys工作：

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值。

Answer 2

试试看（未测试）

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

更优化

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

TIPS：

• 不使用*，而不是只写要在其上使用的所有列检索
• 赚指数的列名与ON & WHERE条件

Reference