为什么要尽量避免使用 IN 和 NOT IN 呢？

昨天晚上十一点多吧，我在公司楼下抽烟（别学哈），我们组小李跑过来一句：“哥，线上又卡了，SQL 看着也不复杂啊，就一个IN……”我当时就有点预感了，你们懂的，凡是“看着不复杂”的 SQL，最后都能把你折腾到怀疑人生。

事情是这样，业务要查一批用户的订单，前端一次能选几千个用户，后端同学图省事直接拼了个where user_id in (...)，然后还配了个“排除名单”，又加了个not in (select ...)。压测没事，真到晚上活动流量上来，DB 直接喘不上气，接口超时一片红。你说离谱不离谱。

先说IN吧，很多人以为它就是“多个等值条件”，实际上数据库优化器对它没你想的那么友好，尤其是列表很大那种。IN (1,2,3)这种小列表，优化器还能当成几次 index lookup；但一旦变成IN (几千个)，计划可能就变了：有的库会走范围扫描，有的会搞临时结构，有的干脆觉得“全表扫+过滤”更划算（反正它估算错了你也没脾气）。更坑的是，你一不小心把类型搞错，比如user_id是 bigint，你传进来一堆字符串"123"，数据库为了比较会做隐式转换，索引直接废掉，现场就变成“明明建了索引怎么还慢”。

还有个很现实的点：IN列表太大，SQL 文本会变得巨大。计划缓存命中率掉，解析开销上升，网络包也变大。你要是用 ORM/拼接，参数个数还可能撞上驱动或数据库的上限（有些库对 bind 参数数量是有限制的），然后就开始报一些你看不懂但又很“合理”的错。

再说NOT IN，这个真的是老坑王中王。它有两个大问题，一个是性能，一个是语义。

性能上，NOT IN (subquery)很容易让优化器很难做出漂亮的执行计划，尤其子查询结果集大、还没索引的时候，基本就是“做一堆比较然后过滤”，你看着像排除，数据库做起来像搬砖。

语义上更要命：只要子查询里出现了NULL，NOT IN可能会直接把你整懵。因为 SQL 三值逻辑嘛，x NOT IN (1,2,NULL)结果不是 true 也不是 false，而是 unknown，最后过滤出来可能一条都没有。线上我见过好几次了，排除名单表里某条数据user_id为空，结果主查询直接“查不出任何人”，然后业务同学还以为是我缓存没清……我当时真想把显示器抱起来砸了。

所以一般我会跟团队说，能不用IN/NOT IN就别硬用，尤其是大列表和子查询的那种。那用啥？看场景。

比如“排除子查询”这种，优先NOT EXISTS，它对NULL免疫，而且优化器更容易把它变成半连接/反连接（反正大概率比NOT IN好伺候）：

-- 别这样（子查询里有 NULL 会坑你）

SELECT o.*

FROM t_order o

WHERE o.user_id NOTIN (SELECT b.user_id FROM t_blacklist b);

-- 推荐这样

SELECT o.*

FROM t_order o

WHERENOTEXISTS (

SELECT1

FROM t_blacklist b

WHERE b.user_id = o.user_id

);

或者用LEFT JOIN ... IS NULL也行，语义也直观：

SELECT o.*

FROM t_order o

LEFT JOIN t_blacklist b ON b.user_id = o.user_id

WHERE b.user_id IS NULL;

那IN的大列表怎么办？别硬拼字符串，别一次塞几千个。比较稳的做法是“把这堆 id 变成一张表”，然后 join。你可以用临时表，也可以用派生表（小批量还凑合），核心思想就是：让数据库做它擅长的 join，而不是让它拿着一长串常量去猜计划。

我随手给你们一个 Java 里“临时表 + join”的写法（JDBC 版本，别嫌土，线上就靠它救过火）：

import java.sql.*;

import java.util.List;

publicclass OrderQuery {

  public List<Long> queryOrderIds(Connection conn, List<Long> userIds) throws Exception {

      // 1）建临时表（会话级别）

      try (Statement st = conn.createStatement()) {

          st.execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmp_user_id (user_id BIGINT PRIMARY KEY)");

          st.execute("TRUNCATE TABLE tmp_user_id");

      }

      // 2）批量塞 ids

      try (PreparedStatement ps = conn.prepareStatement("INSERT INTO tmp_user_id(user_id) VALUES (?)")) {

          for (Long id : userIds) {

              if (id == null) continue; // 顺手把 null 干掉，省得恶心

              ps.setLong(1, id);

              ps.addBatch();

          }

          ps.executeBatch();

      }

      // 3）join 查询（索引更可控，计划也稳定）

      String sql = """

          SELECT o.id

          FROM t_order o

          JOIN tmp_user_id t ON t.user_id = o.user_id

          WHERE o.status = ?

          """;

      try (PreparedStatement ps = conn.prepareStatement(sql)) {

          ps.setString(1, "PAID");

          try (ResultSet rs = ps.executeQuery()) {

              new java.util.ArrayList<Long>();

              var list = new java.util.ArrayList<Long>();

              while (rs.next()) list.add(rs.getLong(1));

              return list;

          }

      }

  }

}

这套方案的好处就是“稳定”，你传 100 个 id 和 5000 个 id，本质都是“临时表行数变了”，SQL 文本不变，缓存命中也舒服，优化器也更容易选对索引。你要是用 MyBatis 也能做类似的，别在 XML 里疯狂 foreach 拼IN，真的，后面维护的人会骂你（可能就是未来的你）。

当然也不是说IN一刀切全禁了哈，小列表、明确命中索引、类型一致、还能走准备语句的那种，用IN没问题。真正要躲的是那种“用户输入一堆 id 我就全塞进去”的写法，以及“NOT IN 子查询还可能出 NULL”的写法。

对了，昨天那单故障最后怎么收的？我跟小李在会议室里对着慢查询日志瞪了半小时，先把NOT IN换NOT EXISTS，再把大IN改成临时表 join，接口耗时从十几秒掉到几百毫秒。小李当场说要请我喝奶茶，我说行啊但别点太甜我最近…哎算了不聊这个了，我先去回个消息，群里又有人@我说他in里放了 2 万个 id 还问为啥慢，这人也是绝了…