多左连接SQL查询加载耗时优化求助：400条数据需15秒

针对你提供的查询（仅返回400条记录却耗时15秒，移除最后3个JOIN后性能恢复），核心优化方向集中在work_orders、service_report、condemn_committee_reports三个关联表的索引及关联逻辑上，具体方案如下：

1. 构建精准复合索引 #

索引失效是这类JOIN性能问题的常见根源，针对关联条件和过滤条件创建复合索引：

• 给work_orders创建覆盖关联与后续JOIN字段的索引：

  CREATE INDEX idx_wo_id_phase ON work_orders(id, phase);
  

• 给service_report创建覆盖过滤+关联条件的复合索引（按过滤性从强到弱排序字段）：

  CREATE INDEX idx_sr_workorder_phase_teamlead ON service_report(work_order_id, phase, is_team_lead);
  

• 给condemn_committee_reports创建对应复合索引：

  CREATE INDEX idx_ccr_workorder_phase_convener ON condemn_committee_reports(work_order_id, phase, is_convener);
  

• 优化主表berc_equipment的过滤与排序效率：

  CREATE INDEX idx_berc_condemned_deleted_id ON berc_equipment(is_condemned, p_is_deleted, ID DESC);
  

2. 提前过滤子查询减少JOIN数据量 #

将service_report和condemn_committee_reports的过滤逻辑提前到子查询中，减少JOIN时的数据交互量：

SELECT e.*, en.equipment_name as conventional_name, mm.name as make_new, 
mn.name as model_new, l.name as location_name, ht.shortcode as type_code, 
c.name as city_name, dt.name as district_name, d.name as division_name, 
z.name as zone_name, wo.problem_description, wo.created_at as complaint_date,
sr.action as team_lead_remarks, sr.submission_date as assessment_date, 
ccr.member_remarks as convener_remarks, ccr.updated_at as committee_date, 
wo.condemn_director_remarks as director_remarks, wo.updated_at as approval_date
FROM berc_equipment e
LEFT JOIN equipment_names en ON en.ID = e.conventional_name
LEFT JOIN makemodals mm ON e.make = mm.ID
LEFT JOIN makemodals mn ON e.model = mn.ID
LEFT JOIN locations l ON l.ID = e.city_id
LEFT JOIN hospital_type ht ON ht.ID = l.type_id
LEFT JOIN cities c ON c.ID = l.city_id
LEFT JOIN districts dt ON dt.ID = c.district_id
LEFT JOIN divisions d ON d.ID = dt.division_id
LEFT JOIN work_orders wo ON wo.id = e.non_functional_wo_id
-- 提前过滤team lead的记录
LEFT JOIN (
    SELECT work_order_id, action, submission_date, phase
    FROM service_report
    WHERE is_team_lead = 'T'
) sr ON sr.work_order_id = e.non_functional_wo_id AND sr.phase = wo.phase
-- 提前过滤convener的记录
LEFT JOIN (
    SELECT work_order_id, member_remarks, updated_at, phase
    FROM condemn_committee_reports
    WHERE is_convener = 'yes'
) ccr ON ccr.work_order_id = e.non_functional_wo_id AND ccr.phase = wo.phase
WHERE e.is_condemned = '1'
AND e.p_is_deleted = 'F'
ORDER BY e.ID DESC

3. 排查隐式类型转换与重复记录 #

• 类型一致性检查：确认所有关联字段类型完全匹配（比如e.non_functional_wo_id与wo.id、sr.work_order_id的类型），类型不匹配会导致索引失效，强制全表扫描。
• 重复记录排查：检查关联表中是否存在重复的关联键记录，比如执行以下语句查看work_orders是否有重复ID：

  SELECT id, COUNT(*) FROM work_orders GROUP BY id HAVING COUNT(*) > 1;
  

  若service_report或condemn_committee_reports中同一work_order_id+phase存在多条记录，使用聚合函数（如MAX/MIN）获取唯一值，避免一对多JOIN导致临时表数据膨胀：

  LEFT JOIN (
      SELECT work_order_id, phase, MAX(action) as team_lead_remarks, MAX(submission_date) as assessment_date
      FROM service_report
      WHERE is_team_lead = 'T'
      GROUP BY work_order_id, phase
  ) sr ON sr.work_order_id = e.non_functional_wo_id AND sr.phase = wo.phase
  

4. 先过滤主表再关联 #

通过CTE先过滤并排序主表数据，再关联其他表，减少后续JOIN的数据基数：

WITH filtered_equip AS (
    SELECT *
    FROM berc_equipment
    WHERE is_condemned = '1' AND p_is_deleted = 'F'
    ORDER BY ID DESC
)
SELECT fe.*, en.equipment_name as conventional_name, mm.name as make_new, 
mn.name as model_new, l.name as location_name, ht.shortcode as type_code, 
c.name as city_name, dt.name as district_name, d.name as division_name, 
z.name as zone_name, wo.problem_description, wo.created_at as complaint_date,
sr.action as team_lead_remarks, sr.submission_date as assessment_date, 
ccr.member_remarks as convener_remarks, ccr.updated_at as committee_date, 
wo.condemn_director_remarks as director_remarks, wo.updated_at as approval_date
FROM filtered_equip fe
LEFT JOIN equipment_names en ON en.ID = fe.conventional_name
LEFT JOIN makemodals mm ON fe.make = mm.ID
LEFT JOIN makemodals mn ON fe.model = mn.ID
LEFT JOIN locations l ON l.ID = fe.city_id
LEFT JOIN hospital_type ht ON ht.ID = l.type_id
LEFT JOIN cities c ON c.ID = l.city_id
LEFT JOIN districts dt ON dt.ID = c.district_id
LEFT JOIN divisions d ON d.ID = dt.division_id
LEFT JOIN work_orders wo ON wo.id = fe.non_functional_wo_id
LEFT JOIN (
    SELECT work_order_id, action, submission_date, phase
    FROM service_report
    WHERE is_team_lead = 'T'
) sr ON sr.work_order_id = fe.non_functional_wo_id AND sr.phase = wo.phase
LEFT JOIN (
    SELECT work_order_id, member_remarks, updated_at, phase
    FROM condemn_committee_reports
    WHERE is_convener = 'yes'
) ccr ON ccr.work_order_id = fe.non_functional_wo_id AND ccr.phase = wo.phase

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