子查询本身不必然导致索引失效，但MySQL 5.7及更早版本中IN(SELECT...)或=(SELECT...)这类非物化子查询会使外层表WHERE条件完全绕过索引，优化器采用“先执行、后匹配”策略，外层表全表扫描。
子查询本身不必然导致索引失效，但 MySQL 5.7 及更早版本中，IN (SELECT ...) 或 = (SELECT ...) 这类非物化子查询，会让外层表的 WHERE 条件完全绕过索引——不是“没建好”，而是优化器压根不尝试用它。
MySQL 5.7 及之前：子查询触发“先执行、后匹配”策略
优化器对这类子查询默认采用非物化（non-materialized）执行模式：先完整执行子查询（可能全表扫描），把结果存进临时集合，再拿这个结果集逐行去匹配外层表。整个过程外层表不走任何索引定位，EXPLAIN 中典型表现为 type: ALL 或 Extra: Using join buffer。
- 哪怕子查询里
WHERE status = 1能走索引，也救不了外层id IN (SELECT ...)的索引 - 子查询返回多行且没加
LIMIT 1，又未被自动物化，就大概率触发该路径 - MySQL 8.0+ 默认仍不自动物化，需显式加
/*+ MATERIALIZE */提示才可能生效
IN 改 JOIN 时最容易忽略的三个语义陷阱
这是最稳的改法，但直接套 INNER JOIN 很可能改出逻辑错误：
- 原
WHERE id IN (SELECT user_id FROM logs WHERE status = 1)允许user_id IS NULL，而JOIN会自动过滤掉 NULL 匹配 → 需补OR t.id IS NULL或改用LEFT JOIN ... WHERE tmp.user_id IS NOT NULL - 子查询含
GROUP BY或聚合（如COUNT），直接 JOIN 会导致主表行数被放大 → 必须先用派生表压平：FROM t INNER JOIN (SELECT DISTINCT user_id FROM logs WHERE status = 1) AS tmp ON t.id = tmp.user_id -
IN对重复值自动去重，JOIN若不加DISTINCT或聚合，可能让主表同一行被多次关联 → 结果条数膨胀
EXISTS 看似友好，但相关子查询会悄悄拖垮性能
EXISTS 通常能保留外层索引，因为它是半连接语义，优化器更容易下推条件。但前提是子查询不依赖外层字段；一旦变成相关子查询，问题就来了：
- 写成
WHERE EXISTS (SELECT 1 FROM logs WHERE logs.user_id = t.id AND logs.status = 1)，外层每行都触发一次子查询执行 → I/O 次数翻倍，即使索引在，延迟也线性增长 -
NOT IN和NOT EXISTS行为完全不同：NOT IN遇到子查询任意一行是NULL，整个条件直接判UNKNOWN，结果为空；NOT EXISTS完全不受影响 - 如果子查询本身很轻（比如只查主键），
EXISTS是优选；但如果子查询要扫大表或带复杂条件，不如先物化或改 JOIN
真正容易被忽略的是：子查询失效常和其它索引失效场景叠加。比如子查询里写了 UPPER(name)，或者外层 WHERE id = '123'（id 是 INT），这些都会单独触发隐式转换或函数失效——排查时若只盯着子查询，反而会漏掉真正瓶颈。
