关联子查询导致全表扫描是因为数据库必须为外层每一行重新执行内层查询，若内层无索引，n行外层数据将触发n次全表扫描，性能线性恶化。

关联子查询导致全表扫描，不是因为语法“错”，而是数据库必须为外层每一行重新执行内层查询；只要内层没走索引，100 行外层数据就会触发 100 次全表扫描——性能是线性恶化的，不是慢一点。

为什么DEPENDENT SUBQUERY一定扫很多次

只要子查询里引用了外层表字段（比如 u.department 或 o.user_id），它就是关联子查询。数据库无法提前算出结果，只能等外层某一行进来后，把值代入再跑一遍内层逻辑。

• 外层返回 5 万行 → 内层最多执行 5 万次
• 若内层 WHERE 条件没命中索引（如 department 无索引），每次都是 type=ALL
• MySQL 5.7 及更早版本几乎不优化这类结构；8.0 虽支持部分物化，但只对简单聚合生效，且严重依赖统计信息准确度
• EXPLAIN 中看到 DEPENDENT SUBQUERY + rows 值远大于外层预估行数，基本可确认正在反复扫表

IN (SELECT ...)在什么情况下等于主动放弃索引

看似简洁的写法，实际常退化为嵌套循环或低效物化。关键看子查询是否能走索引、结果集大小、以及优化器是否启用半连接。

• 子查询含 GROUP BY、DISTINCT 或聚合函数 → MySQL 5.7+ 半连接优化直接失效
• 子查询结果集太大（如上万行），优化器认为走索引回表成本高于全扫 → type=ALL 出现
• IN 右侧字段没索引，或外层条件没驱动到索引列（如 user_id IN (SELECT id FROM users WHERE region = 'CN')，但 region 索引无效）
• EXPLAIN 显示 Extra: Using where; Using join buffer 或 Using temporary，就是危险信号

怎么让子查询真正走索引

核心不是“怎么写子查询”，而是“哪些列必须有索引”以及“怎么避免索引被悄悄废掉”。

• 内层 WHERE 字段必须有索引：比如 users.vip_level > 3，就要确保 vip_level 有单列索引，或复合索引以它为最左前缀
• 关联字段类型必须严格一致：外层传 123（INT），内层字段却是 VARCHAR → 触发隐式转换，key 显示 NULL
• 联合索引要匹配查询顺序：建了 (a, b, c)，但子查询只写 WHERE b = ? AND c = ? → type=ALL，因为跳过了最左列 a
• 别在索引列上用函数：WHERE YEAR(create_time) = 2023 废掉索引；改成 create_time BETWEEN '2023-01-01' AND '2023-12-31'
• 检查 key_len：如果联合索引三列共 12 字节，但 key_len 只显示 4，说明只有第一列参与了查找

比改子查询更有效的替代方案

多数时候，换写法比调优子查询更直接有效。优先级：JOIN > EXISTS > 子查询。

• 能用 JOIN 就别用 IN：它明确表达表间关系，优化器更容易选择 ref 或 eq_ref 访问路径，只要 ON 字段有索引就能稳定命中
• EXISTS 更适合逻辑判断场景：它天然短路，找到第一条就退出，不生成完整结果集；但注意 EXISTS 子句里仍不能有 OR、函数或 !=，否则照样 type=ALL
• 避免 OR EXISTS 这类组合：执行计划中表现为多个 FILTER 节点嵌套，最小 ID 的子路径若返回大量行，会反复驱动更大 ID 的子路径执行数千次
• 真没法改 SQL？可尝试加 /*+ USE_INDEX(users (vip_level)) */ 强制走索引，但效果不稳定，且不同 MySQL 版本语法差异大

最容易被忽略的是字段类型隐式转换和联合索引最左前缀对齐——这两点不解决，哪怕写了 EXISTS 或建了索引，EXPLAIN 里照样显示 type=ALL。