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

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 *——字段多了可能干扰优化器判断

什么时候该信优化器，什么时候得自己动手？

信优化器的前提是：子查询干净、索引到位、无隐式转换。一旦 EXPLAIN 显示 Using temporary 或 Using filesort，说明重写失败或不充分，就得人工干预。

• 子查询本身慢？先单独 EXPLAIN 它，加索引，再看整体
• 外层数据量小（比如只查 10 行用户），子查询表大（比如日志表千万行）→ 强制用 EXISTS 写法，让驱动表可控
• 子查询带 DISTINCT 或 ORDER BY？优化器大概率放弃 semi-join，此时手写 JOIN 反而更稳
• 不确定时，用 SHOW WARNINGS 看优化器实际重写了什么 SQL，比猜靠谱

真正决定快慢的，从来不是 IN 还是 EXISTS 这两个词，而是那一行 EXPLAIN 输出里有没有 key、rows 是多少、Extra 里有没有刺眼的警告。