【presto】测试各种优化建议对presto性能的提升
使用大表 join 小表对性能的提升
案例一：
原sql：
select
t3.repay_dt,
count(1),
count(case when t3.his_had_overdue=1 then a.loan_id end),
count(case when t3.his_had_overdue=1 then a.loan_id end)*1.0000/count(1) ,
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>1 and t3.his_overdue_days>1 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>1 then a.loan_id end) ),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>3 and t3.his_overdue_days>3 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>3 then a.loan_id end) ),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>5 and t3.his_overdue_days>5 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>5 then a.loan_id end) ) ,
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>10 and t3.his_overdue_days>10 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>10 then a.loan_id end) )
from riskn.test a
join riskn.test t2 on a.loan_id=t2.loan_id
and t2.etl_dt='2021-06-06'
join riskn.test2 t3 on t3.loan_id =t2.loan_id
and t3.etl_dt='2021-06-06' and t3.repay_per_num=1
and t3.repay_plan_type!='prepay'
and(( t3.repay_stat not in (30) and t3.source_system ='1' )or t3.source_system='1')
where a.etl_dt='2021-06-06'
and a.biz_line='1'
and a.bank_id=1
and a.loan_dt>=date'2019-12-31'
and t3.repay_dt>=date'2021-05-31'
and t3.repay_dt<=date'2021-06-06'
and a.app_name='1'
group by 1
order by 1
资源使用情况：
- 峰值内存：834MB
- 用户内存：2.89G
执行计划：
优化建议：
使用大表去 join 小表
更改操作：
- from riskn.dws_loan_order_wide_001 a
- join riskn.dwd_order_cash_result_001 t2 on a.loan_id=t2.loan_id
+ from riskn.dwd_order_cash_result_001 t2
+ join riskn.dws_loan_order_wide_001 a on a.loan_id=t2.loan_id
优化后的sql：
select
t3.repay_dt ,
count(1) ,
count(case when t3.his_had_overdue=1 then a.loan_id end),
count(case when t3.his_had_overdue=1 then a.loan_id end)*1.0000/count(1),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>1 and t3.his_overdue_days>1 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>1 then a.loan_id end) ),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>3 and t3.his_overdue_days>3 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>3 then a.loan_id end) ),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>5 and t3.his_overdue_days>5 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>5 then a.loan_id end) ),
try(count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>10 and t3.his_overdue_days>10 then a.loan_id end)*1.0000 / count(case when DATE_DIFF('day',t3.repay_dt,cast(t3.etl_dt as date))>10 then a.loan_id end) ),
from riskn.test t2
join riskn.testa a on a.loan_id=t2.loan_id
and t2.etl_dt='2021-06-06'
join riskn.testb t3 on t3.loan_id =t2.loan_id
and t3.etl_dt='2021-06-06' and t3.repay_per_num=1
and t3.repay_plan_type!='1'
and(( t3.repay_stat not in (30) and t3.source_system ='1' )or t3.source_system='1')
where a.etl_dt='2021-06-06'
and a.biz_line='1'
and a.bank_id=1
and a.loan_dt>=date'2019-12-31'
and t3.repay_dt>=date'2021-05-31'
and t3.repay_dt<=date'2021-06-06'
and a.app_name='1'
group by 1
order by 1
执行计划:
资源使用情况：
案例二：
内存使用了减少了近3倍
原sql：
select
json_extract_scalar(apply.extend_field, '$.1') as customer_type,
try(count(distinct case when t1.decision='1' then t1.apply_id end )*1.0000/count(distinct t1.apply_id)) innerrule_rate,
try(count(distinct case when apply.credit_result ='pass' then apply.apply_id end )*1.0000/(count(distinct case when t2.decision is not null then t2.apply_id end )-count(distinct case when t2.decision='2' then t2.apply_id end ) ))model_rate,
1-try(count(distinct case when t2.decision='2' then t2.apply_id end )*1.0000/count(distinct t2.apply_id)) outrule_rate,
try(count(distinct case when apply.step ='1' and apply.credit_result ='pass' then apply.apply_id end )*1.0000/
(count(distinct case when t2.decision is not null then t2.apply_id end )-count(distinct case when t2.decision='2' then t2.apply_id end ) ))model_rate,
try(count(distinct case when apply.credit_result='pass' then apply.apply_id end )*1.0000/count(distinct apply.apply_id )) approve_rate,
count(distinct apply.apply_id ) apply_cnt,count(distinct case when apply.credit_result='pass' then apply.apply_id end )approve_cnt--, count(apply.apply_id)
from rsk.rreal_flink_bl_sgfq_risk_control_sgfq_credit_result_presto apply
join rsk.rreal_flink_decision_engine_ruleset_run_record_presto t1
on t1.apply_id=apply.apply_id
and regexp_like(t1.ruleset_name,'rule' ) and t1.ruleset_name not like '%outer%'
and t1.etl_dt>='2020-01-17' and t1.step in ('1','2')
and t1.ruleset_name not like '%3%'
left join rsk.test t2
on apply.apply_id= t2.apply_id
and regexp_like(t2.ruleset_name,'rule')
and regexp_like(t2.ruleset_name,'outer')
and t2.etl_dt>='2020-01-17' and t2.step in ('3','2')
and t2.ruleset_name not like '%1%'
where
apply.step in ('3','4')
and apply.app_name='jsd'
--and apply.etl_dt='2019-10-11'
and apply.etl_dt='2021-06-07'
and json_extract_scalar(apply.extend_field, '$.apply_type')!= '257'
and format_datetime(from_unixtime(cast (substring(apply.create_time,1,10) as bigint)),'yyyy-MM-dd HH:mm:ss')>='2021-06-07 00:00:00'
group by 1
结论
大表 jion 小表性能较之小表 join 大表要提升三倍。所以大表join优化项比较关键。
使用WITH
原SQL语句：
select distinct a.etl_dt,a.cost,de.hightype,de.type,a.desc as description,
case when a.cost>0 then 'test'
else '非投放渠道' end as pay_type
from
(select cost_dt as etl_dt,
sum(cost) as cost,
desc
from rdw.test
where cost_dt>=date('2021-06-06')
and cost_dt<=date('2021-06-06')
and platform in ('4')
group by cost_dt,desc)a
left join
(select distinct hightype,
type,
description,
etl_dt
from rmk.hha
where date(etl_dt) between date('2021-06-06') and date('2021-06-06')
and platform='APP'
and app_subname in ('null','')
) as de
on trim(lower(cast(a.desc as varchar)))=trim(lower(cast(de.description as varchar)))
and date(de.etl_dt)=date(a.etl_dt)
group by a.etl_dt,a.cost,de.hightype,de.type,a.desc,case when a.cost>0 then '投放'
else '非投放' end
优化方向：
- 使用with替换
- join 字段避免出现函数
- group by后面跟数字
- 大表join小表
优化后：
with a as (select distinct date(cost_dt) as etl_dt,
sum(cost) as cost,
trim(lower(cast(desc as varchar))) as desc
from rdw.test
where cost_dt>=date('2021-06-06')
and cost_dt<=date('2021-06-06')
and platform in ('4')
group by cost_dt,desc),
de as (select distinct hightype,
type,
trim(lower(cast(description as varchar))) as description,
date(etl_dt) as etl_dt
from rmk.hha
where date(etl_dt) between date('2021-06-06') and date('2021-06-06')
and platform='APP'
and app_subname in ('null','')
)
select a.etl_dt,a.cost,de.hightype,de.type,a.desc as description,
case when a.cost>0 then '投放渠道'
else '非投放渠道' end as pay_type
from de right join a on a.desc = de.description
and de.etl_dt = a.etl_dt
group by 1,2,3,4,5,6
结论：
经过测试，对性能提升不大。
