EXPLAIN FORMAT=TREE是唯一可靠入口，必须用它确认物化或半连接是否生效；MATERIALIZE、FIRSTMATCH、LOOSESCAN三类节点代表优化成功，其余如DUPLICATEWEEDOUT或仅Filter+Table scan表明退化为低效执行。
EXPLAIN FORMAT=TREE 是唯一可靠入口
MySQL 8.0+ 必须用 EXPLAIN FORMAT=TREE 查看是否触发物化或半连接——传统 EXPLAIN 的 select_type 和 Extra 列信号太模糊，容易误判。比如 select_type=SUBQUERY 看似正常，但若没配合 FirstMatch 或 Materialize 提示，实际仍是逐行执行。
执行时注意两点：
- 必须对完整 SQL 执行，不能只
EXPLAIN子查询部分 - 确保
optimizer_switch中materialization=on和semijoin=on均启用（默认开启，但线上可能被改过）
看懂 FORMAT=TREE 输出里的关键节点
输出中真正代表优化生效的节点类型只有三种：
-
MATERIALIZE：子查询被提前执行并缓存为临时表，后续外层查询直接查该表 -
FIRSTMATCH：对内表做短路扫描，找到第一行匹配即退出，对应Extra: Using where; FirstMatch(table_name) -
LOOSESCAN：利用索引去重扫描，要求关联字段有唯一索引或主键，Extra显示Using where; LooseScan(table_name)
如果树结构里只有 -> Filter 套着 -> Table scan，或出现 DUPLICATEWEEDOUT，说明优化器退到了兜底策略，性能大概率已受损。
为什么加了索引还是没物化？检查这三处
常见误区是给子查询 WHERE 条件字段建索引就完事，但物化失败往往卡在更隐蔽的地方：
- 子查询
SELECT列未被索引覆盖：例如SELECT user_id FROM orders WHERE status = 'paid'，即使orders(status)有索引，user_id不在索引中就得回表——应建联合索引INDEX(status, user_id) - 子查询含
LIMIT、GROUP BY或UNION：这些语法会直接禁用物化，优化器强制走DEPENDENT SUBQUERY - 子查询结果集过大：默认阈值约 1000 行，超过后优化器认为物化成本高于嵌套循环——可用
SELECT COUNT(*)预估规模，或临时关掉物化测试：SET optimizer_switch='materialization=off';
NOT IN 的 NULL 陷阱会绕过所有优化
NOT IN (SELECT ...) 在子查询结果含 NULL 时，整行被过滤（三值逻辑），且此时优化器无法转半连接，EXPLAIN 里必然出现 DEPENDENT SUBQUERY。这不是索引或配置问题，是语义限制。
替代方案只有两个：
- 改写为
NOT EXISTS，它不受NULL影响，且仍有机会触发FirstMatch - 在子查询中显式排除
NULL：NOT IN (SELECT id FROM t WHERE id IS NOT NULL)，但需确认业务允许此过滤
真正难排查的是那种“明明索引都建了、EXPLAIN看着也像优化了，但慢查询日志里依然高频出现”的情况——八成是子查询里藏了个隐式 CAST 或函数调用，比如 WHERE id + 0 = ?，让索引失效，也同时让物化判定失败。
