IN先执行子查询物化结果集再哈希匹配，EXISTS对外表逐行扫描并短路判断；IN依赖外表索引，EXISTS依赖内表索引；NOT IN有NULL陷阱，NOT EXISTS更安全可靠。
IN 和 EXISTS 的执行逻辑本质不同
IN 是先执行子查询，拿到完整结果集（比如 SELECT id FROM large_table WHERE status = 1），再拿这个结果集去和外表做哈希匹配；EXISTS 则是对外表逐行扫描，每行都带入子查询中做一次关联判断（比如 SELECT 1 FROM large_table WHERE large_table.id = outer_table.id），只要子查询返回一行就短路退出。
这意味着：IN 的子查询必须“跑完”，而 EXISTS 的子查询可能只查一条就停。但反过来，IN 能利用外表字段的索引加速匹配，EXISTS 却只能靠内表字段的索引——外表索引在 EXISTS 中基本用不上。
- IN 适合：子查询结果集小、外表大、外表相关字段有索引（如
WHERE user_id IN (SELECT id FROM temp_ids)） - EXISTS 适合：子查询结果集大、外表小、内表关联字段有索引（如
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)） - 别被“小表驱动大表”误导——真正决定效率的是「外表行数」和「子查询结果集大小」，不是物理表大小
看执行计划比背口诀更可靠
MySQL 8.0+ 的 EXPLAIN FORMAT=TREE 或传统 EXPLAIN 能直接暴露关键差异：
-
IN通常显示为materialized+hash join，子查询被物化成临时哈希表，外表走索引查找 -
EXISTS显示为dependent subquery或semijoin，外表扫描后触发 Block Nested Loop（BNL），内表走索引范围查找 - 如果
EXISTS的子查询里出现Using where; Using index，说明内表索引生效了；而IN如果外表没走索引，会出现type: ALL，性能会断崖下跌
实操建议：对同一语义的 SQL，分别跑 EXPLAIN，重点对比 rows、type、key 三列，而不是只看 Extra 里有没有 “Using index”。
NOT IN 的空值陷阱比性能问题更致命
只要子查询返回任意一个 NULL，NOT IN 整个条件就恒为 UNKNOWN，最终结果为空集——这是 SQL 三值逻辑导致的语义错误，不是慢，是错。
- 例如：
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM logs)，若logs.user_id允许为 NULL，哪怕只有一条 NULL，这条语句就查不出任何用户 -
NOT EXISTS没这个问题，它只关心“是否存在匹配行”，NULL 不影响布尔判断 - 即使你确认子查询字段非空，也建议统一用
NOT EXISTS，避免未来加字段或改约束时埋雷
性能上，NOT EXISTS 几乎总是快于 NOT IN，因为前者能用内表索引，后者强制全表扫描内外表。
真实场景下别硬选，优先让优化器决定
MySQL 5.7+ 对 IN 和 EXISTS 的等价重写越来越成熟。如果你写的 EXISTS 语义清晰、关联条件明确，优化器可能自动转成半连接（semijoin）并选择最优路径；反过来，IN 子查询若带聚合或复杂条件，优化器也可能拒绝物化，退化成类似 EXISTS 的行为。
- 确保内表关联字段有索引（如
orders.user_id），这是 EXISTS/NOT EXISTS 快起来的前提 - 子查询尽量简单，避免
ORDER BY、LIMIT、窗口函数——这些会让优化器放弃物化，IN 失去优势 - 当不确定时，用
SELECT ... FROM t1 WHERE col IN (SELECT col FROM t2 WHERE ...)更安全：语义直观、兼容性好、多数情况下优化器能处理好
最常被忽略的一点：EXISTS 的子查询里写 SELECT * 或 SELECT 1 没区别，但千万别写 SELECT COUNT(*)——那会强制遍历全部匹配行，彻底破坏短路逻辑。
