mysql 5.7及更早版本中，in(select...)等非物化子查询导致外层where无法走索引，因优化器先执行子查询再匹配外层，绕过外层索引，explain显示type:all或using join buffer。
子查询让外层WHERE无法走索引的底层原因
MySQL 5.7 及更早版本中，IN (SELECT ...) 或 = (SELECT ...) 这类非物化子查询，会触发“先执行子查询、再匹配外层”的策略。这意味着优化器根本不会尝试用外层表的索引去加速关联，而是先把子查询结果全拉出来（可能全表扫），再拿这个结果集去逐行比对外层表——外层索引完全被绕过。
典型表现是 EXPLAIN 中出现 type: ALL 或 Extra: Using where; Using join buffer。哪怕子查询本身能走索引，也救不了外层。
- 子查询返回多行且没加
LIMIT 1，又没被优化器自动物化，就大概率触发该问题 - MySQL 8.0+ 虽支持
/*+ MATERIALIZE */提示，但默认不生效，需显式指定 -
EXISTS是例外：它只判断存在性，优化器更容易下推条件，外层索引通常还能用
把 IN (SELECT) 改成 JOIN 的关键细节
这是最稳、兼容性最好的改法，但不是简单套语法就行。核心是保证语义等价：去重逻辑、NULL 处理、重复行行为必须和原 IN 一致。
- 原语句：
WHERE id IN (SELECT user_id FROM logs WHERE status = 1)→ 改为INNER JOIN logs ON t.id = logs.user_id AND logs.status = 1 - 如果子查询可能返回
NULL（比如user_id允许为空），JOIN会自动过滤掉这些行，而原IN语义中NULL会导致整个条件为UNKNOWN；此时得补OR t.id IS NULL或换用LEFT JOIN ... WHERE logs.user_id IS NOT NULL - 子查询含
GROUP BY或聚合时，直接JOIN会放大主表行数，应先用派生表压平：FROM t INNER JOIN (SELECT DISTINCT user_id FROM logs WHERE status = 1) AS tmp ON t.id = tmp.user_id
EXISTS 替代 IN 的适用边界与陷阱
EXISTS 通常能保留外层索引，但它不是万能解药，语义和性能都和 IN 不同。
- 原
WHERE x IN (SELECT y FROM t2 WHERE ...)可改为WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.y = t1.x AND ...) - 相关子查询（引用了外层字段）下，
EXISTS仍可能每行都触发一次子查询执行，I/O 次数翻倍——索引虽在，性能未必好 -
NOT IN和NOT EXISTS行为差异极大：NOT IN遇到子查询任意一行是NULL，整个结果变空；NOT EXISTS完全不受影响
函数、类型转换、模糊查询这些“老熟人”也在捣乱
子查询失效常和其它索引失效场景叠加，排查时容易误判。比如子查询里用了 UPPER(name)，或外层 WHERE id = '123'（id 是 INT），都会进一步恶化执行计划。
- 对子查询中的索引列用函数（如
WHERE DATE(create_time) = '2024-01-01'）→ 子查询自己就全表扫，外层更没机会 - 子查询字段和外层比较值类型不一致（如
user_id VARCHAR却写成= 123）→ 隐式转换让子查询无法走索引 -
LIKE '%abc'出现在子查询里 → 同样破坏子查询效率，连带拖垮整体
真正卡住性能的，往往不是单一问题，而是子查询策略 + 外层写法 + 索引设计三者叠加。查 EXPLAIN 时，得一层层看子查询是否物化、外层 key 是否非空、rows 是否严重偏离实际。
