子查询本身不必然导致索引失效，但MySQL 5.7及更早版本中，IN(SELECT...)或=(SELECT...)等非物化子查询会强制优化器先执行子查询再匹配外层，绕过外层索引，EXPLAIN显示type:ALL或Using join buffer即为典型信号。
子查询本身不必然导致索引失效，但 MySQL 5.7 及更早版本中，IN (SELECT ...) 或 = (SELECT ...) 这类非物化子查询会强制优化器先执行子查询、再匹配外层，从而绕过外层表的索引——这是最常见、最隐蔽的失效原因。
EXPLAIN 显示 type: ALL 或 Using join buffer 就是典型信号
这不是“没建索引”，而是优化器压根没尝试用索引。关键看外层 WHERE 是否被跳过：
-
EXPLAIN中外层表的type是ALL，且key为NULL，说明完全没走索引 -
Extra出现Using where; Using join buffer，代表匹配逻辑移到 Server 层，InnoDB 引擎层索引下推（ICP）已失效 - 即使子查询自己
EXPLAIN显示能走索引，也救不了外层——两层执行计划不互通
把 IN (SELECT) 改成 JOIN 时必须处理的三个细节
语法改写只是第一步，语义不对等会引入数据偏差：
- 原
WHERE id IN (SELECT user_id FROM logs WHERE status = 1)→ 改为INNER JOIN logs ON t.id = logs.user_id AND logs.status = 1 - 若
logs.user_id允许为NULL，JOIN 会自动过滤掉这些行，而原IN在遇到NULL时整个条件判为UNKNOWN；需补OR t.id IS NULL或改用LEFT JOIN ... WHERE logs.user_id IS NOT NULL - 子查询含
GROUP BY或聚合（如COUNT），直接 JOIN 会放大主表行数；应先用派生表压平：INNER JOIN (SELECT DISTINCT user_id FROM logs WHERE status = 1) AS tmp ON t.id = tmp.user_id
EXISTS 能保留外层索引，但不是万能解药
它只判断存在性，优化器更容易下推条件，所以外层通常还能走索引。但陷阱明显：
- 相关子查询（引用外层字段）会导致每行都触发一次子查询执行，I/O 次数可能翻倍——索引虽在，性能未必好
-
NOT IN和NOT EXISTS行为完全不同：NOT IN遇到子查询任意一行是NULL，结果集直接为空；NOT EXISTS完全不受影响 - 改写必须严格对应：原
WHERE x IN (SELECT y FROM t2 WHERE cond)→WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.y = t1.x AND cond)；漏掉t2.y = t1.x就变成笛卡尔积
视图里嵌套子查询会让索引下推彻底失效
哪怕视图定义简单如 CREATE VIEW v AS SELECT * FROM (SELECT id, name FROM user WHERE status = 1) t，后续查 SELECT * FROM v WHERE age > 30 就无法触发 ICP——因为 age 不是直接来自基表扫描，而是派生表输出。
-
EXPLAIN中Extra从Using index condition退化为Using where，联合索引中非最左列（如(name, age)中的age）过滤失效 - 含
UNION、DISTINCT、函数（如UPPER(name)）、窗口函数的视图，ICP 几乎必然关闭 - 替代方案优先级：内联逻辑 > CTE（MySQL 8.0+ 对简单 CTE 可能保留下推） > 视图；高频字段可单独建覆盖索引，减少对 ICP 的依赖
真正难处理的不是语法改写，而是类型隐式转换和 NULL 语义——它们藏在字段定义和数据分布里，EXPLAIN 看不出，但会让所有优化努力归零。
