optimizer_switch 怎样控制派生条件下推

如果一条查询把聚合或其他不能直接合并的子查询放进派生表，外层 WHERE 条件仍可能被提前送进派生表，从而减少需要物化和继续处理的行。MySQL 控制这项能力的开关是 optimizer_switch 里的 derived_condition_pushdown。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

实战中建议先按会话查看和调整默认行为；只有某条 SQL 需要例外时，再使用 DERIVED_CONDITION_PUSHDOWN 或 NO_DERIVED_CONDITION_PUSHDOWN。这样不会把一条语句的试验性设置扩散到整个连接。

先判断它到底控制什么

派生条件下推针对的是“外层查询条件能否安全地向派生表内部移动”。例如下面的派生表包含聚合，不能简单依靠派生表合并消除；外层对分组列的条件可能进入内部 WHERE，对聚合结果的条件则更接近内部 HAVING。

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS summary
WHERE customer_id > 100
  AND total_amount > 5000;

这里真正要控制的是 derived_condition_pushdown，不是泛泛地打开所有优化器能力。它与 derived_merge 的职责也不同：前者讨论条件向派生表内部移动，后者讨论派生表是否可以合并进外层查询。先把这两个问题分开，排障时才不会反复修改错误的开关。

用 optimizer_switch 控制会话级默认行为

先读取当前连接的配置，再决定是否调整。MySQL 8.4 手册说明，derived_condition_pushdown 默认是开启的；显式设置的价值主要在于测试、回归对比或需要为当前会话固定行为。

-- 查看当前连接的派生条件下推配置
SELECT @@SESSION.optimizer_switch;

-- 只在当前会话关闭该优化，避免影响其他连接
SET SESSION optimizer_switch = 'derived_condition_pushdown=off';

-- 当前会话完成对比后恢复为开启
SET SESSION optimizer_switch = 'derived_condition_pushdown=on';

注意 optimizer_switch 是一组以逗号分隔的开关。生产连接里不要把整串值凭手工抄写后覆盖掉，否则可能连其他优化开关一起丢失。更稳妥的做法是先保存当前值，在隔离会话中做单项对比，并在连接归还连接池前恢复设置。

如果问题只在一条 SQL 上出现，会话级关闭通常过重：它会改变同一连接后续所有符合条件的查询。此时应优先考虑下一节的查询级 hint。

会话开关和查询级 hint 怎么选

MySQL 提供查询级的 DERIVED_CONDITION_PUSHDOWN 和 NO_DERIVED_CONDITION_PUSHDOWN。它们适合把实验范围限制在一个查询块，特别适合比较同一连接中两条 SQL 的计划差异。

-- 只为这条语句启用派生条件下推
SELECT /*+ DERIVED_CONDITION_PUSHDOWN */ *
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS summary
WHERE customer_id > 100;

-- 只为这条语句禁用派生条件下推
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN */ *
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS summary
WHERE customer_id > 100;

选择可以按这个顺序：默认行为适合大多数查询时保持会话配置不变；需要临时对比时使用单条 hint；只有要在一个专用会话中批量测试时，才调整 SESSION optimizer_switch。hint 也不是“强制得到更快结果”，它只是告诉优化器是否考虑这项优化，最终仍受查询结构和可安全下推条件的限制。

用 EXPLAIN 检查是否值得保留

修改开关后不要只看参数值就下结论，应该把同一条 SQL 在两种设置下的执行计划放在一起比较。可以先用 EXPLAIN FORMAT=JSON，重点观察派生表的物化、过滤条件位置和估算行数变化。

-- 用 JSON 计划观察派生表和过滤条件的变化
EXPLAIN FORMAT=JSON
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS summary
WHERE customer_id > 100
  AND total_amount > 5000;

若计划没有变化，不代表开关失效，可能是这条查询没有可安全下推的条件，或者派生表已经走了另一种优化路径。尤其要注意非确定性表达式、递归公共表表达式等限制；不要为了让计划“看起来变化”而扩大开关范围。

生产落地时保留三项记录就够了：原始 SQL、会话或 hint 的具体设置、两份 EXPLAIN 计划。这样后续升级 MySQL 或改变索引后，可以重新判断，而不是凭一次测试结果永久关闭优化。

常见问题

关闭 derived_condition_pushdown 会同时关闭 derived_merge 吗？
不会。两者控制的优化方向不同，应该分别根据执行计划和查询结构判断。

为什么设置为 on 后执行计划还是没有变化？
开关只是允许优化器考虑派生条件下推，实际是否下推还取决于条件能否安全移动、派生表结构以及最终成本判断。

应该改 GLOBAL 还是 SESSION？
做单条排障或回归时优先使用 SESSION；需要长期改变默认值前，先用隔离连接和查询级 hint 完成对比。