mysql中子查询的使用方法与性能优化

子查询在WHERE中慢是因为相关子查询对每行外层数据都执行一次，导致N×M级扫描；应改用JOIN、确保索引、避免函数索引失效，并用EXPLaiN验证执行计划。

子查询写在 WHERE 里为什么慢得离谱

因为 mysql 在 WHERE 子句中使用相关子查询（即子查询依赖外层表字段）时，可能对每行外层数据都执行一次子查询，形成 N×M 级扫描。比如：

select * FROM orders o WHERE o.customer_id IN (SELECT id FROM customers c WHERE c.status = 'active' AND c.id = o.customer_id);

这里 c.id = o.customer_id 让子查询变成“相关”的，MySQL 5.6 及更早版本几乎无法优化，即使加了索引也常走全表扫描。

常见错误现象：EXPLAIN 显示 type 是 ALL 或 index，rows 列数值极大，Extra 出现 using where; Using join buffer。

• 优先改写为 JOIN：上例可转成 INNER JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active'
• 确认子查询是否真需“相关”：如果只是过滤固定集合，用非相关子查询（如 (SELECT id FROM customers WHERE status = 'active')），MySQL 能物化为临时表
• MySQL 8.0+ 开启 optimizer_switch='materialization=on' 可提升非相关子查询性能，但不解决相关子查询本质问题

IN、EXISTS、JOIN 三者选哪个

语义不同，执行计划和性能差异明显，不能简单互换。

IN 适合右侧结果集小且无 NULL 值的场景；EXISTS 天然适合相关子查询，且对 NULL 安全；JOIN 最适合需要取子查询中额外字段或做聚合的场景。

• IN 遇到 NULL 会整体返回空结果（如 1 IN (1,2,NULL) → UNKNOWN → 过滤掉该行），而 EXISTS 不受 NULL 影响
• 当子查询结果集较大（比如 > 1000 行），IN 可能触发“IN-list limit”，MySQL 会退化为全表扫描；此时 EXISTS 或 JOIN 更稳
• EXISTS 在有合适索引时通常走 range 或 ref，但必须确保子查询的关联字段上有索引（如 EXISTS (SELECT 1 FROM logs l WHERE l.order_id = o.id)，需 logs(order_id) 索引）

子查询放在 SELECT 列表里要注意什么

标量子查询（返回单值、单行）可以出现在 SELECT 列表，但极易引发性能灾难，尤其在外层是大表时。

典型反例：

SELECT id, (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS item_count FROM orders o;

若 orders 有 10 万行，且没索引，就是 10 万次全表扫描 order_items。

• 必须确保子查询中的关联条件字段有高效索引（如 order_items(order_id)）
• 考虑用 LEFT JOIN + GROUP BY 替代：先聚合再连接，让 MySQL 一次性完成统计
• MySQL 8.0+ 支持 CTE 和窗口函数，复杂逻辑优先用 WITH 拆解，比嵌套子查询更易读且常更快

子查询被优化器“误判”导致走错索引

MySQL 有时会低估子查询结果集大小，或错误估算关联代价，导致本该走索引却选了全表扫描，尤其在统计信息过期或列基数异常时。

验证方式：EXPLAIN format=jsON 查看 query_block.nested_loop 和 table.used_columns，重点观察 filtered 字段是否远低于实际选择率。

• 手动更新统计信息：ANALYZE TABLE orders, customers;（注意：会锁表，生产慎用）
• 用 FORCE INDEX 强制走索引（仅临时救急）：

  SELECT * FROM orders o WHERE o.customer_id IN (SELECT id FROM customers FORCE INDEX (PRIMARY) WHERE status = 'active');

• 避免在子查询中用函数包装字段（如 YEAR(create_time) = 2023），这会让索引失效；改用范围条件：create_time BETWEEN '2023-01-01' AND '2023-12-31'

子查询不是不能用，而是容易在不知不觉中把单点查询放大成全表扫描。真正关键的不是语法怎么写，而是每次写完都该问一句：这个子查询会被执行多少次？它的驱动表是什么？有没有索引能覆盖？