如何重构优化LEFT JOIN语句解决SQL慢查询耗时过长问题

现有SQL查询运行时长超500秒，经排查性能瓶颈为查询中第二个LEFT JOIN关联：

• 注释掉该LEFT JOIN逻辑、同时移除对应表查询的两列字段时，查询仅需3秒即可运行完成
• 保留全部业务所需查询逻辑、不注释任何内容时，查询耗时超过500秒
  当前使用Pervasive数据库，已知相关表已创建索引，但索引配置无法适配当前查询场景，且供应商不允许自行调整索引配置，需要可行的LEFT JOIN重构优化思路；若所有SQL优化方案均无效，将考虑在PHP应用层拆分第二条查询获取所需数据。

select concat(concat(job_header.job,'-'),job_header.suffix) as Job,job_header.part,job_header.qty_order,
 sum(case when job_operations_wc.workcenter = '0750' then job_operations_wc.hours_actual end) as WaterJet,
 sum(case when job_operations_wc.workcenter IN ('0705','0710','0715') then job_operations_wc.hours_actual end) as Laser,
 sum(case when job_operations_wc.workcenter = ('1006') then job_operations_wc.hours_actual end) as Prep,
 sum(case when job_operations_wc.workcenter IN ('1310','0755') then job_operations_wc.hours_actual end) as Machining,
 sum(case when job_operations_wc.workcenter IN ('1002','1003') then job_operations_wc.hours_actual end) as Fab,
 sum(case when job_operations_wc.workcenter = '1100' then job_operations_wc.hours_actual end) as Paint,
 sum(case when job_operations_wc.workcenter = '1000' then job_operations_wc.hours_actual end) as Belts,
 sum(case when job_operations_wc.workcenter = '1001' then job_operations_wc.hours_actual end) as Electrical,
 sum(case when job_operations_wc.workcenter = '1520' then job_operations_wc.hours_actual end) as Crating_Skids,
 sum(case when job_operations_wc.workcenter IN ('1004','1005','1350','1201') then job_operations_wc.hours_actual end) as Final_Assy,
 sum(job_operations_wc.hours_estimated) as total_hours_estimated
/* min(gab_source_cause_codes.source) as source,
min(gab_source_cause_codes.cause) as cause */
from job_header
left join job_operations_wc on job_operations_wc.job = job_header.job and job_header.suffix = job_operations_wc.suffix
/* left join gab_source_cause_codes on gab_source_cause_codes.job = job_operations_wc.job and gab_source_cause_codes.suffix = job_operations_wc.suffix and gab_source_cause_codes.seq = job_operations_wc.seq */
where job_header.product_line = '05' and job_header.date_closed > '000000' and job_operations_wc.LMO = 'L' and job_operations_wc.seq < '99000'
group by Job,job_header.part,job_header.qty_order

性能损耗核心原因是gab_source_cause_codes表在聚合计算前就和工序表做关联，若两表为一对多关系，会先将中间结果集行数放大数倍，再执行聚合计算，导致耗时陡增。在无法调整索引的前提下，可通过以下方式改写规避该问题：

• 先聚合后关联：先完成job_header与job_operations_wc的关联、聚合计算，拿到Job维度的小结果集后，再关联gab_source_cause_codes取source、cause字段，避免大结果集参与聚合。
  改写参考：

  select 
    base.Job,
    base.part,
    base.qty_order,
    base.WaterJet,
    base.Laser,
    base.Prep,
    base.Machining,
    base.Fab,
    base.Paint,
    base.Belts,
    base.Electrical,
    base.Crating_Skids,
    base.Final_Assy,
    base.total_hours_estimated,
    min(gab.source) as source,
    min(gab.cause) as cause
  from (
    select 
      concat(concat(jh.job,'-'),jh.suffix) as Job,
      jh.part,
      jh.qty_order,
      jh.job as raw_job,
      jh.suffix as raw_suffix,
      sum(case when jow.workcenter = '0750' then jow.hours_actual end) as WaterJet,
      sum(case when jow.workcenter IN ('0705','0710','0715') then jow.hours_actual end) as Laser,
      sum(case when jow.workcenter = '1006' then jow.hours_actual end) as Prep,
      sum(case when jow.workcenter IN ('1310','0755') then jow.hours_actual end) as Machining,
      sum(case when jow.workcenter IN ('1002','1003') then jow.hours_actual end) as Fab,
      sum(case when jow.workcenter = '1100' then jow.hours_actual end) as Paint,
      sum(case when jow.workcenter = '1000' then jow.hours_actual end) as Belts,
      sum(case when jow.workcenter = '1001' then jow.hours_actual end) as Electrical,
      sum(case when jow.workcenter = '1520' then jow.hours_actual end) as Crating_Skids,
      sum(case when jow.workcenter IN ('1004','1005','1350','1201') then jow.hours_actual end) as Final_Assy,
      sum(jow.hours_estimated) as total_hours_estimated
    from job_header jh
    left join job_operations_wc jow on jow.job = jh.job and jh.suffix = jow.suffix
    where jh.product_line = '05' and jh.date_closed > '000000' and jow.LMO = 'L' and jow.seq < '99000'
    group by Job,jh.part,jh.qty_order,jh.job,jh.suffix
  ) base
  left join gab_source_cause_codes gab on gab.job = base.raw_job and gab.suffix = base.raw_suffix
  group by base.Job,base.part,base.qty_order,base.WaterJet,base.Laser,base.Prep,base.Machining,base.Fab,base.Paint,base.Belts,base.Electrical,base.Crating_Skids,base.Final_Assy,base.total_hours_estimated
  

  注意：如果业务要求gab_source_cause_codes必须和工序表seq字段匹配关联，可将jow.seq也加入内层子查询的聚合字段，关联后再做二次聚合，性能依然远高于先关联再聚合的写法。

• 标量子查询替代JOIN：如果Pervasive数据库对标量子查询优化支持较好，可直接在SELECT部分写子查询取source、cause的最小值，完全规避JOIN带来的结果集膨胀：

  select concat(concat(jh.job,'-'),jh.suffix) as Job,jh.part,jh.qty_order,
  sum(case when jow.workcenter = '0750' then jow.hours_actual end) as WaterJet,
  sum(case when jow.workcenter IN ('0705','0710','0715') then jow.hours_actual end) as Laser,
  sum(case when jow.workcenter = '1006' then jow.hours_actual end) as Prep,
  sum(case when jow.workcenter IN ('1310','0755') then jow.hours_actual end) as Machining,
  sum(case when jow.workcenter IN ('1002','1003') then jow.hours_actual end) as Fab,
  sum(case when jow.workcenter = '1100' then jow.hours_actual end) as Paint,
  sum(case when jow.workcenter = '1000' then jow.hours_actual end) as Belts,
  sum(case when jow.workcenter = '1001' then jow.hours_actual end) as Electrical,
  sum(case when jow.workcenter = '1520' then jow.hours_actual end) as Crating_Skids,
  sum(case when jow.workcenter IN ('1004','1005','1350','1201') then jow.hours_actual end) as Final_Assy,
  sum(jow.hours_estimated) as total_hours_estimated,
  (select min(source) from gab_source_cause_codes g where g.job = jh.job and g.suffix = jh.suffix) as source,
  (select min(cause) from gab_source_cause_codes g where g.job = jh.job and g.suffix = jh.suffix) as cause
  from job_header jh
  left join job_operations_wc jow on jow.job = jh.job and jh.suffix = jow.suffix
  where jh.product_line = '05' and jh.date_closed > '000000' and jow.LMO = 'L' and jow.seq < '99000'
  group by Job,jh.part,jh.qty_order
  

• 应用层拆分兜底：如果上述SQL改写后性能仍不达标，直接先执行去掉gab_source_cause_codes关联的基础查询（3秒可返回结果），拿到结果集中所有job、suffix的集合，再批量查询gab_source_cause_codes表中对应job+suffix的source、cause最小值，在PHP代码中完成数据合并，整体耗时通常可控制在10秒以内。

内容的提问来源于stack exchange，提问作者SkylarP