人人都会AI编程

13.4 子查询优化与改写建议

更新时间：2026-07-10

子查询是 SQL 中表达能力很强的语法，一条 SELECT 嵌套在另一条 SQL 里，就能实现多步骤的逻辑。但在 MySQL 中，子查询的执行效率常常不理想，很多看似简洁的写法，底层却可能触发逐行扫描，成为性能杀手。本节会梳理常见的子查询性能问题，给出可操作的改写方法，并说明在什么情况下可以放心使用子查询。

13.4.1 子查询的基本分类与性能直觉

从语法位置看，子查询主要有三种：

  • 标量子查询：返回单个值，通常用在 SELECT 列表、WHERE 条件中，如 SELECT name, (SELECT MAX(salary) FROM salaries s WHERE s.emp_id = e.id) FROM employees e。
  • 表子查询：返回多行多列结果集，常用于 FROM 子句（派生表）或 IN、EXISTS 条件。
  • 行子查询：返回单行多列，较少见。

按是否依赖外层查询，又可分为：

  • 非相关子查询：子查询可以独立执行，只执行一次，结果被外层使用。
  • 相关子查询：子查询引用了外层查询的列，外层每返回一行，子查询可能都要执行一次。

性能问题的根源通常集中在相关子查询。它类似于应用程序里的 N+1 查询：外层结果有多少行，子查询就可能被执行多少次，数据量稍大就会严重拖慢性能。

13.4.2 IN 子查询的优化与改写

WHERE col IN (SELECT ...) 是使用频率最高的子查询之一。虽然 MySQL 5.6 之后引入了半连接（Semi-Join）优化，会对部分 IN 子查询自动重写为 JOIN 执行，但优化器并不是万能的，很多场景仍然需要手动干预。

无法自动优化的典型情况：子查询包含 UNION、GROUP BY、LIMIT 等复杂结构，或使用了非等值连接条件。

当你通过 EXPLAIN 看到 DEPENDENT SUBQUERY 或 SUBQUERY 而且 rows 非常大时，就说明优化器没有把子查询转换为高效的连接。这种情况下，可以手动改写为 INNER JOIN。

示例：查找有订单记录的用户

原写法：

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

如果子查询没能自动转为 semi-join，执行顺序可能是：外层逐行扫描 users，每行代入 id，去 orders 里查找是否存在匹配的 user_id。这种逐行探查的代价极高。

改写为内连接：

SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id AND o.amount > 100;

通过等值连接，优化器可以使用 orders 的索引（假设 user_id 有索引）快速匹配。DISTINCT 用于去重，因为一个用户可能有多条符合条件的订单。如果确认 user_id 在 orders 中唯一，可以省去 DISTINCT。

更严谨的等号改写是使用 EXISTS，MySQL 在处理 EXISTS 时通常会将其转为半连接或使用索引高速扫描，性能往往优于 IN + 子查询：

SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 100
);

改写原则：能用 JOIN 或 EXISTS 解决的，尽量不用 IN（子查询）。

13.4.3 NOT IN 与 NOT EXISTS 的陷阱

NOT IN 是比 IN 更脆弱的写法，它除了性能问题，还存在一个隐性的大坑：NULL 值导致的逻辑错误。

当子查询结果集中包含 NULL 时，NOT IN 会返回空集。因为 x NOT IN (1, 2, NULL) 的逻辑被 SQL 标准定义为：x != 1 AND x != 2 AND x != NULL。而与 NULL 的比较永远返回 NULL（即“未知”），整个条件永远不为真，所以外层一条数据都查不出来。

示例：

-- 假设计划删除没有订单的用户
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

如果 orders.user_id 中存在 NULL 值（也许是脏数据），那么这条 SQL 将始终返回 0 行。这可能导致业务逻辑完全失效，排查难度很高。

更安全、性能也更优的改写是 LEFT JOIN ... IS NULL：

SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

左连接会保留 users 的所有行，没有匹配订单的 o.user_id 会是 NULL，在 WHERE 中直接过滤即可。这种方法语义明确，不惧怕 NULL，而且优化器可以很好地利用连接算法。即使 orders.user_id 有索引，查询也能高效完成。

很多人也推荐使用 NOT EXISTS，它的语义同样安全，且在一些版本中性能表现很好：

SELECT u.* FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

实际工作中，LEFT JOIN ... IS NULL 和 NOT EXISTS 是替代 NOT IN 的首选方案，前者更容易理解，后者在某些场景下执行计划更优。

13.4.4 SELECT 列表中的标量子查询

在 SELECT 后使用标量子查询，是一种看起来很清晰的写法，但它的执行方式与外层结果行数紧密相关。例如：

SELECT
    e.name,
    (SELECT d.name FROM dept d WHERE d.id = e.dept_id) AS dept_name
FROM employee e;

这相当于：外层 employee 每输出一行，就要查一次 dept 表。如果 employee 有 10 万行，就需要执行 10 万次子查询，即使每次走主键查询很快，总开销也不可忽视。

改写方法：使用 LEFT JOIN。

SELECT e.name, d.name AS dept_name
FROM employee e
LEFT JOIN dept d ON d.id = e.dept_id;

这样两个表一次性关联完成，MySQL 可以选择更优的连接算法（比如索引嵌套循环连接），避免多次重复查询。

这项改写不仅适用于单表关联，对于多层嵌套的子查询同样有效。如果你需要关联多张表的聚合结果，也尽量用连接配合派生表或 CTE，而不是在 SELECT 列表中嵌子查询。

13.4.5 FROM 子查询（派生表）的优化

FROM 后面的子查询会生成一张临时的派生表，优化器通常会将其物化（Materialize）：先把子查询结果存到临时表里，再参与外层查询。这种方式本身不是坏事，但如果派生表没有合适的过滤、或者与外层关联时丢失了索引，就可能拖慢整体性能。

优化思路：

  1. 优先考虑用连表替代简单派生表。如果子查询只是为了做一层简单的投影或过滤，可以直接与外层表连接，并把条件合并到 WHERE 中。
  2. 为派生表添加必要的索引条件。如果派生表必须存在，尽量在子查询内部就大幅削减数据量，比如加上 LIMIT、使用内部索引。
  3. 利用 MySQL 8.0 的 CTE。公用表表达式 (WITH) 在语义上与派生表类似，但可读性更高，优化器有时能生成更好的执行计划，并且支持递归。

例如：

-- 原派生表写法
SELECT * FROM
  (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) AS t
WHERE t.total > 1000;

这与直接 HAVING 等价，优化器一般能处理得不错，但复杂场景下可以尝试改为 CTE：

WITH user_total AS (
  SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT * FROM user_total WHERE total > 1000;

关键还是要看 EXPLAIN：如果派生表被标记为 DERIVED 且没有良好的索引使用，就要思考是否可以改写为直接连接或合并到外层。

13.4.6 MySQL 8.0 对子查询的自动优化

MySQL 从 5.6 开始不断强化子查询优化，8.0 版本在这一领域又有显著进步：

  • Semi-Join 转换：对 IN / =ANY 子查询自动尝试转为内连接，支持多种 semi-join 策略（表拉出、首次匹配、松散扫描等）。
  • Anti-Join 转换：对 NOT IN / NOT EXISTS 子查询自动尝试转为反连接（Anti-Join），不再是逐行关联子查询。
  • 物化执行：将子查询结果自动物化到临时表，并为临时表添加索引，加速外层查询。
  • 条件下推：将外层条件推入子查询内部，提前减少数据量。

这些优化意味着，一部分以前必须手动改写的子查询，现在 MySQL 自己就能处理得很好。 但优化器并非万能，复杂子查询、多层嵌套、非等值条件仍然可能让它选择低效的执行路径。所以建议是：

先相信优化器，但用 EXPLAIN 验证。 如果执行计划中出现 DEPENDENT SUBQUERY、大 rows 数、无索引可用等情况，果断手动改写为 JOIN 或 EXISTS。

13.4.7 实用改写清单与检查项

日常开发中，可以按以下优先级检查子查询：

  1. 看到 NOT IN：立刻改成 LEFT JOIN ... IS NULL 或 NOT EXISTS，同时检查关联列是否有 NULL 陷阱。
  2. 看到 IN 后面跟复杂子查询（UNION/GROUP BY）：检查 EXPLAIN 是否出现依赖子查询，如果性能不佳则改写为 JOIN。
  3. 看到 SELECT 列表中有子查询：尝试用 JOIN 替换，对外层数据量大的查询尤其重要。
  4. 看到多层嵌套的 FROM 子查询：分析是否可以用 CTE 或连接简化，减少临时表物化的层次。
  5. 如果子查询带有 LIMIT 且被外层引用：考虑用衍生表或临时表暂存，防止优化器错误估算。

最后，子查询并非总是“坏的”。当你只是查询少量数据，且子查询能够利用主键或唯一索引快速执行时，保留子查询写法反而更清晰，不必过早优化。优化永远要基于实际的执行计划和测试数据，而不是纯凭经验重写所有子查询。