如何优化MySQL多关联查询：多表分别校验同一UpdateFlag标识

现有方案存在的问题 #

你计划单独给各表UpdateFlag字段加索引的优化效果非常有限：

• 多表JOIN后再用OR判断不同表的字段，MySQL优化器很难匹配到对应的单字段索引，大概率还是会走全表扫描
• 多表LEFT JOIN会产生大量临时中间数据，最后还要通过GROUP BY去重，数据量稍大时内存、CPU开销都会很高

可落地的优化方案 #

1. 用EXISTS替换JOIN+GROUP BY（核心优化，完全不改变业务逻辑） #

你要的结果只要满足「分组本身或其任意关联层级的记录存在非空UpdateFlag」即可，不需要实际关联所有表的全量数据，用EXISTS可以在匹配到第一条符合条件的记录时就终止判断，同时省去了最后的GROUP BY去重逻辑，性能提升幅度可达数倍到数十倍。
改写后的SQL如下：

SELECT g.id 
FROM ci_Groups AS g 
WHERE 
  -- 分组本身有更新
  g.UpdateFlag <> ''
  -- 关联的PartGroupings有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    WHERE pg.GroupID = g.id AND pg.UpdateFlag <> ''
  )
  -- 关联的PartMaster有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    WHERE pg.GroupID = g.id AND pm.UpdateFlag <> ''
  )
  -- 关联的PartPriceInv有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartPriceInv AS ppi ON ppi.PartMasterID = pm.id
    WHERE pg.GroupID = g.id AND ppi.UpdateFlag <> ''
  )
  -- 关联的PartToAppCombo有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    WHERE pg.GroupID = g.id AND ptac.UpdateFlag <> ''
  )
  -- 关联的ApplicationCombination有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_ApplicationCombination AS ac ON ac.id = ptac.ApplicationComboID
    WHERE pg.GroupID = g.id AND ac.UpdateFlag <> ''
  )
  -- 关联的Fitment有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_Fitment AS fit ON fit.PartToAppComboID = ptac.ID
    WHERE pg.GroupID = g.id AND fit.UpdateFlag <> ''
  )
  -- 关联的TypeMakeModelYear有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_Fitment AS fit ON fit.PartToAppComboID = ptac.ID
    JOIN ci_TypeMakeModelYear AS tmmy ON tmmy.id = fit.TMMYID
    WHERE pg.GroupID = g.id AND tmmy.UpdateFlag <> ''
  )
  -- 关联的Models有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_Fitment AS fit ON fit.PartToAppComboID = ptac.ID
    JOIN ci_TypeMakeModelYear AS tmmy ON tmmy.id = fit.TMMYID
    JOIN ci_Models AS md ON tmmy.ModelID = md.id
    WHERE pg.GroupID = g.id AND md.UpdateFlag <> ''
  )
  -- 关联的Makes有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_Fitment AS fit ON fit.PartToAppComboID = ptac.ID
    JOIN ci_TypeMakeModelYear AS tmmy ON tmmy.id = fit.TMMYID
    JOIN ci_Makes AS mk ON tmmy.MakeID = mk.id
    WHERE pg.GroupID = g.id AND mk.UpdateFlag <> ''
  )
  -- 关联的Years有更新
  OR EXISTS (
    SELECT 1 FROM ci_PartGroupings AS pg 
    JOIN ci_partmaster AS pm ON pg.PartMasterID = pm.id
    JOIN ci_PartToAppCombo AS ptac ON pm.id = ptac.PartmasterID
    JOIN ci_Fitment AS fit ON fit.PartToAppComboID = ptac.ID
    JOIN ci_TypeMakeModelYear AS tmmy ON tmmy.id = fit.TMMYID
    JOIN ci_Years AS y ON tmmy.YearID = y.id
    WHERE pg.GroupID = g.id AND y.UpdateFlag <> ''
  );

2. 联合索引优化（配合EXISTS实现覆盖索引，无需回表） #

不需要单独给UpdateFlag建索引，而是给每个关联表建「关联字段 + UpdateFlag」的联合索引即可，所有子查询都可以直接通过索引拿到结果，不需要读取表数据：

• ci_PartGroupings：(GroupID, PartMasterID, UpdateFlag)
• ci_partmaster：(id, UpdateFlag)
• ci_PartPriceInv：(PartMasterID, UpdateFlag)
• ci_PartToAppCombo：(PartmasterID, ApplicationComboID, UpdateFlag)
• ci_ApplicationCombination：(id, UpdateFlag)
• ci_Fitment：(PartToAppComboID, TMMYID, UpdateFlag)
• ci_TypeMakeModelYear：(id, ModelID, MakeID, YearID, UpdateFlag)
• ci_Models：(id, UpdateFlag)
• ci_Makes：(id, UpdateFlag)
• ci_Years：(id, UpdateFlag)

3. 可选的字段类型优化 #

如果UpdateFlag只有「空/未更新」和「非空/已更新」两种状态，建议改成tinyint类型，默认值0表示未更新，1表示已更新：

• 数值类型的判断效率远高于字符串判断
• 索引体积更小，查询速度更快
• 避免空字符串判断的边界问题

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