- where條件優(yōu)化
select a.dt,a.key1,a.col1,b.col1,c.col1
from a
left join b
on a.dt=b.dt
and a.key1=b.key1
left join c
on a.dt=c.dt
and a.key1=c.key1
where a.dt='2023-01-01';
此時的執(zhí)行過程是
image.png
此時可以在每次鏈接表之前先篩選出所需分區(qū)數(shù)據(jù)坑填。
select a.dt,a.key1,a.col1,b.col1,c.col1
from (select a.coll,a.dt,a.key1 from a where a.dt='2023-01-01') a
left join (select b.coll,b.dt,b.key1 from b where b.dt='2023-01-01') b
on a.dt=b.dt
and a.key1=b.key1
left join (select c.coll,c.dt,c.key1 from c where c.dt='2023-01-01') c
on a.dt=c.dt
and a.key1=c.key1;
執(zhí)行過程如下
image.png
- COUNT DISTINCT 優(yōu)化
sql語句計算經(jīng)常會用到 COUNT(DISTINCT)，但在數(shù)據(jù)比較傾斜的時候 COUNT(DISTINCT) 會比較慢這時可以嘗試用 GROUP BY 改寫代碼計算 。
select pt,count(distinct device_id) as dau
from olap.olap_log_bigc_dau_sbase_di
where pt='${-1d_yyyyMMdd}'
group by pt
優(yōu)化后
select pt,count(*) as dau
from (select pt,device_id from olap.olap_log_bigc_dau_sbase_di where pt = '${-1d_yyyyMMdd}'
group by pt,device_id) t
group by pt
distinct 會將所有數(shù)據(jù)放到同一個內(nèi)存中執(zhí)行，而group by是先排序革骨，然后再計數(shù)鳍鸵，會有一定程度的優(yōu)化。
