SQL里同时出现子查询和全连接时，查询优化器往往面临两难：子查询需要先产出中间结果，全连接又要求左右两侧未匹配的行全部保留，两者组合会迅速放大临时表和排序开销。要处理这类复杂关联，不能只盯着某一种写法，而要从执行顺序、连接语义和索引匹配三个层面一起调整。

一、先看清子查询与全连接各自的执行代价

SQL标准中的 FULL OUTER JOIN 会返回左右表所有行，匹配不上的补NULL。MySQL 8.0仍未内置该语法，只能通过 LEFT JOIN 加上 UNION 再加上 RIGHT JOIN 来模拟。这个模拟过程本身会触发两次表扫描和一次合并去重，如果左右两边又是子查询派生表，执行器可能还要把子查询结果落到临时表，再对临时表做全连接。临时表通常没有合适的索引，连接条件变成全量匹配，复杂度很容易接近O(N*M)。

相关子查询更隐蔽。写在SELECT列表或WHERE里的相关子查询，外层每返回一行就会执行一次，相当于在SQL内部跑循环。当它和全连接放在一起时，执行计划里经常出现 DEPENDENT SUBQUERY 以及 Using temporary、Using filesort。这类计划一旦出现，数据量稍大就会从秒级退化到分钟级。以下是用MySQL模拟全连接的基础写法：

SELECT a.id AS a_id, b.id AS b_id, a.amount, b.amount
FROM orders a
LEFT JOIN refunds b ON a.order_id = b.order_id
UNION ALL
SELECT a.id AS a_id, b.id AS b_id, a.amount, b.amount
FROM orders a
RIGHT JOIN refunds b ON a.order_id = b.order_id
WHERE a.order_id IS NULL;

这里使用 UNION ALL 不会产生重复行，因为左连接已经包含所有左侧行和匹配行，右连接中通过 WHERE a.order_id IS NULL 只保留仅在右侧出现的行。相比 UNION，UNION ALL 省去了一次全量去重，性能通常更好。

二、优化第一步：把子查询从连接中剥离并提前缩小范围

不少复杂SQL习惯把子查询直接放在全连接的ON条件里，或者把多个聚合子查询作为全连接的左右表。优化器对ON子句中的子查询处理能力有限，尤其涉及多个条件时，无法把谓词下推到最内层。正确做法是先把子查询改成派生表，在派生表内部完成过滤和聚合，再让连接面对较小的结果集。

另一个原则是只保留参与连接和最终输出的列。SELECT * 会把无关字段带进临时表，一旦全连接触发磁盘临时表，每多一列都会显著增加IO。比如订单和退款对账场景，只需要 order_id、user_id、amount、status，就不要拉取订单备注、收货地址等大字段。下面是对账查询的优化示例：

SELECT o.order_id, o.amount, r.refund_amount
FROM (
  SELECT order_id, amount
  FROM orders
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
) o
LEFT JOIN (
  SELECT order_id, refund_amount
  FROM refunds
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
) r ON o.order_id = r.order_id
UNION ALL
SELECT r.order_id, o.amount, r.refund_amount
FROM (
  SELECT order_id, amount
  FROM orders
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
) o
RIGHT JOIN (
  SELECT order_id, refund_amount
  FROM refunds
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
) r ON o.order_id = r.order_id
WHERE o.order_id IS NULL;

这段SQL把时间过滤下推到两个派生表内部，让订单表和退款表在进入连接前就已经缩小到目标月份。如果 created_at 列上有合适索引，两边的过滤都能走索引范围扫描，派生表行数大幅减少，后续全连接产生的临时表也变得很小。反之，如果把时间条件放在最外层，执行器需要先扫描整年数据做连接，再用 WHERE 过滤，代价完全不同。

还需要注意，派生表在MySQL中默认会物化，如果两个派生表结果较大，应观察执行计划是否出现 Using temporary 和 Using filesort。必要时可以通过调整 tmp_table_size、max_heap_table_size 避免临时表写磁盘，但更根本的方式仍然是提前过滤，减少进入临时表的数据量。

三、用EXISTS和物化策略处理相关子查询

当子查询用于存在性判断时，优先使用 EXISTS 或 NOT EXISTS，而不是 IN 或 NOT IN。原因有两点：IN 需要先构造完整结果集，遇到NULL值还会出现语义陷阱；NOT IN 一旦子查询结果包含NULL，整个比较会直接返回空集，这是对账场景里最常见的隐性错误。EXISTS 在优化器支持较好，可转为半连接或反连接，有些引擎会采用物化加索引查找的方式执行。

例如，要找出不在黑名单中的订单，用 NOT EXISTS 可以避免NULL陷阱：

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1
  FROM blacklist b
  WHERE b.order_id = o.order_id
);

如果后续还需要对这些订单与退款表做全连接，可以先把这段过滤逻辑放到派生表中，让黑名单过滤在进入全连接前完成。这样右侧结果集更小，连接阶段的比较次数和临时表大小都会明显降低。需要记住的是，NOT EXISTS 中的子查询仅用于判断，不返回实际列，所以 SELECT 1 足够，不需要写出具体字段。

有些数据库支持 WITH 定义公共表表达式，MySQL 8.0也支持。复杂全连接里重复使用同一子查询，用CTE可以避免重复扫描源表。但要注意不是所有CTE都会被优化器内联展开，MySQL可能把CTE物化到临时表，这时应评估物化代价。如果源表很大而CTE结果很小，物化有利；如果CTE只是简单过滤，写重复派生表反而可能更灵活。

四、复杂关联实战：日期补全、多表对账与索引设计

典型复杂关联场景是把日期维度表与多个业务聚合结果做全连接，以补全缺失日期，或者补齐没有订单但有退款的情况。这类报表不要直接对原始交易表做全连接，而应先按天聚合，再以日期为主表左连接各聚合结果。日期维度表通常行数少，作为驱动表可以控制循环次数，执行计划也更稳定。

SELECT c.dt,
       COALESCE(s.order_cnt, 0) AS order_cnt,
       COALESCE(r.refund_cnt, 0) AS refund_cnt
FROM calendar c
LEFT JOIN (
  SELECT DATE(created_at) AS dt, COUNT(*) AS order_cnt
  FROM orders
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
  GROUP BY DATE(created_at)
) s ON c.dt = s.dt
LEFT JOIN (
  SELECT DATE(created_at) AS dt, COUNT(*) AS refund_cnt
  FROM refunds
  WHERE created_at >= '2024-01-01'
    AND created_at < '2024-02-01'
  GROUP BY DATE(created_at)
) r ON c.dt = r.dt
WHERE c.dt >= '2024-01-01'
  AND c.dt < '2024-02-01';

这里使用 COALESCE 把NULL转换为0，能够保证报表中每一天都有订单量和退款量。注意在 WHERE 条件中对 created_at 使用 DATE() 函数会导致无法使用普通索引，最好改为对原始列做范围过滤后再聚合。如果日期转换不可避免，可以考虑使用生成列并建立索引，或者使用支持函数索引的数据库版本。

多表全连接时，不建议把多个 FULL OUTER JOIN 直接串联，优化器可能会生成多层UNION和临时表，导致执行计划失控。更稳妥的做法是先把所有维度键合并成一个去重集合，再用这个集合分别左连接各业务表。示例如下：

SELECT dim.order_id, o.amount, r.refund_amount, p.penalty_amount
FROM (
  SELECT order_id FROM orders
  UNION
  SELECT order_id FROM refunds
  UNION
  SELECT order_id FROM penalties
) dim
LEFT JOIN orders o ON o.order_id = dim.order_id
LEFT JOIN refunds r ON r.order_id = dim.order_id
LEFT JOIN penalties p ON p.order_id = dim.order_id;

这种写法以维度键集合作为驱动表，能够避免多层全连接带来的连接顺序混乱，同时让每张业务表的访问都能利用主键或唯一索引。执行计划中应重点关注每个左连接的实际扫描行数，如果某个左连接扫描行数接近全表，说明维度键筛选力不足或关联列索引失效。

最后，优化完SQL后一定要用 EXPLAIN 或 EXPLAIN ANALYZE 验证。关注 filtered 百分比、是否有 DEPENDENT SUBQUERY、是否出现文件排序和磁盘临时表。复杂关联场景的优化没有固定模板，只能通过不断缩小中间结果、减少循环执行和降低临时表体积，才能让子查询与全连接的组合查询稳定运行在可接受的时间内。