EXPLAIN中key为NULL主因是关联子查询触发Nested-Loop逐行执行，或JOIN字段类型/字符集不一致导致优化器弃用索引；须比对collation、统一类型，并优先用EXISTS替代IN。
关联子查询本身不直接导致索引失效，但它的执行方式会让优化器放弃使用被驱动表上的索引——尤其是当子查询无法被重写为 JOIN 或未满足驱动表选择条件时。
为什么EXPLAIN里key是NULL，但JOIN字段明明有索引
这是最典型的误判信号。MySQL 在处理 IN / EXISTS 关联子查询时，会先执行外层查询，再对每行结果去执行一次子查询（即 Nested-Loop 行为）。如果子查询里涉及的字段没被选作驱动表的等值条件，或字段类型/字符集不一致，优化器就无法复用索引。
- 常见现象：
EXPLAIN显示type=ALL或type=range，key=NULL，Extra出现Using where; Using index condition但扫描行数爆炸 - 真实原因：不是“索引没了”，而是“这条子查询语句没触发索引查找路径”，优化器把它当成了一个需要逐行计算的过滤器
- 验证方法：把子查询单独拿出来跑一遍
EXPLAIN，看它自己是否走索引；再和主查询合并后对比执行计划变化
IN vs EXISTS：哪个更容易踩坑
IN 和 EXISTS 在语义上不同，但 MySQL 对它们的优化策略差异极大——尤其在被驱动表数据量大、外层结果集小的时候。
-
IN (SELECT ...)：若子查询返回空结果或 NULL，整个条件可能变为 UNKNOWN，导致优化器退化为全表扫描；更危险的是，如果子查询字段类型与外层不一致（比如user_id INT对比sub.user_id VARCHAR），会触发隐式转换，索引直接失效 -
EXISTS (SELECT 1 FROM ... WHERE t1.id = t2.ref_id)：只要t2.ref_id上有索引且类型匹配，通常能命中；但如果WHERE条件里对t2.ref_id做了函数操作（如UPPER(t2.ref_id)），索引同样失效 - 实操建议：优先用
EXISTS替代IN，并确保关联字段两侧完全同类型、同COLLATION；用SHOW FULL COLUMNS FROM table_name LIKE 'ref_id'对比两端定义
如何把关联子查询安全转成JOIN
不是所有子查询都能无损转 JOIN，但绝大多数业务场景下，只要语义允许去重或保留主表全部行，就可以重构。
- 能直接转的情况：
SELECT * FROM t1 WHERE t1.id IN (SELECT t2.t1_id FROM t2 WHERE t2.status = 1)→ 改成SELECT DISTINCT t1.* FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id WHERE t2.status = 1 - 必须加
DISTINCT或GROUP BY：否则 JOIN 可能因一对多关系放大结果集，和原语义不符 - 不能简单转的情况：子查询含聚合（
COUNT、MAX）、LIMIT、或依赖外层字段做相关子查询（如WHERE x > (SELECT AVG(y) FROM t2 WHERE t2.a = t1.a)）；这类需拆解为派生表或窗口函数 - 关键检查点：改完后用
EXPLAIN看key是否非 NULL，rows是否显著下降，且Extra不再出现Using temporary或Using filesort
字符集不一致是隐藏最深的杀手
哪怕你把子查询改成 JOIN，只要关联字段的 COLLATION 不同，索引照样失效——而且 EXPLAIN 看不出明显异常，只会默默变成 type=ALL。
- 典型错误：
t1.name是utf8mb4_0900_as_cs，t2.name是utf8mb4_unicode_ci，即使都属utf8mb4字符集，JOIN 时也会放弃索引 - 查法：
SELECT column_name, collation_name FROM information_schema.COLUMNS WHERE table_name IN ('t1', 't2') AND column_name = 'name'; - 修法：必须用
ALTER TABLE t2 MODIFY name VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;，只改CONVERT TO或只设COLLATE都无效 - 注意：JDBC 连接串里的
collation参数不会覆盖字段级COLLATION，它只影响连接默认排序行为
真正卡住人的从来不是“会不会写 SQL”，而是“有没有意识到子查询背后驱动表的选择逻辑、字段比较的实际执行路径、以及字符集这种底层细节对索引的硬性约束”。每次看到 key=NULL，先别急着加索引，花三十秒查两端字段的 COLLATION 和类型，往往比调优一小时更有效。
