执行时长相近的两个SELECT在INSERT...SELECT场景下性能差异悬殊的问题

核心原因 #

两个语句的性能差异本质是执行计划的逻辑顺序和中间结果集大小差异，具体拆解：

• JOIN阶段的过滤时机：CASE放在INNER JOIN内部时，数据库会在关联匹配的过程中就应用CASE的逻辑，提前过滤掉不符合条件的行，大幅减少后续需要处理的数据集；而CASE放在外部时，数据库会先完成全量的JOIN操作，生成完整的中间结果集后再逐个计算CASE，中间数据量可能是前者的数倍甚至数十倍，导致磁盘IO、内存占用暴涨。
• 索引利用效率：JOIN内的CASE条件可能被优化器识别为关联过滤条件，从而选择更高效的索引（比如覆盖索引、联合索引）来加速JOIN匹配；而外部CASE不影响JOIN阶段的索引选择，可能触发全表扫描或低效的JOIN算法（比如嵌套循环JOIN在大数据量下性能骤降）。
• 资源开销放大：500万行的场景下，额外的全量CASE计算、临时表写入/读取、索引维护开销会被指数级放大。如果慢语句的中间结果集需要写入磁盘临时表，IO瓶颈会直接把耗时拉到小时级。

排查与优化建议 #

• 查看执行计划：对两个语句分别执行EXPLAIN ANALYZE，重点对比：
  • JOIN的类型（比如是HASH JOIN、MERGE JOIN还是NESTED LOOP）
  • 预估行数与实际行数的偏差
  • 是否出现Using temporary或Using filesort标记
• 下推过滤逻辑：尽量将CASE这类过滤/转换逻辑下推到JOIN、WHERE等早期执行阶段，避免全量数据处理。
• 优化索引：确保JOIN关联列、CASE中用到的过滤列有合适的索引，优先考虑联合索引覆盖JOIN和CASE的条件。
• 检查目标表负载：确认目标表是否存在过多索引、触发器或外键约束。INSERT时维护索引的开销会随插入行数线性增长，慢语句的大中间结果集会让这种开销翻倍。

内容的提问来源于stack exchange，提问作者Adrián Ventura Navarro