MySQL VALUES语句在INNER JOIN与LEFT JOIN中的性能差异咨询

核心差异：MySQL对两种JOIN的执行计划优化逻辑不同 #

1. INNER JOIN场景下VALUES子句的处理 #

使用INNER JOIN连接十万条行的VALUES子句时，MySQL会将这个VALUES集合视为无索引的临时表。INNER JOIN需要完成两张表的关联匹配，此时MySQL通常会采用嵌套循环连接（Nested Loop Join）：以其中一张表作为驱动表，逐行去另一张表做匹配。由于VALUES生成的临时表没有主键或索引，MySQL大概率会把业务表t1作为驱动表，然后逐行对十万条数据的临时表做全表扫描——十万次全表扫描叠加，性能自然暴跌。

VALUES生成的临时表默认不带任何索引，MySQL无法利用索引快速定位匹配项，只能暴力遍历，这是该场景下性能差的核心原因。

2. LEFT JOIN + WHERE IS NULL场景的优化 #

这种写法本质是反连接（Anti-Join），用于筛选t1中不存在于VALUES集合的行。MySQL对反连接有专门的优化逻辑：它不会像INNER JOIN那样执行嵌套循环匹配，而是会把VALUES集合的数据加载到哈希表中，或者基于EXISTS的反向逻辑处理。

具体来说，MySQL会先将十万条VALUES数据一次性加载到内存哈希表，然后遍历t1的每一行，去哈希表中快速查找是否存在匹配项——哈希表的查找是O(1)级别的，因此哪怕数据量十万级，整个过程也会非常快。这种场景下MySQL不会触发全表扫描的嵌套循环，而是采用更高效的哈希反连接算法，这就是两者性能差异的关键。

关于子查询vs VALUES语句的性能 #

不存在“子查询一定比VALUES快”的通用规则，核心取决于MySQL如何优化执行计划：

• 类似INNER JOIN的场景中，不管用VALUES还是SELECT ... UNION ALL这类子查询生成数据集，只要临时表没有索引，性能都会很差。但如果给子查询生成的临时表显式添加索引，性能会明显提升：

  SELECT * 
  FROM table t1 
  INNER JOIN (
      SELECT 1 AS col1, 2 AS col2 UNION ALL
      SELECT 2 AS col1, 3 AS col2
      -- 十万条UNION ALL语句
  ) AS sub_join
  ON t1.col1 = sub_join.col1
  -- 可以通过FORCE INDEX强制使用索引，或在子查询中定义索引
  

• 但VALUES语句生成的临时表无法直接添加索引，这是它在INNER JOIN场景下的劣势。如果要优化INNER JOIN+VALUES的性能，可以先将VALUES数据插入到临时表并创建索引，再执行JOIN：

  CREATE TEMPORARY TABLE temp_vals (col1 INT, col2 INT, PRIMARY KEY(col1));
  INSERT INTO temp_vals VALUES (1,2), (2,3), ...; -- 插入十万条数据
  SELECT * FROM table t1 INNER JOIN temp_vals ON t1.col1 = temp_vals.col1;
  

总结 #

两种JOIN场景的性能差异，本质是MySQL对INNER JOIN和反连接（LEFT JOIN+IS NULL）的优化策略不同：

• INNER JOIN搭配无索引临时表时，会触发低效的嵌套循环全表扫描
• 反连接场景会利用哈希表快速匹配，避免全表扫描
  VALUES语句本身并非性能瓶颈，问题在于它生成的临时表无索引且无法直接添加索引，导致INNER JOIN场景下性能拉胯。

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