MySQL查询优化技巧之二

1.    多表聚合查询优化

在实际的业务场景中经常会需要多表关联，需要使用到多个表的字段关联聚合结果，对于新手来说，最直观的想法就是把所有数据源join成一张大表，再在大表中进行指标的计算，但是这样子就会效率非常低下

最好的方法就是：

先把多张表的数据进行字段聚合，再在聚合过后的基础上进行表关联

例如：

表A： adv_id, date, ad_id, material_id,ad_name, self_imp, self_click

表B：adv_id, date,ad_id,material_id,ad_name,target_imp_taget_click

表C； adv_id, date,ad_id,material_id,ad_name,third_imp,third_click

需要的查询结果；adv_id，self_imp, self_click, target_imp_taget_click, third_imp,third_click

最好的查询方案：

select t1.*, t2.targt_imp,t2.target_click,t3.third_imp,t3.third_click

from

(select adv_id,, sum(self_imp)as self_imp,sum(self_click) as self_click from table_A  group by adv_id) t1

left join

(select adv_id, sum(target_imp) astarget_imp, sum(target_click) as target_click from table B group by adv_id) t2

on t1.adv_id=t2.adv_id

left join

(select adv_id, sum(third_imp) as third_imp,sum(third_click) as third_click from table C group by adv_id) t3

on t1.adv_id=t3.adv_id