EXISTS因语义为存在性判断且天然短路，优化器更易识别并转为半连接；IN需物化子查询结果，仅当无关联列且结果集小时才可能触发半连接，否则常降级为嵌套循环或临时表查找。
EXISTS 为什么走半连接（semi-join）而 IN 不一定走
MySQL 5.6+ 默认将 EXISTS 重写为半连接（semi-join），这是优化器主动选择的执行策略，核心目标是“只确认存在性，不取值、不去重、不回表”。而 IN 子查询在旧版本（如 5.7 及之前）常被重写为 LEFT JOIN + DISTINCT 或临时表哈希匹配，尤其当子查询含 GROUP BY、ORDER BY 或无索引字段时，优化器大概率放弃 semi-join 路径。
是否走半连接，关键看执行计划中是否有 Using semi-join 提示。你可以在 EXPLAIN FORMAT=TREE 输出里直接看到类似：
-> Semi-join (duplicate removal)
而等价的 IN 查询通常显示为 Materialize 或 Cache，意味着它真把子查询结果缓存下来了。
什么时候 IN 的执行计划反而比 EXISTS 更优
不是所有场景 EXISTS 都更快。当出现以下情况时，IN 可能因更早利用外表索引或更小的结果集而胜出：
- 子查询返回结果极少（比如 IN 可走
range或ref访问，而EXISTS因驱动外表全扫 + 每行查内表，反而触发ALLon inner - 子查询本身带
LIMIT 1或确定性过滤（如WHERE id = ?），IN被优化为常量传播，EXISTS却仍按循环逻辑展开 - MySQL 版本较老（optimizer_switch='semijoin=on'，
EXISTS退化为普通嵌套循环
简单验证：对同一语句跑 EXPLAIN FORMAT=JSON，重点对比 rows_examined_per_scan 和 using_join_buffer 字段——前者越小越好，后者出现往往意味着性能风险。
如何强制 EXISTS 走半连接并避免常见翻车
即使写了 EXISTS，也不代表一定走半连接。容易被忽略的三个硬性条件：
- 子查询中必须有明确的关联条件，例如
WHERE c.id = o.customer_id；如果漏写或写成c.id = 123这种常量，优化器会当作独立子查询，失去半连接资格 - 子查询不能含
SELECT *以外的聚合、窗口函数或DISTINCT；哪怕只加一个COUNT(*)，整个路径就降级为 materialize -
EXISTS子查询的SELECT列必须是常量（推荐SELECT 1），不能是字段名或表达式；某些版本对SELECT id会误判为需要回表取值
错误示例：EXISTS (SELECT customer_id FROM customers WHERE status = 'active') —— 缺少关联条件，等价于 EXISTS (SELECT 1 FROM customers WHERE status = 'active' LIMIT 1)，但优化器无法推导出半连接语义。
NOT IN 和 NOT EXISTS 的执行计划差异更大
这是最容易踩坑的点：NOT IN 遇到子查询结果含 NULL 时，整条条件恒为 UNKNOWN，直接返回空结果集——这不是慢，是错。而 NOT EXISTS 完全不受 NULL 影响，且同样走半连接（准确说是 anti-semi-join）。
执行计划上，NOT EXISTS 会出现 Using anti-semi-join，而 NOT IN 常伴随 Using where; Not exists，后者实际是先 materialize 再逐行判断，内存和 CPU 开销都更高。
所以只要逻辑允许用 NOT EXISTS，就不要碰 NOT IN。哪怕子查询结果确定不含 NULL，也建议加 IS NOT NULL 显式约束，否则优化器不敢轻易启用半连接。
