有关多表Left join的优化
SELECT COUNT(DISTINCT T1.A1) + COUNT(DISTINCT T2.B1) + COUNT(DISTINCT T3.C1) FROM T1 LEFT JOIN T2 on T1.A1 = T2.A1 LEFT JOIN T3 on T1.A1 = T3.A1 LEFT JOIN T4 on T3.C1 = T4.C1 GROUP BY T1.A2, T1.A3 上面这个SQL效率太差了, T1,T2,T3,T4表达到5千条的时候,需要两,三分钟 希望大家帮忙优化一下 多谢大家帮帮忙吧!
最满意答案
1、因为T1表式主表,所以 【select COUNT(DISTINCT T1.A1) from T1】和你求出的 【COUNT(DISTINCT T1.A1)】值是一样的。 2、而由于T2等是从表并且你使用了【COUNT(DISTINCT T2.B1)】因此null值会被排除掉,实际上和下面的语句求出的值是一样的 select COUNT(DISTINCT T2.B1) from T1 inner join T2 on T1.A1 = T2.A1; 3、从上面的分析可以看出你使用【left join】的目的只有一个就是得到【T1】表全部数据的【COUNT(DISTINCT T1.A1)】,所以试试改成下面的sql是否性能能够快些 select cnt1+cnt2+cnt3 from( (select COUNT(DISTINCT T1.A1) cnt1 from T1 GROUP BY T1.A2, T1.A3)t1, (select COUNT(DISTINCT T2.B1) cnt2 from T1 inner join T2 on T1.A1 = T2.A1 GROUP BY T1.A2, T1.A3)t2, (select COUNT(DISTINCT T3.C1) cnt3 from T1 inner join T3 on T1.A1 = T3.A1 inner join T4 on T3.C1 = T4.C1 GROUP BY T1.A2, T1.A3)t3; --- 以上,希望对你有所帮助。更多推荐
发布评论