含UNION的大型SELECT语句优化方案咨询

首先，针对你这个带UNION的查询，性能开销大的核心原因之一是UNION自带的去重排序逻辑，再加上两个分支大量重复的JOIN操作，我们可以从几个方向入手优化：

1. 优先替换UNION为UNION ALL #

UNION会对两个结果集做去重和排序，数据量较大时这部分开销极高。观察你的两个查询分支：

• 第一个分支关联srv_obj_intermediate且oet.code='INTERMEDIATE'，最后过滤value=1
• 第二个分支关联srv_obj_attributes且oet.code='INITIAL'，还加了soi.value IS NULL的过滤

这两个分支的结果集完全不会有重复行，所以直接用UNION ALL替代UNION，可以立刻省去去重排序的开销，这是最快速见效的优化。优化后的基础版本如下：

SELECT value, srv.osp_id, soi.stya_id, eax.estpt_id, eax.discount, seo.id AS sero_id 
FROM estimate_attr_xref eax 
JOIN attribute_types attl ON attl.id = eax.attr_id 
JOIN object_attr_type_links oatl ON oatl.attr_id = attl.id 
JOIN service_type_attributes sta ON sta.objt_attr_id = oatl.id 
JOIN srv_obj_intermediate soi ON soi.stya_id = sta.id AND ((soi.value = 0) OR (soi.value = 1 AND festpae_id IS NOT NULL)) 
JOIN service_objects seo ON seo.id = soi.sero_id 
JOIN services srv ON srv.id = seo.srv_id 
JOIN order_event oet ON oet.code = 'INTERMEDIATE' 
WHERE eax.rate = 1 AND eax.ordet_id = oet.id AND eax.objt_attr_id = sta.objt_attr_id
AND value = 1 

UNION ALL 

SELECT soa.value, srv.osp_id, soa.stya_id, eax.estpt_id, eax.discount, seo.id AS sero_id 
FROM estimate_attr_xref eax 
JOIN attribute_types attl ON attl.id = eax.attr_id 
JOIN object_attr_type_links oatl ON oatl.attr_id = attl.id 
JOIN service_type_attributes sta ON sta.objt_attr_id = oatl.id 
JOIN srv_obj_attributes soa ON soa.stya_id = sta.id AND soa.value = 1 
LEFT JOIN srv_obj_intermediate soi ON soi.stya_id = sta.id AND soi.value = 1 
JOIN service_objects seo ON seo.id = soa.sero_id 
JOIN services srv ON srv.id = seo.srv_id 
JOIN order_event oet ON oet.code = 'INITIAL' 
WHERE eax.rate = 1 AND eax.ordet_id = oet.id AND eax.objt_attr_id = sta.objt_attr_id 
AND soi.value IS NULL

2. 合并重复的JOIN逻辑，减少重复计算 #

两个查询分支有80%以上的JOIN是重复的（比如estimate_attr_xref、attribute_types、object_attr_type_links这些表的关联），我们可以把公共部分抽出来，用条件分支处理差异部分，避免数据库重复执行相同的JOIN操作。这里是一个合并后的版本：

SELECT 
  COALESCE(soi.value, soa.value) AS value,
  srv.osp_id,
  COALESCE(soi.stya_id, soa.stya_id) AS stya_id,
  eax.estpt_id,
  eax.discount,
  COALESCE(seo_inter.id, seo_initial.id) AS sero_id
FROM estimate_attr_xref eax
JOIN attribute_types attl ON attl.id = eax.attr_id
JOIN object_attr_type_links oatl ON oatl.attr_id = attl.id
JOIN service_type_attributes sta ON sta.objt_attr_id = oatl.id
JOIN order_event oet ON oet.id = eax.ordet_id
-- 针对INTERMEDIATE场景关联中间表
LEFT JOIN srv_obj_intermediate soi 
  ON soi.stya_id = sta.id 
  AND oet.code = 'INTERMEDIATE'
  AND ((soi.value = 0) OR (soi.value = 1 AND soi.festpae_id IS NOT NULL))
-- 针对INITIAL场景关联属性表
LEFT JOIN srv_obj_attributes soa 
  ON soa.stya_id = sta.id 
  AND oet.code = 'INITIAL'
  AND soa.value = 1
-- 关联对应的service_objects
LEFT JOIN service_objects seo_inter ON seo_inter.id = soi.sero_id
LEFT JOIN service_objects seo_initial ON seo_initial.id = soa.sero_id
-- 关联services表
JOIN services srv 
  ON srv.id = COALESCE(seo_inter.srv_id, seo_initial.srv_id)
WHERE eax.rate = 1
  AND eax.objt_attr_id = sta.objt_attr_id
  -- 过滤符合两个场景的有效数据
  AND (
    (oet.code = 'INTERMEDIATE' AND soi.value = 1)
    OR
    (oet.code = 'INITIAL' AND soa.value = 1 AND soi.value IS NULL)
  )

这个版本把公共JOIN只执行一次，然后通过LEFT JOIN分别关联两个差异表，最后用WHERE条件过滤出符合各自场景的数据，能有效减少数据库的计算量。

3. 优化索引，消除全表扫描 #

性能差的另一个常见原因是缺少合适的索引，建议给以下列创建复合索引：

• order_event(code, id)：覆盖oet.code的过滤和eax.ordet_id = oet.id的JOIN
• estimate_attr_xref(ordet_id, objt_attr_id, rate)：覆盖WHERE里的三个条件，同时支持JOIN
• srv_obj_intermediate(stya_id, value, festpae_id, sero_id)：覆盖关联条件和过滤条件，以及需要SELECT的列
• srv_obj_attributes(stya_id, value, sero_id)：同样覆盖关联、过滤和查询列
• 检查各个外键列（比如service_objects(srv_id)、service_type_attributes(objt_attr_id)）是否有索引，确保JOIN时能快速定位数据

另外，不要用SELECT *，明确写出需要的列（比如你原来查询里的value, osp_id, stya_id, estpt_id, discount, sero_id），这样不仅减少数据传输量，还能让索引更好地发挥覆盖索引的作用。

4. 用EXPLAIN分析执行计划 #

最后，建议执行EXPLAIN命令查看你的查询执行计划，重点关注：

• 是否有Using filesort或Using temporary（这通常是性能瓶颈）
• 哪些表是ALL（全表扫描），对应添加索引
• JOIN的顺序是否合理，必要时可以用STRAIGHT_JOIN调整

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