MySQL查询：EXISTS与IN的性能疑问及大数据场景选择建议

场景说明 #

我有三张数据表：

• jcxx_device_check_log：64万条数据
• sys_depart：260条数据
• jcxx_device_info：840条数据

分别编写了使用EXISTS和IN的查询SQL，通过EXPLAIN format=Json查看执行计划后，发现：

• EXISTS语句的query_cost为453972.80
• IN语句的query_cost为2025375.58

我的猜测：EXISTS语句的结果集不会占用内存，仅返回true或false。
我的问题：该猜测是否正确？后续遇到大数据量场景时，应如何选择使用EXISTS或IN语句？

EXISTS查询SQL #

EXPLAIN
format = Json
SELECT state,check_time,note,device_id,COUNT(1) FROM jcxx_device_check_log jc
WHERE  exists (
SELECT jdi.id FROM sys_depart sd
INNER JOIN jcxx_device_info jdi on sd.id=jdi.sys_depart_id
where jc.device_id = jdi.id
) and check_time BETWEEN '2023-4-18 10:00:00' AND ' 2023-4-18 18:00:00'
GROUP BY device_id,state
ORDER BY device_id,state

IN查询SQL #

EXPLAIN
format = Json
SELECT state,check_time,device_id,COUNT(1) FROM jcxx_device_check_log jc
WHERE device_id in (
SELECT jdi.id FROM sys_depart sd
INNER JOIN jcxx_device_info jdi on jdi.sys_depart_id=sd.id
) and check_time BETWEEN '2023-4-18 10:00:00' AND ' 2023-4-18 18:00:00'
GROUP BY device_id,state
ORDER BY device_id,state;

问题解答 #

1. 关于EXISTS的猜测是否正确？ #

这个猜测基本正确，更精准的执行逻辑是：
EXISTS属于半连接查询，核心逻辑是对外部表（此处为jcxx_device_check_log）的每一行，执行子查询，只要子查询找到至少一条匹配记录，就返回true并立即停止子查询扫描，不会继续查找更多匹配行。它不会生成并存储子查询的完整结果集，仅判断是否存在匹配，因此内存占用远低于IN的常规执行模式。

2. 大数据量场景下如何选择EXISTS或IN？ #

优先选EXISTS的场景

• 子查询结果集很大时：EXISTS不需要生成并存储整个子查询结果，逐行匹配找到即停，避免大量内存/磁盘占用，执行成本更低。
• 子查询是相关子查询（依赖外部表列，比如示例中jc.device_id = jdi.id）：EXISTS的逐行匹配逻辑更高效，无需提前生成所有可能的匹配值。
• 子查询存在大量重复值时：IN需要先对结果集去重，而EXISTS不需要，额外的去重步骤会增加IN的成本。

可以选IN的场景

• 子查询结果集很小时：数据库会缓存子查询结果，外部表匹配时可快速查找，此时IN和EXISTS性能差异不大，甚至IN可能更快。
• 子查询是非相关子查询（不依赖外部表列）：此时IN的子查询仅执行一次，若生成的临时结果集很小，匹配效率很高。

额外注意点

• 索引优化：无论用EXISTS还是IN，确保关联列（如jcxx_device_check_log.device_id、jcxx_device_info.id）有合适的索引，这会大幅降低查询成本。
• 执行计划验证：不同数据库的优化器对IN和EXISTS的处理逻辑可能有差异，遇到性能问题时，一定要用EXPLAIN查看实际执行计划，不要仅凭经验判断。

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