子查询的性能问题，往往不是“子查询不能用”，而是“子查询被放在了优化器难以处理的位置”。MySQL 对 SELECT 列表里的标量子查询、WHERE 条件里的 IN 或 EXISTS 子查询、以及 FROM 后面的派生表，采用了不同的执行策略。一个结构看起来差不多的查询，可能瞬间完成，也可能因为临时表写入和逐行检查而变慢。因此，优化子查询的第一步不是把所有子查询都改写成 JOIN，而是先判断当前的子查询属于哪一类，能否让优化器自动转换，再决定是否需要人工调整查询结构。
优化器内部常见动作有三个：半连接转换、派生表合并、物化扫描。半连接用于处理 IN 和 EXISTS，能避免先查内层再回外层；派生表合并适合 FROM 子查询，把内层查询融入外层连接；物化则是先执行子查询并把结果写入临时表，再与外部表关联。三种路径成本差距很大，需要依靠 EXPLAIN 输出来确认最终走了哪条。
一、先分清子查询的执行类型
按返回结果形式，子查询可分为标量子查询、行子查询、表子查询。标量子查询只返回一行一列，常放在 SELECT、WHERE 或 HAVING 里；行子查询返回一行多列，通常配合括号比较；表子查询返回多行多列，最常见的位置是 FROM 后面作为派生表。实际调优时更重要的分类是“相关子查询”和“非相关子查询”。非相关子查询可以先执行内层得到结果，再执行外层，而相关子查询的每一行外层数据都要传给内层执行一次，代价通常更高。
下面三种写法虽然都叫子查询，但执行方式完全不同：
-- 标量子查询：放在SELECT列表中，外层每行都会触发一次
SELECT o.order_id,
o.amount,
(SELECT u.name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o
WHERE o.created_at >= '2024-01-01';
-- 非相关IN子查询：可以先执行内层
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');
-- 派生表：内层结果物化或合并后再连接
SELECT d.user_id, SUM(d.amount) AS total_amount
FROM (SELECT user_id, amount FROM orders WHERE created_at >= '2024-01-01') AS d
GROUP BY d.user_id;
第一段里的标量子查询是相关子查询典型代表，如果 orders 返回十万行，子查询就可能被执行十万次。即使 users.id 上有主键索引，十万次随机回表也不如一次 JOIN 来得高效。判断子查询类型不要只看语法，还要看它是否引用了外层列。
二、用EXPLAIN识别低效子查询结构
EXPLAIN 输出中的 select_type 字段是最直接的线索。出现 DEPENDENT SUBQUERY 意味着相关子查询；出现 DERIVED 通常说明 FROM 后的子查询被当成派生表处理；出现 MATERIALIZED 表示子查询结果写入了临时表。优化器如果成功把 IN 子查询转换成半连接，select_type 往往显示 SEMIJOIN，这时不需要先物化内层结果，执行路径更接近普通 JOIN。
例如下面这个查询：
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE register_time >= '2024-01-01');
如果 EXPLAIN 里的第二条记录显示 DEPENDENT SUBQUERY，说明 MySQL 没有做半连接转换，可能是内层没有合适的唯一索引，或者子查询包含 LIMIT、UNION、聚合等结构。此时可以尝试给 users.id 建唯一索引，或者把 IN 改写成 EXISTS 甚至 JOIN。反过来，如果显示 SEMIJOIN 或 SUBQUERY 后跟 materialized，优先检查内层筛选条件是否能够走索引，避免把整表数据写入临时文件。
还有一个常被忽视的字段是 rows，特别是内层查询的 rows 很大时，即使外层行数很少，也会因为内层物化成本而拖慢整体。此时应该调整连接顺序，或者将内层条件提前到外层合并。
三、五种常见优化调整方式
第一，IN 子查询尽量改造成半连接或 JOIN。MySQL 8.0 对 IN 的半连接优化已经比较成熟，但前提是内层列有唯一约束。若内层查询不包含重复键，可以用 JOIN 直接替换：
-- 原查询 SELECT o.* FROM orders o WHERE o.user_id IN (SELECT id FROM users u WHERE u.status = 'active'); -- 改写成JOIN SELECT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active';
这样改写后，执行计划可以更自由地选择 users 驱动还是 orders 驱动，并且能充分利用 users.status 和 orders.user_id 的索引。但如果内层结果可能重复，直接 JOIN 需要加 DISTINCT，否则行数会膨胀。此时用 EXISTS 更安全。
第二，FROM 后的派生表通过条件上推减少数据量。派生表的常见问题是优化器把内层结果全部计算出来再和外层连接，尤其是内层没有 WHERE 或 WHERE 条件很宽。改写时可以把外层条件推进派生表内部，或用 CTE 提高可读性。比如下面的查询：
-- 低效：先查出所有订单，再过滤用户 SELECT t.user_id, t.total_amount FROM (SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id) t WHERE t.total_amount > 10000; -- 调整：先过滤再聚合，减少派生表行数 SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE user_id IN (SELECT id FROM vip_users) GROUP BY user_id HAVING SUM(amount) > 10000;
第三，相关 EXISTS 改写为 JOIN 或使用去相关。相关 EXISTS 子查询若外层行数较多，逐行查内层会很慢。例如：
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM payments p
WHERE p.order_id = o.id AND p.pay_status = 'success'
);
如果 payments.order_id 有索引，这个查询可能已经足够快；但如果外层 orders 有几百万行，而 payments 表也很大，逐行检查不如直接 JOIN 后 DISTINCT，或者用半连接提示。MySQL 8.0.16 以后，优化器支持将部分相关子查询转换为半连接，不再需要手动改写。若 EXPLAIN 显示处于 DEPENDENT SUBQUERY，可以尝试调整统计信息或改用 JOIN。
第四，标量子查询更适合合并到 FROM 或 JOIN。标量子查询在外层行数不大时没有问题，但数据量上来后，每次引用外层列都会增加一次索引查找。常见的调整是把它放到 LEFT JOIN 里：
-- 原查询：每行订单都查一次用户名
SELECT o.id, o.amount,
(SELECT u.name FROM users u WHERE u.id = o.user_id) AS user_name
FROM orders o;
-- 调整：一次JOIN拿回用户名
SELECT o.id, o.amount, u.name AS user_name
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;
第五，NOT IN 和 NOT EXISTS 的选择。NOT IN 遇到内层结果包含 NULL 时逻辑会整体失效，且容易产生 DEPENDENT SUBQUERY。建议优先使用 NOT EXISTS 并给关联列建索引：
-- 存在NULL风险
SELECT * FROM orders o
WHERE o.user_id NOT IN (SELECT id FROM users WHERE last_login >= '2024-01-01');
-- 更稳定
SELECT * FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM users u
WHERE u.id = o.user_id AND u.last_login >= '2024-01-01'
);
这些调整方式并不是机械套用，有些场景下 JOIN 会产生重复行，有些场景下 EXISTS 更适合大表小结果集。关键是每改一处都要查看 EXPLAIN，确认执行计划真的发生了变化。
四、案例：从全表物化到索引半连接
下面用一个订单与用户的关系表来说明结构调整的效果。原始需求是查询所有有效用户的订单总额。最初查询写成：
SELECT o.user_id, SUM(o.amount) AS total_amount FROM orders o WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.status = 'active') GROUP BY o.user_id;
在测试环境里 EXPLAIN 显示 users 表执行了全表扫描，并且子查询被物化为临时表，因为 users.status 字段没有索引。执行时间约 12 秒。调整步骤分成三步：先给 users.status 建索引，再给 orders.user_id 建索引，然后观察优化器是否将 IN 转成 SEMIJOIN。
CREATE INDEX idx_users_status ON users(status); CREATE INDEX idx_orders_user_id ON orders(user_id); EXPLAIN SELECT o.user_id, SUM(o.amount) AS total_amount FROM orders o WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.status = 'active') GROUP BY o.user_id;
加索引后，执行计划中的 select_type 从 MATERIALIZED 变成 SEMIJOIN，内层通过 idx_users_status 定位有效用户，再通过 idx_orders_user_id 回表取订单，最终时间降到 0.3 秒左右。这个案例说明，子查询优化不一定要改写 SQL，索引和统计信息往往能直接改变优化器的路径选择。
如果建索引后仍然走物化，可以把 IN 改写成 JOIN 并去重：
SELECT o.user_id, SUM(o.amount) AS total_amount FROM orders o INNER JOIN users u ON o.user_id = u.id AND u.status = 'active' GROUP BY o.user_id;
由于 users.id 是主键，不会产生重复用户，因此 JOIN 不会膨胀行数。改写后优化器可以先筛选 users 的 active 用户，再通过 user_id 索引关联订单，数据量进一步减少。
五、执行计划验证与维护建议
调整完子查询后，不要只凭执行时间判断，还要记录 EXPLAIN ANALYZE 的实际行数和耗时。MySQL 8.0.18 开始支持 EXPLAIN ANALYZE，它会显示每一步的实际访问行数、过滤后行数和耗时，比传统 EXPLAIN 的估算值更有参考价值。例如：
EXPLAIN ANALYZE SELECT o.user_id, SUM(o.amount) AS total_amount FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active' GROUP BY o.user_id;
输出中重点关注 actual time 和 rows 的差值。如果某个步骤预估 rows 是 100，实际 rows 是 50 万，说明统计信息失真或索引列基数不对，可以通过 ANALYZE TABLE 重新收集。也可以检查是否因为字段类型不一致导致索引失效，例如 users.id 是 INT，orders.user_id 是 VARCHAR，比较时发生隐式转换，优化器可能放弃索引。
维护阶段还要避免在子查询内部使用不必要的 ORDER BY 或 DISTINCT，这些操作会阻止半连接和派生表合并。如果必须去重或排序，把它们放到外层。比如：
-- 内层DISTINCT会阻止优化 SELECT * FROM orders o WHERE o.user_id IN (SELECT DISTINCT u.id FROM users u WHERE u.status = 'active'); -- 把DISTINCT放到外层或改用JOIN SELECT DISTINCT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id AND u.status = 'active';
最后，子查询优化不是一次性行为。表结构、数据量分布和 MySQL 版本都会影响优化器选择。建议把重点查询的执行计划纳入版本变更记录，在数据量增长一个数量级后重新验证。遇到 DEPENDENT SUBQUERY、MATERIALIZED 等标记时，优先检查索引和统计信息，再考虑人工改写 SQL 结构。这样既能保留 SQL 的可读性，也能获得稳定的查询性能。