如何在MySQL 8.0中查看SQL优化器重写子查询为半连接的细节-mysql教程-PHP中文网

MySQL 8.0 中需启用 optimizer_trace 才能捕获子查询重写为半连接的过程，关键看 trace 中 join_optimization → semijoin_strategy 节点；结合 EXPLAIN FORMAT=TREE 可直观确认策略，而 IN 与 EXISTS 行为差异源于优化器对语义和条件推导的处理逻辑不同。

如何在mysql 8.0中查看sql优化器重写子查询为半连接的细节

用 `optimizer_trace` 捕获子查询重写过程

MySQL 8.0 默认不输出优化器内部决策，必须手动开启 optimizer_trace 才能看到子查询是否被转成半连接、用了哪种策略（如 Materialization 或 FirstMatch）。它比 EXPLAIN 更底层，能展示“为什么选这个计划”，而不是只告诉你“选了什么”。

操作步骤：

执行前先启用追踪：SET optimizer_trace="enabled=on,one_line=off";
运行你的目标 SQL，例如：SELECT * FROM ruleDetail a WHERE EXISTS (SELECT 1 FROM rule a0 WHERE a0.groupId=123456789 AND a0.id=a.ruleId);
查结果：SELECT * FROM information_schema.OPTIMIZER_TRACE;
关掉避免干扰：SET optimizer_trace="enabled=off";

关键看 trace 字段里的 join_optimization → semijoin_strategy 节点。如果出现 "strategy": "Materialization" 或 "strategy": "FirstMatch"，就说明优化器已将 EXISTS 子查询识别为半连接并应用了对应策略。

`EXPLAIN FORMAT=TREE` 直观确认半连接执行方式

EXPLAIN FORMAT=TREE 是 MySQL 8.0 新增的可读性更强的执行计划格式，会明确标出 -> Semi-join 和具体策略名，比传统 EXPLAIN 的 type 和 Extra 更直白。

示例输出片段：

-> Nested loop semi-join  (cost=2.25 rows=1)
    -> Table scan on a  (cost=1.05 rows=1)
    -> Materialize with deduplication  (cost=0.10 rows=1)
        -> Filter: (a0.groupId = 123456789)  (cost=0.35 rows=1)
            -> Index lookup on a0 using PRIMARY (id=a.ruleId)  (cost=0.35 rows=1)

这里 Materialize with deduplication 就对应物化策略；如果是 FirstMatch(a)，则表示对外表 a 的每行只查内表一次匹配即停。

注意：FORMAT=TREE 不显示成本估算细节，但它和 optimizer_trace 结合使用，能快速定位“是否触发”和“怎么触发”两个层次的问题。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

为什么 `IN` 和 `EXISTS` 在 trace 中表现可能不同

优化器对 IN 和 EXISTS 的处理逻辑并不总是一致——尤其当子查询含聚合、GROUP BY 或不可推导的条件时，IN 可能退化为非半连接路径，而 EXISTS 更容易被识别为半连接候选。

常见差异点：

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE ...)：若子查询无 DISTINCT 且外键关系清晰，通常能走半连接；但若子查询含 ORDER BY 或 LIMIT，优化器可能放弃半连接优化
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.ref = t1.id)：只要内表有可用索引（如 t2.ref 上的索引），几乎总是触发半连接，且优先尝试 FirstMatch 或 Materialization
空值（NULL）存在时，IN 的语义更复杂，优化器可能绕过半连接以保证正确性；EXISTS 对 NULL 不敏感，更稳定

验证方法：在 optimizer_trace 的 steps 数组里搜索 "subquery_transformations" 和 "semijoin_transformations"，看是否有 "transformed": true 标记。

容易忽略的配置与陷阱

即使语法符合条件，半连接也可能被禁用或降级——不是所有 EXISTS 都自动优化。

关键影响因素：

optimizer_switch 中的 semijoin 和 materialization 必须为 on（默认是开启的，但某些运维脚本可能改过）：SELECT @@optimizer_switch LIKE '%semijoin=on%';
子查询中若引用了外部列但未出现在 WHERE 条件的等值判断中（如 WHERE c.name LIKE CONCAT('%', a.keyword, '%')），无法使用索引，优化器大概率跳过半连接
外表（主查询）数据量极小（如 SELECT * FROM (SELECT 1 AS x) t WHERE EXISTS (...)），优化器可能认为嵌套循环更便宜，不触发物化
临时表引擎限制：物化策略依赖内部临时表，若 tmp_table_size 或 max_heap_table_size 过小，物化会回退到磁盘临时表，甚至放弃该策略