如何用JOIN替代子查询优化多表关联的慢SQL查询？

问题分析 #

原查询通过IN子查询先筛选出拥有多个canonical脚本的作品ID，再关联三张表获取详细数据，但IN子查询在数据量较大时容易触发性能瓶颈——数据库可能会对外部查询的每一行重复执行子查询，或者无法有效利用索引，这就是你当前查询耗时45秒的核心原因之一。

优化方案：用JOIN替代IN子查询 #

我们可以先通过JOIN + GROUP BY提前筛选出符合条件的作品ID（拥有≥2个canonical脚本），再将这个结果集与原表关联获取详细数据。这种方式能让数据库生成更高效的执行计划，减少重复计算。

优化后的查询语句 #

SELECT 
    p.id AS production_id, 
    p.production,
    s.id AS script_id, 
    s.script
FROM productions p
JOIN productions_scripts ps ON p.id = ps.production_id
JOIN scripts s ON ps.script_id = s.id
-- 关联预筛选的符合条件的作品ID集合
JOIN (
    SELECT ps_inner.production_id
    FROM productions_scripts ps_inner
    JOIN scripts s_inner ON ps_inner.script_id = s_inner.id
    WHERE s_inner.canonical = 1
    GROUP BY ps_inner.production_id
    HAVING COUNT(*) > 1
) filtered_prods ON p.id = filtered_prods.production_id
WHERE s.canonical = 1
ORDER BY production_id;

进阶优化：用窗口函数简化逻辑 #

如果你的数据库支持窗口函数（如PostgreSQL、MySQL 8.0+、SQL Server等），可以直接在一次扫描中完成统计和筛选，避免额外的JOIN操作，代码更简洁，性能也更优：

SELECT 
    production_id,
    production,
    script_id,
    script
FROM (
    SELECT 
        p.id AS production_id,
        p.production,
        s.id AS script_id,
        s.script,
        -- 统计当前作品关联的canonical脚本总数
        COUNT(*) OVER (PARTITION BY p.id) AS canonical_script_count
    FROM productions p
    JOIN productions_scripts ps ON p.id = ps.production_id
    JOIN scripts s ON ps.script_id = s.id
    WHERE s.canonical = 1
) sub_query
WHERE canonical_script_count > 1
ORDER BY production_id;

关键性能提升建议 #

• 添加复合索引：
  • 在productions_scripts(production_id, script_id)上创建复合索引，加快表关联速度
  • 在scripts(id, canonical)上创建复合索引，快速筛选canonical类型的脚本
  • 确保productions(id)为主键索引（通常默认已配置）
• 统一使用显式JOIN：原查询中用逗号分隔表的隐式连接语法已过时，显式JOIN更清晰，也更利于数据库优化执行计划

方案优势 #

替换IN子查询为JOIN后，数据库可以一次性计算出符合条件的作品ID集合，避免了重复执行子查询的开销；窗口函数方案则直接在一次表扫描中完成统计与筛选，进一步减少了表关联次数，逻辑更紧凑。

内容的提问来源于stack exchange，提问作者Lemmy