介绍一些简单实用的SQL优化。
提前过滤
--对于两个需要关联的大表,因为where的顺序在Join之后,可以先建立临时表进行where过滤
with tbl_a as (
select
user_id
from
a
where
p_date = '${DATE}'
),
with tbl_b as (
select
user_id
from
b
where
p_date = '${DATE}'
)
select tbl_a.user_id
from
tbl_a
join
tbl_b
on tbl_a.user_id = tbl_b.user_id
group by代替distinct
--distinct需要去重,在大表关联中使用distinct是十分消耗资源的,
--此时可以先采用group by对字段进行去重
with tbl_a as (
select
user_id
from
a
where
p_date = '${DATE}'
group by
user_id
),
with tbl_b as (
select
user_id
from
b
where
p_date = '${DATE}'
group by
user_id
)
select count(tbl_a.user_id)
from
tbl_a
join
tbl_b
on tbl_a.user_id = tbl_b.user_id
多表JOIN顺序调整
full join的使用
--对于取全集的情况,可以用full join代替left join union all right join
--但需要注意的是,为了避免为NULL的情况,需要在外面嵌套一层取MAX的字段
--对于维度字段,则可以使用coalesce来避免为NULL的情况
select
MAX(a_num),
MAX(b_num)
from
(
select
coalesce(a.p_date, b.p_date) as p_date,
a.num as a_num,
b.num as b_num
from
a
full join
b
on a.p_date = b.p_date
)tmp