MySQL 8.0+ 半连接（Semi-join）优化仅在满足全部条件时生效：子查询为非相关或弱相关单等值关联、无GROUP BY/聚合/LIMIT、NOT IN不支持、关联字段需有有效索引；否则仍走嵌套循环，性能可能更差。
MySQL 8.0+ 默认启用 Semi-join 优化，但仅当查询结构匹配且索引到位时才会真正生效；盲目依赖优化器自动改写，大概率仍走嵌套循环（type=ALL 或 type=DEPENDENT SUBQUERY），性能不升反降。
哪些 IN / EXISTS 子查询能触发 Semi-join
只有满足以下全部条件，优化器才可能将子查询转为 Semi-join：
- 子查询是独立的、非相关（non-correlated）或弱相关（如
WHERE t1.id = t2.ref_id这类单等值关联） - 外部查询表与子查询表之间无聚合、无
GROUP BY、无LIMIT在子查询内 - 子查询返回结果不为空（
NOT IN因 NULL 语义问题，不会 走 Semi-join） - 子查询中被关联字段（如
o.customer_id）在orders表上有有效索引
典型可优化结构示例：
SELECT * FROM customers c WHERE c.id IN (SELECT customer_id FROM orders o WHERE o.status = 'paid');
若 orders(customer_id) 无索引，即使语法合规，执行计划里仍会显示 type=ALL + Using where; Using join buffer —— 这是危险信号。
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
EXISTS 改写为 LEFT JOIN + IS NOT NULL 的实操要点
当 Semi-join 未命中（比如子查询含 OR、ORDER BY 或跨库表），手动改写更可控。核心是用 LEFT JOIN 模拟存在性判断，并用 IS NOT NULL 过滤。
- 原语句（低效）：
SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.created_at > '2025-01-01'); - 改写后（显式驱动+索引友好）：
SELECT DISTINCT c.* FROM customers c LEFT JOIN orders o ON c.id = o.customer_id AND o.created_at > '2025-01-01' WHERE o.customer_id IS NOT NULL;
-
o.customer_id必须与c.id类型严格一致（INT 对 INT），否则隐式转换使索引失效 - 复合索引应覆盖
(customer_id, created_at)，顺序不能颠倒——created_at是范围条件，放后面 -
DISTINCT不可省：因一个客户可能有多笔订单，JOIN 会产生重复行
为什么 Semi-join 有时比手写 JOIN 还慢
MySQL 的 Semi-join 策略（如 DuplicateWeedout、FirstMatch）虽减少回表，但仍有隐藏开销：
- 若子查询结果集大（如百万级订单），
DuplicateWeedout需维护临时去重结构，内存暴涨 -
FirstMatch在驱动表每行都尝试匹配，遇到第一个匹配就停；但如果匹配率低（如只有 0.1% 客户有新订单），等于白跑 999 行 - 优化器估算错误时，可能选错驱动表——比如拿大表
orders去驱动小表customers，造成rows量级飙升 -
EXPLAIN中看到select_type=SEMISPACE或Extra=Start temporary，说明已启用但代价高
此时直接强制用 STRAIGHT_JOIN 指定驱动顺序，比等优化器猜更稳：
SELECT STRAIGHT_JOIN c.* FROM customers c JOIN orders o ON c.id = o.customer_id AND o.created_at > '2025-01-01';
容易被忽略的索引细节
半连接效率最终卡在索引是否“够用”，而非“有没有”：
- 联合索引必须包含所有 WHERE 和 ON 中的字段，且顺序匹配最左前缀：例如
WHERE status='paid' AND created_at > '2025-01-01'，索引应建为(status, created_at, customer_id)，而非(customer_id, status) -
IN列表超过 200 项时，MySQL 可能放弃使用索引，退化为全表扫描；此时应拆成临时表JOIN或分批查 - 分区表上，若子查询条件未落在分区键上，Semi-join 无法下推到分区层，整个分区扫描照旧
-
EXPLAIN FORMAT=TREE（MySQL 8.0.16+）比传统格式更能看清 Semi-join 实际采用的策略，别只看type
