优化包含联接和子查询的查询

debugcn 发表于 Dev

乔达基

我已经为客户端继承了一个应用程序，并且一直在进行以下查询，该查询耗时187秒，并搜索304M表行以发送回444个结果行。

我是否想摆脱子选择并将其替换为联接？我找不到正确执行此操作的方法。优化此查询的任何帮助将不胜感激。谢谢...

SELECT Business.name, Business.primary_city, Count(click.id) as clicks, Count(DISTINCT email_leads.parent_message_id) as tot_email_leads, Count(bc.business_id) as county_no, Count(br.business_id) as region_no, count(reveals.id) as reveals_no

FROM businesses as Business

LEFT JOIN business_clickthroughs as click ON ( Business.id = click.business_id  AND (click.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59'))
LEFT JOIN users as U ON Business.id = U.business_id
LEFT JOIN messages as email_leads ON (U.id = email_leads.from_to AND (email_leads.parent_message_id is null OR email_leads.parent_message_id = email_leads.id )  AND (email_leads.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59'))
LEFT JOIN business_counties as bc ON Business.id = bc.business_id                   
LEFT JOIN businesses_business_types as bt ON Business.id = bt.business_id 
LEFT JOIN business_reveals as reveals ON (reveals.business_id = Business.id  AND (reveals.created BETWEEN '2014-04-01 00:00:00' and '2014-04-30 23:59:59'))
LEFT JOIN business_regions as br ON Business.id = br.business_id 

WHERE 1=1 

Group By Business.id;

戈登·利诺夫（Gordon Linoff）

从您正在编写的查询来看，子选择可能是正确的选择。您没有显示实际的原始查询，因此这实际上只是猜测。

您的查询正在沿着可能是单独维度的数据进行联接-用户，消息，县，“显示”（无论是什么）。然后，这些联接将为每个业务产生这些尺寸的笛卡尔积，从而进一步放大数据并减慢查询速度。

而且，如果较大的表具有300+百万行，那么300秒的时间来汇总8个左右的表中的所有数据似乎并不合理。您的查询没有任何where条件可以过滤数据。的joins为所有left joins，这减少滤波。

如果性能是一个问题，请问另一个问题。包括实际查询，查询explain计划和表的布局（尤其是索引结构）。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。