exists 能走 semi-join 是因语义天然匹配，但需满足 select 1、等值关联、索引完备等硬约束；否则退化为 dependent subquery，导致全表扫描。
EXISTS 为什么能走 Semi-Join，但经常没走成
EXISTS 本身语义就是“是否存在匹配行”，优化器天然倾向把它转成 Semi-Join——但前提是它真能识别出这个意图。一旦漏掉关联条件、子查询里塞了聚合、或 SELECT 列不是常量，优化器就会放弃转换，退化为 DEPENDENT SUBQUERY，每行都全表扫一遍内表。
-
EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)✅ 有明确等值关联，customer_id有索引，大概率走FirstMatch策略 -
EXISTS (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id)❌ 含聚合函数，强制物化，EXPLAIN显示Using temporary; Using filesort -
EXISTS (SELECT customer_id FROM orders o WHERE o.customer_id = c.id)❌ SELECT 列是字段名而非常量，某些 MySQL 版本会误判需回表取值，跳过 Semi-Join - 子查询中
WHERE条件用了函数（如DATE(created_at) = '2025-01-01'），索引失效，Semi-Join 也跟着失效
看执行计划确认 Semi-Join 是否生效
别猜，直接用 EXPLAIN FORMAT=TREE 看输出里有没有 Semi-join (duplicate removal) 或 FirstMatch 字样。如果看到的是 MATERIALIZE 或 Cache，说明优化器在把子查询结果缓存成临时结构，这不是 Semi-Join，而是更重的物化路径。
- 真正走 Semi-Join 的典型提示：
-> Semi-join (firstmatch)或-> Semi-join (duplicateweedout) - 危险信号：
type=ALL+Extra=Using where; Using join buffer，代表嵌套循环+全表扫描 - 检查
rows_examined_per_scan：Semi-Join 下这个值应接近 1（找到第一个就停）；若等于内表总行数，说明短路没生效
让 EXISTS 稳定触发 Semi-Join 的实操要点
想让它稳定走 Semi-Join，得同时满足语法、索引、配置三方面硬约束，缺一不可。
- 子查询必须写
SELECT 1，不能是SELECT *或任意字段名 - 关联字段（如
o.customer_id = c.id）两边类型必须严格一致，否则隐式转换使索引失效 - 内表被关联字段（
orders.customer_id）必须有单列索引，或复合索引的最左前缀；若还有范围条件（如AND o.created_at > '2025-01-01'），复合索引顺序必须是(customer_id, created_at) - 确认
optimizer_switch中semijoin=on（MySQL 默认开启，但线上环境可能被改过） - 避免在子查询里用
OR、UNION、GROUP BY，这些直接禁用 Semi-Join 转换
当 Semi-Join 失效时，手动改写比硬扛更可靠
一旦发现 EXPLAIN 里没出现 Semi-Join 提示，且内表扫描行数爆炸，别纠结“为什么没自动优化”，直接手写等价逻辑。最稳的替代是 LEFT JOIN + IS NOT NULL，它显式控制驱动顺序和索引使用。
- 原句：
SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'shipped') - 改写后：
SELECT DISTINCT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped' WHERE o.customer_id IS NOT NULL - 注意：
DISTINCT不可省——一个客户多笔已发货订单会导致重复行；ON里必须包含所有过滤条件，不能挪到WHERE，否则变成交叉连接 - 如果
orders表数据量极大，而匹配率极低（比如只有 0.5% 订单是 shipped），Semi-Join 的FirstMatch策略反而白跑大量行，这时手写 JOIN 反而更可控
Semi-Join 不是开关一开就自动生效的魔法，它是优化器在一堆硬性条件满足后的主动选择。最容易被忽略的，其实是子查询里那个看似无害的 SELECT * 或关联字段上缺失的索引——它们会让整个优化路径静默失效。
