怎样在SQL中排查关联子查询引起的索引失效问题-SQL-PHP中文网

EXPLAIN中key为NULL主因是关联子查询触发Nested-Loop逐行执行，或JOIN字段类型/字符集不一致导致优化器弃用索引；须比对collation、统一类型，并优先用EXISTS替代IN。

怎样在sql中排查关联子查询引起的索引失效问题

关联子查询本身不直接导致索引失效，但它的执行方式会让优化器放弃使用被驱动表上的索引——尤其是当子查询无法被重写为 JOIN 或未满足驱动表选择条件时。

这是最典型的误判信号。MySQL 在处理 IN / EXISTS 关联子查询时，会先执行外层查询，再对每行结果去执行一次子查询（即 Nested-Loop 行为）。如果子查询里涉及的字段没被选作驱动表的等值条件，或字段类型/字符集不一致，优化器就无法复用索引。

常见现象：EXPLAIN 显示 type=ALL 或 type=range，key=NULL，Extra 出现 Using where; Using index condition 但扫描行数爆炸
真实原因：不是“索引没了”，而是“这条子查询语句没触发索引查找路径”，优化器把它当成了一个需要逐行计算的过滤器
验证方法：把子查询单独拿出来跑一遍 EXPLAIN，看它自己是否走索引；再和主查询合并后对比执行计划变化

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，但绝大多数业务场景下，只要语义允许去重或保留主表全部行，就可以重构。

能直接转的情况：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，它只影响连接默认排序行为