MySQL中WHERE IN与INNER JOIN查询性能差异原因探究

执行环境 #

• companies表约有20万条记录
• name列约有19.5万个唯一值（两条查询均返回约1500条重复结果）
• name列已创建nameindex索引

WHERE IN 查询（耗时0.31142875秒） #

SELECT *
FROM `companies`
WHERE `name` IN (
    SELECT `name`
    FROM `companies`
    GROUP BY `name`
    HAVING COUNT(`name`) > 1
);

INNER JOIN 查询（耗时0.07034850秒） #

SELECT *
FROM `companies`
INNER JOIN (
    SELECT `name`
    FROM `companies`
    GROUP BY `name`
    HAVING COUNT(`name`) > 1
) AS `duplicate_names` ON `companies`.`name` = `duplicate_names`.`name`;  

注：两条查询的子查询逻辑完全一致，为何此特定场景下第二条JOIN查询速度明显更快？

WHERE 查询的EXPLAIN结果 #

JOIN 查询的EXPLAIN结果 #

原因分析 #

WHERE IN 查询的性能瓶颈 #

主查询（id=1）对companies表执行了全表扫描（type=ALL），完全没有利用nameindex索引。数据库需要遍历全部19万+条记录，逐一判断每条记录的name是否存在于子查询返回的重复列表中。这种逐行匹配的方式在数据量较大时开销极高，是查询变慢的核心原因。

INNER JOIN 查询的优化逻辑 #

JOIN查询的执行路径更高效：

1. 派生表（id=2，DERIVED）和WHERE IN的子查询一样，通过索引快速获取重复的name列表，这部分效率一致。
2. 主查询阶段，数据库将companies表与派生表duplicate_names关联时，利用了nameindex索引（type=ref）。通过索引可以直接定位到与派生表中name匹配的记录，每条匹配仅需扫描约1条记录（rows=1），彻底避免了全表扫描，直接过滤出目标数据，因此执行速度大幅提升。

简单来说：两种写法的子查询效率相同，但主查询的执行路径差异巨大——WHERE IN触发全表扫描，而INNER JOIN利用索引做精准匹配，这就是两者性能差距的关键。

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