优化多个JOIN连接的复杂SQL查询

说说JOIN优化这件事。 不是等SQL跑完才看慢不慢，而是写完第一版就得跑EXPLAIN FORMAT=TRADITIONAL（MySQL）或EXPLAIN (ANALYZE, BUFFERS)（PostgreSQL）。重点盯三个指标：rows列、type字段、filtered百分比。rows暴增说明中间结果集失控，type出现ALL或index说明索引失效，filtered低于5%意味着95%以上的行被后续条件干掉——这部分本该提前压进ON里。

为什么EXPLAIN里rows突然暴增就是JOIN出问题了

这个问题其实很好判断。如果rows从1万跳到500万，基本上可以断定中间结果集失控了。那么，问题出在哪里？ 核心原因不外乎三个： * 左边JOIN的右边表没加WHERE条件？把它挪进ON——比如把WHERE b.status = 'active'改成ON a.id = b.a_id AND b.status = 'active'。 * ON里用了函数？比如UPPER(a.name) = UPPER(b.name)，数据库无法下推索引，直接放弃SARGable，先全量组合再过滤。 * 复合JOIN漏字段？ON a.id = b.a_id AND a.dt = b.dt少写a.dt = b.dt，数据量可能翻倍甚至指数级放大。 其实，这些问题在EXPLAIN的输出里，直接暴露了三大硬伤：中间结果集膨胀、索引无效、过滤时机错误。

临时表不是缓存，是提前固化过滤逻辑

超过5张表硬JOIN，大概率崩。但拆成多个独立SELECT在应用层合并，反而更慢——各子查询重复扫主表、无法共享t1.id上下文、大字段拖慢传输。 真正有效的临时表，是用CREATE TEMPORARY TABLE SELECT ...一步到位，只保留后续JOIN和WHERE真正用到的字段。比如只取id、status、created_at，坚决不带TEXT或超长VARCHAR。 实践经验是： * 索引必须按实际JOIN顺序建。比如JOIN temp_t3 ON t1.b = temp_t3.b，那INDEX(b)是底线；如果还有WHERE temp_t3.status = 'active'，就建INDEX(b, status)，别倒过来。 * 主表（如orders）一般不放临时表——它是驱动源，提前过滤反而丢失时机。 * 被LEFT JOIN的右表，如果本身只有几千行，没必要临时化；如果是百万级且只用其中某天的数据，就值得做。

JOIN字段没索引，等于裸奔

两张百万级表JOIN，没索引时MySQL默认用Nested Loop Join，复杂度O(M×N)。200万乘100万等于2万亿次比较，CPU直接满载。有索引后变成B+树查找，单次匹配从O(N)降到O(logN)，200万行对应约21次磁盘IO。验证很简单：EXPLAIN里type要是ref或eq_ref，key字段非空。 说几个常见陷阱： * orders.user_id上必须建索引，哪怕它已是外键——外键约束不自动建索引。 * 复合索引要覆盖JOIN + WHERE。比如WHERE u.status = 'active' AND u.city = 'shanghai'，那就建INDEX(status, city, id)，id放最后用于回表。 * 字段类型不一致会隐式转换。t1.a是VARCHAR(20)，t2.a是VARCHAR(50)，JOIN时若没显式CAST，索引可能失效。

LEFT JOIN的NULL陷阱比语法更危险

LEFT JOIN本身不报错，但右边表如果是一行对多行（比如聚合结果没加DISTINCT或GROUP BY），左表会被悄悄放大。更隐蔽的是：右边表数据分布不均时，比如99%的user_id只对应1条订单，但1%的VIP用户有上千条，优化器按平均值估算rows，实际执行却爆内存。 几个关键判断点： * 右表是子查询？确保它带LIMIT或强过滤条件，否则MySQL 8.0之前默认不物化派生表，可能反复执行。 * 右表有OR条件？比如ON a.id = b.x OR a.id = b.y，优化器常放弃索引合并，退化为全表扫描。 * 用SEMI JOIN替代IN或EXISTS（部分数据库支持），能避免右表重复匹配同一行。 复杂点不在语法有多难，而在中间结果集是否可控——所有优化动作，本质都是让rows数字从“猜”变成“控”。