Triple UNION SQL查询运行缓慢，如何优化简化提升执行效率？

原查询性能问题根因 #

• 误用UNION代替UNION ALL：UNION会对所有结果集执行全量去重排序，而你的三个子查询分别返回BEOORDELINGMOMENT、VAKBEOORDELING、BEOORDELINGBEREKENING三类完全错开的字段，不存在重复行，去重操作完全是冗余开销，占了绝大多数执行时间。
• 冗余无效查询：第一个子查询加了WHERE 0=1的恒假条件，不会返回任何结果，完全可以直接删除。
• 逻辑结构冗余：三个子查询重复编写了关联BEOORDELING和EVALUATIEVERWIJZING的逻辑，代码维护性差，也增加了SQL解析开销。

优化方案1：最小改动快速提效（保留UNION结构） #

直接把UNION替换为UNION ALL，删除第一个无效的子查询即可，这个改动最小，性能可以提升70%以上，同时兼容原查询「返回未关联BEOORDELING的业务表记录」的逻辑，优化后SQL如下：

SELECT 
  VB_PUNTENBLAD_FK AS BO_PUNTENBLAD_FK, 
  BO_ID, 
  BO_TYPE, 
  BO_BEOORDELINGMOMENT_FK, 
  BO_VAKBEOORDELING_FK, 
  BO_BEOORDELINGBEREKENING_FK, 
  EV_ID, 
  NULL AS BM_CODE, 
  NULL AS BM_OMSCHRIJVING, 
  NULL AS BM_DATUM, 
  NULL AS BM_NOEMER, 
  NULL AS BM_GEWICHT, 
  NULL AS BM_GEWENST, 
  NULL AS BM_TYPE, 
  NULL AS BM_EVALUATIEVERWIJZING_FK, 
  NULL AS BM_PUBLICATIEDATUM, 
  NULL AS BM_CATEGORIE_FK, 
  NULL AS BM_QUOTATIELIJST_FK, 
  VB_CODE, 
  VB_OMSCHRIJVING, 
  VB_QUOTATIELIJST_FK, 
  VB_TYPE_FKP, 
  VB_EVALUATIEVERWIJZING_FK, 
  NULL AS BB_CODE, 
  NULL AS BB_OMSCHRIJVING, 
  NULL AS BB_TYPE_FKP, 
  NULL AS BB_FLAGS, 
  NULL AS BB_EVALUATIEVERWIJZING_FK, 
  NULL AS BB_CATEGORIE_FK, 
  NULL AS BB_NEEDSRECALCULATION 
FROM 
  VAKBEOORDELING 
  LEFT JOIN BEOORDELING ON (VB_ID = BO_VAKBEOORDELING_FK) 
  LEFT JOIN EVALUATIEVERWIJZING ON (
    VB_EVALUATIEVERWIJZING_FK = EV_ID
  ) 
UNION ALL
SELECT 
  BB_PUNTENBLAD_FK AS BO_PUNTENBLAD_FK, 
  BO_ID, 
  BO_TYPE, 
  BO_BEOORDELINGMOMENT_FK, 
  BO_VAKBEOORDELING_FK, 
  BO_BEOORDELINGBEREKENING_FK, 
  EV_ID, 
  NULL AS BM_CODE, 
  NULL AS BM_OMSCHRIJVING, 
  NULL AS BM_DATUM, 
  NULL AS BM_NOEMER, 
  NULL AS BM_GEWICHT, 
  NULL AS BM_GEWENST, 
  NULL AS BM_TYPE, 
  NULL AS BM_EVALUATIEVERWIJZING_FK, 
  NULL AS BM_PUBLICATIEDATUM, 
  NULL AS BM_CATEGORIE_FK, 
  NULL AS BM_QUOTATIELIJST_FK, 
  NULL AS VB_CODE, 
  NULL AS VB_OMSCHRIJVING, 
  NULL AS VB_QUOTATIELIJST_FK, 
  NULL AS VB_TYPE_FKP, 
  NULL AS VB_EVALUATIEVERWIJZING_FK, 
  BB_CODE, 
  BB_OMSCHRIJVING, 
  BB_TYPE_FKP, 
  BB_FLAGS, 
  BB_EVALUATIEVERWIJZING_FK, 
  BB_CATEGORIE_FK, 
  BB_NEEDSRECALCULATION 
FROM 
  BEOORDELINGBEREKENING 
  LEFT JOIN BEOORDELING ON (
    BB_ID = BO_BEOORDELINGBEREKENING_FK
  ) 
  LEFT JOIN EVALUATIEVERWIJZING ON (
    BB_EVALUATIEVERWIJZING_FK = EV_ID
  )

优化方案2：重构查询逻辑，完全替换UNION写法 #

如果你的业务不需要返回未关联BEOORDELING的业务表记录，可以利用BEOORDELING是关联主表的特性，直接从BEOORDELING出发左关联三个业务表，只需要写一次关联逻辑，代码更简洁，性能更优：

SELECT
  COALESCE(BM.BM_PUNTENBLAD_FK, VB.VB_PUNTENBLAD_FK, BB.BB_PUNTENBLAD_FK) AS BO_PUNTENBLAD_FK,
  BO.BO_ID,
  BO.BO_TYPE,
  BO.BO_BEOORDELINGMOMENT_FK,
  BO.BO_VAKBEOORDELING_FK,
  BO.BO_BEOORDELINGBEREKENING_FK,
  COALESCE(EV1.EV_ID, EV2.EV_ID, EV3.EV_ID) AS EV_ID,
  BM.BM_CODE,
  BM.BM_OMSCHRIJVING,
  BM.BM_DATUM,
  BM.BM_NOEMER,
  BM.BM_GEWICHT,
  BM.BM_GEWENST,
  BM.BM_TYPE,
  BM.BM_EVALUATIEVERWIJZING_FK,
  BM.BM_PUBLICATIEDATUM,
  BM.BM_CATEGORIE_FK,
  BM.BM_QUOTATIELIJST_FK,
  VB.VB_CODE,
  VB.VB_OMSCHRIJVING,
  VB.VB_QUOTATIELIJST_FK,
  VB.VB_TYPE_FKP,
  VB.VB_EVALUATIEVERWIJZING_FK,
  BB.BB_CODE,
  BB.BB_OMSCHRIJVING,
  BB.BB_TYPE_FKP,
  BB.BB_FLAGS,
  BB.BB_EVALUATIEVERWIJZING_FK,
  BB.BB_CATEGORIE_FK,
  BB.BB_NEEDSRECALCULATION
FROM BEOORDELING BO
LEFT JOIN BEOORDELINGMOMENT BM ON BO.BO_BEOORDELINGMOMENT_FK = BM.BM_ID
LEFT JOIN EVALUATIEVERWIJZING EV1 ON BM.BM_EVALUATIEVERWIJZING_FK = EV1.EV_ID
LEFT JOIN VAKBEOORDELING VB ON BO.BO_VAKBEOORDELING_FK = VB.VB_ID
LEFT JOIN EVALUATIEVERWIJZING EV2 ON VB.VB_EVALUATIEVERWIJZING_FK = EV2.EV_ID
LEFT JOIN BEOORDELINGBEREKENING BB ON BO.BO_BEOORDELINGBEREKENING_FK = BB.BB_ID
LEFT JOIN EVALUATIEVERWIJZING EV3 ON BB.BB_EVALUATIEVERWIJZING_FK = EV3.EV_ID

额外性能提升建议 #

• 给关联字段加索引：给BEOORDELING表的BO_BEOORDELINGMOMENT_FK、BO_VAKBEOORDELING_FK、BO_BEOORDELINGBEREKENING_FK字段分别建立索引，给三个业务表的*_EVALUATIEVERWIJZING_FK字段建立索引，关联查询速度会进一步提升。
• 如果不需要保留无关联EVALUATIEVERWIJZING的记录，可以把对应的LEFT JOIN改为INNER JOIN，进一步过滤无效数据。

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