MySQL优化器为何会把IN子查询自动改写为EXISTS？

作者：袖梨 2026-07-09

MySQL 5.7+ 默认将 IN (SELECT...) 自动重写为 EXISTS（半连接），由 optimizer_switch 中 semijoin=on 控制；但 NOT IN 不会自动转换，必须手动改写为 NOT EXISTS，且性能取决于索引、类型一致性和无函数操作。

MySQL 5.7+ 默认把 IN 改写为 EXISTS 是优化器行为，不是你手动写的语法生效

MySQL 5.7 及之后版本的优化器对 IN (SELECT ...) 这类非固定列表子查询，**默认启用 semi-join 优化策略**，会自动重写为等价的 EXISTS 形式（即半连接），目的是避免物化整个子结果集。这不是“建议你改”，而是它已经悄悄替你改了——只要你没禁用相关优化开关。

常见错误现象：EXPLAIN 显示 type=eq_ref 或 type=ref，Extra 里没有 Using temporary，但原 SQL 写的是 IN；执行计划里出现 /* select#1 */ SELECT ... FROM t2 JOIN t1 WHERE t1.col = t2.col ——这就是优化器重写后的痕迹。

该行为受 optimizer_switch 控制，关键开关是 semijoin=on 和 materialization=off（默认开启）
若子查询含 GROUP BY、LIMIT 或聚合函数，优化器可能退回到物化策略，EXPLAIN 就会显示 Using temporary
IN ('a','b','c') 这种字面量列表不会被改写，仍走 hash 比较，和 EXISTS 无关

为什么改写后不一定更快？关键看索引是否下推

改写本身不等于性能提升。优化器把 IN 变成 EXISTS 只是第一步，真正卡住的地方是：关联字段有没有索引、类型是否一致、外层条件是否可下推。

例如 WHERE user_id IN (SELECT user_id FROM logs WHERE status = 'error')，即使被重写为 EXISTS，如果 logs(status) 没索引，或 logs(status, user_id) 联合索引缺失，优化器仍会扫全表——这时 EXISTS 和 IN 一样慢。

确保子查询中 WHERE 条件字段有索引，且联合索引把关联字段放在最后（如 (status, user_id)）
外层字段和子查询关联字段类型必须严格一致，比如 orders.user_id BIGINT 对应 users.id BIGINT，别用 CAST(id AS CHAR)
避免在关联字段上用函数，如 WHERE DATE(created_at) = '2026-07-01' 会让索引失效，改写再彻底也白搭

NOT IN 不能靠优化器自动修复，必须手动改成 NOT EXISTS

NOT IN 是特例：优化器**不会**自动把它转成 NOT EXISTS，因为语义不同——NOT IN 遇到子查询返回 NULL 时整个条件恒为 FALSE，而 NOT EXISTS 不受 NULL 影响。

直接执行 WHERE col NOT IN (SELECT col FROM t2)，只要 t2.col 有任意 NULL，结果就为空，且优化器大概率放弃索引，走 type=ALL 扫描。

必须显式改写为 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col)
千万别用 LEFT JOIN ... WHERE t2.col IS NULL 混充，如果 t2.col 允许 NULL，这个写法会漏掉匹配 NULL 的行
NOT EXISTS 子查询里 SELECT 1 和 SELECT * 没区别，但别写 SELECT *——字段多了可能干扰优化器判断