MySQL optimizer_switch derived_merge 怎样临时关闭派生表合并

如果你只想让当前连接暂时不要合并派生表，不需要修改服务器启动参数，也不应该直接改全局配置。最小做法是执行 SET SESSION optimizer_switch='derived_merge=off'，它只影响当前会话；排查结束后再用 derived_merge=default 或 derived_merge=on 恢复。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/switchable-optimizations.html

本文只讨论这个开关的临时使用、范围和结果判断。MySQL 文档说明，derived_merge 默认开启，用来控制派生表、视图引用和 CTE 是否尝试合并进外层查询块。

先理解 derived_merge 控制什么

派生表大致有两种处理方向：合并到外层查询块，或者物化成内部临时表。合并后，优化器可以把外层条件继续推入派生表；关闭 derived_merge 后，优化器不再主动尝试这类合并，但其他规则仍可能决定最终计划。

这不是“强制所有子查询都生成临时表”的万能开关。聚合、DISTINCT、GROUP BY、LIMIT、UNION 等构造本身就可能阻止合并；视图的 ALGORITHM 规则也可能优先于该开关。

先读取当前会话的开关状态

临时调整前先记录原值，避免排查结束后忘记恢复。下面的查询读取当前连接看到的完整开关字符串，再单独判断 derived_merge 是否为 on。

-- 读取当前连接的优化器开关，先保留原状态
SELECT @@SESSION.optimizer_switch;

-- 只判断派生表合并当前是否开启
SELECT @@SESSION.optimizer_switch LIKE '%derived_merge=on%' AS derived_merge_enabled;

optimizer_switch 是一组以逗号分隔的开关。不要把某一次查询返回的完整字符串硬编码回去，因为不同版本或环境的默认项可能不同。

只在当前会话临时关闭

用 SESSION 明确表达范围。省略作用域时通常也是会话级，但写出来更容易在排障记录中确认意图。

-- 仅关闭当前连接的派生表合并尝试
SET SESSION optimizer_switch = 'derived_merge=off';

-- 立即读取当前连接，确认目标开关已变为 off
SELECT @@SESSION.optimizer_switch;

这个设置只跟当前连接绑定：连接池中的另一个连接不会自动继承。若应用在执行查询前重新取了连接，必须在那个实际执行查询的会话中设置，不能只在管理工具的另一个窗口里设置。

排查结束后恢复默认行为

如果只是临时排查，优先恢复该开关自己的默认值，而不是手工拼接整个配置字符串。MySQL 支持把单个优化项设为 default；当前版本的 derived_merge 默认值是 on。

-- 恢复当前会话中该优化项的默认值
SET SESSION optimizer_switch = 'derived_merge=default';

-- 也可以明确恢复为开启状态
SET SESSION optimizer_switch = 'derived_merge=on';

如果一次设置里包含多个命令，命令之间用逗号分隔；同一个优化项不要重复出现。设置值中任意一项出错，整个赋值会失败，原配置保持不变。

用 EXPLAIN 判断是否真的影响了计划

不要只看开关字符串就断言查询已经物化。先固定同一条查询，在默认状态和 derived_merge=off 状态下分别查看 EXPLAIN。合并时，派生表可能不再作为独立节点出现；物化时，计划才可能显示单独的派生表对象。

-- 用同一条查询观察派生表在计划中的表现
EXPLAIN
SELECT d.customer_id, d.total_amount
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS d
WHERE d.total_amount > 1000;

如果计划没有变化，常见原因不是设置失效，而是该查询本来就含有阻止合并的结构，或者其他优化规则已经决定了相同的路径。需要对比的是执行计划中的派生表处理方式，而不是只比较 SQL 文本。

常见误区与速查

• 想临时调整：使用 SET SESSION optimizer_switch='derived_merge=off'。
• 想影响所有新连接：那属于全局配置变更，不是本文的临时排障范围，应先评估连接池和业务影响。
• 想确认结果：记录原值，执行同一条 EXPLAIN，再用 default 恢复。

相关问题

derived_merge 关闭后一定会生成临时表吗？不应这样绝对理解；它表示不再尝试派生表合并，最终计划仍受其他规则影响。

为什么新连接看不到这个设置？因为 SESSION 设置属于当前连接，连接池切换连接后需要在实际执行查询的会话中重新设置。