为何MySQL Derived Condition Pushdown Optimization对含GROUP BY的查询不生效？

核心原因 #

你的查询无法触发Derived Condition Pushdown优化，本质是因为派生表中被外部WHERE引用的非GROUP BY列不满足功能依赖于GROUP BY键的要求，同时LEFT JOIN的存在加剧了值的不确定性，导致优化器判定下推条件会改变查询结果，因此拒绝执行优化：

1. 非GROUP BY列的功能依赖缺失
   派生表的GROUP BY键是ST.SERVICE_TICKET_ID，但外部WHERE中引用的SERVICE_CATEGORY_ID、BRANCH_ID都不在GROUP BY列表中：

   • SERVICE_CATEGORY_ID来自主表SERVICE_TICKET，即便SERVICE_TICKET_ID是该表主键（理论上每个主键对应唯一的SERVICE_CATEGORY_ID），MySQL优化器默认不会自动识别这种业务层面的功能依赖，除非你显式用聚合函数（如MAX(ST.SERVICE_CATEGORY_ID)）包裹该列，让优化器确认分组后该值的唯一性。
   • BRANCH_ID来自LEFT JOIN的ELEMENT_CUSTOMER表，一个CUSTOMER_ID可能对应多条ELEMENT_CUSTOMER记录（即多个BRANCH_ID）。在ONLY_FULL_GROUP_BY关闭的情况下，GROUP BY后BRANCH_ID会被随机选取，此时将BRANCH_ID IN ('10081')下推到HAVING，会过滤掉所有分组中不存在该值的组，这和外部WHERE过滤“分组后随机选出的BRANCH_ID是否符合条件”的逻辑完全不同，优化器判定这种下推不安全。

2. LEFT JOIN引入的不确定性
   派生表中包含多层LEFT OUTER JOIN，这些JOIN可能引入NULL值或多对多关联，进一步增加了非GROUP BY列值的不确定性，优化器无法保证下推条件后结果的一致性，因此放弃优化。

解决方案 #

方案1：手动将条件下推到派生表的HAVING子句

直接在派生表的GROUP BY后添加HAVING条件，强制实现优化效果：

SELECT *
FROM (SELECT ST.SERVICE_TICKET_ID,
             ST.SERVICE_CATEGORY_ID,
             ST.STATUS_ID,
             EC.BRANCH_ID,
             ST.PRICE,
             GROUP_CONCAT(STTPER.FIRST_NAME, ' ', STTPER.LAST_NAME ORDER BY STTPER.LAST_NAME, STTPER.FIRST_NAME SEPARATOR ', ')
      FROM SERVICE_TICKET ST
               LEFT OUTER JOIN ELEMENT_CUSTOMER EC ON ST.CUSTOMER_ID = EC.PARTY_ID
               LEFT OUTER JOIN SERVICE_TICKET_TECHNICIAN STT
                               ON ST.SERVICE_TICKET_ID = STT.SERVICE_TICKET_ID AND (NOT STT.INACTIVE <=> 1)
               LEFT OUTER JOIN PERSON STTPER ON STT.TECHNICIAN_ID = STTPER.PARTY_ID
       WHERE ((ST.SERVICE_TICKET_CATEGORY_ID = 'SERVICE' AND ST.STATUS_ID <> 'SVC_TICK_DEACTIVATED'))
      GROUP BY ST.SERVICE_TICKET_ID
      -- 手动添加下推的条件
      HAVING BRANCH_ID IN ('10081') AND SERVICE_CATEGORY_ID = '10080'
      ) REPORT

方案2：通过聚合函数明确非GROUP BY列的唯一性

将非GROUP BY列用聚合函数包裹，让优化器确认分组后该值的唯一性，从而允许条件下推：

SELECT *
FROM (SELECT ST.SERVICE_TICKET_ID,
             -- 用聚合函数确保分组后值唯一
             MAX(ST.SERVICE_CATEGORY_ID) AS SERVICE_CATEGORY_ID,
             MAX(ST.STATUS_ID) AS STATUS_ID,
             MAX(EC.BRANCH_ID) AS BRANCH_ID,
             MAX(ST.PRICE) AS PRICE,
             GROUP_CONCAT(STTPER.FIRST_NAME, ' ', STTPER.LAST_NAME ORDER BY STTPER.LAST_NAME, STTPER.FIRST_NAME SEPARATOR ', ')
      FROM SERVICE_TICKET ST
               LEFT OUTER JOIN ELEMENT_CUSTOMER EC ON ST.CUSTOMER_ID = EC.PARTY_ID
               LEFT OUTER JOIN SERVICE_TICKET_TECHNICIAN STT
                               ON ST.SERVICE_TICKET_ID = STT.SERVICE_TICKET_ID AND (NOT STT.INACTIVE <=> 1)
               LEFT OUTER JOIN PERSON STTPER ON STT.TECHNICIAN_ID = STTPER.PARTY_ID
       WHERE ((ST.SERVICE_TICKET_CATEGORY_ID = 'SERVICE' AND ST.STATUS_ID <> 'SVC_TICK_DEACTIVATED'))
      GROUP BY ST.SERVICE_TICKET_ID
      ) REPORT
WHERE REPORT.BRANCH_ID IN ('10081') AND REPORT.SERVICE_CATEGORY_ID = '10080'

方案3：优化JOIN逻辑减少不确定性

如果业务上一个CUSTOMER_ID仅对应一个BRANCH_ID，可以修改ELEMENT_CUSTOMER的JOIN逻辑，确保每个SERVICE_TICKET_ID只关联一条ELEMENT_CUSTOMER记录（比如添加DISTINCT或调整WHERE条件），消除BRANCH_ID的不确定性，让优化器自动触发下推。

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