带IN子句的MariaDB查询返回空集却无限运行，求分析原因

核心问题：子查询被识别为相关子查询（DEPENDENT SUBQUERY） #

从执行计划能看到，子查询的select_type是DEPENDENT SUBQUERY，这意味着MariaDB没有将其作为独立子查询先执行完得到结果，而是对主表cv_attribute的每一行记录，都重新执行一次子查询。

主表有29.6万条记录，就会触发29.6万次全表扫描+分组计算的子查询操作——哪怕单独执行一次子查询只需要11秒，29.6万次的总耗时也会达到天文数字，这就是查询无限运行的根本原因。

为什么会出现这种误判？虽然你的子查询没有引用主表的任何字段，但MariaDB的优化器没有正确识别它是独立子查询，反而将其处理为依赖主表每一行的相关子查询，导致执行逻辑完全偏离预期。

执行计划细节佐证 #

• 主查询和子查询的type都是ALL，说明全表扫描，没有利用到MD5字段已有的索引；
• 主查询的Extra显示Using temporary，再加上DISTINCT会额外消耗内存和CPU来做去重操作，进一步拖慢执行速度。

方法1：强制子查询先执行（用临时表包装） #

将子查询包装成独立的临时表，让优化器先计算出子查询结果（空集），再和主表匹配：

SELECT DISTINCT ca.* 
FROM cv_attribute ca
WHERE ca.`MD5` IN (
  SELECT sub.`MD5` 
  FROM (SELECT `MD5` FROM cv_attribute GROUP BY `MD5` HAVING COUNT(*) > 1) AS sub
);

或者更直接地先把子查询结果存入临时表，再关联查询：

CREATE TEMPORARY TABLE temp_dup_md5 AS 
SELECT `MD5` FROM cv_attribute GROUP BY `MD5` HAVING COUNT(*) > 1;

SELECT DISTINCT ca.* 
FROM cv_attribute ca
JOIN temp_dup_md5 t ON ca.`MD5` = t.`MD5`;

DROP TEMPORARY TABLE temp_dup_md5;

方法2：优化子查询的索引利用 #

虽然子查询单独执行耗时11秒，但可以通过优化让它更快：

• 强制使用MD5字段的索引，尝试修改子查询为：SELECT MD5FROM cv_attribute FORCE INDEX(MD5) GROUP BYMD5 HAVING COUNT(*) > 1;（注意替换MD5为该字段实际的索引名称）；
• 如果MD5存在大量NULL值，考虑用COUNT(MD5)代替COUNT(*)，因为索引通常不会包含NULL值（除非索引允许NULL），这样可能让优化器选择索引扫描而非全表扫描。

方法3：去掉不必要的DISTINCT #

如果cv_attribute表中没有所有字段完全重复的记录，DISTINCT是多余的，可以直接去掉，减少临时表的创建开销。

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