最近在群里看到个哥们写了句 SQL：

SELECT * FROM user WHERE id IN (SELECT id FROM order WHERE amount > 100);

然后还一脸无辜地问为啥慢成狗。 我当时差点没喷出来，这都 2025 年了还在用 IN 查？兄弟，咱可都是干生产的，不是在写毕业论文。

一、IN 的坑你真的懂吗？

IN 看起来挺方便，逻辑也清晰，可你要知道它在 MySQL 里的执行方式其实坑挺多的。 如果子查询结果集很大，比如几万甚至几十万行，那 IN 直接就能让你的 SQL 从“轻盈飘逸”变成“生死未卜”。

MySQL 的优化器在处理 IN 时，会先把子查询结果放到一个临时表，然后在外层表里逐行对比。这个过程相当于——

你先查出来一个大名单，然后挨个去问“你是不是在名单上”。

想象一下你公司 HR 拿着 5 万人的名单挨个查，啥感觉？CPU 都得冒烟。

更可怕的是，NOT IN 比 IN 还要命。 因为它会导致优化器无法使用索引。举个例子：

SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM order);

这里 user_id 一旦有 NULL，整个查询结果都不确定，MySQL 直接弃疗，扫描全表。

二、那该怎么写？

聪明的老程序员都知道，这种场景下换成 EXISTS 或 JOIN 基本就能起飞。

✅ 用 EXISTS 替代 IN

SELECT * FROM user u

WHERE EXISTS (

  SELECT 1 FROM order o

  WHERE o.user_id = u.id AND o.amount > 100

);

EXISTS 一旦匹配到就返回 true，不会像 IN 一样先把结果全捞出来放内存里。 这在大数据量下的性能差距，基本能达到一个数量级。

✅ 用 LEFT JOIN + IS NULL 替代 NOT IN

SELECT u.*

FROM user u

LEFT JOIN order o ON u.id = o.user_id

WHERE o.user_id IS NULL;

这写法不仅能规避 NULL 的坑，还能触发索引优化。 别问我咋知道的，我当年在一个 3 亿行的表上被 NOT IN 干崩过。

三、Java 实战里的坑

有些同学说：“我们又不是直接写 SQL，用 MyBatis 的 <foreach> 不就行了？” 确实，IN 在动态 SQL 场景里挺常见，比如：

@Select("<script>" +

    "SELECT * FROM user WHERE id IN " +

    "<foreach collection='ids' item='id' open='(' separator=',' close=')'>#{id}</foreach>" +

    "</script>")

List<User> findByIds(@Param("ids") List<Long> ids);

但问题是——如果 ids 里有几千个甚至上万个值，数据库直接给你一记“语法爆头”。 MySQL 默认支持的 SQL 长度有限（一般是 4MB），而且优化器遇到超长 IN 列表会直接放弃索引。

正确做法是分页执行或临时表方案：

// 分批执行

List<List<Long>> partitions = Lists.partition(ids, 1000);

List<User> result = new ArrayList<>();

for (List<Long> part : partitions) {

    result.addAll(userMapper.findByIds(part));

}

或者干脆把 ids 批量插入临时表，再做 JOIN：

SELECT u.* 

FROM user u 

JOIN temp_ids t ON u.id = t.id;

四、说点底层的事儿

其实从执行计划看，IN 本质是多值匹配，优化器会决定是用索引查还是全表扫。 如果子查询结果大，它一般就会建个临时表（tmp table），而临时表是存储在磁盘上的，这就慢得离谱。

EXISTS 则不同，它是半连接（semi-join），MySQL 会在匹配成功后立刻停止扫描，这对性能极其友好。

你可以通过 EXPLAIN 对比下两种写法的差距，比如：

EXPLAIN SELECT * FROMuserWHEREidIN (SELECT user_id FROMorder);

EXPLAINSELECT * FROMuserWHEREEXISTS (SELECT1FROMorderWHERE order.user_id = user.id);

大多数情况下，第二条 SQL 会使用索引并走 “Semi Join” 策略，而第一条会多出临时表或全表扫描。

五、总结一句话

IN 用来查少量静态值还行，比如：

SELECT * FROM user WHERE id IN (1,2,3);

但一旦涉及子查询或大批量 ID 集合，就赶紧换成 EXISTS 或 JOIN。NOT IN 更别碰，性能炸裂还容易出错。

—— 如果你团队里还有人写 IN + 子查询，真的建议他赶紧改。 这不是写法问题，是生产事故的种子。

-END-

原文来源：https://mp.weixin.qq.com/s/8MBt_MxywSB3v3caARIeHg