【 使用环境 】生产环境
【 OB or 其他组件 】OBCE-4.2.2
【 使用版本 】OBCE-4.2.2
【问题描述】同一条SQL 在mysql 跟OB 执行差距很大，
【复现路径】问题出现前后相关操作
select /*+ORDERED full(sp) */
distinct wo.wo_id,
(
case
when wo.type = 2 then '维护工单'
else '工单'
end
) as wo_type,
wo.wo_code,
wo.request_name,
wo.request_phone,
wo.location,
wo.wo_desc,
wo.payment_item_num,
(
case wo.`status`
when 0 then '已创建'
when 1 then '已派工'
when 2 then '处理中'
when 3 then '已暂停'
when 4 then '已终止'
when 5 then '已完成'
when 6 then '已验证'
when 7 then '已存档'
when 8 then '待审批'
end
) as wo_status,
wo.created_date,
wo.actual_arrival_datetime,
wo.actual_completion_datetime,
wo.estimated_arrival_datetime,
wo.estimated_completion_datetime,
wo.work_content,
servicetyp3_.full_name as stfullname,
priority1_.priority_name,
(
case sp.proj_name
when '工程中心SC' then 'SC1'
when '储能HQ1' then 'HQ1'
when '创新21C' then '21C'
else sp.proj_name
end
) as projectname,
tc.value as actual_working_time,
(
case tc.unit
when 3 then '秒'
when 4 then '分钟'
when 5 then '小时'
when 6 then '天'
end
) as actual_working_value,
tc2.value as estimated_working_time,
(
case tc2.unit
when 3 then '秒'
when 4 then '分钟'
when 5 then '小时'
when 6 then '天'
end
) as estimated_working_value,
tc3.value as stop_time,
(
case tc3.unit
when 3 then '秒'
when 4 then '分钟'
when 5 then '小时'
when 6 then '天'
end
) as stop_time_value,
employee2_.em_name,
workorderl1_.actual_working_time * 60 as em_work_time,
workorderl1_.responsible,
(
case period.type
when 0 then '年度'
when 1 then '季度'
when 2 then '月度'
when 3 then '周'
when 4 then '天'
end
) as periodType,
wo.modified_date,
(
case workorderl1_.status
when 0 then '未接单'
when 1 then '已接单'
when 2 then '已退单'
when 3 then '已提交'
end
) as work_status,
b.finished_status,
b.file_hash_status
from
wo
left join priority priority1_ on wo.priority_id = priority1_.priority_id
left join wo_laborer workorderl1_ on wo.wo_id = workorderl1_.wo_id
left join em employee2_ on workorderl1_.laborer_id = employee2_.em_id
left join service_type servicetyp3_ on wo.stype_id = servicetyp3_.stype_id
left join sys_project sp on wo.proj_id = sp.proj_id
left join em_info_project eif on employee2_.em_id = eif.em_id
left join time_count tc on wo.actual_working_time = tc.time_count_id and tc.unit = 4
left join time_count tc2 on wo.estimated_working_time = tc2.time_count_id
left join time_count tc3 on wo.stop_time = tc3.time_count_id and tc.unit = 4
left join pm on wo.pm_id = pm.pm_id
left join period on period.period_id = pm.period_id
left join (
select
group_concat(
con
order by
con separator '|'
) as finished_status,
group_concat(fm_file_hash) as file_hash_status,
wo_id
from
(
select
concat_ws(
';',
ps.sort,
ps.comment,(
case ps.finished
when 0 then '未完成'
when 1 then '已完成'
end
)
) as con,
psf.fm_file_hash fm_file_hash,
wo_id,
ps.sort
from
pm_step ps
left join pm_step_file psf on ps.pm_step_id = psf.pm_step_id
) a
group by
a.wo_id
) b on wo.wo_id = b.wo_id
where
wo.deleted = 0
and sp.deleted = 0
and sp.proj_name != 'DEMO'
and wo.created_date > '2024-7-1'
order by
created_date
OB执行计划：
执行 70s
MySQL 执行计划：
MySQL 执行 9s
