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 里有没有刺眼的警告。
