复杂SQL优化

最新推荐文章于 2025-03-28 18:21:20 发布

越烟

最新推荐文章于 2025-03-28 18:21:20 发布

阅读量1.4k

点赞数 1

分类专栏： SQL语句 SQL优化

本文链接：https://blog.youkuaiyun.com/daiqiulong2/article/details/88996360

版权

SQL优化同时被 2 个专栏收录

29 篇文章

订阅专栏

SQL语句

20 篇文章

订阅专栏

SQL：

select *
from (select distinct action_Type_Name as actionName,
toRZ,
custName,
partyTypeCd,
t.ol_nbr,
channelName,
staffName,
soDate,
ct.status,
ct.status as stName,
t.staff_number,
t.region_id,
t.region_name as areaName,
t.access_number,
'0' as wzType,
'' deal,
'' checkLog,
'' hzPdf,
'' cut,
'' olNbrLink,
pdf_type,
'' pdfZg,
case
when e.attr_value_name is null then
t.SYSTEM_SOURCE
else
e.attr_value_name
end system_source,
d.source_name as sourceName,
t.sd_staff as sdStaff,
t.sd_staff_name as sdStaffName
from YWJH.checkwz_scene t
left join YWJH.check_wz_zg_status ct
on t.ol_nbr = ct.ol_nbr
left join YWJH.checkwz_source d
on d.source_order = t.order_source
left join YWJH.sys_source_desc e
on e.attr_value = t.SYSTEM_SOURCE
where 1 = 1
AND (EXISTS (select 1
from YWJH.V_CHANNEL_BSS_staff_nj sto
where sto.staff_code = 'WTXF002'
and sto.org_id = t.channel_id) or
(t.sd_staff = 'WTXF002') or
(t.staff_number = 'WTXF002'))
and ct.status in ('F', 'RF', 'WC')
and t.city_id = '20'
union
select distinct action_Type_Name as actionName,
toRZ,
custName,
partyTypeCd,
t.ol_nbr,
channelName,
staffName,
soDate,
ct.status,
ct.status as stName,
t.staff_number,
t.region_id,
t.region_name as areaName,
t.access_number,
'1' as wzType,
'' deal,
'' checkLog,
'' hzPdf,
'' cut,
'' olNbrLink,
pdf_type,
'' pdfZg,
'' system_source,
'' sourceName,
'' sdStaff,
'' sdStaffName
from YWJH.checkwz_scene_wz_old t
left join YWJH.check_wz_zg_status ct
on t.ol_nbr = ct.ol_nbr
where 1 = 1
AND (EXISTS (select 1
from YWJH.V_CHANNEL_BSS_staff_nj sto
where sto.staff_code = 'WTXF002'
and sto.org_id = t.channel_id) or
(t.staff_number = 'WTXF002'))
and ct.status in ('F', 'RF', 'WC')
and t.city_id = '20'
) aa
order by aa.ol_nbr desc ;

执行计划：

瞄了几眼，这个SQL如果不该写无法优化。

信不信由你，性能问题在 ID 14,20.

改写后的SQL：

select *
from (select distinct action_Type_Name as actionName,
toRZ,
custName,
partyTypeCd,
t.ol_nbr,
channelName,
staffName,
soDate,
ct.status,
ct.status as stName,
t.staff_number,
t.region_id,
t.region_name as areaName,
t.access_number,
'0' as wzType,
'' deal,
'' checkLog,
'' hzPdf,
'' cut,
'' olNbrLink,
pdf_type,
'' pdfZg,
case
when e.attr_value_name is null then
t.SYSTEM_SOURCE
else
e.attr_value_name
end system_source,
d.source_name as sourceName,
t.sd_staff as sdStaff,
t.sd_staff_name as sdStaffName
from YWJH.checkwz_scene t
left join YWJH.check_wz_zg_status ct
on t.ol_nbr = ct.ol_nbr
left join YWJH.checkwz_source d
on d.source_order = t.order_source
left join YWJH.sys_source_desc e
on e.attr_value = t.SYSTEM_SOURCE
left join YWJH.V_CHANNEL_BSS_staff_nj sto on sto.staff_code = 'WTXF002' and sto.org_id = t.channel_id
where 1 = 1
and (sto.staff_id is not null or t.sd_staff = 'WTXF002'or t.staff_number = 'WTXF002')
and ct.status in ('F', 'RF', 'WC')
and t.city_id = '20'
union
select distinct action_Type_Name as actionName,
toRZ,
custName,
partyTypeCd,
t.ol_nbr,
channelName,
staffName,
soDate,
ct.status,
ct.status as stName,
t.staff_number,
t.region_id,
t.region_name as areaName,
t.access_number,
'1' as wzType,
'' deal,
'' checkLog,
'' hzPdf,
'' cut,
'' olNbrLink,
pdf_type,
'' pdfZg,
'' system_source,
'' sourceName,
'' sdStaff,
'' sdStaffName
from YWJH.checkwz_scene_wz_old t
left join YWJH.check_wz_zg_status ct on t.ol_nbr = ct.ol_nbr
left join YWJH.V_CHANNEL_BSS_staff_nj sto on sto.staff_code = 'WTXF002' and sto.org_id = t.channel_id
where 1 = 1
and ( sto.staff_id is not null or t.staff_number = 'WTXF002')
and ct.status in ('F', 'RF', 'WC')
and t.city_id = '20'
) aa
order by aa.ol_nbr desc ;

效率对比

优化前：125S， 2分钟左右

优化后：13S

信不信由你，该SQL优化后，业务高峰期业务量增加近2倍，CPU压力减少 50%左右。

补一个后记吧。

本身该SQL优化不难，只是对该项目有里程碑意义。本身业务量就大，业务性能提升不上来，需要整体优化，但并不是很难，本次优化基本上一眼定位到系统瓶颈。之前写博客纪念了一个SQl，也是简单看两眼，从1997S优化到 0.2S。那次个例上面看意义很大，这次对整体意义更大。从接手优化这个系统，到定位到问题，再到解决问题。几乎是很快的。所以这次也该纪念下。